Testing Persistent Connections


Before you decide to use persistent connections in a real-world application, you should perform extensive testing. This section explains what you have to take care of and how persistent connections work internally.

Open Connections and Backend Processes

In most cases, theoretical explanations will satisfy the demands of people thinking theoretically. Sometimes, however, it is much easier to show how things work by means of some simple examples. This section is dedicated to all those of you who are looking for some small practical examples of how persistent database connections can be used and how the use of this kind of connection affects the way your system behaves.

Normally, backend processes are terminated automatically at the end of a PHP script (as long as the connection has not been closed manually). Closing a connection means that all memory allocated by the backend process is freed and the garbage will be left on your machine. In case of persistent connections, backend processes stay in memory until a connection timeout occurs. Take a look at the following script:

 <?php         $number=0;         for($number=1; $number<=5; $number++)         {                 $db[$number]=pg_pconnect("dbname=mydb");                 if (!$db[$number])                 {                         echo("connection number $number cannot be established<br>\ n");                 }                 else                 {                         echo("connection number $number established<br>\ n");                         echo("connection handle: ".bin2hex($db[$number])."<br><br>\ n");                 }         } ?> 

Our script does nothing else than connect to the same database 5 times. Depending on whether the connection to the database has been established, a message is displayed on the screen. If a connection has been established, we display the number of the connection and a hexadecimal representation of the connection handle. We included this to see whether the connection handles are always the same.

Let's execute the script in our favorite Web browser and analyze the result:

 connection number 1 established connection handle: 5265736f75726365206964202331 connection number 2 established connection handle: 5265736f75726365206964202332 connection number 3 established connection handle: 5265736f75726365206964202333 connection number 4 established connection handle: 5265736f75726365206964202334 connection number 5 established connection handle: 5265736f75726365206964202335 

The connection has been established successfully every time the loop is processed . We can also see that the connection handles differ for every connection established. This is not surprising, because we expect a connection handle to be unique.

Now let's see what is going on inside the system:

 [hs@athlon persistent]$ p  s   ax  grep post  2992 pts/2    S      0:00 /usr/bin/postmaster -D /var/lib/pgsql/data -i  3000 pts/2    S      0:00 postgres: hs mydb [local] idle  3098 pts/1    S      0:00 grep post 

We use ps ax (check the man pages of your UNIX system if you are not a user of RedHat Linux) and grep to retrieve all processes related to PostgreSQL. Process number 2992 is the supervisor daemon, which handles the backend processes. Process number 3000 handles an open connection. Only one backend process is open. If we would connect to five different databases, we would also see five backend processes in the process table.

We extracted the list of processes shown after running the PHP script in the browser, which shows that backend processes stay in memory after the execution of the PHP script has been finished.

In the next step, we set the maximum number of connections allowed on the system to 3 . This has to be done in postgresql.conf :

 max_connections = 3 

Let's see whether the previous script still works if we want to open five connections:

 connection number 1 established connection handle: 5265736f75726365206964202331 connection number 2 established connection handle: 5265736f75726365206964202332 connection number 3 established connection handle: 5265736f75726365206964202333 connection number 4 established connection handle: 5265736f75726365206964202334 connection number 5 established connection handle: 5265736f75726365206964202335 

All five pg_pconnect commands have been executed successfully again, which means that restricting the number of connections allowed does not affect the number of connections you can open with the same parameters.

The fact that no additional backend processes are started is a significant advantage of PHP, because it might save a lot of memory (this also works with non-persistent connections).

Now that we have tested the script with the following pg_pconnect command, one backend process is in memory:

 $db[$number]=pg_pconnect("dbname=mydb"); 

No matter how often we start the script, there will only be only one active backend process. Now let's change the connect string to this command:

 $db[$number]=pg_pconnect("dbname=mydb user=hs host=localhost"); 

We execute the program again and see whether something has changed in the process table:

 [hs@athlon persistent]$  ps ax  grep post  4257 pts/2    S      0:00 su postgres  4300 pts/2    S      0:00 /usr/bin/postmaster -D /var/lib/pgsql/data/ -i -B 400  4302 pts/2    S      0:00 postgres: hs mydb [local] idle  4318 pts/2    S      0:00 postgres: hs mydb 127.0.0.1 idle 

Now two different backend processes are in memory. Although both connections have been established to the same database for the same user on the same host, the software is not able to figure out whether the two connections are identical, because only the parameters of the connect string are compared. Both connect strings lead to the same result, but they are not identical, which causes PostgreSQL to create an additional backend process.

Persistent Connections and Transactions

