實(shí)例詳解Linux下MySQL數(shù)據(jù)庫(kù)性能調(diào)優(yōu)方法
以下的環(huán)境具備一定的代表性，可以說(shuō)是中小企業(yè)一般配置和工作環(huán)境。希望通過(guò)本文能讓大家理解Linux下MySQL數(shù)據(jù)庫(kù)性能調(diào)優(yōu)方法。51CTO向您推薦《MySQL數(shù)據(jù)庫(kù)入門與精通教程》。
硬件準(zhǔn)備環(huán)境：
硬盤： 16塊 SAS 15K RAID5 帶512MCache
CPU： AMD 4核兩顆
內(nèi)存： 16G
軟件環(huán)境：
操作系統(tǒng)： RedHat AS4.6 2.6.9-67.ELsmp MySQL: 5.0
下面分別根據(jù)不同場(chǎng)景進(jìn)行性能調(diào)優(yōu)：
使用場(chǎng)景A： 寫操作（插入/查詢/刪除）密集，而且數(shù)據(jù)容量遠(yuǎn)遠(yuǎn)超硬件準(zhǔn)備環(huán)境：
硬盤： 16塊 SAS 15K RAID5 帶512MCache
CPU： AMD 4核兩顆
內(nèi)存： 16G
軟件環(huán)境
操作系統(tǒng)： RedHat AS4.6 2.6.9-67.ELsmp
MySQL: 5.0
下面分別根據(jù)不同場(chǎng)景進(jìn)行性能調(diào)優(yōu)：
使用場(chǎng)景A：
寫操作（插入/查詢/刪除）密集，而且數(shù)據(jù)容量遠(yuǎn)遠(yuǎn)超過(guò)內(nèi)存的大?。ㄗ钚∈?00G以上，甚至更大到1.5T以上）， 每秒需要以千為單位的事務(wù)操作
數(shù)據(jù)可靠性要求不高，允許丟幾條記錄（硬件出故障時(shí)）
優(yōu)化方式：
在這種情況下，數(shù)據(jù)庫(kù)的Cache基本上命中率是非常低的，對(duì)數(shù)據(jù)庫(kù)的操作基本上就是對(duì)磁盤進(jìn)行操作， 因此側(cè)重點(diǎn)應(yīng)該是對(duì)磁盤相關(guān)優(yōu)化。
首先是考慮數(shù)據(jù)庫(kù)存儲(chǔ)引擎，由于涉及插入/刪除/查詢等操作，而且要支持事務(wù)，因此考慮使用InnoDB存儲(chǔ)引擎，不考慮MyIsam原因是該存儲(chǔ)引擎是表級(jí)鎖，有數(shù)據(jù)刪除時(shí)會(huì)非常慢。
設(shè)置合適的MySQL 參數(shù)，由于有16G內(nèi)存，因此參數(shù)設(shè)置如下
- innodb_buffer_pool_size=8G
- innodb_additional_mem_pool_size = 800M,
- innodb_log_buffer_size = 4M
- innodb_log_file_size = 500M
- innodb_log_files_in_group = 4
- log-bin
因?yàn)槭褂弥鱾鋸?fù)制，因此需要打開(kāi)Log-Bin，這里就涉及一個(gè)sync-bin的概念，缺省情況下，sync-bin 在MySQL中是關(guān)閉的，但存在一個(gè)風(fēng)險(xiǎn)，因?yàn)閷懭罩緵](méi)有刷新到硬盤中去的話，日志是寫在操作系統(tǒng)的文件系統(tǒng)里的Cache中，這樣若機(jī)器斷電，那么日志信息將部分丟失。為了減少數(shù)據(jù)丟失，我們測(cè)試了sync-bin=1,sync-bin=5和sync-bin=20，三種情況，sync-bin=1的情況下，丟失數(shù)據(jù)最壞概率是丟一個(gè)事務(wù)的數(shù)據(jù)，但性能非常差；設(shè)置sync-bin=5的情況下，性能可以提高1倍左右；設(shè)置sync-bin=20時(shí)，性能比sync-bin=5又可以提高1倍，設(shè)置大于20的值，基本上相差不大，性能提高不了多少，最壞20個(gè)事務(wù)數(shù)據(jù)丟失是在我們的允許范圍之內(nèi)，因此設(shè)置20是個(gè)合理的值，這個(gè)值具體取決于你的系統(tǒng)能夠最壞允許丟少的事務(wù)數(shù)據(jù)。
若你的系統(tǒng)對(duì)數(shù)據(jù)丟失不于考慮，可以關(guān)閉sync-bin,這時(shí)數(shù)據(jù)刷新到硬盤完全取決于操作系統(tǒng)的配置，相關(guān)的配置參數(shù)有如下：
- /proc/sys/vm/dirty_ratio
這 個(gè)參數(shù)控制一個(gè)進(jìn)程在文件系統(tǒng)中的文件系統(tǒng)寫緩沖區(qū)的大小，單位是百分比，表示系統(tǒng)內(nèi)存的百分比，表示當(dāng)一個(gè)進(jìn)程中寫緩沖使用到系統(tǒng)內(nèi)存多少的時(shí)候，再有 磁盤寫操作時(shí)開(kāi)始向磁盤寫出數(shù)據(jù)。增大之會(huì)使用更多系統(tǒng)內(nèi)存用于磁盤寫緩沖，也可以極大提高系統(tǒng)的寫性能。但是，當(dāng)你需要持續(xù)、恒定的寫入場(chǎng)合時(shí)，應(yīng)該降 低其數(shù)值，一般缺省是 40。更新方法
echo 30 >/proc/sys/vm/dirty_ratio (或則修改/etc/sysctl.conf文件，增加sys.vm.dirty_ratio=30 重起機(jī)器)
- /proc/sys/vm/dirty_background_ratio
這個(gè)參數(shù)控制文件系統(tǒng)的pdflush進(jìn)程，在何時(shí)刷新磁盤。單位是百分比，表示系統(tǒng)總內(nèi)存的百分比，意思是當(dāng)磁盤的臟數(shù)據(jù)緩沖到系統(tǒng)內(nèi)存多少的時(shí)候，pdflush開(kāi)始把臟數(shù)據(jù)刷新到磁盤。增大會(huì)使用更多系統(tǒng)內(nèi)存用于磁盤寫緩沖，也可以極大提高系統(tǒng)的寫性能。但是，當(dāng)你需要持續(xù)、恒定的寫入場(chǎng)合時(shí)，應(yīng)該降低其數(shù)值，一般缺省是10。
- /proc/sys/vm/dirty_writeback_centisecs
Pdflush寫后臺(tái)進(jìn)程每隔多久被喚醒并執(zhí)行把臟數(shù)據(jù)寫出到硬盤。單位是 1/100 秒。缺省數(shù)值是500，也就是 5 秒。如果你的系統(tǒng)是持續(xù)地寫入動(dòng)作，那么實(shí)際上還是降低這個(gè)數(shù)值比較好，這樣可以把尖峰的寫操作削平成多次寫操作。設(shè)置方法如下：
- echo 200 >/proc/sys/vm/dirty_writeback_centisecs
- /proc/sys/vm/dirty_expire_centisecs
這個(gè)參數(shù)聲明Linux內(nèi)核寫緩沖區(qū)里面的臟數(shù)據(jù)多“舊”了之后，pdflush進(jìn)程就開(kāi)始考慮寫到磁盤中去。單位是 1/100秒。缺省是 30000，也就是 30 秒的數(shù)據(jù)就算舊了，將會(huì)刷新磁盤。對(duì)于特別重載的寫操作來(lái)說(shuō)，這個(gè)值適當(dāng)縮小也是好的，但也不能縮小太多，因?yàn)榭s小太多也會(huì)導(dǎo)致IO提高太快。建議設(shè)置為 1500，也就是15秒算舊。
- echo 1500 >/proc/sys/vm/ dirty_expire_centisecs
因此若沒(méi)有調(diào)整這些參數(shù)，全部以缺省值，而且關(guān)閉sync-bin的話，那么最多丟失的數(shù)據(jù)是：
5秒種（dirty_writeback_centisecs）之內(nèi)的，小于1.6G的數(shù)據(jù)（dirty_background_ratio，16G/10=1.6G）
當(dāng)然，實(shí)際上5秒之內(nèi)不太可能寫1.6G的數(shù)據(jù)，因此最壞就是5秒鐘之內(nèi)的數(shù)據(jù)丟失。因此若要關(guān)閉sync-bin，又不想丟失太多數(shù)據(jù)的話，可以通過(guò)調(diào)整dirty_writeback_centisecs這個(gè)參數(shù)，如調(diào)整到200（2秒），這樣最多就丟2秒鐘的數(shù)據(jù)。又可以提高數(shù)據(jù)的寫能力。
MySQL里還有一個(gè)參數(shù)可以調(diào)整，提高數(shù)據(jù)庫(kù)的寫能力，那就是
- innodb_flush_log_at_trx_commit
這個(gè)參數(shù)默認(rèn)是1，即每次事務(wù)Commit時(shí)，都刷新日志，以免數(shù)據(jù)丟失。因?yàn)槲覀兊南到y(tǒng)允許丟失少量數(shù)據(jù)，因此可以把innodb_flush_log_at_trx_commit設(shè)置為2，允許丟失一個(gè)事務(wù)的數(shù)據(jù)，經(jīng)測(cè)試，發(fā)現(xiàn)2可以提高25%左右的性能。
另外對(duì)于文件系統(tǒng)的mount方式，noatime方式也可以提高部分性能（數(shù)據(jù)庫(kù)專用的服務(wù)器，一般是noatime）
當(dāng)數(shù)據(jù)有刪除更新操作后，時(shí)間長(zhǎng)后一般有碎片，導(dǎo)致索引空間不緊湊，占用更多的硬盤空間，因此會(huì)導(dǎo)致查詢編碼，解決辦法是定期執(zhí)行下面的語(yǔ)句：
- ALTER TABLE tbl_name ENGINE=INNODB
另外若sql語(yǔ)句中有sort 和group by之類,需要增大sort_buffer_size
這個(gè)參數(shù) 是每客戶端連接的,當(dāng)有sort/group查詢時(shí),會(huì)分配sort_buffer_size大小的內(nèi)存,因此若連接很多,則要小心;合適的值可以查看 SHOW GLOBAL STATUS里面Sort_merge_passes的信息以及Created_tmp_tables之類信息。
【編輯推薦】
