加载中…MySQL读写分离session_track_gtids解析
(2022-12-20 17:58:02)
标签：
mysql |
分类： mysql源码相关 |
目前业内常见的一种mysql读写分离实现可以基于wait_for_executed_gtid_set实现，即在去从库查询前，为了保证查询的一致性，等待从库达到要求的gtid值后再进行查询。典型的实现例如proxysql。
333
340
341 class Session_gtids_tracker
342
: public State_tracker,
343
Session_consistency_gtids_ctx::Ctx_change_listener
{
344 private:
345 void
reset();
346
Session_gtids_ctx_encoder *m_encoder;
347
348 public:
349
350
Session_gtids_tracker()
351
:
Session_consistency_gtids_ctx::Ctx_change_listener(),
352
m_encoder(nullptr) {}
353
354
~Session_gtids_tracker() override {
355
359
if (m_enabled && current_thd)
360
current_thd->rpl_thd_ctx.session_gtids_ctx()
361
.unregister_ctx_change_listener(this);
362
if (m_encoder) delete m_encoder;
363
}
364
365 bool
enable(THD *thd) override { return update(thd); }
366 bool
check(THD *, set_var *) override { return false; }
367 bool
update(THD *thd) override;
368 bool
store(THD *thd, String &buf) override;
369 void
mark_as_changed(THD *thd, LEX_CSTRING tracked_item_name)
override;
370
371 //
implementation of the Session_gtids_ctx::Ctx_change_listener
372 void
notify_session_gtids_ctx_change() override {
373
mark_as_changed(nullptr, {});
374
}
375 };
67 bool
Session_consistency_gtids_ctx::notify_after_transaction_commit(
68
const THD *thd) {
69
DBUG_TRACE;
70
assert(thd);
71 bool
res = false;
72
73 if
(!shall_collect(thd)) return res;
74
75 if
(m_curr_session_track_gtids == SESSION_TRACK_GTIDS_ALL_GTIDS)
{
76
82
global_sid_lock->wrlock();
83
res =
m_gtid_set->add_gtid_set(gtid_state->get_executed_gtids())
!=
84
RETURN_STATUS_OK;
85
global_sid_lock->unlock();
86
87
if (!res) notify_ctx_change_listener();
88
}
89
90
return res;
91
}
93 bool
Session_consistency_gtids_ctx::notify_after_gtid_executed_update(
94
const THD *thd) {
95
DBUG_TRACE;
96
assert(thd);
97 bool
res = false;
98
99 if
(!shall_collect(thd)) return res;
at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/binlog.cc:8552
at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/binlog.cc:9061
276 bool
encode(THD *thd, String &buf) override {
277
const Gtid_set *state =
thd->rpl_thd_ctx.session_gtids_ctx().state();
278
279
if (!state->is_empty()) {
280
285
ulonglong tracker_type_enclen
=
286
1 ;
287
ulonglong encoding_spec_enclen
=
288
1 ;
289
ulonglong gtids_string_len
=
290
state->get_string_length(&Gtid_set::default_string_format);
291
ulonglong
gtids_string_len_enclen = net_length_size(gtids_string_len);
292
ulonglong entity_len =
293
encoding_spec_enclen + gtids_string_len_enclen +
gtids_string_len;
294
ulonglong entity_len_enclen =
net_length_size(entity_len);
295
ulonglong total_enclen =
tracker_type_enclen + entity_len_enclen +
296
encoding_spec_enclen +
gtids_string_len_enclen +
297
gtids_string_len;
298
299
300
uchar *to = (uchar
*)buf.prep_append(total_enclen, EXTRA_ALLOC);
301
302
305
306
307
*to =
(uchar)SESSION_TRACK_GTIDS;
308
to++;
309
310
311
to = net_store_length(to,
entity_len);
312
313
314
*to =
(uchar)encoding_specification();
315
to++;
316
317
318
to = net_store_length(to,
gtids_string_len);
319
320
321
state->to_string((char
*)to);
322
}
323
return false;
324
}
909 if
(protocol->has_client_capability(CLIENT_SESSION_TRACK)
&&
910
thd->session_tracker.enabled_any()
&&
911
thd->session_tracker.changed_any()) {
912
server_status |=
SERVER_SESSION_STATE_CHANGED;
913
state_changed = true;
914
}
937 if
(protocol->has_client_capability(CLIENT_SESSION_TRACK)) {
938
939
if (state_changed || (message &&
message[0]))
940
pos = net_store_data(pos,
pointer_cast(message),
941
message ?
strlen(message) : 0);
942
943
if (unlikely(state_changed)) {
944
store.set_charset(thd->variables.collation_database);
945
946
950
store.append((const char
*)start, (pos - start), MYSQL_ERRMSG_SIZE);
951
952
953
thd->session_tracker.store(thd, store);
954
955
start = (uchar
*)store.ptr();
956
pos = start +
store.length();
957
}
958 }
else if (message && message[0]) {
那proxy如何去获取从库需要等待的gtid呢？一种方式是proxy后台线程定期去后端主库查询，这样有时效性问题，不能确保某个会话去从库查询一定能查到自己刚写入的数据。另一种是基于mysql的Session
State Trackers。
Session State
Trackers中有一个tracker是gtid相关的，由参数session_track_gtids
控制。session_track_gtids 是mysql session状态跟踪相关的一个参数，允许的值为：
OFF： 默认值，不追踪gtid
OWN_GTID： 追踪上次反馈后当前会话所提交的所有新增gtid
ALL_GTIDS： 直接返回gtid_executed系统变量的值，在当前会话事务完成提交后读取。
1. Session_gtids_tracker
MySQL内部使用session_tracker.cc中的Session_tracker来追踪所有需要追踪的session状态，主要有
1501 void Session_tracker::init(const CHARSET_INFO *char_set)
{
1502
m_trackers[SESSION_SYSVARS_TRACKER] =
1503
new (std::nothrow)
Session_sysvars_tracker(char_set);
1504
m_trackers[CURRENT_SCHEMA_TRACKER] =
1505
new (std::nothrow) Current_schema_tracker;
1506
m_trackers[SESSION_STATE_CHANGE_TRACKER] =
1507
new (std::nothrow)
Session_state_change_tracker;
1508
m_trackers[SESSION_GTIDS_TRACKER] = new
(std::nothrow) Session_gtids_tracker;
1509
m_trackers[TRANSACTION_INFO_TRACKER] =
1510
new (std::nothrow)
Transaction_state_tracker;
1511
m_trackers[TRACK_TRANSACTION_STATE] =
1512
new (std::nothrow)
Session_transaction_state;
1513 }
可以看出，gtid的具体实现tracker为Session_gtids_tracker
2.
GTID的Trace代码入口
GTID的trace模式有OWN_GTID和ALL_GTIDS，两者获取的时间点还有锁粒度不同。
OWN_GTID是在ordered_commit的process_commit_stage_queue阶段获取，是纯会话内操作，无需锁保护。
ALL_GTIDS是在会话完成事务提交之后获取的，在trans_commit_stmt
中完成ha_commit_trans后；因为读取的是gtid_state的gtid_executed，所以用的是全局大锁global_sid_lock，并且是写锁（这里感觉没必要用写锁，给官方报了个bug https://bugs.mysql.com/109435）。
2.1
SESSION_TRACK_GTIDS_ALL_GTIDS
在rpl_context.cc中，可以看出是使用全局大锁。
调用路径为transaction.cc:trans_commit调用
thd->rpl_thd_ctx.session_gtids_ctx().notify_after_transaction_commit
#0
Session_consistency_gtids_ctx::notify_after_transaction_commit
(this=0x7fff40003198, thd=0x7fff40000da0) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/rpl_context.cc:73
#1 0x000000000340c50f in trans_commit_stmt
(thd=0x7fff40000da0, ignore_global_read_lock=false) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/transaction.cc:538
#2 0x00000000032793df in
mysql_execute_command (thd=0x7fff40000da0, first_level=true) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:4778
#3 0x000000000327a872 in
dispatch_sql_command (thd=0x7fff40000da0,
parser_state=0x7fffa82f2b10) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:5207
#4 0x0000000003270e6d in dispatch_command
(thd=0x7fff40000da0, com_data=0x7fffa82f3c00, command=COM_QUERY) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:1957
#5 0x000000000326ef7b in do_command
(thd=0x7fff40000da0) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:1352
#6 0x0000000003450a95 in handle_connection
(arg=0xa886900) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/conn_handler/connection_handler_per_thread.cc:302
#7 0x0000000004fff5d2 in pfs_spawn_thread
(arg=0xa8a2260) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/storage/perfschema/pfs.cc:2942
#8 0x00007ffff7bc16ba in start_thread
(arg=0x7fffa82f4700) at pthread_create.c:333
#9 0x00007ffff5e1241d in clone () at
../sysdeps/unix/sysv/linux/x86_64/clone.S:109
2.2
SESSION_TRACK_GTIDS_OWN_GTID
在rpl_context.cc中，无需锁
100
101 if
(m_curr_session_track_gtids == SESSION_TRACK_GTIDS_OWN_GTID)
{
102
assert(global_gtid_mode.get() !=
Gtid_mode::OFF);
103
assert(thd->owned_gtid.sidno > 0);
104
const Gtid >id = thd->owned_gtid;
105 if
(gtid.sidno == -1) // we need to add
thd->owned_gtid_set
106
{
107
108 #ifdef HAVE_GTID_NEXT_LIST
109
assert(!thd->owned_gtid_set.is_empty());
110
res =
m_gtid_set->add_gtid_set(&thd->owned_gtid_set) !=
RETURN_STATUS_OK;
111 #else
112
assert(0);
113 #endif
114 }
else if (gtid.sidno > 0) // only one
gtid
115
{
116
124
rpl_sidno local_set_sidno =
m_sid_map->add_sid(thd->owned_sid);
125
126
assert(!m_gtid_set->contains_gtid(local_set_sidno,
gtid.gno));
127
res =
m_gtid_set->ensure_sidno(local_set_sidno) !=
RETURN_STATUS_OK;
128
if (!res)
m_gtid_set->_add_gtid(local_set_sidno, gtid.gno);
129
}
130
131 if
(!res) notify_ctx_change_listener();
132 }
133 return res;
134 }
调用路径为rpl_gtid_state.cc的Gtid_state
::update_gtids_impl_own_gtid_set调用thd->rpl_thd_ctx.session_gtids_ctx().notify_after_gtid_executed_update。
#0
Session_consistency_gtids_ctx::notify_after_gtid_executed_update
(this=0x7fff40003198, thd=0x7fff40000da0) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/rpl_context.cc:99
#1 0x000000000443a580 in
Gtid_state::update_gtids_impl_own_gtid (this=0xa75de70,
thd=0x7fff40000da0, is_commit=true) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/rpl_gtid_state.cc:881
#2 0x0000000004437c43 in
Gtid_state::update_commit_group (this=0xa75de70,
first_thd=0x7fff40000da0) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/rpl_gtid_state.cc:184
#3 0x00000000043bec64 in
MYSQL_BIN_LOG::process_commit_stage_queue (this=0x80df1c0 ,
thd=0x7fff40000da0, first=0x7fff40000da0)
#4 0x00000000043c052f in
MYSQL_BIN_LOG::ordered_commit (this=0x80df1c0 , thd=0x7fff40000da0,
all=false, skip_commit=false)
#5 0x00000000043be2c8 in
MYSQL_BIN_LOG::commit (this=0x80df1c0 , thd=0x7fff40000da0,
all=false) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/binlog.cc:8303
#6 0x00000000035f25ae in ha_commit_trans
(thd=0x7fff40000da0, all=false, ignore_global_read_lock=false) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/handler.cc:1766
#7 0x000000000340c454 in trans_commit_stmt
(thd=0x7fff40000da0, ignore_global_read_lock=false) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/transaction.cc:532
#8 0x00000000032793df in
mysql_execute_command (thd=0x7fff40000da0, first_level=true) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:4778
#9 0x000000000327a872 in
dispatch_sql_command (thd=0x7fff40000da0,
parser_state=0x7fffa82f2b10) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:5207
#10 0x0000000003270e6d in dispatch_command
(thd=0x7fff40000da0, com_data=0x7fffa82f3c00, command=COM_QUERY) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:1957
#11 0x000000000326ef7b in do_command (thd=0x7fff40000da0) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:1352
#12 0x0000000003450a95 in handle_connection (arg=0xa886900) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/conn_handler/connection_handler_per_thread.cc:302
#13 0x0000000004fff5d2 in pfs_spawn_thread (arg=0xa8a2260) at
/home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/storage/perfschema/pfs.cc:2942
#14 0x00007ffff7bc16ba in start_thread (arg=0x7fffa82f4700) at
pthread_create.c:333
#15 0x00007ffff5e1241d in clone () at
../sysdeps/unix/sysv/linux/x86_64/clone.S:109
3.
Trace的GTID值通过OK包返回客户端
其中负责协议包编码的是Session_gtids_ctx_encoder *m_encoder; 具体的子类为：class
Session_gtids_ctx_encoder_string : public
Session_gtids_ctx_encoder。
调用路径为：Session_tracker::store ->
Session_gtids_tracker::store ->
Session_gtids_ctx_encoder_string::encode
具体的encode实现如下，主要的payload为 [ tracker type] [len] [ encoding
spec ] [gtid string len] [gtid string]
最终是在OK包中编码gtid信息：protocol_classic.cc的net_send_ok函数里实现
static bool net_send_ok {
...
...
...
}
MySQL的OK包payload协议参见：
https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_ok_packet.html
4.
Proxy层获取GTID
Proxy层只需设置后端mysql开启session_track_gtids=OWN_GTID，并通过解析ok包获取刚提交会话的gtid值。对于后续同会话的读请求，可以在从库wait_for_executed_gtid_set来等待，确保从库执行完成对应gtid后，再执行查询，确保一致性。
这里使用OWN_GTID主要原因有：
1. OWN_GTID是会话内操作，相对轻量，对性能影响可控
2. OWN_GTID已足够确保会话内的因果序一致性了
3.
即便使用了ALL_GTIDS也无法实现强一致性，因为gtid是在事务提交后获取的，并不是查询执行前获取的。而ALL_GTIDS需要全局大锁，明显影响性能。
转载请注明转自高孝鑫的博客！
喜欢
0
赠金笔
前一篇：GPLV2协议重点FQ整理与总结