Production MySQL performance tuning

mysql For the past 9 years I’ve been working almost exclusively with MySQL (with a little PostgreSQL thrown in) and while I don’t do nearly as much DBA work these days, I still find myself troubleshooting a query or tuning my.cnf. While in general I find MySQL to be a lot more straightforward to work with, it’s still equally important to tune it for your applications needs.

To that end one of the tools I want to give a shout out to is the MySQL Performance Tuning Primer Script. You download and run it against a production system (that has preferably been running under normal load for a day or two so that it’s gathered stats). It’s a read-only script so you don’t need to worry about it changing anything but it makes some great recommendations about which tuning parameters may need adjustment. Here’s a snippet from from a production server:

Current MyISAM index space = 173 M
Current key_buffer_size = 1 G
Key cache miss rate is 1 : 1003
Key buffer fill ratio = 7.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

It’s no replacement for a DBA but if you want to get a somewhat sane my.cnf going for your particular application it’s a great place to start!

This entry was posted in Database, FreeBSD, Linux, MySQL, Systems Administration. Bookmark the permalink.