Xaprb

Stay curious!

How to tune MySQL’s sort_buffer_size

with 12 comments

I perpetually see something 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 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

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.

  1. 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.
  2. 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.

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? It’s like a flame drawing the moths in.

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.

Written by Xaprb

May 9th, 2010 at 7:20 am

Posted in SQL

Tagged with

«
»

12 Responses to 'How to tune MySQL’s sort_buffer_size'

Subscribe to comments with RSS

  1. Disclaimer: I am a beginner. In life, in everything.

    I believe the answer to your question lies within the sample my.cnf files shipped with mysql.
    When one walks through my-small.cnf, my-medium.cnf, my-large.cnf, …, one sees an advance in values of all sorts of settings. *Including* sort_buffer_size, which increases from 64K to 2M.

    It just figures out one should extrapolate for larger memory.

    The default? If the my-*.cnf sample files don’t use the default, then why would you expect people to behave differently?

    Shlomi Noach

    9 May 10 at 10:39 am

  2. So . . . you tune this variable by making fun of people who don’t know how to tune this variable? (I guess I might have mis-read your post.)

    -danny

    Danel Howard

    9 May 10 at 3:10 pm

  3. No. I just don’t get people who insist that they DO know how to tune it. Humility is a good place to start when asking for help.

    OP: “Why is my server crashing?”

    Reply: “You are allocating a quarter gig of memory every time a sort happens. Stop doing that, and see if it stops crashing.”

    OP: “You have no clue what you’re talking about. Everyone knows that this variable needs to be large if you’re doing a lot of sorting.”

    Xaprb

    9 May 10 at 9:11 pm

  4. Good tutorial.

    Can we expect something similar for other usual suspects in my.cnf (over)tweaking such as join_buffer_size, read_buffer_size and read_rnd_buffer_size?

    kmike

    10 May 10 at 3:46 am

  5. The problem with sort_buffer_size is it can make some badly written queries execute much faster, so when the average developer tests them in isolation (say, on the developer’s machine/staging server) they see their old crummy query’s execution time go from 0.32s to 0.07s and say: I’ve just quadrupled my server’s performance! Which is kind of true, in a way.

    Sentenza

    10 May 10 at 7:11 am

  6. kmike, I considered a polemic against over-tuning those too, but decided not to rant against everything at once :-) But you are right. People also try increasing these without much information as to whether it is a good idea. Unfortunately, tuning any of these configuration variables is really hard work — justified only in uncommon cases in my experience.

    Sentenza, you’re exactly right. The GLOBAL tuning of these variables is the problem. Changing them on a per-query basis might be a good thing, and is usually how I advise to change them when it’s really needed. Although, it can still cause a lot of collateral damage that you won’t see in the development machine when lots of other queries aren’t running at the same time.

    Xaprb

    10 May 10 at 10:10 am

  7. As Sentenza says, increasing the sort buffer usually does increase the server’s performance. I’m not sure where you get that increasing the sort buffer kills performance, though I agree that it kills stability if you are trying to allocate too much memory and MySQL crashes.

    I’m very disappointed that you are just flaming here and not actually explaining why — as an author of High Performance MySQL I’d think you’d be interested in teaching people something other than, “you’re an idiot if you increase this variable”.

    What Pythian recommends is “if you need performance right now, increase the sort buffer size WHILE looking at queries to eliminate the problems. Then decrease the sort buffer size when the appropriate queries are fixed.”

    Sheeri K. Cabral

    10 May 10 at 10:19 am

  8. Baron – thanks for the reminder. Others have also documented the performance degradation that can occur from a too large value for sort_buffer_size:

    Nice work from Neel:
    http://bugs.mysql.com/bug.php?id=37359
    http://blogs.sun.com/realneel/entry/improving_filesort_performance_in_mysql

    From Percona:
    http://www.mysqlperformanceblog.com/2007/08/18/how-fast-can-you-sort-data-with-mysql/

    Mark Callaghan

    10 May 10 at 5:49 pm

  9. I think your post has overlooked some of the clear reasons “why” you need to tune this, and more importantly “why” you can’t because of the lack of appropriate instrumentation. You don’t detail any information even on artificial benchmarks about sort_buffer_size or per session buffers in general.

    http://ronaldbradford.com/blog/more-on-understanding-sort_buffer_size-2010-05-10/

    Ronald Bradford

    10 May 10 at 6:48 pm

  10. Ronald, I think your blog post did a great job picking up where I stopped, and explaining more about it. Thanks for the link.

    Xaprb

    10 May 10 at 7:15 pm

  11. May I speak for the underpaid, sub-commodity server DBA (usually in a profitable dept) who supports Baron’s humor and outrage. It really is all good.

    Spent valuable time reading due to excellent responses, incl xaprb’s. All I knew it’s per session default 2 mb.

    Anonymous

    11 May 10 at 6:20 pm

  12. Wish I could add a comment for a particular system variable in MySQL reference manual pointing to this post, as it encourages experimentation.

    “Experiment to find the best value for your workload.”

    http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_sort_buffer_size

    Jeremy Breece

    12 May 10 at 11:35 am

Leave a Reply