How to tune MySQL's sort_buffer_sizeSun, May 9, 2010 in Databases
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:
- Non-experts should leave this setting at its default, and comment it out of the configuration file.
- Experts don’t need me to tell them what to do, but most of them will leave this setting at its default, and comment it out of the configuration file.
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.
- Realize that configuration generally doesn’t improve performance; it is more often the case that bad configuration harms it. So it’s more a matter of avoiding harm, than creating performance improvements. For more, read Configuring MySQL for Performance.
- Focus on query performance and not on server configuration. There are much greater gains to be achieved that way.
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.
I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and many open-source tools for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB.