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 June 28, 2002 12:51 AM

Reader Comments
# Basil Hussain said:

If there is far too much memory allocation/deallocation because of a high usage of excessively-sized record buffers, this is because there are a lot of sequential table scans taking place. (The manual says: "Each thread that does a sequential scan allocates a buffer of this size for each table it scans.")

This surely points to overall poor index usage on the MySQL server. So, it sounds like a high record buffer setting isn't this guys only problem... perhaps he should optimise his table structure and queries?

on June 28, 2002 06:26 AM
Disclaimer: The opinions expressed here are mine and mine alone. My current, past, or previous employers are not responsible for what I write here, the comments left by others, or the photos I may share. If you have questions, please contact me. Also, I am not a journalist or reporter. Don't "pitch" me.

 

Privacy: I do not share or publish the email addresses or IP addresses of anyone posting a comment here without consent. However, I do reserve the right to remove comments that are spammy, off-topic, or otherwise unsuitable based on my comment policy. In a few cases, I may leave spammy comments but remove any URLs they contain.