目錄

• 一.數(shù)據(jù)庫(kù)服務(wù)器配置
• 二.CPU的優(yōu)化
• 三.內(nèi)存的優(yōu)化
• 四.IO的優(yōu)化
• 五.連接的優(yōu)化
• 六.數(shù)據(jù)一致性的優(yōu)化

一.數(shù)據(jù)庫(kù)服務(wù)器配置

CPU：48C
內(nèi)存：128G
DISK：3.2TSSD

二.CPU的優(yōu)化

innodb_thread_concurrency=32
表示SQL經(jīng)過(guò)解析后，允許同時(shí)有32個(gè)線程去innodb引擎取數(shù)據(jù)，如果超過(guò)32個(gè)，則需要排隊(duì)；
值太大會(huì)產(chǎn)生熱點(diǎn)數(shù)據(jù)，global鎖爭(zhēng)用嚴(yán)重，影響性能

三.內(nèi)存的優(yōu)化

query_cache_type=0
query_cache_size=0
緩存查詢(xún)，5.6默認(rèn)關(guān)閉，在應(yīng)用層實(shí)現(xiàn)，比如MC、redis

四.IO的優(yōu)化

1.innodb_buffer_pool_size=50G

類(lèi)似SGA，衡量總的IO處理能力上限，一般為物理內(nèi)存的60%-70%，這里128G部署2個(gè)實(shí)例，剩下28G分配給OS和mysql連接等使用

2.innodb_io_capacity=20000

每秒后臺(tái)進(jìn)程處理IO數(shù)據(jù)的上限，一般為IO QPS總能力的75%
比如SSD是3W QPS，75%大概是2W，雙實(shí)例減半，為1W，幾個(gè)實(shí)例除以幾

3.innodb_log_files_in_group=4

幾個(gè)innodb redo log日志組

4.innodb_log_file_size=1000M

redo log日志循化寫(xiě)，生產(chǎn)必須大于1G，
如果太小，那么innodb_buffer_pool_size的數(shù)據(jù)有可能不能及時(shí)寫(xiě)入redo log造成halt等待；查看是否夠用？如果value大于0，則提高改參數(shù)或者增加日志組

root@master 12:51:  [(none)]> show global status like "%log_wait%";+------------------+-------+| Variable_name    | Value |+------------------+-------+| Innodb_log_waits | 0     |+------------------+-------+1 row in set (0.00 sec)root@master 12:54:  [(none)]> show global status like "%Innodb_os_log_written%";+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Innodb_os_log_written | 1024  |+-----------------------+-------+1 row in set (0.00 sec)#此參數(shù)大小可作為設(shè)置日志文件size大小參考值

5.innodb_flush_method=O_DIRECT

SSD直接寫(xiě)硬盤(pán)，不寫(xiě)硬盤(pán)cache，也就是繞過(guò)fsync()刷硬盤(pán)

6.innodb_max_dirty_pages_pct=50

當(dāng)臟塊達(dá)到innodb_buffer_pool_size的50%時(shí)，觸發(fā)檢查點(diǎn)，寫(xiě)磁盤(pán)

7.innodb_file_per_table=on

一表一文件，可以避免共享表空間的IO競(jìng)爭(zhēng)

8.innodb_page_size=4k

默認(rèn)是16K，這里是SSD，寫(xiě)SSD前要擦除，擦除單位是extent，一個(gè)extent有128個(gè)page組成，16128 > 4128 ，效率會(huì)更高

9.innodb_flush_neighbors=0

SSD設(shè)置為0，SAS打開(kāi)刷新相鄰塊，隨機(jī)訪問(wèn)轉(zhuǎn)換為順序訪問(wèn)

五.連接的優(yōu)化

1.back_log=300

默認(rèn)是50，TCP/IP的連接數(shù)量，一個(gè)連接占用256KB內(nèi)存，最大是64MB，256 * 300 =75MB內(nèi)存
和三次握手有關(guān)系

