Understanding MySQL Persistent Connections with mysql_pconnect()

What is mysql_pconnect():

The main purpose of using mysql_pconnect() function is to maintain a persistent connection to the mysql server. A persistent connection is a connection that do not get closed even after the excecution of the script is over which opened the connection. Even function mysql_close() can't close a persisitent connection. In contrast a normal connection opened using mysql_connect() gets closed either by mysql_close() or at the end of the scripts execution.

Why mysql_pconnect():

In one word the answer is "Efficiency". Persistent connections are good if the overhead to create a link to your db server is high. This overhead may be high due to various reasons. Persistent connections can help you considerably if the connection overhead is high.

How mysql_pconnect() works:

The most popular method to run PHP is to run it as a module in a multiprocess web server like Apache. A multiprocess server typically has one parent process which coordinates with a set of child processes. These child processes actually do the work of serving up web pages. When a request comes in from a client, it is handed over to one of the children that is free. This means that when the same client makes a second request to the server, it may be served by a different child process than the first time.

When opening a persistent connection, the function would first try to find a persistent link that is already open with the same host, username and password combination. If one is found, instead of opening a new connection an identifier for it will be returned. It causes the child process to simply connect only once for its entire lifespan, instead of every time it processes a page that requires connecting to the same db server. Every child that opened a persistent connection will have its own open persistent connection to the db server. For example, if you had 20 different child processes that ran a script that made a persistent connection to your db server, you would have 20 different connections to the db server, one from each child.

Issues with mysql_pconnect():

• Persistent database connections don't necessarily reflect subsequent privilege changes.
• Be very careful when using persistent connections and temporary tables on MySQL. With normal connections temporary tables are visible only to the current connection, but if you have a persistent connection the temporary tables will be visible to everybody sharing the same persistent connection. This can lead to major trouble.
• Don't use mysql_pconnect() in situations where multiple MySQL servers are running on multiple ports of the same host. The connection pooling algo in php apparently only checks for the host, username and password combination but not the port. Therefore, if you use the same host, username and password but a different port, you might get a connection that is connected to a different port than the one you asked for.
• Do not use transactions with persistent connections.  If your script stops or exits for any reason, your transaction will be left open and your locks will be left on.  You have to reset MySQL to release them. They won't rollback automatically on error, like they ought to. When you restart the script, you'll get a new connection, so you can't rollback or commit for the previous script.
• You should be very careful when using LOCK TABLES with persistent connections. If the script terminates before the UNLOCK TABLES is executed, the the table(s) will stay locked, and very likely hang future scripts.

Things to remember:

• Persistent connections were designed to have one-to-one mapping to regular connections. That means that you should always be able to replace persistent connections with non-persistent connections, and it won't change the way your script behaves. It may change the efficiency of the script, but not its behavior.
• Any script with a start transaction, rollback, or commit SQL statement should use regular, not persistent connections.
• Use totally random temporary table names when using persistent connections to avoid major problems.
• Make damn sure that max connections limit in your my.cnf has a limit with a few more connections than your httpd.conf has for number of apache children. Less MySQL connections than apache children means some apache children will be starved for db.
• Leave a few extra mysql connections, so that in case of a problem it can leave you with the ability to log in from shell to diagnose/fix it. Otherwise, you will have to bring down all of apache to get into your database.
• You can also use register_shutdown_function() to register a simple cleanup function to unlock your tables or roll back your transactions. But it's better to avoid the problem entirely by not using persistent connections in scripts which use table locks or transactions.
• Instead of use wait_timeout, you can set interactive_timeout to short period of time (for ex. 20 sec.) this is a lot better solution in apache + mysql environment than wait_timeout.