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

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

二.CPU的優(yōu)化

innodb_thread_concurrency=32
表示SQL經(jīng)過解析后，允許同時(shí)有32個(gè)線程去innodb引擎取數(shù)據(jù)，如果超過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
緩存查詢，5.6默認(rèn)關(guān)閉，在應(yīng)用層實(shí)現(xiàn)，比如MC、redis

四.IO的優(yōu)化

1.innodb_buffer_pool_size=50G

類似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日志循化寫，生產(chǎn)必須大于1G，
如果太小，那么innodb_buffer_pool_size的數(shù)據(jù)有可能不能及時(shí)寫入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直接寫硬盤，不寫硬盤cache，也就是繞過fsync()刷硬盤

6.innodb_max_dirty_pages_pct=50

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

7.innodb_file_per_table=on

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

8.innodb_page_size=4k

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

9.innodb_flush_neighbors=0

SSD設(shè)置為0，SAS打開刷新相鄰塊，隨機(jī)訪問轉(zhuǎ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占滿，應(yīng)用還沒來(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  類似活動(dòng)場(chǎng)所
sync接收隊(duì)列的長(zhǎng)度，默認(rèn)是1024，當(dāng)mysql在很短時(shí)間內(nèi)得到很多的請(qǐng)求，需要增加，太大會(huì)消耗資源
太小的話會(huì)在show processlist出現(xiàn)未認(rèn)證錯(cuò)誤
net.core.somaxconn=1024   類似活動(dòng)場(chǎng)所中的座位數(shù)
盡可能防止丟包，超過這個(gè)值會(huì)觸發(fā)超時(shí)或者重傳，限制在net.ipv4.ip_local_port_range這個(gè)范圍之內(nèi)

2.max_connections=3000

連接的創(chuàng)建和銷毀都需要系統(tǒng)資源，比如內(nèi)存、文件句柄
業(yè)務(wù)說(shuō)的支持多少并發(fā)，指的是每秒請(qǐng)求數(shù)，也就是QPS
同一時(shí)刻并行的SQL由innodb_thread_concurrency決定，最大不能超過該值
如果一個(gè)用戶的請(qǐng)求數(shù)據(jù)超過64MB（比如排序），就會(huì)申請(qǐng)臨時(shí)空間，放到硬盤上
如果3000個(gè)用戶同時(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ù)過高，不一定帶來(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

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

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*查詢同時(shí)用到的表個(gè)數(shù)或者

5.thread_cache_size=512

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

6.wait_timeout=120

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

7.interactive_timeout=120

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

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

1.innodb_flush_log_at_trx_commit=1

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

2.sync_binlog=1

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

3.日志寫盤過程

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

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

最新評(píng)論