syn_queue取64和tcp_max_sync_backlog最大者，默認(rèn)是1024，當(dāng)瞬時(shí)很多連接進(jìn)來(lái)這個(gè)參數(shù)會(huì)進(jìn)行限制，否則太大容易消耗資源
accept queue取back_log和somaxconn最小者，用來(lái)防止丟包，當(dāng)瞬時(shí)很多連接進(jìn)來(lái)達(dá)到上限后，后來(lái)連接將超時(shí)觸發(fā)重傳機(jī)制
當(dāng)有3000個(gè)連接進(jìn)來(lái)，將隊(duì)列accept queue占滿(mǎn)，應(yīng)用還沒(méi)來(lái)得及將請(qǐng)求從隊(duì)列中取出，剩下的2700個(gè)連接將被拒絕，每取走一個(gè)請(qǐng)求（一個(gè)連接，mysql一個(gè)線程一個(gè)連接），將創(chuàng)建一個(gè)thread線程

net.ipv4.tcp_max_sync_backlog= 8192  類(lèi)似活動(dòng)場(chǎng)所sync接收隊(duì)列的長(zhǎng)度，默認(rèn)是1024，當(dāng)mysql在很短時(shí)間內(nèi)得到很多的請(qǐng)求，需要增加，太大會(huì)消耗資源太小的話(huà)會(huì)在show processlist出現(xiàn)未認(rèn)證錯(cuò)誤net.core.somaxconn=1024   類(lèi)似活動(dòng)場(chǎng)所中的座位數(shù)盡可能防止丟包，超過(guò)這個(gè)值會(huì)觸發(fā)超時(shí)或者重傳，限制在net.ipv4.ip_local_port_range這個(gè)范圍之內(nèi)

2.max_connections=3000

連接的創(chuàng)建和銷(xiāo)毀都需要系統(tǒng)資源，比如內(nèi)存、文件句柄
業(yè)務(wù)說(shuō)的支持多少并發(fā)，指的是每秒請(qǐng)求數(shù)，也就是QPS
同一時(shí)刻并行的SQL由innodb_thread_concurrency決定，最大不能超過(guò)該值
如果一個(gè)用戶(hù)的請(qǐng)求數(shù)據(jù)超過(guò)64MB（比如排序），就會(huì)申請(qǐng)臨時(shí)空間，放到硬盤(pán)上
如果3000個(gè)用戶(hù)同時(shí)連上mysql，最小需要內(nèi)存3000256KB=750M，最大需要內(nèi)存300064MB=192G，如果innodb_buffer_pool_size是80GB，可用內(nèi)存不到48G，192GB>48GB，將會(huì)產(chǎn)生SWAP，此時(shí)將會(huì)影響性能
連接數(shù)過(guò)高，不一定帶來(lái)吞吐量的提高，而且可能占用更多的系統(tǒng)資源
一個(gè)DB 3W QPS計(jì)算，前端有100個(gè)web服務(wù)器，每個(gè)web服務(wù)器需要300個(gè)QPS，每個(gè)QPS占用時(shí)間=網(wǎng)絡(luò)來(lái)回時(shí)間+SQL執(zhí)行時(shí)間，以20ms計(jì)算，需要6個(gè)連接數(shù)（300/1000/20ms=6）
示例1：有100臺(tái)web服務(wù)器，PHP/JAVA的最大連接數(shù)可設(shè)置為：3000/100=30
示例2：有30臺(tái)web服務(wù)器，要擴(kuò)容到60臺(tái)，web服務(wù)器連接數(shù)怎么配置？web服務(wù)器最大連接數(shù)：之前是3000/30=100，現(xiàn)在3000/60=50即可

3.max_user_connections=2980

剩余連接數(shù)用作管理

4.table_open_cache=1024

