A database is a fundamental component of many web applications. But connecting to it is an overhead that affects traditional application architectures such spil CGI and Verlichtingstoestel. Te this article, wij vertoning how wij can manage database connections more efficiently with Apache Two’s threaded MPMs, and thus achieve improvements te spectacle and scalability.
A more efficient Verlichtingstoestel
Many web-based applications generate dynamic content te entire or ter part from a backend server. Where the backend is designed spil connection-oriented, there is a mismatch with the request-oriented HTTP protocol. This can lightly lead to inefficiency ter applications. The most common case is that of an SQL backend, where there is always an overhead to creating a connection and logging ter. This is exemplified by the environment commonly known spil Lantaarn (Linux, Apache, MySQL, [Perl|PHP|Python]). When the connection is TCP/IP overheen a network, that is an extra overhead.
Note by anonymous, Sat Jul 17 11:27:01 2004
This paragraph needs reordering. Describe the application very first, then the mismatch with HTTP.
Note by anonymous, Fri Feb 9 05:00:22 2007
i want to see how is working
Note by anonymous, Mon Sep Ten Legal:25:42 2007
This seems rather elaborate
The ordinary CGI case
A CGI script services a single request. So the baseline for CGI to access a database is to open a connection, run any necessary queries, close the connection, and comeback content to the Client.
This is fine for a low-traffic webpagina, but grows inefficient spil the klapper rate rises above a few ems vanaf minute. So spil traffic rises, an alternative specimen is required. For CGI, wij can use an alternative implementation such spil FastCGI. But the most widely-used architecture is Lantaarn.
The classic Verlichtingstoestel case
The classic solution to this, spil provided for many years by application development environments such spil mod_perl and PHP, is for the Apache server process to hold a database connection open, saving the overhead of opening and closing a connection for every request. With Apache 1.x, this is essentially the best you can do, and is the usual way of working.
However, this solution has its own problems. Albeit it substantially reduces the per-hit overhead, it introduces another: namely that of holding a large number of backend connections open. This te itself puts a blast both on the webserver itself and the backend and thresholds the number of users that can be concurrently serviced. This doesn’t just affect database-driven traffic: requests for static webpages also have to be served by an Apache process that is keeping an open connection to the backend.
Taking advantage of Apache Two
With Apache Two and threaded MPMs, a broader range of altogether more efficient and scalable options present themselves. Embarking from what wij already have, wij can list our options:
- Classic CGI: one connection vanaf request.
- Classic Verlichtingstoestel: one persistent connection vanaf thread.
- Alternative Verlichtingstoestel: one persistent connection vanaf process, with a mechanism for a thread to take and lock it.
- Connection Pooling: more than one connection vanaf process, but fewer than one vanaf thread, with a mechanism for a thread to take and lock a connection from the pool.
- Dynamic Connection Pooling: a variable-size connection pool, that will grow or shrink according to actual database traffic levels.
Looking at thesis te order, wij can see the advantages and drawbacks of each option. Wij have already dealt with the very first two.
The third dispenses with the Verlichtingstoestel overhead at the cost of preventing parallel accesses to the backend. It may be an efficient solution te some cases, but clearly presents its own problems with servicing mededinger requests.
The fourth and fifth present an optimal solution whose scalability is limited only by the available hardware and operating system. The number of backend connections to threads should reflect the proportion of the total traffic that requires the backend. So, te plain terms, if one te every five requests to the webserver requires the database, then a pool might have one connection vanaf five threads. Just spil Apache itself maintains a dynamic pool of threads to service incoming HTTP connections, so the optimal solution to managing backend connections is a dynamic pool whose size is driven by actual request rather than best-guess configuration.
Note by anonymous, Fri Dec Two Ten:05:13 2005
So, do I have to do to use this fresh feature from Perl running under FastCGI?
Implementation of Connection Pooling
Albeit the case for connection pooling is clear, implementation is a latest development. Its conception wasgoed around the time of ApacheCon ter November 2003, when I floated the idea ter a “Birds of a Feather” session entitled “the module developers wishlist”. Having discussed it and found I wasgoed not alone ter wanting it, I procceed to implement, ter addition to the Webpagina Valet connection pooling module, open-source PostgreSQL and MySQL connection pooling modules. Paul Querna has implemented a similar module for connecting to a database with libdbi.
The PostgreSQL and MySQL implementations are available at apache.webthing.com. The libdbi implementation is at outoforder.cc.
Note by anonymous, Sat Jul 17 11:Legitimate:03 2004
Maybe add references here?
Note by niq, Wed Jul 21 11:26:34 2004
Done. But I still need to document the modules:-)
Note by anonymous, Zon Jul 23 16:55:53 2006
Thank you for posting something like this. This will help for our understanding of connection pooling. IT would be much better if it shows a more practical example for implementing connection pooling for web aaplication.
Note by anonymous, Tue Mar 27 Nineteen:02:22 2007
[FEATURE EXPLANATION] , [httpd.conf EXAMPLE] , [Virtual host config EXAMPLE] , [pagina.PHP/pagina.Perl EXAMPLES] , Zah. maybe it’s just mij.
Dynamic Resource Pools: apr_reslist
Implementation of connection pooling te practice is straightforward. An API for maintaining a dynamic respource pool is already provided by the apr_reslist module. All wij need to do is provide a constructor and destructor for our resource, and functions to retrieve an example of our resource from the pool, and release it back to the pool. The surplus is managed by apr_reslist .
To illustrate this, here are the core functions of the MySQL implementation mod_mysql_pool. Very first, a constructor and destructor. Wij never call thesis directly, apr_reslist calls them when required.
Now, wij need a function to register our dynamic resource list. This is called te a post_config hook, and calls apr_reslist_create to set up a pool of MySQL connections:
Ultimately, wij provide functions other modules can use to acquire a connection from the pool and come back it. Wij’ll just vertoning one function here: mysql_acquire is for the commonest case, where a connection is needed for the duration of a request. Note that:
- By registering a cleanup on the request pool, mysql_acquire assures the connection is returned to the pool at the end of the request.
- By storing the connection treat on our own request_config, wij ensure that the same connection is returned every time sql_acquire is called within a single request.
The surplus is housekeeping. The total code for this module, spil well spil a PostgreSQL companion module, is available at WebÞ,ing.
Note by anonymous, Fri Jul 16 16:30:23 2004
Please describe the structs!
Note by anonymous, Wed Oct 13 21:12:37 2004
Another Apache module that uses Connection Pooling is mod_dbi_pool: , http://www.outoforder.cc/projects/apache/mod_dbi_pool/ ,
Note by anonymous, Tue Jul Five 03:56:37 2005
y Como bajo ese modulo?
Note by anonymous, Thu Jun 8 02:51:27 2006
, You also see following web sites,so You are able to understand all you want to know!! , , The Database Pooling Modules also use apr_reslist: , http://apache.webthing.com/mod_pg_pool.c , http://apache.webthing.com/mod_mysql_pool.c , and, , http://cvs.sourceforge.televisiekanaal/viewcvs.py/mod-auth/mod_dbi_pool/src/mod_dbi_pool.c?rev=1.8&,view=wagen ,
Note by anonymous, Wed Jun 21 23:11:11 2006
can this be used with PHP
Note by anonymous, Thu May 15 06:17:58 2008
Proprietor niq, Last Updated: Wed Aug 06 00:11:13 2008.