mysql max_connections and lighttpd+fastcgi
I'm running MySQL, lighttpd, mod-fastcgi and php-cgi on Debian 5.0 (360M). fastcgi is configured to spawn 8 php-cgi workers. The maxconnections config variable in MySQL is set to 32. When I configured the variable to this value, I had the understanding that php-cgi workers handle requests serially. So at any given time, there shouldn't be more than 8 open MySQL connections. When I stress test my setup using ab (Apache Benchmark), the maximum number of concurrent connection does stay <= 8 indeed (as reported by the maxused_connections variable in MySQL).
I'm reviewing my config and trying to find a source that confirms my understanding. However, I can't find any sources to confirm either way. All what I find is people increasing max_connections just to be safe. I'd like to keep the number low (if possible) as MySQL tuning scripts complain about the fact that the maximum possible memory usage of MySQL exceeds the available system memory if I increase the number to 50.
The website is doing well and I don't see any errors in the logs. But I'm concerned that PHP will fail to connect to MySQL if my website becomes under real high load.
Is my understanding correct? Or should I increase the max connection limit and decrease other variables (buffers) to compensate?
Thanks.
3 Replies
Does your php script call mysqlconnect more than once with several different username/passwords or $newlink = true? This could lead to more than one connection per worker.
Do you have any other services that connect to mysql? For example, Nagios or Cacti storing their configuration in a mysql database. This could lead to more connections that aren't made from php.
Do you run any shell scripts or cron jobs (or use the mysql client) that connect to mysql? This could lead to more connections that aren't made from php.
How do you backup your database? If you're using MyISAM and mysqldump then the database will be locked while it's begin backed up and other connections will queue up (and so your php workers will queue up, but only to the maximum of 8, at which point your website will stop working
:) ).Do you have any slow queries? This point is similar to backups above: the faster you get into and out of the database, the sooner your connection will be available for someone else to use (as long as you close it when you're done instead of waiting until the end of the script).
You should configure mysql to have a handful of spare connections but you shouldn't need 32. Xaprb's improved mysql templates for cacti are a great way to monitor your actual connection usage.
I found 12-16 to be a good number of connections for a 360.
Fortunately, I don't have any other sources of MySQL connections. I'm also monitoring the slow query log and so far I don't have any.
Looks like 32 connections is a safe setting then. Still, replies are welcome, especially if someone has a link that supports my understanding of how requests are serially handled.