mysql optimization

Zach suggested it last week, but it’s only now that I’ve gotten around to setting up MySQL’s slow query log.

It’s easy enough, you’ve just got to put a couple lines like this in your my.cnf (which is in /etc on my server):

log-slow-queries = /var/log/mysql/mysql-slow.log<br /> long_query_time = 10

This should get most people running, but this story in Database Journal offers a few more details. Potentially more useful is this guide to query and index optimization (though it’s probably a little out of date).

A couple notes from the past few days of tweaks and fixes:

• Hyper-threading has a huge effect on LAMP performance.
   
• From now on, I’ll have bad dreams about running MySQL without Query Caching in the way that I used to have nightmares about going to school wearing only my underwear. The difference is that big.
   
• WordPress rocks, but it has some queries that will kill large databases. I’m playing with baseline when I fix ’em, but it’s worth it.
   
• Being highly ranked for searches related to bears and bear lovers on the weekend that Grizzly Man airs on TV is both good and bad: it doubled my previous high for daily page-loads, but killed my server and taught me some lessons about sloppy coding.
   
• The support techs are Lunarpages continue to pretty much rock.
   
• Having friends who cut their teeth on high-performance PHP/MySQL and are willing to spend the night workshopping it with you is indispensable.
   
• It’s hard to beat the calming beauty of driving home through a snowfall on back roads at 2 AM carried by your life’s soundtrack.
   

I have over 1000 posts here at MaisonBisson, but even so, the table with all those posts is under 3MB. Now I’ve got a project with 150,000 posts — yes, 150,000 posts! — and the table is about 500MB. An associated table, structured sort of like WP’s postsmeta, has over 1.5 million records and weighs in at over 100MB (not including the 150MB of indexes).

Up to now I’ve been a “throw more hardware at it” sort of guy — and in a server with only 1GB of RAM, that’s probably the best solution — but I also think it’s time I learned some MySQL optimization tricks. Zach‘s been pushing me to get mytop installed for quite a while, and I finally got around to it.

mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server. It runs on most Unix systems (including Mac OS X) which have Perl, DBI, and Term::ReadKey installed. And with Term::ANSIColor installed you even get color. If you install Time::HiRes, you’ll get good real-time queries/second stats.