When to use MySQL persistent connections.

In PHP a persistent connection is a constant connection to the database. It will not be deleted or closed after a php script exits. This provides a reduction in connection time bypassing the time needed to create a new connection. In timed tests, the persistent connection is obviously much faster than the regular connection after the persistent connection is made. With this in mind, we probable want to switch all of our MySQL connections to persistent just about now right?

According to php.net, a persistent connection does not mean one connection for one database resource. It actually means one connection for each child instant of Apache (I check the MS IIS6.0 server and I think that it also mean a persistent connection for each IIS worker process.). So if a web server has 255 instances and it hosts 100 applications that require persistent connections, then the MySQL server would eventually need to serve 255,000 connections. As you can see, this is a very large number.

Tim the CTO of Digital Content Solutions has posed a solution for a high volume high traffic website setup (http://www-css.fnal.gov/dsg/external/freeware/mysqlTuning.html). This would be a good example of where persistent connection would be good. Tim mentioned that there were up to 3000 users that log in about 5-7 hours a day. Since the max client for apache is 255, only a maximum of 255 persistent connections is ever needed from mySQL.

Time Test

Brinkster Server

System Windows NT ORF-SINGLE6 5.2 build 3790/ Microsoft-IIS/6.0

PHP Version 4.3.6 with mysql.allow_persistent = off.

MySQL 4.1.8-nt running

All time results are measured in microseconds. 1 us = 0.0000001 second.

The result for opening mysql connection 100 times in one page. Brinkster server
	Mysql_connect	Mysql_pconnect
100 Loops	0.352324008942 Average 0.00352324008942	9.40135145187 Average 0.0940135145187

The result for opening mysql connection 100 times in one page accessing a table containing 4 records. Brinkster server
	Mysql_connect	Mysql_pconnect
100 Loops	18.3291544914 Average 0.183291544914	26.7511310577 Average 0.267511310577

The times from one connection of 10 tests while reading 4 records from a table using brinkster’s mysql server..

	brinkster mysql server.
	Mysql_connect	Mysql_pconnect
1.	0.529908895493	0.621995567452
2.	0.531473875046	0.265028953552
3.	0.537230014801	0.26303601265
4.	0.530024051666	0.268359899521
5.	0.527902126312	0.268362045288
6.	0.541395902634	0.264112949371
7.	0.538422107697	0.270877838135
8.	0.53887295723	0.26437997818
9.	0.530326128006	0.26514005661
10.	0.530980825424	0.268352985382
Average	0.533653688	0.301964629

The times from one connection of 10 tests.

	Brinkster’s mysql server.
	Mysql_connect	Mysql_pconnect
1.	0.351463079453	0.511427879333
2.	0.349606990814	0.0880341529846
3.	0.350552082062	0.0887169837952
4.	0.350438117981	0.086434841156
5.	0.350795984268	0.0876920223236
6.	0.349910974503	0.0873341560364
7.	0.35268497467	0.0879299640656
8.	0.352124929428	0.087671995163
9.	0.351628065109	0.0874209403992
10.	0.349985122681	0.0872449874878
Average	0.350919032	0.129990792

Local Server

Apache Version Apache/2.0.55 (Win32) PHP/5.1.2

System Windows NT LIXP1 5.1 build 2600

MySQL - 5.0.19-nt

The result for opening mysql connection 100 times in one page. My local server
	Mysql_connect	Mysql_pconnect
100 Loops	0.00388431549072 Average 3.88431549072E-005	0.0451853275299 Average 0.000451853275299
50 Loops	0. 00345373153687 Average 6.90746307373E-005	1. 0278699398041 Average 0.000557398796082

The times from one connection of 10 tests

	My local mysql server.
	Mysql_connect	Mysql_pconnect
1.	0.00294303894043	0.040155172348
2.	0.00240993499756	0.000385999679565
3.	0.00236296653748	0.000363111495972
4.	0.00245308876038	0.0017511844635
5.	0.00341415405273	0.000352144241333
6.	0.00240993499756	0.000328063964844
7.	0.00242400169373	0.000353097915649
8.	0.00235104560852	0.000352144241333
9.	0.00242209434509	0.000353097915649
10.	0.00242209434509	0.000355005264282
Average	0.002561235	0.004474902

The result for opening mysql connection 100 times in one page while reading a table of 4 records. My local server
Mysql_connect	Mysql_pconnect
Time used mysql_connect for 100 loops: 0.17444396019 Average Time used for 100 loops: 0.0017444396019	Time used mysql_pconnect for 100 loops: 0.221433877945 Average Time used for 100 loops: 0.00221433877945

The result for opening mysql connection 50 times in one page while reading a table of 4 records. My local server
Mysql_connect	Mysql_pconnect
Time used mysql_connect for 50 loops: 0.0991790294647 Average Time used for 50 loops: 0.00198358058929	Time used mysql_pconnect for 50 loops: 0.116796016693 Average Time used for 50 loops: 0.00233592033386

The times from one connection of 10 tests while reading 4 records from a table.

	My local mysql server.
	Mysql_connect	Mysql_pconnect
11.	0.00404787063599	0.0169429779053
12.	0.00396800041199	0.00390601158142
13.	0.00410914421082	0.00385713577271
14.	0.00394082069397	0.00535202026367
15.	0.00400495529175	0.0048680305481
16.	0.00412011146545	0.00550007820129
17.	0.00731420516968	0.00530409812927
18.	0.0049569606781	0.00393199920654
19.	0.00558114051819	0.00378203392029
20.	0.0039439201355	0.00528407096863
Average	0.004598713	0.005872846

The times from one connection of 10 tests while reading 4 records from a table with mysql.allow_persistent = off.

	My local mysql server.
	Mysql_connect	Mysql_pconnect
1.	0.0159568786621	0.0151901245117
2.	0.00385808944702	0.0039529800415
3.	0.00757598876953	0.00386190414429
4.	0.00387406349182	0.00486707687378
5.	0.00535702705383	0.00385594367981
6.	0.00966596603394	0.00410199165344
7.	0.00771403312683	0.00383496284485
8.	0.00882005691528	0.00390315055847
9.	0.00531911849976	0.0038628578186
10.	0.00528693199158	0.00381398200989
Average	0.007342815	0.005124497

The following observations are made from the data collected.

(1) Multiple instance of calling the same persistent connections takes additional time while regular connections do not.

(2) The persistent connection requires significantly higher resources when it is called for the 1^st time.

(3) Persistent connection uses up a lot of server resources.

(4) Persistent connection gives better performance when the MySQL server is located at a remote location.

(5) Do not use the mysql_pconnect() more than once in each page, because the MySQL actually takes time to do something if you do use it more than once. I do not observer this performance decrease in mysql_connect().

(6) The persistent connection directive appears to have minimal effect on the speed of access.

Note: There don’t seem to be a lot of problems associated with the persistent connection for MySQL. But many hits result for using PostgreSQL and ODBC.