MySQL核心引數優化檔案my.ini實現

2023-02-03 18:04:07

一.資料庫伺服器設定

CPU：48C
記憶體：128G
DISK：3.2TSSD

二.CPU的優化

innodb_thread_concurrency=32
表示SQL經過解析後，允許同時有32個執行緒去innodb引擎取資料，如果超過32個，則需要排隊；
值太大會產生熱點資料，global鎖爭用嚴重，影響效能

三.記憶體的優化

query_cache_type=0
query_cache_size=0
快取查詢，5.6預設關閉，在應用層實現，比如MC、redis

四.IO的優化

1.innodb_buffer_pool_size=50G

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

2.innodb_io_capacity=20000

每秒後臺程序處理IO資料的上限，一般為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紀錄檔循化寫，生產必須大於1G，
如果太小，那麼innodb_buffer_pool_size的資料有可能不能及時寫入redo log造成halt等待；檢視是否夠用？如果value大於0，則提高改引數或者增加紀錄檔組

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)
#此引數大小可作為設定紀錄檔檔案size大小參考值

5.innodb_flush_method=O_DIRECT

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

6.innodb_max_dirty_pages_pct=50

當髒塊達到innodb_buffer_pool_size的50%時，觸發檢查點，寫磁碟

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開啟重新整理相鄰塊，隨機存取轉換為順序存取

五.連線的優化

1.back_log=300

預設是50，TCP/IP的連線數量，一個連線佔用256KB記憶體，最大是64MB，256 * 300 =75MB記憶體
和三次握手有關係

syn_queue取64和tcp_max_sync_backlog最大者，預設是1024，當瞬時很多連線進來這個引數會進行限制，否則太大容易消耗資源
accept queue取back_log和somaxconn最小者，用來防止丟包，當瞬時很多連線進來達到上限後，後來連線將超時觸發重傳機制
當有3000個連線進來，將佇列accept queue佔滿，應用還沒來得及將請求從佇列中取出，剩下的2700個連線將被拒絕，每取走一個請求（一個連線，mysql一個執行緒一個連線），將建立一個thread執行緒

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

2.max_connections=3000

連線的建立和銷燬都需要系統資源，比如記憶體、檔案控制程式碼
業務說的支援多少並行，指的是每秒請求數，也就是QPS
同一時刻並行的SQL由innodb_thread_concurrency決定，最大不能超過該值
如果一個使用者的請求資料超過64MB（比如排序），就會申請臨時空間，放到硬碟上
如果3000個使用者同時連上mysql，最小需要記憶體3000256KB=750M，最大需要記憶體300064MB=192G，如果innodb_buffer_pool_size是80GB，可用記憶體不到48G，192GB>48GB，將會產生SWAP，此時將會影響效能
連線數過高，不一定帶來吞吐量的提高，而且可能佔用更多的系統資源
一個DB 3W QPS計算，前端有100個web伺服器，每個web伺服器需要300個QPS，每個QPS佔用時間=網路來回時間+SQL執行時間，以20ms計算，需要6個連線數（300/1000/20ms=6）
範例1：有100臺web伺服器，PHP/JAVA的最大連線數可設定為：3000/100=30
範例2：有30臺web伺服器，要擴容到60臺，web伺服器連線數怎麼設定？web伺服器最大連線數：之前是3000/30=100，現在3000/60=50即可

3.max_user_connections=2980

剩餘連線數用作管理

4.table_open_cache=1024

開啟表的快取，跟表數量沒關係
1000個連線上來，都需要存取A表，那麼會開啟1000個表，開啟1000個表是指mysql建立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*查詢同時用到的表個數或者

5.thread_cache_size=512

都是短連線進來容易產生短連線風暴
對談層：事務狀態、認證對談
連線層：網路連線、包傳輸
一個使用者對應一個session 對應一個connection
connection - thread:作業系統呼叫
3000個使用者進來使用cache的512個執行緒，用完就放回去，避免建立、銷燬執行緒的開銷

6.wait_timeout=120

指的是app應用連線mysql進行操作完畢後，空閒120秒後斷開

7.interactive_timeout=120

指的是mysql client連線mysql進行操作完畢後，空閒120秒後斷開

六.資料一致性的優化

1.innodb_flush_log_at_trx_commit=1

0，不管有沒有提交，每秒鐘都寫到binlog紀錄檔裡
1，每次提交事務，都會把log buffer的內容寫到磁碟裡去，對紀錄檔檔案做到磁碟重新整理，安全最好
2，每次提交事務，都寫到作業系統快取，由OS重新整理到磁碟，效能最好

2.sync_binlog=1

0，事務提交後，mysql不做fsync之類的刷盤，由檔案系統來決定什麼落盤
n，多少次提交，每n次提交持久化磁碟
生產設為1

3.紀錄檔寫盤過程

1)三個update對談，三個執行緒都會產生的操作紀錄檔
2 )commit後提交到公共的cache中，三個程序之間不能相互看到對方的操作內容
3)經過write寫入到標準I/O cache中，也就是檔案系統控制程式碼，執行緒快取
4)如果需要讓其他執行緒看到檔案控制程式碼內容，就需要通過flush重新整理到全域性可見檔案系統快取
5)最後最重的一步是將記憶體資料sync落盤

到此這篇關於MySQL核心引數優化檔案my.ini實現的文章就介紹到這了,更多相關MySQL my.ini內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！