MySQL版《一周一議》之如何優(yōu)化mysql配置參數(shù)（積分已轉賬-2012-12-19）

chinafenghao

20積分已轉賬，請注意查收！

2012年12月11日
感謝大家對上周話題的關注。
現(xiàn)在隆重宣布上周獲得《Cassandra實戰(zhàn)指南》的用戶是：huzi1986
另外cenalulu和xike2002各獲得200積分獎勵。
其他參與討論的用戶到將至少獲得20積分,積分是需要管理員手工加，加上后會發(fā)站內(nèi)信的。

本周的話題也是偏技術方面的。
對于所有類型的數(shù)據(jù)庫優(yōu)化來說，優(yōu)化的地方無非是,硬件,系統(tǒng),數(shù)據(jù)庫軟件配置,數(shù)據(jù)庫架構,程序架構,sql語句這些方面下工夫。但是這些優(yōu)化方案中成本最低，效果最好的是數(shù)據(jù)庫軟件配置參數(shù)優(yōu)化,也就是通過調(diào)整mysql的參數(shù)來達到數(shù)據(jù)庫優(yōu)化的目的,不需要任何應用層的介入，也不會增加任何的硬件成本。所以mysql配置參數(shù)優(yōu)化是刀中之王-------不是屠龍寶刀,是菜刀,居家\旅行必備工具。

今天我們的話題就是mysql 配置參數(shù)優(yōu)化方案。

1、你認為最重要的,對mysql性能影響最大的幾個配置參數(shù)。

2、針對OLTP環(huán)境+SSD硬盤,你的mysql優(yōu)化參數(shù)。

3、貼出你的配置文件,附帶業(yè)務環(huán)境和硬件配置,重點字段的備注。
例如:
業(yè)務環(huán)境:3D MMORPG
硬件環(huán)境：DELL R610  Intel(R) Xeon(R) CPU E5620  @ 2.40GHz *2  32GRAM
配置文件:


活動獎勵:
1、每周會評選一位最活躍的用戶，有一本mysql相關的書籍送出,本周送出的書籍是《PHP與MySQL權威指南》。

2、由于是話題討論，所以每位參加者都能獲得適當?shù)姆e分獎勵。最低20分，最高不限,^_^想要賺分的朋友也可以來湊熱鬧喲。

chinafenghao

我個人覺得對數(shù)據(jù)庫性能影響最大的參數(shù)有以下幾個:
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 2
sync_binlog=1
query_cache_size = 512M


大家繼續(xù)補充吧。

hunter_search

chinafenghao 發(fā)表于 2012-12-11 16:58
我個人覺得對數(shù)據(jù)庫性能影響最大的參數(shù)有以下幾個:
innodb_buffer_pool_size = 4G
innodb_flush_log_at_t ...

隨便聊天一下

innodb_flush_log_at_trx_commit  0 ,1, 2 這個就無需過度闡釋，論壇討論N次了
innodb_buffer_pool_size  這個雖說越大越好，但注意大內(nèi)存下numa問題, 避免出現(xiàn)內(nèi)存還有，但出現(xiàn)swap
innodb_flush_method  fsync【默認】 , o_direct , o_dsync,  這些參數(shù)設置后，會對事務日志IO, 數(shù)據(jù)刷盤的IO,產(chǎn)生影響,這些參數(shù)都跟linux kernel的實現(xiàn)有關系, 實際生產(chǎn)環(huán)境中，o_direct 跟默認模式的性能相差沒有想象中那么大.

另外將ibdata 和 ib_logfile 分別放到不同的磁盤，進一步提升 IO的性能

另外跟IO相關的還有
innodb_io_capacity   默認是200頁
innodb_log_file_size  這個太小需要計算合適大小, 如果是空間不夠【達到某個百分比】，到checkpoint時候，很可能阻塞,嚴重影響性能
另外sync_binlog = 1  也就是說，每向二進制日志文件寫入1條SQL或1個事務就需要flush一次磁盤, 這個雖然安全，性能應該不高, 生產(chǎn)環(huán)境中，我們是默認的0，
  樓下繼續(xù)
   

lxw2016

ssd  硬盤沒有在生產(chǎn)中用，這個不好說。
我們公司在生產(chǎn)中除了配置好LS參數(shù)外，硬盤用的是raid10，以便提高IO性能，另外如果數(shù)據(jù)量較大，一定要做讀寫分離。

seesea2517

我們主要用的是 innodb，這個參數(shù)是首要配置的：innodb_flush_log_at_trx_commit
然后我們必然要配置的是：skip-name-resolve

3樓講的很好了，都沒什么特別要說的了。
推薦大家一個工具吧：mysqltuner。

chinafenghao

@lxw2016@hunter_search
SSD的話
nnodb_io_capacity 這個參數(shù)是必須調(diào)整。

chinafenghao

https://tools.percona.com/ 給大家分享一個percona的工具吧，可以幫助你生成一個mysql配置文件。

chinafenghao

@seesea2517
這個工具的結果挺直觀的。很棒

action08

seesea2517 發(fā)表于 2012-12-12 10:18
我們主要用的是 innodb，這個參數(shù)是首要配置的：innodb_flush_log_at_trx_commit
然后我們必然要配置的是： ...

這個工具可靠度有限，就跟相親算命軟件一樣，還要注冊{:3_197:}

seesea2517

回復 9# action08


    不會吧，這個工具就是一個腳本，隨處可以下載的。