MySQL慢查詢，[MySQL 5.6] MySQL 5.6 group commit 性能測試及內部實現流程

2023-10-15 阅读 24 评论 0

摘要：[MySQL 5.6] MySQL 5.6 group commit 性能測試及內部實現流程 http://mysqllover.com/?p=581 盡管Mariadb以及Facebook在long long time ago就fix掉了這個臭名昭著的問題，但官方直到 MySQL5.6 版本才Fix掉，本文主要關注三點： 1.MySQL 5.6的性能如何

[MySQL 5.6] MySQL 5.6 group commit 性能測試及內部實現流程

http://mysqllover.com/?p=581

盡管Mariadb以及Facebook在long long time ago就fix掉了這個臭名昭著的問題，但官方直到 MySQL5.6 版本才Fix掉，本文主要關注三點：

1.MySQL 5.6的性能如何

2.在5.6中Group commit的三階段實現流程

?binlog和innodb提交過程，內部xa事務，先寫redo log再寫binlog，任何一個環節失敗，都會回滾事務，保證binlog和redo log一致

http://www.cnblogs.com/MYSQLZOUQI/p/5431472.html

從庫比主庫多數據，主庫掛了，寫binlog成功從庫也應用了binlog，主庫起來后回滾數據導致從庫比主庫多數據，原因就是先寫redo log再寫binlog

http://www.cnblogs.com/MYSQLZOUQI/p/5596590.html

新參數

MySQL 5.6提供了兩個參數來控制binlog group commit：

binlog_max_flush_queue_time

單位為微妙，用于從flush隊列中取事務的超時時間，這主要是防止并發事務過高，導致某些事務的RT上升。

可以閱讀函數MYSQL_BIN_LOG::process_flush_stage_queue 來理解其功能

binlog_order_commits

當設置為0時，事務可能以和binlog不相同的順序被提交，從下面的測試也可以看出，這會稍微提升點性能，但并不是特別明顯.

性能測試

老規矩，先測試看看性能

sysbench, 全內存操作，5個sbtest表，每個表1000000行數據

基本配置：

innodb_flush_log_at_trx_commit=1

table_open_cache_instances=5

metadata_locks_hash_instances = 32

metadata_locks_cache_size=2048

performance_schema_instrument = ‘%=on’

performance_schema=ON

innodb_lru_scan_depth=8192

innodb_purge_threads = 4

關閉Performance Schema consumer：

mysql> update setup_consumers set ENABLED = ‘NO’;

Query OK, 4 rows affected (0.02 sec)

Rows matched: 12? Changed: 4? Warnings: 0

sysbench/sysbench –debug=off –test=sysbench/tests/db/update_index.lua? –oltp-tables-count=5? –oltp-point-selects=0 –oltp-table-size=1000000 –num-threads=1000 –max-requests=10000000000 –max-time=7200 –oltp-auto-inc=off –mysql-engine-trx=yes –mysql-table-engine=innodb? –oltp-test-mod=complex –mysql-db=test ? –mysql-host=$HOST –mysql-port=3306 –mysql-user=xx run

update_index.lua

threads	sync_binlog = 0	sync_binlog = 1	sync_binlog =1binlog_order_commits=0
1	?900	?610	?620
20	13,800	7,000	7,400
60	20,000	14,500	16,000
120	25,100	21,054	23,000
200	27,900	25,400	27,800
400	33,100	30,700	31,300
600	32,800	31,500	29,326
1000	20,400	20,200	20,500

我的機器在壓到1000個并發時，CPU已經幾乎全部耗完。

可以看到，并發度越高，group commit的效果越好，在達到600以上并發時，設置sync_binlog=1或者0已經沒有TPS的區別。

但問題是。我們的業務壓力很少會達到這么高的壓力，低負載下，設置sync_binlog=1依舊增加了單個線程的開銷。

另外也觀察到，設置binlog_max_flush_queue_time對TPS的影響并不明顯。

實現原理

我們知道，binlog和innodb在5.1及以后的版本中采用類似兩階段提交的方式，關于group commit問題的前世今生，可以閱讀MATS的博客，講述的非常詳細。嗯，評論也比較有意思。。。。。

以下集中在5.6中binlog如何做group commit。在5.6中，將binlog的commit階段分為三個階段：flush stage、sync stage以及commit stage。5.6的實現思路和Mariadb的思路類似，都是維護一個隊列，第一個進入該隊列的作為leader線程，否則作為follower線程。leader線程收集follower的事務，并負責做sync，follower線程等待leader通知操作完成。

這三個階段中，每個階段都會去維護一個隊列：

Mutex_queue m_queue[STAGE_COUNTER];

