MySQL數(shù)據(jù)庫優(yōu)化的方案與實踐是怎樣的

155次閱讀

共計 3417 個字符，預計需要花費 9 分鐘才能閱讀完成。

這篇文章給大家介紹 MySQL 數(shù)據(jù)庫優(yōu)化的方案與實踐是怎樣的，內(nèi)容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

最近一段時間，我們整理了一些關(guān)于 Percona，Linux，F(xiàn)lashcache，硬件設(shè)備的優(yōu)化經(jīng)驗，分享給大家：
硬件
1. 開啟 BBWC
RAID 卡都有寫 cache(Battery Backed Write Cache)，寫 cache 對 IO 性能的提升非常明顯，因為掉電會丟失數(shù)據(jù)，所以必須由電池提供支持。電池會定期充放電，一般為 90 天左右，當發(fā)現(xiàn)電量低于某個閥值時，會將寫 cache 策略從 writeback 置為 writethrough，相當于寫 cache 會失效，這時如果系統(tǒng)有大量的 IO 操作，可能會明顯感覺到 IO 響應(yīng)速度變慢。目前，新的 RAID 卡內(nèi)置了 flash 存儲，掉電后會將寫 cache 的數(shù)據(jù)寫入 flash 中，這樣就可以保證數(shù)據(jù)永不丟失，但依然需要電池的支持。
解決方案有兩種：
(1) 人工觸發(fā)充放電，可以選擇在業(yè)務(wù)低谷時做，降低對應(yīng)用的影響。
(2)設(shè)置寫 cache 策略為 force write back，即使電池失效，也保持寫 cache 策略為 writeback，這樣存在掉電后丟失數(shù)據(jù)的風險。
目前，有一些硬件廠家提供了電容供電的 RAID 卡，沒有電池充放電的問題，可以聯(lián)系自己的硬件廠商。
2.RAID 卡配置
關(guān)閉讀 cache：RAID 卡上的 cache 容量有限，我們選擇 direct 方式讀取數(shù)據(jù)，從而忽略讀 cache。
關(guān)閉預讀：RAID 卡的預讀功能對于隨機 IO 幾乎沒有任何提升，所以將預讀功能關(guān)閉。
關(guān)閉磁盤 cache：一般情況下，如果使用 RAID，系統(tǒng)會默認關(guān)閉磁盤的 cache，也可以用命令強制關(guān)閉。
以上設(shè)置都可以通過 RAID 卡的命令行來完成，比如 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 無需做任何調(diào)整，下列三個參數(shù)可能會提升性能：
options iomemory-vsl use_workqueue=0
對于 fusionio 設(shè)備，忽略 Linux IO 調(diào)度，相當于使用 NOOP。
options iomemory-vsl disable-msi=0
開啟 MSI 中斷，如果設(shè)備支持，則打開。
options iomemory-vsl use_large_pcie_rx_buffer=1
打開 Large PCIE buffer，可能會提升性能。

操作系統(tǒng)
1.IO 調(diào)度算法
Linux 有四種 IO 調(diào)度算法：CFQ，Deadline，Anticipatory 和 NOOP，CFQ 是默認的 IO 調(diào)度算法。完全隨機的訪問環(huán)境下，CFQ 與 Deadline，NOOP 性能差異很小，但是一旦有大的連續(xù) IO，CFQ 可能會造成小 IO 的響應(yīng)延時增加，所以數(shù)據(jù)庫環(huán)境建議修改為 deadline 算法，表現(xiàn)更穩(wěn)定。我們的環(huán)境統(tǒng)一使用 deadline 算法。
IO 調(diào)度算法都是基于磁盤設(shè)計，所以減少磁頭移動是最重要的考慮因素之一，但是使用 Flash 存儲設(shè)備之后，不再需要考慮磁頭移動的問題，可以使用 NOOP 算法。NOOP 的含義就是 NonOperation，意味著不會做任何的 IO 優(yōu)化，完全按照請求來 FIFO 的方式來處理 IO。
減少預讀：/sys/block/sdb/queue/read_ahead_kb，默認 128，調(diào)整為 16。
增大隊列：/sys/block/sdb/queue/nr_requests，默認 128，調(diào)整為 512。
2.NUMA 設(shè)置
單機單實例，建議關(guān)閉 NUMA，關(guān)閉的方法有三種：
(1) 硬件層，在 BIOS 中設(shè)置關(guān)閉。
(2) OS 內(nèi)核，啟動時設(shè)置 numa=off。
(3) 可以用 numactl 命令將內(nèi)存分配策略修改為 interleave(交叉)，有些硬件可以在 BIOS 中設(shè)置。
單機多實例，請參考：http://www.hellodb.net/2011/06/mysql_multi_instance.html
3. 文件系統(tǒng)設(shè)置
我們使用 XFS 文件系統(tǒng)，XFS 有兩個設(shè)置：su(stripe size) 和 sw(stirpe width)，要根據(jù)硬件層 RAID 來設(shè)置這兩個參數(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ù)庫
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)閉機器時，無需將 cache 中的臟塊寫入磁盤。
flashcache.reclaim_policy = 1：臟塊刷出策略，0：FIFO，1：LRU。
flashcache.dirty_thresh_pct = 90：flashcache 上每個 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 方式刷新臟頁。
innodb_io_capacity：根據(jù) IOPS 能力設(shè)置，使用 fuionio 可以設(shè)置 10000 以上。
innodb_flush_neighbor_pages = 0：針對 fusionio 或者 SSD，因為隨機 IO 足夠好，所以關(guān)閉此功能。
innodb_flush_method=ALL_O_DIRECT：公版的 MySQL 只能將數(shù)據(jù)庫文件讀寫設(shè)置為 DirectIO，對于 Percona 可以將 log 和數(shù)據(jù)文件設(shè)置為 direct 方式讀寫。但是我不確定這個參數(shù)對于 innodb_flush_log_at_trx_commit 的影響。
innodb_read_io_threads = 1：設(shè)置預讀線程設(shè)置為 1，因為線性預讀的效果并不明顯，所以無需設(shè)置更大。
innodb_write_io_threads = 16：設(shè)置寫線程數(shù)量為 16，提升寫的能力。
innodb_fast_checksum = 1：開啟 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ù)庫優(yōu)化的方案與實踐是怎樣的就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

正文完