Many years ago, I was a rabid (vapid?) PostgreSQL fanboi. I took the time to study how to tune postgres, it was very rewarding to see queries go from taking 30 seconds, to completing in under a second. This involved tuning knobs in the prostres config, to adding indexes. All done manually.
If some software would only work on mysql, I wouldn’t bother looking at it.
A few years ago I wanted to run WordPress on my Linode, I gave up my extremist software bias, and uninstalled postgres, and installed mysql. To tune it, I picked one of the three or four my.cnf files provided. And that was that. Although I gave up postgres for mysql, I showed it no love.
During my recent webserver admin gorge, I came across a shell script that would help you tune mysql’s knobs. Most knobs weren’t even mentioned in my my.cnf file, so were at the default. I found much delight in it telling me to raise this, and lower that. In the end, I ended up with mysql using about the same amount of memory, but much more efficiently, by using more memory here where required, and using less memory there where not needed.
The script is called tuning-primer.sh and can be found at http://www.day32.com/MySQL/.
It took me many days to tune my mysql databases, run the script, adjust knobs, restart mysql, let it run for a day or two, and repeat. It turns out some knobs don’t need adjusting until other knobs are adjusted first, so you can’t get it right the first go. Plus I was quite cautious in how much I would adjust a knob.
There’s also another script I haven’t yet looked at called mysqltuner.pl, google will hook you up.
And here’s some music I’ve been enjoying lately from Sia, (an adelaide girl) her live performances are better than her albums. Sia – Soon We’ll Be Found (Letterman)
Related posts:
2 Comments
Thank you, very handy post, had to do some MySQL tuning today, which is much easier when you get some hints where to look! The script also suggests logging of queries without an index, which for custom web apps makes it very easy to find those few tables that could do with an extra index.
Thanks Nick, I noticed the comments on turning on logging of queries. I’ve heard from other people this slows mysql down a lot, but to be honest, i haven’t noticed.
Anyway, no harm turning them on for a few days and turning them off when finished.
I also noticed I like to say knob a lot.