Bigger isn’t always better for MySQL

Fairly recently, I did some performance tuning on our MySQL servers and discovered some very interesting things.  The previous “DBA” had the mantra of “set the value higher and it will go faster.”  We are running some pretty beefy hardware so I can definitely see where this logic would come in to play.  I became concerned when my little laptop with a default MySQL installation was getting better performance than our 8 gig of Ram, dual processor quad core monster server.

I narrowed the offending configuration option to read_buffer_size.  It was set to 1 MB.  That doesn’t seem like such a big change when the default value is 128K.  128K seems small doesn’t it?  We have 8 gigs of RAM, shouldn’t a larger value yield better results?

It looks like the guys over at the MySQL Performance Blog came to the same conclusion I did:  Leave read_buffer_size to its default value.  Changing it will only hurt your performance.

I have a very intense recursive stored procedure used for traversing cyclical graphs.  With read_buffer_size set to 1MB I get maybe 100 queries per second.  With read_buffer_size set to its default value, over 3,000 queries per second.

Take a look at the MySQL Performance Blog for a more detailed analysis.

I recommend leaving all of the MySQL values at their default unless you really know what you are doing.  Even though it goes against common logic, increasing a value in proportion to hardware improvements doesn’t always yield better performance.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">