mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 09:55:33 +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>
366 lines
12 KiB
Text
366 lines
12 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
connection slave;
|
|
include/stop_slave.inc
|
|
CHANGE MASTER TO master_use_gtid= slave_pos;
|
|
include/start_slave.inc
|
|
connection master;
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (0, 0);
|
|
*** Test looking up a lot of different event positions and GTIDs.
|
|
CREATE FUNCTION gtid_eq(a VARCHAR(255), b VARCHAR(255)) RETURNS BOOLEAN DETERMINISTIC
|
|
BEGIN
|
|
DECLARE g VARCHAR(255);
|
|
IF a IS NULL OR b IS NULL OR LENGTH(a) != LENGTH(b) THEN
|
|
RETURN FALSE;
|
|
END IF;
|
|
SET a= CONCAT(a, ',');
|
|
SET b= CONCAT(',', b, ',');
|
|
WHILE LENGTH(a) > 0 DO
|
|
SET g= REGEXP_SUBSTR(a, '^[^,]+,');
|
|
SET a= SUBSTRING(a, LENGTH(g)+1);
|
|
SET b= REPLACE(b, CONCAT(',', g), ',');
|
|
END WHILE;
|
|
RETURN b = ',';
|
|
END //
|
|
SET @old_page_size= @@GLOBAL.binlog_gtid_index_page_size;
|
|
SET @old_span_min= @@GLOBAL.binlog_gtid_index_span_min;
|
|
*** A fair amount of work with default GTID index settings.
|
|
*** Testing 200 GTIDs with 50 test connects
|
|
connection master;
|
|
DELETE FROM t1 WHERE a >= 1000;
|
|
FLUSH NO_WRITE_TO_BINLOG BINARY LOGS;
|
|
CREATE TABLE rand_data(idx INT PRIMARY KEY, domain_id INT, server_id INT)
|
|
ENGINE=InnoDB;
|
|
INSERT INTO rand_data(idx, domain_id, server_id) VALUES (0, 0, 1);
|
|
INSERT INTO rand_data(idx, domain_id, server_id)
|
|
SELECT seq,
|
|
@tmp:=floor(5*POW(rand(42),2)),
|
|
100 + 5*@tmp + floor(5*rand(42))
|
|
FROM seq_1_to_200;
|
|
SELECT COUNT(*), SUM(domain_id), SUM(server_id) FROM rand_data;
|
|
COUNT(*) SUM(domain_id) SUM(server_id)
|
|
201 285 21852
|
|
CREATE TABLE gtid_data(
|
|
idx INT PRIMARY KEY,
|
|
gtid VARCHAR(44),
|
|
gtid_pos VARCHAR(255),
|
|
file VARCHAR(100),
|
|
pos INT,
|
|
row_count INT,
|
|
KEY(file, pos)) ENGINE=InnoDB;
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/sync_with_master_gtid.inc
|
|
include/stop_slave.inc
|
|
connection master;
|
|
SET @orig_domain_id= @@gtid_domain_id;
|
|
SET @orig_server_id= @@server_id;
|
|
SET gtid_domain_id= @orig_domain_id;
|
|
SET server_id= @orig_server_id;
|
|
SELECT COUNT(*) FROM gtid_data;
|
|
COUNT(*)
|
|
200
|
|
*** The result should be empty, otherwise some result is wrong:
|
|
SELECT idx, gtid_pos, BINLOG_GTID_POS(file, pos)
|
|
FROM gtid_data
|
|
WHERE NOT gtid_eq(CONVERT(gtid_pos USING utf8),BINLOG_GTID_POS(file, pos))
|
|
ORDER BY idx;
|
|
idx gtid_pos BINLOG_GTID_POS(file, pos)
|
|
connection slave;
|
|
SET @orig_pos= @@GLOBAL.gtid_slave_pos;
|
|
SET @orig_t1_limit= (SELECT MAX(a) FROM t1);
|
|
*** Now connect the slave to each position in turn, and test that
|
|
*** the right amount of data is replicated at each point.
|
|
*** Test slave connecting to some GTID positions where the position in
|
|
*** the master's binlog is different between the different domains.
|
|
*** Revind the slave and test on the same binlog data from the master as before.
|
|
connection slave;
|
|
SET sql_log_bin= 0;
|
|
TRUNCATE gtid_data;
|
|
DELETE FROM t1 WHERE a > @orig_t1_limit;
|
|
SET sql_log_bin= 1;
|
|
SET GLOBAL gtid_slave_pos= @orig_pos;
|
|
connection master;
|
|
connection slave;
|
|
connection master;
|
|
DROP TABLE gtid_data, rand_data;
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/start_slave.inc
|
|
include/sync_with_master_gtid.inc
|
|
connection master;
|
|
*** A lot of GTIDs with small btree pages to stress the Btree code.
|
|
SET GLOBAL binlog_gtid_index_page_size= 64;
|
|
SET GLOBAL binlog_gtid_index_span_min= 1;
|
|
*** Testing 1000 GTIDs with 50 test connects
|
|
connection master;
|
|
DELETE FROM t1 WHERE a >= 1000;
|
|
FLUSH NO_WRITE_TO_BINLOG BINARY LOGS;
|
|
CREATE TABLE rand_data(idx INT PRIMARY KEY, domain_id INT, server_id INT)
|
|
ENGINE=InnoDB;
|
|
INSERT INTO rand_data(idx, domain_id, server_id) VALUES (0, 0, 1);
|
|
INSERT INTO rand_data(idx, domain_id, server_id)
|
|
SELECT seq,
|
|
@tmp:=floor(10*POW(rand(150),2)),
|
|
100 + 5*@tmp + floor(5*rand(150))
|
|
FROM seq_1_to_1000;
|
|
SELECT COUNT(*), SUM(domain_id), SUM(server_id) FROM rand_data;
|
|
COUNT(*) SUM(domain_id) SUM(server_id)
|
|
1001 2881 116394
|
|
CREATE TABLE gtid_data(
|
|
idx INT PRIMARY KEY,
|
|
gtid VARCHAR(44),
|
|
gtid_pos VARCHAR(255),
|
|
file VARCHAR(100),
|
|
pos INT,
|
|
row_count INT,
|
|
KEY(file, pos)) ENGINE=InnoDB;
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/sync_with_master_gtid.inc
|
|
include/stop_slave.inc
|
|
connection master;
|
|
SET @orig_domain_id= @@gtid_domain_id;
|
|
SET @orig_server_id= @@server_id;
|
|
SET gtid_domain_id= @orig_domain_id;
|
|
SET server_id= @orig_server_id;
|
|
SELECT COUNT(*) FROM gtid_data;
|
|
COUNT(*)
|
|
1000
|
|
*** The result should be empty, otherwise some result is wrong:
|
|
SELECT idx, gtid_pos, BINLOG_GTID_POS(file, pos)
|
|
FROM gtid_data
|
|
WHERE NOT gtid_eq(CONVERT(gtid_pos USING utf8),BINLOG_GTID_POS(file, pos))
|
|
ORDER BY idx;
|
|
idx gtid_pos BINLOG_GTID_POS(file, pos)
|
|
connection slave;
|
|
SET @orig_pos= @@GLOBAL.gtid_slave_pos;
|
|
SET @orig_t1_limit= (SELECT MAX(a) FROM t1);
|
|
*** Now connect the slave to each position in turn, and test that
|
|
*** the right amount of data is replicated at each point.
|
|
*** Test slave connecting to some GTID positions where the position in
|
|
*** the master's binlog is different between the different domains.
|
|
*** Revind the slave and test on the same binlog data from the master as before.
|
|
connection slave;
|
|
SET sql_log_bin= 0;
|
|
TRUNCATE gtid_data;
|
|
DELETE FROM t1 WHERE a > @orig_t1_limit;
|
|
SET sql_log_bin= 1;
|
|
SET GLOBAL gtid_slave_pos= @orig_pos;
|
|
connection master;
|
|
connection slave;
|
|
connection master;
|
|
DROP TABLE gtid_data, rand_data;
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/start_slave.inc
|
|
include/sync_with_master_gtid.inc
|
|
connection master;
|
|
*** Small page size with sparse index.
|
|
SET GLOBAL binlog_gtid_index_page_size= 64;
|
|
SET GLOBAL binlog_gtid_index_span_min= 2048;
|
|
*** Testing 200 GTIDs with 50 test connects
|
|
connection master;
|
|
DELETE FROM t1 WHERE a >= 1000;
|
|
FLUSH NO_WRITE_TO_BINLOG BINARY LOGS;
|
|
CREATE TABLE rand_data(idx INT PRIMARY KEY, domain_id INT, server_id INT)
|
|
ENGINE=InnoDB;
|
|
INSERT INTO rand_data(idx, domain_id, server_id) VALUES (0, 0, 1);
|
|
INSERT INTO rand_data(idx, domain_id, server_id)
|
|
SELECT seq,
|
|
@tmp:=floor(10*POW(rand(666),2)),
|
|
100 + 5*@tmp + floor(5*rand(666))
|
|
FROM seq_1_to_200;
|
|
SELECT COUNT(*), SUM(domain_id), SUM(server_id) FROM rand_data;
|
|
COUNT(*) SUM(domain_id) SUM(server_id)
|
|
201 599 23410
|
|
CREATE TABLE gtid_data(
|
|
idx INT PRIMARY KEY,
|
|
gtid VARCHAR(44),
|
|
gtid_pos VARCHAR(255),
|
|
file VARCHAR(100),
|
|
pos INT,
|
|
row_count INT,
|
|
KEY(file, pos)) ENGINE=InnoDB;
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/sync_with_master_gtid.inc
|
|
include/stop_slave.inc
|
|
connection master;
|
|
SET @orig_domain_id= @@gtid_domain_id;
|
|
SET @orig_server_id= @@server_id;
|
|
SET gtid_domain_id= @orig_domain_id;
|
|
SET server_id= @orig_server_id;
|
|
SELECT COUNT(*) FROM gtid_data;
|
|
COUNT(*)
|
|
200
|
|
*** The result should be empty, otherwise some result is wrong:
|
|
SELECT idx, gtid_pos, BINLOG_GTID_POS(file, pos)
|
|
FROM gtid_data
|
|
WHERE NOT gtid_eq(CONVERT(gtid_pos USING utf8),BINLOG_GTID_POS(file, pos))
|
|
ORDER BY idx;
|
|
idx gtid_pos BINLOG_GTID_POS(file, pos)
|
|
connection slave;
|
|
SET @orig_pos= @@GLOBAL.gtid_slave_pos;
|
|
SET @orig_t1_limit= (SELECT MAX(a) FROM t1);
|
|
*** Now connect the slave to each position in turn, and test that
|
|
*** the right amount of data is replicated at each point.
|
|
*** Test slave connecting to some GTID positions where the position in
|
|
*** the master's binlog is different between the different domains.
|
|
*** Revind the slave and test on the same binlog data from the master as before.
|
|
connection slave;
|
|
SET sql_log_bin= 0;
|
|
TRUNCATE gtid_data;
|
|
DELETE FROM t1 WHERE a > @orig_t1_limit;
|
|
SET sql_log_bin= 1;
|
|
SET GLOBAL gtid_slave_pos= @orig_pos;
|
|
connection master;
|
|
connection slave;
|
|
connection master;
|
|
DROP TABLE gtid_data, rand_data;
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/start_slave.inc
|
|
include/sync_with_master_gtid.inc
|
|
connection master;
|
|
*** Medium page size.
|
|
SET GLOBAL binlog_gtid_index_page_size= 512;
|
|
SET GLOBAL binlog_gtid_index_span_min= 512;
|
|
*** Testing 200 GTIDs with 50 test connects
|
|
connection master;
|
|
DELETE FROM t1 WHERE a >= 1000;
|
|
FLUSH NO_WRITE_TO_BINLOG BINARY LOGS;
|
|
CREATE TABLE rand_data(idx INT PRIMARY KEY, domain_id INT, server_id INT)
|
|
ENGINE=InnoDB;
|
|
INSERT INTO rand_data(idx, domain_id, server_id) VALUES (0, 0, 1);
|
|
INSERT INTO rand_data(idx, domain_id, server_id)
|
|
SELECT seq,
|
|
@tmp:=floor(10*POW(rand(1024),2)),
|
|
100 + 5*@tmp + floor(5*rand(1024))
|
|
FROM seq_1_to_200;
|
|
SELECT COUNT(*), SUM(domain_id), SUM(server_id) FROM rand_data;
|
|
COUNT(*) SUM(domain_id) SUM(server_id)
|
|
201 555 23160
|
|
CREATE TABLE gtid_data(
|
|
idx INT PRIMARY KEY,
|
|
gtid VARCHAR(44),
|
|
gtid_pos VARCHAR(255),
|
|
file VARCHAR(100),
|
|
pos INT,
|
|
row_count INT,
|
|
KEY(file, pos)) ENGINE=InnoDB;
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/sync_with_master_gtid.inc
|
|
include/stop_slave.inc
|
|
connection master;
|
|
SET @orig_domain_id= @@gtid_domain_id;
|
|
SET @orig_server_id= @@server_id;
|
|
SET gtid_domain_id= @orig_domain_id;
|
|
SET server_id= @orig_server_id;
|
|
SELECT COUNT(*) FROM gtid_data;
|
|
COUNT(*)
|
|
200
|
|
*** The result should be empty, otherwise some result is wrong:
|
|
SELECT idx, gtid_pos, BINLOG_GTID_POS(file, pos)
|
|
FROM gtid_data
|
|
WHERE NOT gtid_eq(CONVERT(gtid_pos USING utf8),BINLOG_GTID_POS(file, pos))
|
|
ORDER BY idx;
|
|
idx gtid_pos BINLOG_GTID_POS(file, pos)
|
|
connection slave;
|
|
SET @orig_pos= @@GLOBAL.gtid_slave_pos;
|
|
SET @orig_t1_limit= (SELECT MAX(a) FROM t1);
|
|
*** Now connect the slave to each position in turn, and test that
|
|
*** the right amount of data is replicated at each point.
|
|
*** Test slave connecting to some GTID positions where the position in
|
|
*** the master's binlog is different between the different domains.
|
|
*** Revind the slave and test on the same binlog data from the master as before.
|
|
connection slave;
|
|
SET sql_log_bin= 0;
|
|
TRUNCATE gtid_data;
|
|
DELETE FROM t1 WHERE a > @orig_t1_limit;
|
|
SET sql_log_bin= 1;
|
|
SET GLOBAL gtid_slave_pos= @orig_pos;
|
|
connection master;
|
|
connection slave;
|
|
connection master;
|
|
DROP TABLE gtid_data, rand_data;
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/start_slave.inc
|
|
include/sync_with_master_gtid.inc
|
|
connection master;
|
|
*** Large page size.
|
|
SET GLOBAL binlog_gtid_index_page_size= 16384;
|
|
SET GLOBAL binlog_gtid_index_span_min= 1;
|
|
*** Testing 200 GTIDs with 50 test connects
|
|
connection master;
|
|
DELETE FROM t1 WHERE a >= 1000;
|
|
FLUSH NO_WRITE_TO_BINLOG BINARY LOGS;
|
|
CREATE TABLE rand_data(idx INT PRIMARY KEY, domain_id INT, server_id INT)
|
|
ENGINE=InnoDB;
|
|
INSERT INTO rand_data(idx, domain_id, server_id) VALUES (0, 0, 1);
|
|
INSERT INTO rand_data(idx, domain_id, server_id)
|
|
SELECT seq,
|
|
@tmp:=floor(10*POW(rand(12345),2)),
|
|
100 + 5*@tmp + floor(5*rand(12345))
|
|
FROM seq_1_to_200;
|
|
SELECT COUNT(*), SUM(domain_id), SUM(server_id) FROM rand_data;
|
|
COUNT(*) SUM(domain_id) SUM(server_id)
|
|
201 571 23252
|
|
CREATE TABLE gtid_data(
|
|
idx INT PRIMARY KEY,
|
|
gtid VARCHAR(44),
|
|
gtid_pos VARCHAR(255),
|
|
file VARCHAR(100),
|
|
pos INT,
|
|
row_count INT,
|
|
KEY(file, pos)) ENGINE=InnoDB;
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/sync_with_master_gtid.inc
|
|
include/stop_slave.inc
|
|
connection master;
|
|
SET @orig_domain_id= @@gtid_domain_id;
|
|
SET @orig_server_id= @@server_id;
|
|
SET gtid_domain_id= @orig_domain_id;
|
|
SET server_id= @orig_server_id;
|
|
SELECT COUNT(*) FROM gtid_data;
|
|
COUNT(*)
|
|
200
|
|
*** The result should be empty, otherwise some result is wrong:
|
|
SELECT idx, gtid_pos, BINLOG_GTID_POS(file, pos)
|
|
FROM gtid_data
|
|
WHERE NOT gtid_eq(CONVERT(gtid_pos USING utf8),BINLOG_GTID_POS(file, pos))
|
|
ORDER BY idx;
|
|
idx gtid_pos BINLOG_GTID_POS(file, pos)
|
|
connection slave;
|
|
SET @orig_pos= @@GLOBAL.gtid_slave_pos;
|
|
SET @orig_t1_limit= (SELECT MAX(a) FROM t1);
|
|
*** Now connect the slave to each position in turn, and test that
|
|
*** the right amount of data is replicated at each point.
|
|
*** Test slave connecting to some GTID positions where the position in
|
|
*** the master's binlog is different between the different domains.
|
|
*** Revind the slave and test on the same binlog data from the master as before.
|
|
connection slave;
|
|
SET sql_log_bin= 0;
|
|
TRUNCATE gtid_data;
|
|
DELETE FROM t1 WHERE a > @orig_t1_limit;
|
|
SET sql_log_bin= 1;
|
|
SET GLOBAL gtid_slave_pos= @orig_pos;
|
|
connection master;
|
|
connection slave;
|
|
connection master;
|
|
DROP TABLE gtid_data, rand_data;
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/start_slave.inc
|
|
include/sync_with_master_gtid.inc
|
|
connection master;
|
|
connection master;
|
|
SET GLOBAL binlog_gtid_index_page_size= @old_page_size;
|
|
SET GLOBAL binlog_gtid_index_span_min= @old_span_min;
|
|
DROP TABLE t1;
|
|
DROP FUNCTION gtid_eq;
|
|
include/rpl_end.inc
|