How does mysql_pconnect do its stuff?

[vulcand4]

I am using mysql_pconnect to get a connection to my mysql database. I use it instead of regular mysql_connect because of the number of connections I have to make.

I get the connection at the beginning of my script and use it numerous times before the script is done.

I thought that mysql_pconnect would only create a new connection if all of its existing connections were in use. However, I see (via mysql "show processlist") that many of the connections have been idle for some time and pconnect still creates a new one.

Is there some sort of default timeout that causes pconnect to know whether or not a connection is no longer being used? Or should the ending of a script notify pconnect that the connection can now be used by someone else?

-G

 

[jpadie]

why are you bothering to reconnect each time? the connection will last for the duration of the script unless you are actively closing it. if you open the connection once, you can reuse it as much as you like in whatever scope you like.

i have never personally seen a need for mysql_pconnect

 

[vulcand4]

Here's what I found out...

First of all, I was using a mysql_pconnect because I wanted to avoid the expense of getting a new connection each time my php script was activated. pconnect allows me to get one connection and then just keep it available each time the connection is needed.

My problem actually spawned from the fact that I have PHP set up to run as an Apache module. Since it runs in a multi-process environment, each Apache child process contains it own copy of the PHP interpreter (which in turn has its own copy of a pconnect connection pool).

Each instance of PHP (one per Apache process) had a connection pool of ONE pconnection, but if there were more than one simultaneous http request to the server, another httpd process was spawned.

This causes me to have 1 persistent mysql connection for each httpd process running on the server.

There was no timeout issue. The issue is that until an httpd process goes away, any persistent connection associated with that process will hang around.

I think my solution is to bite the bullet and take the (probably not so big) expense of creating a new connection each time my PHP script is called.

 

[jet042]

Where I work, we process hundreds of thousands of mysql connections every hour without any problems. Each script that needs to talk to the database (so most of them) create and destroy their own connection. Like jpadie said, I've never seen a reason for the pconnect functions since a straight connect has almost no overhead.

 

[ingresman]

Interesting debate though. When you use things like Oracle and SQL server there's a lot of permisions style stuff goes on before you get the connection granted, so a persistent connect (maintained by the likes of MTS on Windiows or Tuexedo on Unix) does have a deffinite impact of performance and the number of connections open at any one time.
Perhaps MYSQL is a lot lighter that the others or it was desinged for lots of connects as opposed to the big players who have come from a permanant client-server world.
There are some interesting reads about the debate on the web, the most interesting being that as mysql has got better simple re-use of a connection can give issues aroud complex things like transaction.

http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/

gives some good info.