MySQL 關於slave端Retrieved_Gtid_Set的讀取改進初探
本文為以後學習SLAVE做一個記錄不保證正確性。因為演算法沒看懂。
一、問題由來
今天朋友問我這樣一個問題@K.I.S.S,在官方文件中有這樣一段描述:
When using GTIDs, the slave tells the master which transactions it has already received, executed, or both. To compute this set, it reads
the global value of gtid_executed and the value of the Retrieved_gtid_set column from SHOW SLAVE STATUS. The GTID of the last transmitted
transaction is included in Retrieved_gtid_set only when the full transaction is received. The slave computes the following set:
UNION(@@global.gtid_executed, Retrieved_gtid_set)
Prior to MySQL 5.7.5, the GTID of the last transmitted transaction was included in Retrieved_gtid_set even if the transaction was only
partially transmitted, and the last received GTID was subtracted from this set. (Bug #17943188) Thus, the slave computed the following set: UNION(@@global.gtid_executed, Retrieved_gtid_set - last_received_GTID)
問為什麼要減去last_received_GTID。
二、檢視Bug #17943188的commit
對於這個問題我先檢視了一下這個bug到底修復了什麼問題如下:
BUG#17943188 SHOW SLAVE STATUS/RETRIEVED_GTID_SET MAY HAVE PARTIAL TRX OR MISS COMPLETE TRX
Problem:
=======
The SHOW SLAVE STATUS command contains the column RETRIEVED_GTID_SET.
This is supposed to contain the set of GTIDs that exist in the relay
log. However, the field is updated when the slave receiver thread
(I/O thread) receives a Gtid_log_event, which happens at the beginning
of the transaction.
If the I/O thread gets disconnected in the middle of a transaction,
RETRIEVED_GTID_SET can contain a GTID for a transaction that is only
partially received in the relay log. This transaction will
subsequently be rolled back, so it is wrong to pretend that the
transaction is there.
Typical fail-over algorithms use RETRIEVED_GTID_SET to determine which
slave has received the most transactions to promote the slave to a
master. This is true for e.g. the mysqlfailover utility.
When RETRIEVED_GTID_SET can contain partially transmitted transactions,
the fail-over utility can choose the wrong slave to promote. This can
lead to data corruption later.
This means that even if semi-sync is enabled, transactions that have
been acknowledged by one slave can be lost.
Fix:
===
It was implemented a transaction boundaries parser that will give
information about transaction boundaries of an event stream based on
the event types and their queries (when they are Query_log_event).
As events are queued by the I/O thread, it feeds the Master_info
transaction boundary parser. The slave I/O recovery also uses the
transaction parser to determine if a given GTID can be added to the
Retrieved_Gtid_Set or not.
When the event parser is in GTID state because a Gtid_log_event was
queued, the event's GTID isn't added to the retrieved list yet.
It is stored in an auxiliary GTID variable.
After flushing an event into the relay log, the IO thread verifies if the transaction parser is not inside a transaction anymore (meaning
that the last event of the transaction has been flushed).
If transaction parser is outside a transaction, the I/O thread
verifies if a GTID was stored in the start of the transaction, adding
it to the retrieved list, ensuring that all the transaction has arrived and was flushed to the relay log.
Also, before this patch, after the I/O thread flushed a single received
event into the relaylog, it was possible to rotate the relaylog if the
current relaylog file size exceeded max_binlog_size/max_relaylog_size.
After this patch, when GTIDs are enabled we only allow this rotation by
size if the transaction parser is not in the middle of a transaction.
Note: The current patch removed the changes for BUG#17280176, as it also dealt with similar problem in a different way.
大概就是說對於某些I/O執行緒並沒有完整傳輸的Gtid事物記錄到了RETRIEVED_GTID_SET中這會導致比較嚴重問題,因為某些監控工具根據這個來判斷是否切換之類的,因此我們加入了一個事物邊界分析器來判斷事物是否完整傳輸,如果完整傳輸才記錄到RETRIEVED_GTID_SET中這是5.7.5過後加入的。總的說來為了進行兩個問題的修復:
- 1、最後一個gtid 事物是否完整。
- 2、跨越多個relay log的binlog 得到正確的gtid集合。
三、修改了什麼
其實修改得非常多,不一一列舉,有興趣可以自己看看commit 9dab9dad975d09b8f37f33bf3c522d36fdf1d0f9,這裡列舉幾個我看了的地方。
1、在 MYSQL_BIN_LOG::init_gtid_sets加入瞭如下邏輯/*
If we use GTIDs and have partial transactions on the relay log,
must check if it ends on next relay log files.
We also need to feed the boundary parser with the rest of the
relay log to put it in the correct state before receiving new
events from the master in the case of GTID auto positioning be
disabled.
*/ if (is_relay_log)
{ /*
Suppose the following relaylog:
rl-bin.000001 | rl-bin.000002 | rl-bin.000003 | rl-bin-000004
---------------+---------------+---------------+---------------
PREV_GTIDS | PREV_GTIDS | PREV_GTIDS | PREV_GTIDS
(empty) | (UUID:1) | (UUID:1) | (UUID:1)
---------------+---------------+---------------+---------------
GTID(UUID:1) | QUERY(INSERT) | QUERY(INSERT) | XID
---------------+---------------+---------------+---------------
QUERY(CREATE |
TABLE t1 ...) |
---------------+
GTID(UUID:2) |
---------------+
QUERY(BEGIN) |
---------------+
As it is impossible to determine the current Retrieved_Gtid_Set by only
looking to the PREVIOUS_GTIDS on the last relay log file, and scanning
events on it, we tried to find a relay log file that contains at least
one GTID event during the backwards search.
In the example, we will find a GTID only in rl-bin.000001, as the
UUID:2 transaction was spanned across 4 relay log files.
The transaction spanning can be caused by "FLUSH RELAY LOGS" commands
on slave while it is queuing the transaction.
So, in order to correctly add UUID:2 into Retrieved_Gtid_Set, we need
to parse the relay log starting on the file we found the last GTID
queued to know if the transaction was fully retrieved or not.
*/ /*
Adjust the reverse iterator to point to the relaylog file we
need to start parsing, as it was incremented after generating
the relay log file name.
*/ rit--;//回退一個檔案 因為前面為rit++做操作 exp:1<2<3<{4}<5<6<7 ---> 1<2<3<4<{5}<6<7 /* Reset the transaction parser before feeding it with events */ trx_parser->reset();
gtid_partial_trx->clear();
DBUG_PRINT("info", ("Iterating forwards through relay logs, " "updating the Retrieved_Gtid_Set and updating " "IO thread trx parser before start.")); for (it= find(filename_list.begin(), filename_list.end(), *rit);
it != filename_list.end(); it++)//從匹配的位置繼續向後 { const char *filename= it->c_str();
DBUG_PRINT("info", ("filename='%s'", filename)); if (read_gtids_and_update_trx_parser_from_relaylog(filename, all_gtids, true, trx_parser,
gtid_partial_trx))
{
error= 1; goto end;
}
}
}
}
其實這一塊也說明了解決的什麼問題,我們發現一個事物的binlog event 在relay log中是可以跨檔案的。而在bin log中是不能跨檔案的。僅僅判斷最後一個gtid priv event 是不正確的。因此需要這樣修改。
2、其次加入了邊界分析器Transaction_boundary_parser類這個類是完全新加入的,這裡是其中的一些狀態:
num enum_event_boundary_type {
EVENT_BOUNDARY_TYPE_ERROR= -1, /* Gtid_log_event */ EVENT_BOUNDARY_TYPE_GTID= 0, /* Query_log_event(BEGIN), Query_log_event(XA START) */ EVENT_BOUNDARY_TYPE_BEGIN_TRX= 1, /* Xid, Query_log_event(COMMIT), Query_log_event(ROLLBACK), XA_Prepare_log_event */ EVENT_BOUNDARY_TYPE_END_TRX= 2, /* Query_log_event(XA ROLLBACK) */ EVENT_BOUNDARY_TYPE_END_XA_TRX= 3, /* User_var, Intvar and Rand */ EVENT_BOUNDARY_TYPE_PRE_STATEMENT= 4, /*
All other Query_log_events and all other DML events
(Rows, Load_data, etc.)
*/ EVENT_BOUNDARY_TYPE_STATEMENT= 5, /*
All non DDL/DML events: Format_desc, Rotate, Incident,
Previous_gtids, Stop, etc.
*/ EVENT_BOUNDARY_TYPE_IGNORE= 6 }; /*
Internal states for parsing a stream of events.
DDL has the format:
DDL-1: [GTID]
DDL-2: [User] [Intvar] [Rand]
DDL-3: Query
DML has the format:
DML-1: [GTID]
DML-2: Query(BEGIN)
DML-3: Statements
DML-4: (Query(COMMIT) | Query([XA] ROLLBACK) | Xid | Xa_prepare)
*/ enum enum_event_parser_state { /* NONE is set after DDL-3 or DML-4 */ EVENT_PARSER_NONE, /* GTID is set after DDL-1 or DML-1 */ EVENT_PARSER_GTID, /* DDL is set after DDL-2 */ EVENT_PARSER_DDL, /* DML is set after DML-2 */ EVENT_PARSER_DML, /* ERROR is set whenever the above pattern is not followed */ EVENT_PARSER_ERROR
};
3、read_gtids_and_update_trx_parser_from_relaylog函式新增
這個函式是完全新加入的就是為了完成所說的功能,在read_gtids_and_update_trx_parser_from_relaylog中我看到對檔案所有event進行了讀取,並且用switch進行了不同event型別的處理,但是具體沒有細看。但是最後看到對於對於是否加入retrieve gtid的判斷如下:
/*
If we reached the end of a transaction after storing it's GTID
in gtid_partial_trx variable, it is time to add this GTID to the
retrieved_gtids set because the transaction is complete and there is no
need for asking this transaction again.
*/ if (trx_parser->is_not_inside_transaction())
{ if (!gtid_partial_trx->is_empty())
{
DBUG_PRINT("info", ("Adding Gtid to Retrieved_Gtid_Set as the " "transaction was completed at " "relaylog file '%s': Gtid(%d, %lld).",
filename, gtid_partial_trx->sidno,
gtid_partial_trx->gno));
retrieved_gtids->_add_gtid(gtid_partial_trx->sidno,
gtid_partial_trx->gno);
gtid_partial_trx->clear();
}
}
四、初始化的時候retrieve gtid到底如何計算
實際上就在現在看來應該就是讀取 relay_log的最後一個gtid事物(gtid event或者gtid priv event)同時需要判斷此gtid事物是否完整。對於官方文件給出的UNION(@@global.gtid_executed, Retrieved_gtid_set - last_received_GTID),個人覺得這裡的 last_received_GTID應該是經過判斷的,如果完整則不減,如果不完整則減去。
作者微信:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7728585/viewspace-2151532/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql slave 跟進 master 的關鍵狀態指標MySqlAST指標
- 新特性解讀 | MySQL 8.0 對 UNION 的改進MySql
- MySQL Slave異常關機的處理 (pt-slave-restart)MySqlREST
- MySQL:關於Wating for Slave workers to free pending events等待MySql
- MySQL中關於Qustions計算的改變MySql
- MySQL Slave異常關機的處理MySql
- MySql的Master/SlaveMySqlAST
- 求救:關於讀取excel資料的問題Excel
- [MySQL進階之路][No.0002] SHOW SLAVE STATUSMySql
- MySQL master/slaveMySqlAST
- MySQL新建SlaveMySql
- 關於FY型液下泵的安裝要求和技術改進
- MySQL:show slave status 關鍵值和MGRrelay log的清理策略MySql
- MYSQL 配置slave故障MySql
- 關於POST傳值太大後端獲取不到後端
- MySQL5.6.12的Innodb效能改進MySql
- MySQL核心月報2014.11-MySQL· 5.7改進·Recovery改進MySql
- 關於WebRTC視訊Android端實現的原理解讀WebAndroid
- 關於tensorflow 的資料讀取執行緒管理QueueRunner執行緒
- 關於websphere讀取war配置檔案出錯的問題Web
- 基於viper的配置讀取
- ·關於IFC檔案讀取類的設計的想法(LocalPlacement)
- 11g中關於控制檔案自動備份的改進
- mysql slave錯誤skipMySql
- MySQL Master/Slave Master/MasterMySqlAST
- mysql master和slave配置MySqlAST
- mysql slave 轉為 masterMySqlAST
- MySQL 5.5 Master/Slave 配置MySqlAST
- mysql slave-skip-errorsMySqlError
- Mysql加鎖過程詳解(3)-關於mysql 幻讀理解MySql
- Mysql加鎖過程詳解(2)-關於mysql 幻讀理解MySql
- MySQL表的增刪改查(進階)下MySql
- 使用 ProxySQL 改進 MySQL SSL 的連線效能MySql
- MySQL資料清理的需求分析和改進MySql
- 工具類,關於手工讀取 properties檔案引數
- MySQL資料庫 ---MySQL表的增刪改查(進階)MySql資料庫
- 【MySQL】MySQL 5.7 初探MySql
- 關於MySQLMySql