MySQL效能相關引數

整理MySQL常用效能相關引數如下

general_log

記錄所有執行的語句，在需要分析問題開啟即可，正常服務時不需要開啟，以免帶來io效能影響

 

query_cache_size

快取sql文字和查詢結果的，如果對應的表沒有變化，下次碰到一樣的SQL，跳過解析和查詢，直接返回結果。

但是表變化非常頻繁，SQL也是動態生產的，由於需要不斷更新cache內容，這時鎖力度很大，反而照成瓶頸。這時最好關掉這個功能，設定引數為0

 

sort_buffer_size

針對單個session的引數，

排序時，如果用不到index，session就會申請一塊這麼大的記憶體空間進行排序。如果這個引數值過小會把排序結果寫入硬碟中，會影響效率。

如果太大，又可能導致實體記憶體耗盡，導致OOM。

 

join_buffer_size

在join無法使用到index時候用到的buffer，和sort_buffer_size類似

 

tmp_table_size

在group by和distinct時如果SQL用不到索引，就會使用系統內部臨時表記錄中間狀態。如果該值不夠大，就使用物理硬碟。

mysql> show global status like 'created_tmp%';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 0     |

| Created_tmp_files       | 5     |

| Created_tmp_tables      | 23    |

+-------------------------+-------+

3 rows in set (0.00 sec)

每次建立臨時表，Created_tmp_tables增加，如果臨時表大小超過tmp_table_size，則是在磁碟上建立臨時表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務建立的臨時檔案檔案數，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

預設為16M，可調到64-256最佳，執行緒獨佔，太大可能記憶體不夠I/O堵塞

注意：

MySQL中的  max_heap_table_size 引數會影響到臨時表的記憶體快取大小 。
max_heap_table_size 是MEMORY記憶體引擎的表大小 ， 因為臨時表也是屬於記憶體表所以也會受此引數的限制 所以如果要增加 tmp_table_size 的大小 也需要同時增加 max_heap_table_size 的大小

table_open_cache

所有執行緒開啟表數量，增加這個值會增加mysqld需要的檔案描述符數量

mysql> show global status like 'open%tables%';

如：透過 show processlist 看到大量的 Opening tables、closing tables狀態，導致應用端訪問操作。

需要確認 table_open_cache=最大併發數表數量（join裡可能用到2張表），時候滿足當前配置。

如：但併發執行緒數達到1000，假設這些併發連線中有40%是訪問2張表，其他都是單表，那麼cache size就會達到（1000*40%2+1000*60%*1）=1400

建議定期監控值：

Open_tables / Opened_tables >= 0.85      表的重複使用率

Open_tables / table_open_cache <= 0.95   快取裡存在已開啟的表

Innodb_buffer_pool_size

InnoDB最重要的快取，用來快取innodb索引頁面、undo頁面及其他輔助資料。一般設定實體記憶體50%~75%

 

Innodb_buffer_pool_instances

透過這個引數可以把整塊buffer pool分割為多塊instance記憶體空間，每個空間獨立管理自己的記憶體和連結串列，來提升MySQL請求處理的併發能力。

因為buffer pool是透過連結串列來管理的，同時為了保護頁面，需要在存取的時候對連結串列加鎖，在多執行緒情況下，併發讀寫buffer pool快取會有鎖競爭和等待。

官方說超過1G的Innodb_buffer_pool_size 考慮設定instances去切分記憶體

 

Innodb_log_file_size,innodb_log_files_in_group

兩個引數決定redo空間的大小，設定儲存更新redo越大，有效降低buffer pool髒頁被淘汰的速度，減少了checkpoint此書，降低磁碟I/O

不過設定過大，在資料庫異常當機時，恢復時間越長

 

 

Innodb_old_blocks_pct,innodb_old_blocks_time

innodb_old_blocks_pct：

全域性、動態變數，預設值 37，取值範圍為5~95. 用來確定LRU連結串列中old sublist所佔比例

innodb_old_blocks_time：

全域性、動態變數，預設值 1000，取值範圍為0~2**32-1，單位ms。

用來控制old sublist中page的轉移策略，新的page頁在進入LRU連結串列中時，會先插入到old sublist的頭部，然後page需要在old sublist中停留innodb_old_blocks_time這麼久後，下一次對該page的訪問才會使其移動到new sublist的頭部，

