How to determine the optimal sort_buffer_size?

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.

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/

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.

The best way to determine the optimal sort_buffer_size is to benchmark it.

How? Like @RolandoMySQLDBA said checking Sort_merge_passes might be helpful, but that's not the only factor that affects the performance. You should be careful when you increase the sort_buffer_size.

The document says that

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.

There's a post about the testing which concludes that

sort_merge_passes are not that bad. Setting your sort_buffer_size large enough so there is zero sort_merge_passes may not be optimal.

When I tested, I also get a similar result.

Ideally, it would be best to avoid the situation that you need to optimize the sort_buffer_size. How? This ORDER BY Optimization document might help you to understand how things work under the hood.

"mysql> SET GLOBAL sort_buffer_size = 1024 * 1024 * 4;" its a bad way to put in 4m the sort buffer size, thats makes sort buffer size have 4GB use

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

If i'm been your, i don't try to change short buffer size, that is a good way to make crash your server, and send to trash performance. It's better try to make better querys.