MySQL數據庫優化的方案與實踐
MySQL數據庫優化的方法有很多，本文我們主要從硬件、操作系統和數據庫本身三個方面來講述MySQL的優化方案，首先從硬件來說明：
硬 件
1.開啟BBWC
RAID卡都有寫cache(Battery Backed Write Cache)，寫cache對IO性能的提升非常明顯，因為掉電會丟失數據，所以必須由電池提供支持。電池會定期充放電，一般為90天左右，當發現電量低于某個閥值時，會將寫cache策略從writeback置為writethrough，相當于寫cache會失效，這時如果系統有大量的IO操作，可能會明顯感覺到IO響應速度變慢。目前，新的RAID卡內置了flash存儲，掉電后會將寫cache的數據寫入flash中，這樣就可以保證數據永不丟失，但依然需要電池的支持。
解決方案有兩種：
(1)人工觸發充放電，可以選擇在業務低谷時做，降低對應用的影響。
(2)設置寫cache策略為force write back，即使電池失效，也保持寫cache策略為writeback，這樣存在掉電后丟失數據的風險。
目前，有一些硬件廠家提供了電容供電的RAID卡，沒有電池充放電的問題，可以聯系自己的硬件廠商。
2.RAID卡配置
關閉讀cache：RAID卡上的cache容量有限，我們選擇direct方式讀取數據，從而忽略讀cache。
關閉預讀：RAID卡的預讀功能對于隨機IO幾乎沒有任何提升，所以將預讀功能關閉。
關閉磁盤cache：一般情況下，如果使用RAID，系統會默認關閉磁盤的cache，也可以用命令強制關閉。
以上設置都可以通過RAID卡的命令行來完成，比如LSI芯片的RAID卡使用megacli命令。
3.開啟Fastpath功能
Fastpath是LSI的新特性，在RAID控制器為SSD做了優化，使用fastpath特性可以***程度發揮出SSD的能力。如果使用SSD做RAID的方式，可以開啟fastpath功能。關于fastpath特性，可以從LSI官網下載資料，并咨詢自己的硬件提供商。
4.Fusionio參數調整
基本上，Fusionio無需做任何調整，下列三個參數可能會提升性能：
options iomemory-vsl use_workqueue=0
對于fusionio設備，忽略Linux IO調度，相當于使用NOOP。
options iomemory-vsl disable-msi=0
開啟MSI中斷，如果設備支持，則打開。
options iomemory-vsl use_large_pcie_rx_buffer=1
打開Large PCIE buffer，可能會提升性能。
#p#
操作系統
1.IO調度算法
Linux有四種IO調度算法：CFQ，Deadline，Anticipatory和NOOP，CFQ是默認的IO調度算法。完全隨機的訪問環境下，CFQ與Deadline，NOOP性能差異很小，但是一旦有大的連續IO，CFQ可能會造成小IO的響應延時增加，所以數據庫環境建議修改為deadline算法，表現更穩定。我們的環境統一使用deadline算法。
IO調度算法都是基于磁盤設計，所以減少磁頭移動是最重要的考慮因素之一，但是使用Flash存儲設備之后，不再需要考慮磁頭移動的問題，可以使用NOOP算法。NOOP的含義就是NonOperation，意味著不會做任何的IO優化，完全按照請求來FIFO的方式來處理IO。
減少預讀：/sys/block/sdb/queue/read_ahead_kb，默認128，調整為16。
增大隊列：/sys/block/sdb/queue/nr_requests，默認128，調整為512。
2.NUMA設置
單機單實例，建議關閉NUMA，關閉的方法有三種：
(1) 硬件層，在BIOS中設置關閉。
(2) OS內核，啟動時設置numa=off。
(3) 可以用numactl命令將內存分配策略修改為interleave(交叉)，有些硬件可以在BIOS中設置。
單機多實例，請參考：http://www.hellodb.net/2011/06/mysql_multi_instance.html。
3.文件系統設置
我們使用XFS文件系統，XFS有兩個設置：su(stripe size)和sw(stirpe width)，要根據硬件層RAID來設置這兩個參數，比如10塊盤做RAID10，條帶大小為64K，XFS設置為su=64K，sw=10。
xfs mount參數：defaults,rw,noatime,nodiratime,noikeep,nobarrier,allocsize=8M,attr2,largeio,inode64,swalloc
#p#
數據庫
1.flashcache參數
創建flashcache：flashcache_create -b 4k cachedev /dev/sdc /dev/sdb
指定flashcache的block大小與Percona的page大小相同。
Flashcache參數設置：
flashcache.fast_remove = 1：打開fast remove特性，關閉機器時，無需將cache中的臟塊寫入磁盤。
flashcache.reclaim_policy = 1：臟塊刷出策略，0：FIFO，1：LRU。
flashcache.dirty_thresh_pct = 90：flashcache上每個hash set上的臟塊閥值。
flashcache.cache_all = 1：cache所有內容，可以用黑名單過濾。
flashecache.write_merge = 1：打開寫入合并，提升寫磁盤的性能。
2.Percona參數
innodb_page_size：如果使用fusionio，4K的性能***;使用SAS磁盤，設置為8K。如果全表掃描很多，可以設置為16K。比較小的page size，可以提升cache的命中率。
innodb_adaptive_checkpoint：如果使用fusionio，設置為3，提高刷新頻率到0.1秒;使用SAS磁盤，設置為2，采用estimate方式刷新臟頁。
innodb_io_capacity：根據IOPS能力設置，使用fuionio可以設置10000以上。
innodb_flush_neighbor_pages = 0：針對fusionio或者SSD，因為隨機IO足夠好，所以關閉此功能。
innodb_flush_method=ALL_O_DIRECT：公版的MySQL只能將數據庫文件讀寫設置為DirectIO，對于Percona可以將log和數據文件設置為direct方式讀寫。但是我不確定這個參數對于innodb_flush_log_at_trx_commit的影響。
innodb_read_io_threads = 1：設置預讀線程設置為1，因為線性預讀的效果并不明顯，所以無需設置更大。
innodb_write_io_threads = 16：設置寫線程數量為16，提升寫的能力。
innodb_fast_checksum = 1：開啟Fast checksum特性。
以上就是MySQL數據庫優化方案，如想更多的了解MySQL數據庫請到：http://database.51cto.com/col/484/。
【編輯推薦】
- 淺談mysql在主從服務器中同步的實現
- MySQL數據庫新特性之存儲過程入門教程
- MySQL中創建及優化索引組織結構的思路
- 記MySQL使用UDF自動同步memcached的效率
- MySQL優化：使用慢查詢日志定位效率較低的SQL語句
