Switching from persistent to non-persistent connections in PHP: what can I expect?

In the last weeks we've reached our mysql connection limit of 1500 several times. All of a sudden the Threads_connected just explodes (300 -> 1500 in just a few minutes).

Our front-end servers (3x) use persistent connections to connect to the databa开发者_开发知识库se server (1x). Even when running out of threads our database server seems to be doing well resource wise (CPU, Memory, IO).

I'm thinking of switching from persistent to non-persistent connections in our application (cakePHP). What can I expect?

• Higher MySQL load?
• Higher load on fron-end servers?
• Increasing response times on fron-end servers?

Is it a good idea to do so, or should I just increase the connections limit even more?

Since the database and application server are on different machines, you can at the very least expect an added delay in script execution due to opening a new connection. If both servers are in a LAN, that is probably negligible.

Obviously an additional task will create additional load, but I dare say this will also be barely noticeable. Opening a connection compared to processing a query or whatever else your script does, is a tiny amount of overhead.

Now, to the real problem at hand. As mikey said, you may want to investigate the cause of the issue. If you use persistent connections, each web server thread can open and maintain one database connection. If there is just a small spike in load, this can drastically increase the amount of connections. The problem is really the same for persistent and non-persistent connections, except that the non-persistent ones will just go away afterwards and won't slow down future operations by needlessly hogging memory.

In general you must make sure your mySQL Server can handle as many connections as your web servers accept concurrent connections if you want to guarantee that every database connection succeeds. However, in a real world setup it is likely that your web server will service connections, which are not database related. (Images, css, javascript, etc.). Taking this into account you can offer more web serving threads than database connections, but there is a catch, which could also be the source of your problem:

If someone accesses a large amount of pages on your site in a different manner than would be expected on a normal use pattern, the above rule does not apply. For example if a search engine crawls your site. They do not process content in the same order as a browser does. They can focus on your PHP pages, breaking the assumption that every client will load a mix of data. If this is a problem depends on the amount of pages you have compared to the amount of regular users. This effect can also be accumulative, if your database server slows down under high load, delaying the processing of other connections.

As such it makes sense not to use persistent connections and manage the memory on the database server so that it can support near the maximum of connections at the cost of file cache and buffers, but returns to normal (faster) operations once the spike is over.

P.S.: Make sure you understand how much memory your database will really use when all connections are utilized. http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/. Don't just increase the amount of allowed connections without making sure you do have the required memory. Better to have a few failed connection attempts than your database server grinding to a halt, because its buffers are being swapped out.

You should switch off of persistent connections in PHP, they don't work as you would expect. You actually end up having many more open, idle connections than with not using them. The warning in the manual actually warns about running out of connections. http://php.net/manual/en/function.mysql-pconnect.php

You should also read this: http://www.php.net/manual/en/features.persistent-connections.php

The bottom line is that the persistent connections do not get reused the way they should. The spikes you are seeing is likely a bot crawling your site and a new connection being established for each page load.

You can expect your running out of connections issues to go away. The added time and load of always establishing a new connection will not be noticeable if the network connection between your web server and database server is fast and reliable. MySQL is actually pretty efficient in this regard.

I found out that the problems were caused by an overloaded DNS server.

See Is DNS the Achilles heel in your MySQL installation? and How a DNS problem can put your Mysql server down