13.3 Persistent Connections and Performance

After giving you a slight insight into PHP's internals and after you have seen what processes are running, it's time to have a look at what effects on speed persistent database connections have.

The next example shows a script that connects and disconnects to the database 25,000 times. In addition, the average time needed to connect to a database once is computed:

 <?php         $number = 25000;                        # Number of connections         $begin = time();                        # Starting time         for     ($i = 0; $i < $number; $i++)         {                 $dbh = pg_pconnect("host=localhost                         user=postgres dbname=phpbook");                 if      (!$dbh)                 {                         echo "error while connecting.<br>\n";                 }                 pg_close($dbh);         }         $end = time();                          # Ending time         $diff = $end - $begin;                  # Overall time         $tpconn = $diff / $number;              # Time per connection         # displaying result         echo "Begin: $begin<br>\n";         echo "End: $end<br>\n";         echo "Overall time: $diff<br>\n";         printf("Time per connection: %9.8f<br>\n", $tpconn); ?> 

At the beginning of the script the time before connecting to the database is computed. The time is returned in seconds since January 1, 1970. In the next step the connections to the database are established. In this scenario, persistent connections are established.

After that, the time is retrieved again and computations are performed. Finally, the values are displayed on the screen:

 Begin: 1008540275 End: 1008540276 Overall time: 1 Time per connection: 0.00004000 

As you can see, 25,000 connections can be established in about one second if a pool of connections is already in memory. The time needed for establishing one connection is extremely low because PHP does not have to ask PostgreSQL for user authentication.

With nonpersistent connections, the situation is different. Every time a connection has to be established, a backend process has to be started and PostgreSQL has to check for things like database, user, password, and so forth. In other words, it is a lot of work to establish a connection to the database. Depending on what kind of authentication you are using (trust, password, Kerberos, and so forth), the time needed for authentication will vary significantly.

In the next scenario we try to connect to the same database using the same script. This time, nonpersistent connections are used. To reduce the time of the script, we have used 250 connections instead of 25,000 connections. The next listing shows what comes out when running this scenario:

 Begin: 1008541909 End: 1008541917 Overall time: 8 Time per connection: 0.03200000 

It takes 8 seconds to establish 250 connections, which means that it takes .03 seconds to establish one connection. Keep in mind that the connection is established to the local machine where all users are trusted users.

Let's modify pg_hba.conf and run the script using the official IP of the machine. Here is a modified version of pg_hba.conf:

 local      all                                             trust host       all         127.0.0.1        255.255.255.255    trust hostssl    phpbook     212.186.25.254   255.255.255.255    crypt 

To activate the changes, you can restart PostgreSQL (don't forget to activate SSL using the postmaster's -l flag as you saw in Chapter 10, "PostgreSQL Administration"). To understand the pg_hba.conf file, take a look at Chapter 10 again.

In addition to changing pg_hba.conf, a new user can be created:

 phpbook=# CREATE USER testuser WITH PASSWORD 'mypasswd'; CREATE USER 

In the next step you can use the script in the next listing to connect to the database:

 <?php         $number = 250;                          # Number of connections         $begin = time();                        # Starting time         for     ($i = 0; $i < $number; $i++)         {                 $dbh = pg_connect("host=212.186.25.254 port=5432                         user=testuser password=mypasswd dbname=phpbook");                 if      (!$dbh)                 {                         echo "error while connecting.<br>\n";                 }                 pg_close($dbh);         }         $end = time();                          # Ending time         $diff = $end - $begin;                  # Overall time         $tpconn = $diff / $number;              # Time per connection         # displaying result         echo "Begin: $begin<br>\n";         echo "End: $end<br>\n";         echo "Overall time: $diff<br>\n";         printf("Time per connection: %9.8f<br>\n", $tpconn); ?> 

This time the authentication process takes much longer because you have connected to the database using SSL:

 Begin: 1008544555 End: 1008544581 Overall time: 26 Time per connection: 0.10400000 

In this scenario, working with persistent database connections is about 2600 times faster than working with nonpersistent SSL connections. Of course, persistent connections can hardly be compared with nonpersistent SSL connections, but it shows how much performance can be gained by using persistent database connections. When working with databases for building dynamic Web sites, the time needed to connect to the database is often underestimated. However, authentication is a crucial point and should be taken into consideration when planning high- performance systems.