Todd Pinkerton

In my last post, I talked about how hard it was to find good resources for newbies to learn database tuning, especially with the very popular MySQL database — the free or commercial version.

This post will talk you through the basics of database tuning. Ready? here we go:

1) hardware is important.

Ideally, your mySQL server will be on its own dedicated box. Sharing it with Apache and the rest of your application may be fine starting off, but eventually you’ll want a dedicated machine for it. This also helps establish the right infrastructure for scalability, so you can add more webserver boxes which all connect to your database machine.

So of course, things like RAM (speed and amount of), disk (speed and size of), and CPU horsepower all matter. Get as much as you can.

2) get the right tools. To even begin to tune the performance of your database, you need to be able to measure it. So get some good tools like

* mytop (monitor mysql queries and performance)
* ab (apache benchmark, for load-testing apache)
* top (cpu/memory performance, process management)
* iostat (disk/cpu performance)

and and learn how to use them. Test your database using whatever queries and scenarios your encounter in your ‘real world ‘ application. As you tune you want to see queries per second go up, and time per request go down.
3) you run top, and find that your database is occupying 99% of the CPU. Now what?

If your database is thrashing, now is the time to take action. Start by looking at your sql queries in your application. Turn on ‘log slow queries‘ in mysqldb — this will show you which sql statements are slowing down your db. I had a situation where I was calling ‘.lower()’ on some columns before doing string comparisons in my application, but this was unnecessary — mySQL is case-insensitive by default, and using .lower() was causing my indexes to be ignored! So go through all your SQL statements very carefully, and pay special attention to the functions you call and any that show up in the slow queries log.
4) create good indexes. Indexes make queries on your tables go faster. Creating the right indexes is a fine art, but for starters create an index on everything in your WHERE clause. If there’s more than one column in your WHERE clause, create an index that covers both (or all) those columns. Make sure your indexes are being used by running ‘describe’ on your queries, for example : DESC select * from items;

This will show you what indexes are available, and which ones are being used. Do this for every query in your application.
5) Tune some mySQL parameters on the server. Some basic ones are query-cache-type and query-cache-size (to turn on query caching), and thread_cache_size (to enable thread caching). Turning on both of these should help.

At this point, you should have a reasonably fast database; you should also see your CPU usage for mysqld go way down. But to make your entire app run smoothly, you’ll want to take a look at how mysql interacts with apache (httpd). Although not everyone uses apache for their webserver, chances are you do, and if you adjust some of the apache params things will get even better.

For example, if Apache is set to allow 200 max connections, then your database should never get more than that number of simultaneous connections (one mysql connection per apache client), so you can scale back those attributes — which should free up ram and CPU.