MySQL 性能調優

    使用主備復制因此需要打開LogBin這裡就涉及一個syncbin的概念缺省情況下syncbin 在mysql中是關閉的但存在一個風險因為寫日志沒有刷新到硬盤中去的話日志是寫在操作系統的文件系統裡的Cache中這樣若機器斷電那麼日志信息將部分丟失為了減少數據丟失我們測試了syncbin=syncbin=和syncbin=三種情況syncbin=的情況下丟失數據最壞概率是丟一個事務的數據但性能非常差設置syncbin=的情況下性能可以提高倍左右設置syncbin=時性能比syncbin=又可以提高倍設置大於的值基本上相差不大性能提高不了多少最壞個事務數據丟失是在我們的允許范圍之內因此設置是個合理的值這個值具體取決於你的系統能夠最壞允許丟少的事務數據
    若你的系統對數據丟失不於考慮可以關閉syncbin這時數據刷新到硬盤完全取決於操作系統的配置相關的配置參數有如下
    /proc/sys/vm/dirty_ratio
    這個參數控制一個進程在文件系統中的文件系統寫緩沖區的大小單位是百分比表示系統內存的百分比表示當一個進程中寫緩沖使用到系統內存多少的時候再有磁盤寫操作時開始向磁盤寫出數據增大之會使用更多系統內存用於磁盤寫緩沖也可以極大提高系統的寫性能但是當你需要持續恆定的寫入場合時應該降低其數值一般缺省是 更新方法
    echo >/proc/sys/vm/dirty_ratio （或則修改/etc/nf文件增加sysvmdirty_ratio= 重起機器）
    /proc/sys/vm/dirty_background_ratio
    這個參數控制文件系統的pdflush進程在何時刷新磁盤單位是百分比表示系統總內存的百分比意思是當磁盤的髒數據緩沖到系統內存多少的時候pdflush開始把髒數據刷新到磁盤增大會使用更多系統內存用於磁盤寫緩沖也可以極大提高系統的寫性能但是當你需要持續恆定的寫入場合時應該降低其數值一般缺省是
    /proc/sys/vm/dirty_writeback_centisecs
    Pdflush寫後台進程每隔多久被喚醒並執行把髒數據寫出到硬盤單位是 / 秒缺省數值是也就是 秒如果你的系統是持續地寫入動作那麼實際上還是降低這個數值比較好這樣可以把尖峰的寫操作削平成多次寫操作設置方法如下
    echo >/proc/sys/vm/dirty_writeback_centisecs
    /proc/sys/vm/dirty_expire_centisecs
    這個參數聲明Linux內核寫緩沖區裡面的髒數據多舊了之後pdflush進程就開始考慮寫到磁盤中去單位是 /秒缺省是 也就是 秒的數據就算舊了將會刷新磁盤對於特別重載的寫操作來說這個值適當縮小也是好的但也不能縮小太多因為縮小太多也會導致IO提高太快建議設置為 也就是秒算舊
    echo >/proc/sys/vm/ dirty_expire_centisecs
    因此若沒有調整這些參數全部以缺省值而且關閉syncbin的話那麼最多丟失的數據是
    秒種（dirty_writeback_centisecs）之內的小於G的數據（dirty_background_ratioG/=G）
    當然實際上秒之內不太可能寫G的數據因此最壞就是秒鐘之內的數據丟失因此若要關閉syncbin又不想丟失太多數據的話可以通過調整dirty_writeback_centisecs這個參數如調整到（秒）這樣最多就丟秒鐘的數據又可以提高數據的寫能力
    Mysql裡還有一個參數可以調整提高數據庫的寫能力那就是
    innodb_flush_log_at_trx_commit
    這個參數默認是即每次事務Commit時都刷新日志以免數據丟失因為我們的系統允許丟失少量數據因此可以把innodb_flush_log_at_trx_commit設置為允許丟失一個事務的數據經測試發現可以提高%左右的性能
    另外對於文件系統的mount方式noatime方式也可以提高部分性能（數據庫專用的服務器一般是noatime）
    當數據有刪除更新操作後時間長後一般有碎片導致索引空間不緊湊占用更多的硬盤空間因此會導致查詢編碼解決辦法是定期執行下面的語句
    ALTER TABLE tbl_name ENGINE=INNODB
    另外若sql語句中有sort 和group by之類需要增大sort_buffer_size
    這個參數是每客戶端連接的當有sort/group查詢時會分配sort_buffer_size大小的內存因此若連接很多則要小心合適的值可以查看SHOW GLOBAL STATUS裡面Sort_merge_passes的信息以及Created_tmp_tables之類信息
    原文地址 ?play=reply&id=
From:http://tw.wingwit.com/Article/program/MySQL/201311/29481.html