該引數的設定可以保護new sublist，儘可能的防止其being filled by page that is referenced only for a brief period。

 

預設的緩衝中的頁在第一次被讀取時（也就是命中快取）會被移動到新頁子表頭部，意味著其會長期待在緩衝池中不會被淘汰。這樣就會存在一個問題，一次表掃描（比如使用select查詢）可能會將大量資料放入快取中，並淘汰相應數量的舊資料，但是可能這些資料只使用一次，後面不再使用；同樣地，因為read-ahead也會在下一次訪問該頁時被放入新頁子表頭部。這些情形會將本應會被頻繁使用的頁移動到舊頁子表中。

所以3/8位置處。在後面的第一次命中（被訪問時）的頁會被移動到列表的頭部。因此，那些讀入快取但是後面從來不會被訪問的頁也從不會被放入列表的頭部，也就會在後面被從緩衝池淘汰。

MySQL提供了配置引數,milliseconds）讀取不會被標識為年輕，也就是不會被移動到列表頭部。引數1000，增大這個引數將會造成更多的頁會更快的從緩衝池中被淘汰。

參考:

https://blog.csdn.net/u014710633/java/article/details/94594598

 

Innodb_flush_method

Innodb刷資料和日誌到磁碟的方式，這個值預設為空，其實：

Linux預設fsync

Windows 預設async_unbuffered

SSD和PCIE儲存時可以使用o_direct 提升效能

 

Innodb_doublewrite

MySQL預設每個page size是16k，而OS通常最小I/O單元是4k，所以如果寫page時可能需要呼叫4次OS I/O才能完成。假定在執行兩次時DB crash了，這時page只寫了一部分，就產生了partial write(不完整寫)。

MySQL double write的設定就是為了在發生partial write時任然保證已經commit的資料不丟失，以及資料檔案不損壞。

但如果底層儲存支援原子性可以關閉兩次寫，主要看OS page size和DB page size的關係。

參考：

http://blog.itpub.net/25583515/viewspace-2685493/

 

Innodb_io_capacity

控制後臺不斷將記憶體(dirty data)資料flush硬碟的操作，遇到週期性IO QPS下降時可以考慮提高引數的設定，以加速flush的頻率

參考實驗提高Innodb_io_capacity的設定，已提升QPS

http://blog.itpub.net/26506993/viewspace-2214703/ 

 

Innodb_thread_concurrency

在併發量大的時，增加這個值，兒科降低innodb在併發執行緒之間切換開銷，以增加系統的併發吞吐量

innodb_flush_log_at_trx_commit

控制redo log刷盤機制

innodb_flush_log_at_trx_commit=0

事務提交時，不會處理log buffer的內容，也不會處理log file在OS cache的刷盤操作，由MySQL後臺master執行緒每隔1秒將log buffer重新整理到磁碟的log file中。

在MySQL服務宕掉，伺服器正常或當機時：

由於事務提交不重新整理logbuffer，即使事務提交了，logbuffer也會全部丟失，但只丟失最近1秒的事務

innodb_flush_log_at_trx_commit=1

事務提交時，會將log buffer的內容寫入OS cache檔案中，同時會將OS cache重新整理到磁碟log file中。

在MySQL服務宕掉，伺服器正常或當機時：

由於事務提交會重新整理到磁碟log file中，所以資料都不會丟失

innodb_flush_log_at_trx_commit =2

事務提交時，會將log buffer的內容寫到OS cache檔案中，由MySQL後臺master執行緒每隔1秒將OS cache的log file重新整理到磁碟。

在MySQL服務宕掉，伺服器正常：

由於事務已經重新整理到OS cache中，然而伺服器沒當機，這樣日誌還是會被重新整理到磁碟中，那麼資料就不會丟失

在MySQL服務宕掉，伺服器當機：

由於事務只重新整理到OS cache中，伺服器當機話，日誌沒用被重新整理到磁碟中，會丟失1秒的事務

sync_binlog

控制binlog同步到磁碟的方式

sync_binlog=0,事務提交時將MySQL Binlog資訊寫入OS cache Binlog中，由OS自己空間其快取的重新整理。如果是伺服器當機binlog cache中所有binlog都會丟失

