For most, max connections is something they ignore. You edit the my.cnf and set it to a huge number like 512 and forget about it, ignoring the future problem you have just created for yourself. The max connection setting is in fact one of the most important tools you have for maintaining a functional server. If you get hit hard with a large number of requests suddenly, your number of requests hitting mysql increases. Each request consumes memory and cpu resources. By setting this too high, you will consume all of your memory, all of your swap, and ultimately crash the server. So, how do we set this properly?

First, start with a lower number like 100 for example. Watch the server for several hours or even days. Log into phpmyadmin and instead of viewing your database, click the status button in the top row of menu options (has a stop light for a thumbnail). This tab gives you all sorts of mysql related info that can help you make better tuning decisions. For starters, right there in one of the top boxes it shows you theĀ max. concurrent connections. This is the max number of simultaneous connections you have had on your server since it was started. If you are currently running fine, and using most if not all of your ram (which you should be), take this number increase it by 5 or 10 percent and use it as the value for your new max connections parameter in the my.cnf. You can always tell if it is too low by watching the other field in how statusĀ Failed attempts. If you see a bunch of failed attempts, AND you still have some free memory, you may consider increasing the max connections, OR it may mean you need more server resources to keep up with the workload you are throwing at it (more ram most likely).

Now, you should also take a look at your max connections in apache as well. Setting this too high is another recipe for disaster.

Once both values are tuned properly, it should be virtually impossible for your server to completely crash. You may have moments of degraded service when traffic is especially high, but degraded service is far better than no service.