June 28, 2002

MySQL Tuning Advice

Here's a bit of advice. Don't make your record buffer too large. In fact, if you're adjusting it at all, there's probably something wrong. I was helping someone at work figure out what was wrong with their MySQL server today. It worked pretty well until it was stressed with more queries.

I looked at the disk stats. The machine had relatively fast SCSI disks in it. Running iostat -d -n 5 5 didn't show any particular disk bottlenecks. The CPU was pretty busy, but it wasn't saturated. I checked to see how much ram was in the machine (1GB) and roughly 40% was unused. The CPU wasn't a speed daemon (500MHz Pentium 2). But it should have been able to perform much better.

Then I began to watch the output of vmstat 5 and noticed something very odd. In each 5 seconds worth of output, there were thousands of page faults. The number was really high. There was clearly something funky going on with memory utilization.

I looked again at startup options that were set for MySQL on that machine. It turns out that the record_buffer was set to 24MB. What that means is that each active thread in MySQL was constantly allocating and releasing a lot of memory. The kernel was doing a lot of unnecessary work by keeping track of that.

After taking that setting out and letting MySQL use its default value (roughly 1MB), the server performed much better. CPU utilization was far lower. We were able to see the server plow through 500 queries per second with room to spare.

That was the lesson of the day.

Posted by jzawodn at 12:51 AM