One important ”and often misunderstood ”point is that persistent means that database connections are available for an entire session. Some people think that one connect command is enough for making a database connection across multiple PHP files. This is wrong. Because HTTP is not a connection-oriented protocol, database connections are not open during the whole session of a user. They have to be reestablished by every script.

Take a look at this script:

 <?php         echo("<html><body>\ n");         $db=pg_pconnect("dbname=mydb user=hs host=localhost");         if (!$db)         {                 echo("connection cannot be established<br>\ n");         }         else         {                 echo("connection established<br>\ n");         }         # creating an empty form         echo ('<A HREF="transaction2.php/">next screen</A><br>');         echo ('</body></html>'); ?> 

We establish a connection to the database and display a link on the screen. The next script checks whether the connection is still open:

 <?php         if      ($db)         {                 echo ('database handle still valid<br>');         }         else         {                 echo ('database handle does not exist anymore<br>');         } ?> 

Although only one backend process is in memory during the whole process, the connection handle has to be re-created in the second PHP file.

After a few words about the lifetime of a connection handle, you will have a closer look at persistent database connections and transactions. In general, persistent connections have to be handled like ordinary connections. When dealing with transactions, however, some things have to be taken into consideration.

To show you what you have to take care of, we create a small table called access :

 CREATE TABLE access(id serial, script text, accesstime timestamp); 

Take a look at this script:

 <?php         echo("<html><body>\ n");         $db=pg_pconnect("dbname=mydb user=hs host=localhost");         if (!$db)         {                 echo("connection cannot be established<br>\ n");         }         else         {                 echo("connection established<br>\ n");         }         $status=pg_exec($db, "BEGIN WORK");         $status=pg_exec($db, "INSERT INTO access (script, accesstime) VALUES ('1', now())");         # creating an empty form         echo ('<A HREF="transaction2.php/">next screen</A><br>');         echo ('</body></html>'); ?> 

First, we connect to the database and start a transaction explicitly by using the BEGIN WORK command. In the next step, we insert a record into the table. script contains an identification of our script, and accesstime contains the time the database transaction takes place. Our program terminates without terminating the transaction explicitly. Because the backend process will stay in memory, the transaction won't be committed automatically ”the open connection (including the open transaction) stays in memory.

After the first site has been displayed, we query the table:

 mydb=#  SELECT * FROM access;  id  script  accesstime ----+--------+------------ (0 rows) 

No records can be found in table access because the transaction has not been committed.

If we click the link we have displayed on the screen, we get to the next script:

 <?php         echo("<html><body>\ n");         $db=pg_pconnect("dbname=mydb user=hs host=localhost");         if (!$db)         {                 echo("connection cannot be established<br>\ n");         }         else         {                 echo("connection established<br>\ n");         }         $status=pg_exec($db, "INSERT INTO access (script, accesstime) VALUES ('2', now())");         $status=pg_exec($db, "COMMIT");         echo ('</body></html>'); ?> 

First, we connect to the database again. This time, we do not start a transaction explicitly, but start with inserting a value into the table. We commit the transaction. Here is the content of table access :

 mydb=#  SELECT * FROM access;  id  script        accesstime ----+--------+------------------------   1  1       2001-07-11 20:17:17+02   2  2       2001-07-11 20:17:17+02 (2 rows) 

Two records can be found in the table. The first record is also inserted into the database, because PHP continues the transaction in the second script. Unfortunately, we have no guarantee that this is going to happen. If we were working on a highly accessed database, the connection handle generated by the second script might not be assigned to the same backend process, so the INSERT command in the first script would be neglected. Luckily, the connection handle in the second script has been assigned to the same backend process and no data has been lost.

As you can see, dealing with open connections across multiple PHP files is an extremely dangerous endeavor and it should be avoided. Otherwise , the behavior of a program will be stochastic.

Ordinary connections are much safer when dealing with transactions. If we substitute the persistent connection generated with the following:

 $db=pg_pconnect("dbname=mydb user=hs host=localhost"); 

by an ordinary connection, which can be established to the database by using the following:

 $db=pg_connect("dbname=mydb user=hs host=localhost"); 

the situation will be slightly different. Because the connection cannot be continued after terminating the first script, only one value will be inserted into the database.

Here is the content of the table:

 mydb=#  SELECT * FROM access;  id  script        accesstime ----+--------+------------------------   1  1       2001-07-11 20:17:17+02   2  2       2001-07-11 20:17:17+02   4  2       2001-07-11 20:18:39+02 (3 rows) 

Record number four has been inserted into the database, but we cannot find record number three in the database. The third record has been silently omitted, but the value of the sequence has increased; this is necessary, because otherwise some values might occur more than once in concurrent transactions.



PostgreSQL Developer's Handbook2001
PostgreSQL Developer's Handbook2001
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 125

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net