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

CPU：48C
內存：128G
DISK：3.2TSSD

二.CPU的優(yōu)化

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

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

query_cache_type=0
query_cache_size=0
緩存查詢，5.6默認關閉，在應用層實現(xiàn)，比如MC、redis

四.IO的優(yōu)化

1.innodb_buffer_pool_size=50G

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

2.innodb_io_capacity=20000

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

3.innodb_log_files_in_group=4

幾個innodb redo log日志組

4.innodb_log_file_size=1000M

redo log日志循化寫，生產(chǎn)必須大于1G，
如果太小，那么innodb_buffer_pool_size的數(shù)據(jù)有可能不能及時寫入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ù)大小可作為設置日志文件size大小參考值

5.innodb_flush_method=O_DIRECT

SSD直接寫硬盤，不寫硬盤cache，也就是繞過fsync()刷硬盤

6.innodb_max_dirty_pages_pct=50

當臟塊達到innodb_buffer_pool_size的50%時，觸發(fā)檢查點，寫磁盤

7.innodb_file_per_table=on

一表一文件，可以避免共享表空間的IO競爭

8.innodb_page_size=4k

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

9.innodb_flush_neighbors=0

SSD設置為0，SAS打開刷新相鄰塊，隨機訪問轉換為順序訪問

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

1.back_log=300

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

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

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

2.max_connections=3000

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

3.max_user_connections=2980

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

4.table_open_cache=1024

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

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)

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

5.thread_cache_size=512

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

6.wait_timeout=120

指的是app應用連接mysql進行操作完畢后，空閑120秒后斷開

7.interactive_timeout=120

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

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

1.innodb_flush_log_at_trx_commit=1

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

2.sync_binlog=1

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

3.日志寫盤過程

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

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

最新評論