MySQL數(shù)據(jù)庫(kù)優(yōu)化的方法有很多，本文我們主要從硬件、操作系統(tǒng)和數(shù)據(jù)庫(kù)本身三個(gè)方面來(lái)講述MySQL的優(yōu)化方案，首先從硬件來(lái)說(shuō)明：

硬 件

1.開啟BBWC

RAID卡都有寫cache(Battery Backed Write Cache)，寫cache對(duì)IO性能的提升非常明顯，因?yàn)榈綦姇?huì)丟失數(shù)據(jù)，所以必須由電池提供支持。電池會(huì)定期充放電，一般為90天左右，當(dāng)發(fā)現(xiàn)電量低于某個(gè)閥值時(shí)，會(huì)將寫cache策略從writeback置為writethrough，相當(dāng)于寫cache會(huì)失效，這時(shí)如果系統(tǒng)有大量的IO操作，可能會(huì)明顯感覺到IO響應(yīng)速度變慢。目前，新的RAID卡內(nèi)置了flash存儲(chǔ)，掉電后會(huì)將寫cache的數(shù)據(jù)寫入flash中，這樣就可以保證數(shù)據(jù)永不丟失，但依然需要電池的支持。

解決方案有兩種：

(1)人工觸發(fā)充放電，可以選擇在業(yè)務(wù)低谷時(shí)做，降低對(duì)應(yīng)用的影響。

(2)設(shè)置寫cache策略為force write back，即使電池失效，也保持寫cache策略為writeback，這樣存在掉電后丟失數(shù)據(jù)的風(fēng)險(xiǎn)。

目前，有一些硬件廠家提供了電容供電的RAID卡，沒有電池充放電的問題，可以聯(lián)系自己的硬件廠商。

2.RAID卡配置

關(guān)閉讀cache：RAID卡上的cache容量有限，我們選擇direct方式讀取數(shù)據(jù)，從而忽略讀cache。

關(guān)閉預(yù)讀：RAID卡的預(yù)讀功能對(duì)于隨機(jī)IO幾乎沒有任何提升，所以將預(yù)讀功能關(guān)閉。

關(guān)閉磁盤cache：一般情況下，如果使用RAID，系統(tǒng)會(huì)默認(rèn)關(guān)閉磁盤的cache，也可以用命令強(qiáng)制關(guān)閉。

以上設(shè)置都可以通過RAID卡的命令行來(lái)完成，比如LSI芯片的RAID卡使用megacli命令。

3.開啟Fastpath功能

Fastpath是LSI的新特性，在RAID控制器為SSD做了優(yōu)化，使用fastpath特性可以***程度發(fā)揮出SSD的能力。如果使用SSD做RAID的方式，可以開啟fastpath功能。關(guān)于fastpath特性，可以從LSI官網(wǎng)下載資料，并咨詢自己的硬件提供商。

4.Fusionio參數(shù)調(diào)整

基本上，F(xiàn)usionio無(wú)需做任何調(diào)整，下列三個(gè)參數(shù)可能會(huì)提升性能：

options iomemory-vsl use_workqueue=0

對(duì)于fusionio設(shè)備，忽略Linux IO調(diào)度，相當(dāng)于使用NOOP。

options iomemory-vsl disable-msi=0

開啟MSI中斷，如果設(shè)備支持，則打開。

options iomemory-vsl use_large_pcie_rx_buffer=1

打開Large PCIE buffer，可能會(huì)提升性能。

#p#

操作系統(tǒng)

1.IO調(diào)度算法

Linux有四種IO調(diào)度算法：CFQ，Deadline，Anticipatory和NOOP，CFQ是默認(rèn)的IO調(diào)度算法。完全隨機(jī)的訪問環(huán)境下，CFQ與Deadline，NOOP性能差異很小，但是一旦有大的連續(xù)IO，CFQ可能會(huì)造成小IO的響應(yīng)延時(shí)增加，所以數(shù)據(jù)庫(kù)環(huán)境建議修改為deadline算法，表現(xiàn)更穩(wěn)定。我們的環(huán)境統(tǒng)一使用deadline算法。

IO調(diào)度算法都是基于磁盤設(shè)計(jì)，所以減少磁頭移動(dòng)是最重要的考慮因素之一，但是使用Flash存儲(chǔ)設(shè)備之后，不再需要考慮磁頭移動(dòng)的問題，可以使用NOOP算法。NOOP的含義就是NonOperation，意味著不會(huì)做任何的IO優(yōu)化，完全按照請(qǐng)求來(lái)FIFO的方式來(lái)處理IO。

