mysql sort 性能優化

    這段時間mysql 資料庫的性能明顯降低,iowait達到了30, 響應時間明顯變長.  通過show processlist 檢視,發現有很多session在處理sort 操作, 跟DBA一起調試優化,增大sort_buffer_size 好象效果也不大, 通過檢視監控,也沒發現有硬碟排序. 我懷疑是sort導緻性能下降,固讓開發修改程式, sort由程式來處理. 星期五釋出後,今天發現壓力固然好了很多.

    是以基本上能确定是sort引起的問題. 今天仔細分析問題,檢視mysql的參數時,看到一個叫做max_length_for_sort_data 的參數, 值是1024 仔細檢視mysql 的filesort算法時, 發現mysql的filesort有兩個方法,MySQL 4.1之前是使用方法A, 之後版本會使用改進的算法B, 但使用方法B的前提是列長度的值小于max_length_for_sort_data, 但我們系統中的列的長度的值會大于1024. 是以也就是說在sort的時候, 是在使用方法A, 而方法A的性能比較差, 也就解釋了我們的mysql系統在有sort時,性能差,去掉之後性能馬上提高很多的原因.

   馬上修改max_length_for_sort_data這個值，增大到8096, 果然性能就提高了.

  總結:

    mysql對于排序,使用了兩個變量來控制sort_buffer_size和  max_length_for_sort_data, 不象oracle使用SGA控制. 這種方式的缺點是要單獨控制,容易出現排序性能問題.

   對于filesort的兩個方法介紹,以及優化方式,見

http://forge.mysql.com/wiki/MySQL_Internals_Algorithms

Using the modified

filesort

algorithm, the tuples are longer than the pairs used in the original method, and fewer of them fit in the sort buffer (the size of which is given by

sort_buffer_size

). As a result, it is possible for the extra I/O to make the modified approach slower, not faster. To avoid a slowdown, the optimization is used only if the total size of the extra columns in the sort tuple does not exceed the value of the

max_length_for_sort_data

system variable. (A symptom of setting the value of this variable too high is that you should see high disk activity and low CPU activity.)