MySQL is so abundant in web stacks you’re bound to encounter it at some point. One of the weaknesses of it is the lack of tools and metrics to monitor things like memory usage. It’d be lovely to just see a graph of how much of your database, buffers etc are in memory. The default my.cnf files that ship with MySQL are a little, let’s say general for most circumstances. Two tools that I’ve found helpful in getting MySQL sized correctly are:
MySQL Tuning Primer is a script that you run against your database which will inspect it’s running state and make suggestions for improvements to your MySQL configuration.
This is an online tool that takes you through a set of questions to determine an appropriate set up for your needs.
Both tools can produce a custom my.cnf file but you’ll need to use your own judgement to choose the correct settings. Tuning MySQL is an iterative process: find a setting to tweak, understand what it does, shift it, check the monitors to see if you’ve had a positive change, repeat.
Getting as much of your database in memory is going to give you the best performance but as with all scaling or performance tasks you need to look at your whole system and work out what’s the best thing to tackle first. As a case in point I recently had a simple page that could take 7 seconds to load. I assumed that the problem was with the database and went about sticking my fat fingers in. When I took a step back and actually stuck a profiler on it, yes there was a slow query in there but once it was cached it was actually pretty quick. The most time spent was actually processing the huge (and unnecessary) amount of data that came back. Once I’d sorted out the code there were no more slow queries and the page was snappy again.
Long story short: Find out where your problems actually are and work out how to fix them.