sync_binlog=1,每個事務提交時，MySQL都會把Binlog重新整理到物理磁碟中。這樣安全性最高，效能損耗是最大。特別是在多事務同行提交，會對I/O效能帶來很大影響。

但 group commit可以緩解壓力：

binlog_group_commit_sync_delay=N,預設是0,定時執行，在commit後等待N 微秒後，進行binlog刷盤操作

binlog_group_commit_sync_no_delay_count=N，在commit後等待達到最大事務等待數量N， 就忽視binlog_group_commit_sync_delay的設定，直接開始刷盤,注意如果binlog_group_commit_sync_delay設定為0，則此選項無效

不過group commit的設定，可能會影響commit執行執行速度，可參考： https://www.cnblogs.com/ziroro/p/9600359.html

 

sync_binlog=N, 表示每N次事務提交，MySQL會做刷盤。如果DB服務或者伺服器當機會丟失一些事務

注：開啟Binlog後，MySQL內部會自動將事務當作一個XA事務處理，在提交事務過程中，會自動分配一個唯一的XID，XID會記錄到Binlog和redo log中。事務在提交過程會自動份為Prepare和Commit兩個階段。

Prepare階段：告訴InnoDB做prepare，InnoDB更改事務狀態，並將redo log刷入磁碟

Commit階段：先記錄Binlog，然後告訴InnoDB commit

binlog_format

binlog_format=STATEMENT

寫入執行的SQL語句到binlog，從庫讀取這些SQL並執行

優勢：

技術成熟

減少binlog的寫入量

binlog包含所有修改語句沒便於審計

缺點：

有些函式不能再slave上覆雜，如sleep(),last_insert_id(),udf等會除問題

與基於row的複製比，insert...select需要更多的鎖

隔離級別必須是repeatable-read,而這是發生死鎖的元兇之一

binlog_format=MIXED

預設使用STATEMENT記錄日誌，特定情況下轉換成ROW記錄

binlog_format=ROW

MySQL5.7.7之後的預設值

優點：

複製是最安全的

slave需要的鎖也最少

缺點：

binlog會記錄更多的資料

無法在slave上看到master上獲取的語句，因為都是event。但可以開啟binlog_rows_query_log_events引數，讓binlog記錄events同時也記錄原始SQL語句。

( 複製建議使用row模式，其它模式有可能出現主從資料不一致)

tx_isolation

MySQL隔離級別，預設是 repeatable-read

Read Uncommitted

Read Committed

Repeatable Read

Serializable

這四種級別越來越嚴格，但效能越來越差。

推薦使用Read Committed，同時binlog_format=ROW ，確認binlog同步資料主從庫一致性，兼顧安全，滿足絕大多數業務。

slave_parallel_workers

MySQL 5.6中，設定引數slave_parallel_workers = 4，即可有4個SQL Thread（coordinator執行緒）來進行並行複製，其狀態為：Waiting for an evant from Coordinator。但是其並行只是基於database的。如果資料庫例項中存在多個database，這樣設定對於Slave複製的速度可以有比較大的提升。

其核心思想是：不同database下的表併發提交時的資料不會相互影響，即slave節點可以用對relay log中不同的schema各分配一個類似SQL功能的執行緒，來重放relay log中主庫已經提交的事務，保持資料與主庫一致。

在MySQL 5.7中，引入了基於組提交的並行複製（Enhanced Multi-threaded Slaves），

設定slave_parallel_workers>0並且global.slave_parallel_type＝‘LOGICAL_CLOCK’，即可支援一個database下，slave_parallel_workers個的worker執行緒併發執行relay log中主庫提交的事務。

其核心思想：一個組提交的事務都是可以並行回放(配合binary log group commit);

slave機器的relay log中 last_committed相同的事務（sequence_num不同）可以併發執行。

引數slave_parallel_type可以有兩個值：

DATABASE 預設值，基於庫的並行複製方式

LOGICAL_CLOCK：基於組提交的並行複製方式

參考： https://www.cnblogs.com/langdashu/p/6125621.html

 

MySQL效能相關引數的使用及說明如上，有理解不準確和不完善的後續再補充。

文中內容主要參考：

《MySQL運維內參》書籍

《深入淺出MySQL》書籍

MySQL 5.7官方文件 https://dev.mysql.com/doc/refman/5.7/en/