Should consider staying below one of those values. Larger values may significantly slow down memory allocation, so you On Linux, there are thresholds of 256KB and 2MB where Increase it as a session setting, and only for the sessions that needĪ larger size. Required globally will slow down most queries that sort. The entire buffer isĪllocated even if it is not all needed, so setting it larger than Speed up ORDER BY or GROUP BY operations that cannot be improved with Output, you can consider increasing the sort_buffer_size value to If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS You would run the code periodically to check for other times Sort_merge_passes spikes. You would run this: mysql> SET GLOBAL sort_buffer_size = 1024 * 1024 * 4 SET = - = * 3600 / you find the Sort_merge_passes and the rate too high, then feel free to increase sort_buffer_size. SELECT INTO variable_value INTO information_schema.global_status WHERE variable_name = 'Sort_merge_passes' SELECT variable_value INTO information_schema.global_status WHERE variable_name = 'Sort_merge_passes' It also computes the Sort_merge_passes per hour. Use this code, to check how many Sort_merge_passes happened in the last 5 minutes. Question remains: How do you check the Sort_merge_passes ? 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. 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 Please keep in mind one thing about sort_buffer_size If this value is large, you should consider increasing the value of the sort_buffer_size system variable. Sort_merge_passes : The number of merge passes that the sort algorithm has had to do. That's what you have in the message back in the question : Sort_merge_passes. There is only one status variable that cares about sort_buffer_size.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |