How to tune MySQL's sort_buffer_sizeSun, May 9, 2010 in Databases
I perpetually see something like the following:
There are many problems in this my.cnf file, but the sort_buffer_size is a glaring one that identifies the user as someone who should not be playing with live ammunition. Therefore, I have developed an advanced process for tuning sort_buffer_size, which you can follow to get amazing performance improvements. It’s magical.
My server load is high and my queries are slow and my server crashes. Can you help me tune my server? Here is some information.
[random sample of SHOW GLOBAL STATUS, like the query cache counters] my.cnf:[mysqld] key_buffer_size=1500M query_cache_size= 64M max_connections = 256 key_buffer = 8M sort_buffer_size = 100M read_buffer_size = 8M delay_key_write = ALL
- How expert are you?
- I know that there is a sort buffer, and that it is related to sort_merge_passes. When sort_merge_passes is high, I have been told to increase the sort_buffer_size. I also know that it is somehow related to the number of sorts the server does, so when there are a lot of sorts shown in variables like Sort_rows and Sort_scan, I think I should also increase it. You are a beginner.
- I have been administering MySQL for many years. I know that there are two sort algorithms inside MySQL. I know exactly how to optimize the key cache hit ratio. You are a novice.
- I have read every blog post Peter Zaitsev ever wrote, and I can improve on them all. You are an expert.
Based on your score on the scale above, find your optimal sort_buffer_size tuning algorithm below:
- Beginners and novices 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.
Feel free to ask questions if anything is unclear, but be prepared for a direct answer if you ask for tuning advice.
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 sort_buffer_size, or 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.
About The Author
Baron is the founder and CEO of VividCortex. He is the author of High Performance MySQL and many open-source tools for performance analysis, monitoring, and system administration. Baron contributes to various database communities such as Oracle, PostgreSQL, Redis and MongoDB.