mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
d039346a7a
Improve the performance of slave connect using B+-Tree indexes on each binlog file. The index allows fast lookup of a GTID position to the corresponding offset in the binlog file, as well as lookup of a position to find the corresponding GTID position. This eliminates a costly sequential scan of the starting binlog file to find the GTID starting position when a slave connects. This is especially costly if the binlog file is not cached in memory (IO cost), or if it is encrypted or a lot of slaves connect simultaneously (CPU cost). The size of the index files is generally less than 1% of the binlog data, so not expected to be an issue. Most of the work writing the index is done as a background task, in the binlog background thread. This minimises the performance impact on transaction commit. A simple global mutex is used to protect index reads and (background) index writes; this is fine as slave connect is a relatively infrequent operation. Here are the user-visible options and status variables. The feature is on by default and is expected to need no tuning or configuration for most users. binlog_gtid_index On by default. Can be used to disable the indexes for testing purposes. binlog_gtid_index_page_size (default 4096) Page size to use for the binlog GTID index. This is the size of the nodes in the B+-tree used internally in the index. A very small page-size (64 is the minimum) will be less efficient, but can be used to stress the BTree-code during testing. binlog_gtid_index_span_min (default 65536) Control sparseness of the binlog GTID index. If set to N, at most one index record will be added for every N bytes of binlog file written. This can be used to reduce the number of records in the index, at the cost only of having to scan a few more events in the binlog file before finding the target position Two status variables are available to monitor the use of the GTID indexes: Binlog_gtid_index_hit Binlog_gtid_index_miss The "hit" status increments for each successful lookup in a GTID index. The "miss" increments when a lookup is not possible. This indicates that the index file is missing (eg. binlog written by old server version without GTID index support), or corrupt. Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
66 lines
2.1 KiB
Text
66 lines
2.1 KiB
Text
--source include/have_innodb.inc
|
|
# Don't test this under valgrind, memory leaks will occur
|
|
--source include/not_valgrind.inc
|
|
# Avoid CrashReporter popup on Mac
|
|
--source include/not_crashrep.inc
|
|
# Binary must be compiled with debug for crash to occur
|
|
--source include/have_debug.inc
|
|
--source include/have_binlog_format_row.inc
|
|
|
|
# We have an .opt file that sets a small page size and disables sparseness,
|
|
# so we get something non-trivial in the GTID index even with a small amount
|
|
# of binlogged events.
|
|
|
|
--echo *** Test that binlog GTID index is recovered after a crash.
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
|
|
|
|
--disable_query_log
|
|
INSERT INTO t1 VALUES (0, 0);
|
|
INSERT INTO t1 VALUES (1, 0);
|
|
INSERT INTO t1 VALUES (2, 0);
|
|
--let $i= 10
|
|
while ($i < 20) {
|
|
eval INSERT INTO t1 VALUES ($i, 0);
|
|
inc $i;
|
|
}
|
|
--let $file= query_get_value(SHOW MASTER STATUS, File, 1)
|
|
--let $pos1= query_get_value(SHOW MASTER STATUS, Position, 1)
|
|
--let $gtid1= `SELECT @@gtid_binlog_pos`
|
|
while ($i < 30) {
|
|
eval INSERT INTO t1 VALUES ($i, 0);
|
|
inc $i;
|
|
}
|
|
--let $pos2= query_get_value(SHOW MASTER STATUS, Position, 1)
|
|
--let $gtid2= `SELECT @@gtid_binlog_pos`
|
|
while ($i < 40) {
|
|
eval INSERT INTO t1 VALUES ($i, 0);
|
|
inc $i;
|
|
}
|
|
--let $pos3= query_get_value(SHOW MASTER STATUS, Position, 1)
|
|
--let $gtid3= `SELECT @@gtid_binlog_pos`
|
|
INSERT INTO t1 VALUES (50, 0);
|
|
INSERT INTO t1 VALUES (51, 0);
|
|
|
|
--disable_ps2_protocol
|
|
FLUSH NO_WRITE_TO_BINLOG STATUS;
|
|
eval SELECT BINLOG_GTID_POS('$file', $pos1) = "$gtid1" AS Ok;
|
|
eval SELECT BINLOG_GTID_POS('$file', $pos2) = "$gtid2" AS Ok;
|
|
eval SELECT BINLOG_GTID_POS('$file', $pos3) = "$gtid3" AS Ok;
|
|
--enable_query_log
|
|
SHOW STATUS LIKE 'binlog_gtid_index_%';
|
|
--enable_ps2_protocol
|
|
|
|
--echo *** Crash the server, check that GTID index can be used after restart.
|
|
--source include/crash_mysqld.inc
|
|
|
|
--disable_ps2_protocol
|
|
FLUSH NO_WRITE_TO_BINLOG STATUS;
|
|
--disable_query_log
|
|
eval SELECT BINLOG_GTID_POS('$file', $pos1) = "$gtid1" AS Ok;
|
|
eval SELECT BINLOG_GTID_POS('$file', $pos2) = "$gtid2" AS Ok;
|
|
eval SELECT BINLOG_GTID_POS('$file', $pos3) = "$gtid3" AS Ok;
|
|
--enable_query_log
|
|
SHOW STATUS LIKE 'binlog_gtid_index_%';
|
|
--enable_ps2_protocol
|
|
|
|
DROP TABLE t1;
|