If your site experiences the infamous “Mysql : Too many connections” error, you will need to tune your MySQL server to handle the load of a heavily used website.
There are some configuration variables that you can adjust to make your site handle more connections.
- An easy way to identify which variables need to be changed can be done through phpMyAdmin. Login to your phpMyAdmin control panel and search for the “Show MySQL runtime information” module. Scroll down the page and look for entries that are in red. These are suggested areas in your MySQL configuration that need to be looked at.
- The wait_timeout default in MySQL is 28840 seconds (8 hours), which can seriously limit the number of open connections you have on your page. I adjusted mine to 60 seconds. This frees up the thread after idle timeout, which allows for more connections to connect. The 8 hour timeframe is fine if you only have a few connections to the server. For popular websites, its best to make this value as low as possible. Set this variable to the maximum time your page typically runs. For instance, if you have a page that needs to retrieve some data processed from another server (60-80 seconds) and you only use one mysql_connect, you may lose the mysql connection for processing the remaining parts of the page if your wait_timeout is too low. If the processing takes much longer, a workaround is to use multiple mysql_connects to prevent an idle connection from disconnecting.
- DNS issues. If your DNS suddenly stops working, this will consume all your MySQL connections. MySQL tries to resolve the IP address for every connection it gets, so this will start creating many more threads than released if it DNS is down. In Linux, edit the /etc/hosts file and manually add in your web server IPs/names to this (if you have static IPs) This will speed up performance by resolving IPs locally as opposed to through DNS. You can identify DNS issues or connection attempts through “Failed attempts” section in phpMyAdmin runtime page. A significant number of failed attempts can mean either someone is trying to break into your MySQL box or your DNS isn’t working right.
- Increase the key_buffer value (for MyISAM tables) This helps handle indexes for temporary tables. If you have lots of key misses, its important to increase the size of the key_buffer. You can do this by comparing the key_reads vs. key_read_requests value.
- Increase the innodb_buffer_pool_size (for innodb tables)
- Increase the table_cache value. If you look at your phpMyAdmin runtime “Opened_tables” field and see a large number (over 1,000) you will need to increase your table_cache. This will keep tables in memory to speed up peformance. Default is only 64. I’ve upped mine to over 2,000 since I have many databases and tables running on my server.
- For more information, check out these resources:
http://www.ibm.com/developerworks/linux/library/l-tune-lamp-3.html
http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/index.html
