Working on servers, people seem to think there is a cheat sheet to it at times. While there are many ways to simplify and automate a lot of configurations on a server, MySQL is not the time nor the place to cut corners. If you bog MySQL in turn the system will feel the wrath of your misconfigurations. Here are a few super common mistakes.
- max_connections set way high-Why people think this is even remotely near a good idea is beyond me. It is common to go out to a server, find out it has either flat out hung or is spitting out of memory errors on the screen and has gone brain dead. Just because you can set your connections to 1500 doesn’t mean it’s a good idea. In fact it’s probably better for MOST people to set their connections to 150. The reason is simple; each connection available uses RAM. If a query comes by and there are 150 connections available, you will probably get an out of connections error and can log into the system, figure out what broke and fix it; If you hang the system you get an out of memory error and no real data to work off of. CPanel sets this to 500 by default, if you are using 500 connections (and the system is still up) YOU PROBABLY HAVE A PROBLEM.
- huge buffer sizes-Guess what if you have 100 megabytes of InnoDB tables setting your innodb_buffer_size to 5GB isn’t going to help you out. Plain and simple. If you start adding more tables you could possibly even run out of RAM (see above.)
- Big RAM-MySQL does not like RAM use above 2GB on 32 bit systems. They do not recommend running it because it can cause stability problems. In turn this means that there is little reason not to run a 64 bit OS any more unless you’re running a low end server. You NT guys are pretty much stuck with 2GB no matter if you’re running X86 or X64. That being said for hard core DB applications you should probably be putting it on a unix based OS anyways.
Besides this there is the single biggest reason of all:You will probably have lousy performance compared to a custom my.cnf. When tuning databases, one needs to keep in mind that the my.cnf is tailored to a combination of the database content, how the database is used and the server its self. If there was a magical my.cnf that would make any server work great don’t you think that Oracle/Sun/MySQL would have included it with the server software? We will go into a few things in later blogs such as software to help tuning MySQL, what the parameters mean and how to actually tune a database properly.