Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I read from a sample configuration file which says the following:

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead - See the "Sort_merge_passes"
# status variable. Allocated per thread if sort is needed.

I have a couple of queries which use filesort. How do I determine what is the size of the buffer I need for queries to run smoothly without hitting the disk?

share|improve this question
    
Have you run mysqltuner or tuning-primer You may see something interesting about your my.cnf in these apps. –  David Martinez Mar 17 at 13:15

3 Answers 3

You do not need to change the sort_buffer_size from default. You misunderstand it's use based on the question. You should begin by examining the SQL to see if you are able to tune it and satisfy ORDER BY/GROUP BY conditions using an index. It will generally be a composite index.

Further: http://www.xaprb.com/blog/2010/05/09/how-to-tune-mysqls-sort_buffer_size/

share|improve this answer
    
Sorry to say that I find that post hardly useful. It is like telling people not to do something just because you are not an expert. As pointed out by the first commentator, the sample .cnf files shipped with mysql do not use the default setting. –  Question Overflow Mar 4 at 15:20
    
If you read it again it also says that the expert already knows not to change the value from default. The sample .cnf files should not be used or referenced as good practice. If you need help building a my.cnf file Percona offer a fairly thorough wizard. tools.percona.com/wizard –  eroomydna Mar 5 at 0:09

There is only one status variable that cares about sort_buffer_size. That's what you have in the message back in the question : Sort_merge_passes. The MySQL Documentation says:

Sort_merge_passes : The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

Please keep in mind one thing about sort_buffer_size

If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing

While raising sort_buffer_size can help queries queries with GROUP BYs and ORDER BYs, you are better off improving the queries that you can improve and adding indexes that can be used by the Query Optimizer.

Question remains: How do you check the Sort_merge_passes ???

Use this code, to check how many Sort_merge_passes happened in the last 5 minutes. It also computes the Sort_merge_passes per hour.

SET @SleepTime = 300;
SELECT variable_value INTO @SMP1
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SELECT SLEEP(@SleepTime) INTO @x;
SELECT variable_value INTO @SMP2
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SET @SMP = @SMP2 - @SMP1;
SET @SMP_RATE = @SMP * 3600 / @SleepTime;
SELECT @SMP,@SMP_RATE;

If you find the Sort_merge_passes and the rate too high, then feel free to increase sort_buffer_size. Suppose you want to raise to 4M. You would run this:

mysql> SET GLOBAL sort_buffer_size = 1024 * 1024 * 4;

You would then add this to my.cnf

[mysqld]
sort_buffer_size = 4M

You would run the code periodically to check for other times Sort_merge_passes spikes.

share|improve this answer

The guidance in the manual (5.0-5.5) is

If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing. The entire buffer is allocated even if it is not all needed, so setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload.

From 5.6 onwards the wording indicates that the optimizer can pick a value for a query and that the server can extend the buffer up to the limit. This mitigates the cost of setting the value too high. So it sounds like you may want to be conservative, lower than then default (As the release cnf files do) for releases under 5.6.4 but can afford to have a higher limit of say the default 2MB, or even more, from 5.6.4 as the full amount is not blindly allocated.

As of MySQL 5.6.4, the optimizer tries to work out how much space is needed but can allocate more, up to the limit.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.