How to tune MySQL's sort_buffer_size
Posted in Databases on May 9, 2010
I continually see consulting engagements like the following:
My server load is high and my queries are slow and my server crashes. Can you help me tune my server? Here is the my.cnf:
sort_buffer_size = 100M
Such a large
sort_buffer_size is a serious problem, but unfortunately there
is a lot of cargo cult advice on the Internet, in books, and in “tuning scripts,”
that perpetuates the harmful advice to increase it.
The usual advice is to increase the sort buffer size when
is high. This advice is much like the ratio-based methods for how to optimize
the key cache hit
It’s wrong and harmful.
In general, I have found the following to be true:
So if you’re reading this post because you’re trying to learn how to tune this variable, in all seriousness, the answer is you should not do it.
If you’re looking for guidance about how to get a good baseline configuration for MySQL, tuning it for performance, I have two suggestions.
The most amazing thing about
sort_buffer_size is how many people utterly
ruin their server performance and stability with it, but insist that they know
it’s vital to change it instead of leaving at its default. I do not know why
this is always the case. Why don’t people choose random variables to destroy
their performance? It’s not as though there is a shortage to choose from. Why
does everyone always pick
sort_buffer_size instead of something else?
PS: I considered a simpler tuning guide, such as Domas’s guide to
tuning the query cache, but I
am convinced that people need more a complex guide for the
they will not believe in the validity of the instructions. I base this on
multiple experiences being paid a lot of money to suggest not setting
sort_buffer_size to 256M, and being told that I must be an idiot.