減少預(yù)讀：/sys/block/sdb/queue/read_ahead_kb，默認(rèn)128，調(diào)整為16。

增大隊(duì)列：/sys/block/sdb/queue/nr_requests，默認(rèn)128，調(diào)整為512。

2.NUMA設(shè)置

單機(jī)單實(shí)例，建議關(guān)閉NUMA，關(guān)閉的方法有三種：

(1) 硬件層，在BIOS中設(shè)置關(guān)閉。

(2) OS內(nèi)核，啟動(dòng)時(shí)設(shè)置numa=off。

(3) 可以用numactl命令將內(nèi)存分配策略修改為interleave(交叉)，有些硬件可以在BIOS中設(shè)置。

單機(jī)多實(shí)例，請(qǐng)參考：http://www.hellodb.net/2011/06/mysql_multi_instance.html。

3.文件系統(tǒng)設(shè)置

我們使用XFS文件系統(tǒng)，XFS有兩個(gè)設(shè)置：su(stripe size)和sw(stirpe width)，要根據(jù)硬件層RAID來(lái)設(shè)置這兩個(gè)參數(shù)，比如10塊盤做RAID10，條帶大小為64K，XFS設(shè)置為su=64K，sw=10。

xfs mount參數(shù)：defaults,rw,noatime,nodiratime,noikeep,nobarrier,allocsize=8M,attr2,largeio,inode64,swalloc

#p#

數(shù)據(jù)庫(kù)

1.flashcache參數(shù)

創(chuàng)建flashcache：flashcache_create -b 4k cachedev /dev/sdc /dev/sdb

指定flashcache的block大小與Percona的page大小相同。

Flashcache參數(shù)設(shè)置：

flashcache.fast_remove = 1：打開fast remove特性，關(guān)閉機(jī)器時(shí)，無(wú)需將cache中的臟塊寫入磁盤。

flashcache.reclaim_policy = 1：臟塊刷出策略，0：FIFO，1：LRU。

flashcache.dirty_thresh_pct = 90：flashcache上每個(gè)hash set上的臟塊閥值。

flashcache.cache_all = 1：cache所有內(nèi)容，可以用黑名單過濾。

flashecache.write_merge = 1：打開寫入合并，提升寫磁盤的性能。

2.Percona參數(shù)

innodb_page_size：如果使用fusionio，4K的性能***;使用SAS磁盤，設(shè)置為8K。如果全表掃描很多，可以設(shè)置為16K。比較小的page size，可以提升cache的命中率。

innodb_adaptive_checkpoint：如果使用fusionio，設(shè)置為3，提高刷新頻率到0.1秒;使用SAS磁盤，設(shè)置為2，采用estimate方式刷新臟頁(yè)。

innodb_io_capacity：根據(jù)IOPS能力設(shè)置，使用fuionio可以設(shè)置10000以上。

innodb_flush_neighbor_pages = 0：針對(duì)fusionio或者SSD，因?yàn)殡S機(jī)IO足夠好，所以關(guān)閉此功能。

innodb_flush_method=ALL_O_DIRECT：公版的MySQL只能將數(shù)據(jù)庫(kù)文件讀寫設(shè)置為DirectIO，對(duì)于Percona可以將log和數(shù)據(jù)文件設(shè)置為direct方式讀寫。但是我不確定這個(gè)參數(shù)對(duì)于innodb_flush_log_at_trx_commit的影響。

innodb_read_io_threads = 1：設(shè)置預(yù)讀線程設(shè)置為1，因?yàn)榫€性預(yù)讀的效果并不明顯，所以無(wú)需設(shè)置更大。

innodb_write_io_threads = 16：設(shè)置寫線程數(shù)量為16，提升寫的能力。

innodb_fast_checksum = 1：開啟Fast checksum特性。

以上就是MySQL數(shù)據(jù)庫(kù)優(yōu)化方案，如想更多的了解MySQL數(shù)據(jù)庫(kù)請(qǐng)到：http://database.51cto.com/col/484/。

【編輯推薦】

1. 淺談mysql在主從服務(wù)器中同步的實(shí)現(xiàn)
2. MySQL數(shù)據(jù)庫(kù)新特性之存儲(chǔ)過程入門教程
3. MySQL中創(chuàng)建及優(yōu)化索引組織結(jié)構(gòu)的思路
4. 記MySQL使用UDF自動(dòng)同步memcached的效率
5. MySQL優(yōu)化：使用慢查詢?nèi)罩径ㄎ恍瘦^低的SQL語(yǔ)句