We are running a server right now and the MySQL database is crashing quite often. We know that we need to find good settings for MaxClients and MaxRequestsPerChild as we are getting lots of traffic and when we get a spike the database goes down.
Is there a good rule of thumb or formula that would help us figure this out?
I think there must be some confusion. MaxClients and MaxRequestsPerChild are Apache settings, not MySQL. MySQL has max_connections, which resembles MaxClients, but no equivalent to MaxRequestsPerChild.
What do you mean it 'goes down'? If mysqld dies (what do you see in the mysql error log?), then you should turn something down (possibly the settings you're talking about, in Apache). If mysqld is just refusing connections because it has too many, you can try turning up max_connections (it defaults to a mere 100; if you're at the default, maybe try 200).
If you have a busy site making lots of connections, you need to keep an eye on the number of threads and connections in MySQL. (They're almost the same, but not quite.)
If running SHOW PROCESSLIST shows lots and lots of 'sleeping' threads with times of many seconds, you need to lower variable wait_timeout, probably by quite a long way as it defaults to many minutes and most web pages are over in seconds.
It would be good to look at status threads_created and see if it is rising rapidly over time. If so, you need to raise the variable thread_cache_size. I'd also look at status Opened_tables. If it is rising constantly, you should raise variable Table_cache. Both of these are basic performance improvements in MySQL for a busy database.
You should also setup Munin or Cacti or some other performance monitoring tool on both Apache and MySQL. Apache also has an Extended Status monitoring page which will help you see how to adjust it's child/thread settings.
I'm just guessing here, but are you running a 32-bit OS with far too many connections?
MySQL is multi-threaded and uses one thread per connection. Each thread uses some memory for its stack and various other buffers (which are numerous and mostly tunable).
Running out of address space will crash MySQL in your 32-bit box.
Solutions are:
The 2nd one is what I'd recommend if possible for anything except a mickey-mouse situation. Do not deploy any new application on a 32-bit OS if you can help it.
If mysqld is actually dying, then you may be running into the 1024 file descriptor limit... "Too Many Open Files" is the typical message for that errno. You should configure ulimit and/or pam_limits to bump up the hard and soft limits for open files for your daemon's role account to something higher, set the max_connections variable for the database user that is opening so many connections, and fix your app to throw a whale when that happens...
You'll need to determine why the database is hitting max connections. Generally, the issue isn't that the MySQL is hitting max connections, but a symptom of other problems.
It's easy to overcommit the memory resources with Apache and MySQL settings. Check out mysqltuner and apachebuddy to get an idea of whether or not you're possibly running out of memory. If your server is having to go into swap, it's a sign that you need to speed up the execution of the database queries and your application, add more memory, or split up MySQL and Apache on separate servers (if you haven't already).
You probably have sar logs available to give you some information about the resource utilization of the server.
To show processor utilization:
# sar
To show memory utilization:
# sar -r
You can also use the -f /var/log/sa/sa## flag to specify a sar log from a previous day.
Finally, when the database connections get maxed out, you should log in to MySQL and see what's going on.
mysql> SHOW FULL PROCESSLIST;
That will give you a listing of all of the processes MySQL is handling at a given time. In particular, you want to see if there are a bunch of "sleeping" connections. If you do, then your application is likely not closing connections properly. If you're using persistent connections, stop it. If you see a lot of queries that say "locked" or "waiting for table", you need to see if converting the table from MyISAM to InnoDB is an option. Finally, if you just have some queries that are running for more than a few seconds, you need to look into optimizing those (a whole different question) and ensure you have adequate indexes for your queries. A good way to spot where you need an index is to look at your WHERE conditions and run SELECT queries with EXPLAIN.
One problem that I've seen is with a busy site, not checking, and updating the memory-allocation available to the server for its use.
If you have anything other than a trivial database (literally, a few megabytes), you will want to update the innodb_buffer_pool_size from the default value of as little as 8MB to 70-80% of memory on a dedicated database server. 12G on 16GB box is one reasonable suggestion, though if your database is only at a few GB on disk, then this would be overkill unless you think it likely the amount of data will grow significantly.
There's a similar setting for Myisam tables, though many people have mostly innodb tables (but the mysql-internal database is usually only myisam, so it does need some myisam key_buffer set).
Adding some more details about how big your database is, and the spec of the server that the database is running on (and if it's dedicated to that task) will be able to elicit more help.
