How to determine the optimal sort_buffer_size?

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?

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

StackExchange Database Administrators Q#60078, answer score: 26