Chapter 18. Persistent Database Connections with PHP

Persistent database connections are a core feature of PHP, and they can be used to speed up your applications significantly.

Before we get to some performance tests and an example, let's look at what persistent database connections are and how they can be used.

The difference between a persistent and a non-persistent database connection is, that persistent connections are not closed at the end of a script. If you want to establish a persistent connection to your database, PHP checks whether an identical connection exists. Identical means that the name of the host, the username, and the password have to be the same. If no suitable connection is available, a new connection is established.

The advantage of this algorithm is that a lot of connection overhead can be saved by reducing the amount of authentication processes, and therefore the load on your database might be reduced significantly.

Many people who are not familiar with persistent connections think that persistent database connections provide additional functions for user management, session management, or transaction optimization. This is not true. Persistent database connections do not offer additional features. However, a lot of overhead is saved.

To understand persistent database connections, you need fundamental knowledge about how Web servers work. In general, a Web server can interact with PHP in three ways:

• PHP as a CGI wrapper. In this case, a PHP interpreter is started for every page that has to be processed . Therefore, persistent connections are not supported, because the instance of the PHP interpreter is destroyed after the page is processed. pg_pconnect can be used, but there is no advantage over using pg_connect .

• PHP as a module. In most cases, PHP is used as a module in a multiprocess Web server such as Apache. A parent process controls a set of child processes doing the work. Every time a new request has to be processed, the parent process selects a child process and tells the client process to handle the request. If PHP is used as a module, persistent database connections save a lot of authentication overhead, because every child has to connect to the database only the first time a request is handled. If a second request has to be processed, the child process reuses the same connection it established for processing the first request.

• PHP as a plugin for a multithreaded Web server . At the moment, support for ISAPI, WSAPI, and NSAPI is provided by PHP. The behavior is the same as described earlier for the multi-process model.

As mentioned earlier, in most cases PHP is used as an Apache module. This section assumes that PHP is treated as a module.

It is interesting that database connections ”whether they are persistent or not ”are handled by the same function in PHP. If you look at the file pgsql.c in the source tree of PHP 4.04, you can see this in action:

 /* { { {  proto int pg_connect([string connection_string]  [string host, string port [, string options [, string tty,]] string database)    Open a PostgreSQL connection */ PHP_FUNCTION(pg_connect) {         php_pgsql_do_connect(INTERNAL_FUNCTION_PARAM_PASSTHRU,0); } /* } } }  */ /* { { {  proto int pg_pconnect([string connection_string]  [string host, string port [, string options [, string tty,]] string database)    Open a persistent PostgreSQL connection */ PHP_FUNCTION(pg_pconnect) {         php_pgsql_do_connect(INTERNAL_FUNCTION_PARAM_PASSTHRU,1); } 

Persistent and non-persistent connections are handled by the php_pgsql_do_connect function. We did not include this function here because it is already quite long and rather difficult to understand. We included the PHP function here, because some of you might find it interesting to see how connections are treated by PHP internally.