打開(kāi)表的緩存，跟表數(shù)量沒(méi)關(guān)系
1000個(gè)連接上來(lái)，都需要訪問(wèn)A表，那么會(huì)打開(kāi)1000個(gè)表，打開(kāi)1000個(gè)表是指mysql創(chuàng)建1000個(gè)這個(gè)表的對(duì)象，連接直接訪問(wèn)表對(duì)象，類(lèi)似會(huì)把這張表做一個(gè)class，1000個(gè)連接都訪問(wèn)這個(gè)表對(duì)象，當(dāng)表對(duì)象沒(méi)了，重新new一個(gè)，不需要每次都打開(kāi)物理表

root@master 14:44:  [(none)]> show variables like "%table_open_cache";+------------------+-------+| Variable_name    | Value |+------------------+-------+| table_open_cache | 1024  |+------------------+-------+1 row in set (0.00 sec)root@master 14:46:  [(none)]> show global status like "open%tables%"; +---------------+-------+| Variable_name | Value |+---------------+-------+| Open_tables   | 19    || Opened_tables | 113   |+---------------+-------+2 rows in set (0.00 sec)

可以考慮設(shè)置為max_connections或者max_connections*查詢(xún)同時(shí)用到的表個(gè)數(shù)或者

5.thread_cache_size=512

都是短連接進(jìn)來(lái)容易產(chǎn)生短連接風(fēng)暴
會(huì)話(huà)層：事務(wù)狀態(tài)、認(rèn)證會(huì)話(huà)
連接層：網(wǎng)絡(luò)連接、包傳輸
一個(gè)用戶(hù) 對(duì)應(yīng) 一個(gè)session 對(duì)應(yīng) 一個(gè)connection
connection - thread:操作系統(tǒng)調(diào)用
3000個(gè)用戶(hù)進(jìn)來(lái)使用cache的512個(gè)線程，用完就放回去，避免創(chuàng)建、銷(xiāo)毀線程的開(kāi)銷(xiāo)

6.wait_timeout=120

指的是app應(yīng)用連接mysql進(jìn)行操作完畢后，空閑120秒后斷開(kāi)

7.interactive_timeout=120

指的是mysql client連接mysql進(jìn)行操作完畢后，空閑120秒后斷開(kāi)

六.數(shù)據(jù)一致性的優(yōu)化

1.innodb_flush_log_at_trx_commit=1

0，不管有沒(méi)有提交，每秒鐘都寫(xiě)到binlog日志里
1，每次提交事務(wù)，都會(huì)把log buffer的內(nèi)容寫(xiě)到磁盤(pán)里去，對(duì)日志文件做到磁盤(pán)刷新，安全最好
2，每次提交事務(wù)，都寫(xiě)到操作系統(tǒng)緩存，由OS刷新到磁盤(pán)，性能最好

2.sync_binlog=1

0，事務(wù)提交后，mysql不做fsync之類(lèi)的刷盤(pán)，由文件系統(tǒng)來(lái)決定什么落盤(pán)
n，多少次提交，每n次提交持久化磁盤(pán)
生產(chǎn)設(shè)為1

3.日志寫(xiě)盤(pán)過(guò)程

1)三個(gè)update會(huì)話(huà)，三個(gè)線程都會(huì)產(chǎn)生的操作日志
2 )commit后提交到公共的cache中，三個(gè)進(jìn)程之間不能相互看到對(duì)方的操作內(nèi)容
3)經(jīng)過(guò)write寫(xiě)入到標(biāo)準(zhǔn)I/O cache中，也就是文件系統(tǒng)句柄，線程緩存
4)如果需要讓其他線程看到文件句柄內(nèi)容，就需要通過(guò)flush刷新到全局可見(jiàn)文件系統(tǒng)緩存
5)最后最重的一步是將內(nèi)存數(shù)據(jù)sync落盤(pán)

到此這篇關(guān)于MySQL核心參數(shù)優(yōu)化文件my.ini實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL my.ini內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！