共計(jì) 3417 個(gè)字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。
這篇文章給大家介紹 MySQL 數(shù)據(jù)庫(kù)優(yōu)化的方案與實(shí)踐是怎樣的，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。
最近一段時(shí)間，我們整理了一些關(guān)于 Percona，Linux，F(xiàn)lashcache，硬件設(shè)備的優(yōu)化經(jīng)驗(yàn)，分享給大家：
硬 件
1. 開(kāi)啟 BBWC
RAID 卡都有寫(xiě) cache(Battery Backed Write Cache)，寫(xiě) cache 對(duì) IO 性能的提升非常明顯，因?yàn)榈綦姇?huì)丟失數(shù)據(jù)，所以必須由電池提供支持。電池會(huì)定期充放電，一般為 90 天左右，當(dāng)發(fā)現(xiàn)電量低 于某個(gè)閥值時(shí)，會(huì)將寫(xiě) cache 策略從 writeback 置為 writethrough，相當(dāng)于寫(xiě) cache 會(huì)失效，這時(shí)如果系統(tǒng)有大量的 IO 操作，可能 會(huì)明顯感覺(jué)到 IO 響應(yīng)速度變慢。目前，新的 RAID 卡內(nèi)置了 flash 存儲(chǔ)，掉電后會(huì)將寫(xiě) cache 的數(shù)據(jù)寫(xiě)入 flash 中，這樣就可以保證數(shù)據(jù)永不丟 失，但依然需要電池的支持。
解決方案有兩種：
(1) 人工觸發(fā)充放電，可以選擇在業(yè)務(wù)低谷時(shí)做，降低對(duì)應(yīng)用的影響。
(2)設(shè)置寫(xiě) cache 策略為 force write back，即使電池失效，也保持寫(xiě) cache 策略為 writeback，這樣存在掉電后丟失數(shù)據(jù)的風(fēng)險(xiǎn)。
目前，有一些硬件廠家提供了電容供電的 RAID 卡，沒(méi)有電池充放電的問(wèn)題，可以聯(lián)系自己的硬件廠商。
2.RAID 卡配置
關(guān)閉讀 cache：RAID 卡上的 cache 容量有限，我們選擇 direct 方式讀取數(shù)據(jù)，從而忽略讀 cache。
關(guān)閉預(yù)讀：RAID 卡的預(yù)讀功能對(duì)于隨機(jī) IO 幾乎沒(méi)有任何提升，所以將預(yù)讀功能關(guān)閉。
關(guān)閉磁盤 cache：一般情況下，如果使用 RAID，系統(tǒng)會(huì)默認(rèn)關(guān)閉磁盤的 cache，也可以用命令強(qiáng)制關(guān)閉。
以上設(shè)置都可以通過(guò) RAID 卡的命令行來(lái)完成，比如 LSI 芯片的 RAID 卡使用 megacli 命令。
3. 開(kāi)啟 Fastpath 功能
Fastpath 是 LSI 的新特性，在 RAID 控制器為 SSD 做了了優(yōu)化，使用 fastpath 特性可以最大程度發(fā)揮出 SSD 的能力。如果使用 SSD 做 RAID 的方式，可以開(kāi)啟 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
開(kāi)啟 MSI 中斷，如果設(shè)備支持，則打開(kāi)。
options iomemory-vsl use_large_pcie_rx_buffer=1
打開(kāi) Large PCIE buffer，可能會(huì)提升性能。
操作系統(tǒng)
1.IO 調(diào)度算法
Linux 有四種 IO 調(diào)度算法：CFQ，Deadline，Anticipatory 和 NOOP，CFQ 是默認(rèn)的 IO 調(diào)度算法。完全隨機(jī)的訪問(wèn)環(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)的問(wèn)題，可以使用 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
數(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：打開(kāi) fast remove 特性，關(guān)閉機(jī)器時(shí)，無(wú)需將 cache 中的臟塊寫(xiě)入磁盤。
flashcache.reclaim_policy = 1：臟塊刷出策略，0：FIFO，1：LRU。
flashcache.dirty_thresh_pct = 90：flashcache 上每個(gè) hash set 上的臟塊閥值。
flashcache.cache_all = 1：cache 所有內(nèi)容，可以用黑名單過(guò)濾。
flashecache.write_merge = 1：打開(kāi)寫(xiě)入合并，提升寫(xiě)磁盤的性能。
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ù)文件讀寫(xiě)設(shè)置為 DirectIO，對(duì)于 Percona 可以將 log 和數(shù)據(jù)文件設(shè)置為 direct 方式讀寫(xiě)。但是我不確定這個(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è)置寫(xiě)線程數(shù)量為 16，提升寫(xiě)的能力。
innodb_fast_checksum = 1：開(kāi)啟 Fast checksum 特性。
監(jiān) 控
1.fusionio 監(jiān)控：fio-status 命令
Media status: Healthy; Reserves: 100.00%, warn at 10.00%
Thresholds: write-reduced: 96.00%, read-only: 94.00%
Lifetime data volumes:
Logical bytes written : 2,664,888,862,208
Logical bytes read : 171,877,629,608,448
Physical bytes written: 27,665,550,363,560
Physical bytes read : 223,382,659,085,448
2.flashcache 監(jiān)控：dmsetup status
read hit percent(99)
write hit percent(51)
dirty write hit percent(44)
關(guān)于 MySQL 數(shù)據(jù)庫(kù)優(yōu)化的方案與實(shí)踐是怎樣的就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。
