NYCPHP Meetup

[Kayra Otaner]

Hi,

For one of my domains I was doing search on 'Persistent Database Connections' with Php and MySQL.
There are several aspects of persistent connection, you can't just directly use mysql_pconnect to
have persistent connection. Even in php.net and related sites don't give enough explanation on
this topic. I guess they keep this kind of performance related information to themselves. Here are
my notes on this topic :

1- You need to have Apache (or web server) waiting on the memory if you want to have persistent
connection. Otherwise Php and MySQL may loses traces of connection ID's so MySQL opens new
connection each time. To have this you need to modfiy 'Keep Alive', 'KeepAliveTimeOut' and
'MaxKeepALiveReqeusts' line in httpd.conf (also if you have really high loaded site like mine you
may need to make changes on Apache header files and recompile it) Latest Apache sources by default
enables KeepAlive to ON, but older versions were shipped with OFF value, so this is the first
thing you should check to enable Persistent database connections.

2- You are supposed to use 'mysqlpconnect' on every Php script, 'mysqlconnect' may not be able to
find same connection ID that was created by mysql_pconnect 

3- To check whether you have peristent conn or not your 'show processlist' request get should
produce an output like this one (sleep lines indicate that that conenction is waiting for request
and time is waiting time in seconds) :

mysql> show processlist;
+-------+---------+-----------+---------+---------+------+-------+------------------+
| Id    | User    | Host      | db      | Command | Time | State | Info             |
+-------+---------+-----------+---------+---------+------+-------+------------------+
| 61303 | central | localhost | central | Sleep   | 122  |       | NULL             |
| 61304 | central | localhost | central | Sleep   | 2392 |       | NULL             |
| 61305 | central | localhost | central | Sleep   | 146  |       | NULL             |
| 61306 | central | localhost | central | Sleep   | 2163 |       | NULL             |
| 61307 | central | localhost | central | Sleep   | 170  |       | NULL             |
| 61309 | central | localhost | central | Sleep   | 89   |       | NULL             |
| 61310 | central | localhost | central | Sleep   | 159  |       | NULL             |
| 61311 | central | localhost | central | Sleep   | 89   |       | NULL             |
| 61312 | central | localhost | central | Sleep   | 121  |       | NULL             |
| 61327 | central | localhost | central | Sleep   | 3    |       | NULL             |
| 61508 | cmt     | localhost | cmt     | Sleep   | 669  |       | NULL             |
| 61509 | cmt     | localhost | cmt     | Sleep   | 659  |       | NULL             |
| 61580 | root    | localhost | NULL    | Query   | 0    | NULL  | show processlist |
+-------+---------+-----------+---------+---------+------+-------+------------------+
13 rows in set (0.00 sec)


Best 

Kayra Otaner


__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com