不同session的THD使用the->next_to_commit來鏈接，實際上，在如下三個階段，盡管維護了三個隊列，但隊列中所有的THD實際上都是通過next_to_commit連接起來了。

在binlog的XA_COMMIT階段(MYSQL_BIN_LOG::commit),完成事務的最后一個xid事件后，，這時候會進入MYSQL_BIN_LOG::ordered_commit，開始3個階段的流程：

###flush stage

change_stage(thd, Stage_manager::FLUSH_STAGE, thd, NULL, &LOCK_log)

|–>stage_manager.enroll_for(stage, queue, leave_mutex) //將當前線程加入到m_queue[FLUSH_STAGE]中，如果是隊列的第一個線程，就被設置為leader，否則就是follower線程，線程會這其中睡眠，直到被leader喚醒(m_cond_done)

|–>leader線程持有LOCK_log鎖，從change_state線程返回false.

flush_error= process_flush_stage_queue(&total_bytes, &do_rotate, &wait_queue); //只有leader線程才會進入這個邏輯

|–>首先讀取隊列，直到隊列為空，或者超時（超時時間是通過參數binlog_max_flush_queue_time來控制)為止，對讀到的每個線程做flush_thread_caches，將binlog刷到cache中。注意在出隊列的時候，可能還有新的session被append到隊列中，設置超時的目的也正在于此

|–>如果是超時，這時候隊列中還有session的話，就取出整個隊列的頭部線程，并將原隊列置空(fetch_queue_for)，然后對取出的session進行flush_thread_caches

|–>判斷總的寫入binlog的byte數是否超過max bin log size，如果超過了，就設置rotate標記

flush_error= flush_cache_to_file(&flush_end_pos);

|–>將I/O Cache中的內容寫到文件中

signal_update() ?//通知dump線程有新的Binlog

###sync stage

change_stage(thd, Stage_manager::SYNC_STAGE, wait_queue, &LOCK_log, &LOCK_sync)

|–>stage_manager.enroll_for(stage, queue, leave_mutex) ?//當前線程加入到m_queue[SYNC_STAGE]隊列中，釋放lock_log鎖；同樣的如果是SYNC_STAGE隊列的leader，則立刻返回，否則進行condition wait.

|–>leader線程加上Lock_sync鎖

final_queue= stage_manager.fetch_queue_for(Stage_manager::SYNC_STAGE); ?//從SYNC_STAGE隊列中取出來，并清空隊列，主要用于commit階段

std::pair<bool, bool> result= sync_binlog_file(false); ?//刷binlog 文件(如果設置了sync_binlog的話)

簡單的理解就是，在flush stage階段形成N批的組session，在SYNC階段又會由這N批組產生出新的leader來負責做最耗時的sync操作

###commit stage

commit階段受到參數binlog_order_commits限制

當binlog_order_commits關閉時，直接unlock?LOCK_sync，由各個session自行進入Innodb commit階段(隨后調用的finish_commit(thd))，這樣不會保證binlog和事務commit的順序一致，如果你不關注innodb的ibdata中記錄的binlog信息，那么可以關閉這個選項來稍微提高點性能

當打開binlog_order_commits時，才會進入commit stage，如下描述的

change_stage(thd, Stage_manager::COMMIT_STAGE,final_queue, &LOCK_sync, &LOCK_commit)

|–>進入新的COMMIT_STAGE隊列，釋放LOCK_sync鎖，新的leader獲取LOCK_commit鎖，其他的session等待

THD *commit_queue= stage_manager.fetch_queue_for(Stage_manager::COMMIT_STAGE); ?//取出并清空COMMIT_STAGE隊列

process_commit_stage_queue(thd, commit_queue, flush_error)

|–>這里會遍歷所有的線程，然后調用ha_commit_low->innobase_commit進入innodb層依次提交

完成上述步驟后，解除LOCK_commit鎖

stage_manager.signal_done(final_queue);

|–>將所有Pending的線程的標記置為false（thd->transaction.flags.pending= false）并做m_cond_done廣播，喚醒pending的線程

(void) finish_commit(the); ?//如果binlog_order_commits設置為FALSE，就會進入這一步來提交存儲引擎層事務; 另外還會更新grid信息

Innodb的group commit和mariadb的類似，都只有兩次sync，即在prepare階段sync，以及sync Binlog文件（雙一配置），為了保證rotate時，所有前一個binlog的事件的redo log都被刷到磁盤，會在函數new_file_impl中調用如下代碼段： if (DBUG_EVALUATE_IF(“expire_logs_always”, 0, 1) && (error= ha_flush_logs(NULL))) goto end;

ha_flush_logs 會調用存儲引擎接口刷日志文件