From 942b0e573be179f6e8314a165a19cb54e4a4c778 Mon Sep 17 00:00:00 2001 From: Venkatesh Prasad Venugopal Date: Wed, 19 Aug 2020 11:14:59 +0530 Subject: [PATCH] Bug#100118 Server doesn't restart because of too many gaps in the mysql.gtid_executed table Problem & Analysis ------------------ Since the introduction of a dedicated thread for persisting GTIDs of InnoDB transactions by WL#9211, new behavior for updating the mysql.gtid_executed table is as follows, - If binary log is enabled, the mysql.gtid_executed table is updated on next binlog rotation (either by FLUSH LOGS or server restart). - If binary log is disabled or log_slave_updates is disabled (for slave threads), then - If it is an InnoDB transaction, it leaves it to the gtid persister thread to update. - Otherwise, it writes its GTID into mysql.gtid_executed table. On a binlogless slave having both transactional and non-transactional workloads, as per the current design, when the GTID persister thread reaches the threshold (either once per 1k transactions or every 1 second), it flushes its list (updates the table) to the mysql.gtid_executed table and tries to compress the gtid_executed table. It succeeds compressing only the first few rows (because it filled the gaps present in the beginning of the table by merging transactional updates) and fails to compress further rows of the table (because of the gaps introduced by non-transactional updates). In every attempt it tries to merge the table, it merges only a few consecutive rows and leaves the other rows as is, thereby taking more time for scanning the full table. By the time it finishes the table scan, the slave applier threads would insert few more entries to the end of the table. As a result, when there is a high load on the server, if the GTID persister thread once starts scanning the table, it is more likely that the scan never stops and thus causes the flush list to grow causing more gaps in the mysql.executed table. In addition to the above, on a binlogless slave, explicit gtid compression requests raised from the below queries also caused the persister thread to be stuck while compressing the gtid_executed table. 1. FLUSH ENGINE LOGS 2. RESET MASTER, RESET SLAVE and CHANGE MASTER 3. Binary/relay log rotation 4. expire logs_by_seconds and expire_logs_by_days Fix --- Made the GTID persister thread to compress the mysql.gtid_executed table only on explicit request from innobase_flush_logs() when both binary logging and log_slave_updates are enabled. As per the current fix, the final truth table of server behavior is: +-------------+----------+-------------------+-------------------+--------------+ | Is server | | | Explicit request | Result | | Standalone/ | Binlog | log_slave_updates | created on | | | Slave? | enabled? | enabled? | FLUSH ENGINE LOGS | Persister/ | | | | | | Compressor | +=============+==========+===================+===================+==============+ | Standalone | Yes | Yes | Yes | Persister | +-------------+----------+-------------------+-------------------+--------------+ | Standalone | Yes | No | No | Compressor | +-------------+----------+-------------------+-------------------+--------------+ | Standalone | No | Yes | N/A | | +-------------+----------+-------------------+-------------------+ Invalid case | | Standalone | No | No | N/A | | +-------------+----------+-------------------+-------------------+--------------+ | Slave | Yes | Yes | Yes | Persister | +-------------+----------+-------------------+-------------------+--------------+ | Slave | Yes | No | No | Compressor | +-------------+----------+-------------------+-------------------+--------------+ | Slave | No | Yes | N/A | Invalid case | +-------------+----------+-------------------+-------------------+--------------+ | Slave | No | No | No | Compressor | +-------------+----------+-------------------+-------------------+--------------+ --- .../binlog_gtid_compressor_validation.result | 28 ++++ .../r/binlog_gtid_persister_validation.result | 25 ++++ .../t/binlog_gtid_compressor_validation.test | 97 ++++++++++++++ .../t/binlog_gtid_persister_validation.test | 91 +++++++++++++ .../r/local_basic_gtid_compression.result | 45 +++++++ .../t/local_basic_gtid_compression-master.opt | 1 + .../clone/t/local_basic_gtid_compression.test | 124 ++++++++++++++++++ ...logless_slave_compressor_validation.result | 41 ++++++ ...rpl_gtid_slave_persister_validation.result | 38 ++++++ ...less_slave_compressor_validation-slave.opt | 1 + ...inlogless_slave_compressor_validation.test | 116 ++++++++++++++++ .../rpl_gtid_slave_persister_validation.test | 110 ++++++++++++++++ sql/rpl_gtid_persist.cc | 28 ++-- sql/rpl_gtid_persist.h | 5 + storage/innobase/clone/clone0repl.cc | 10 +- storage/innobase/handler/ha_innodb.cc | 11 +- 16 files changed, 758 insertions(+), 13 deletions(-) create mode 100644 mysql-test/suite/binlog_gtid/r/binlog_gtid_compressor_validation.result create mode 100644 mysql-test/suite/binlog_gtid/r/binlog_gtid_persister_validation.result create mode 100644 mysql-test/suite/binlog_gtid/t/binlog_gtid_compressor_validation.test create mode 100644 mysql-test/suite/binlog_gtid/t/binlog_gtid_persister_validation.test create mode 100644 mysql-test/suite/clone/r/local_basic_gtid_compression.result create mode 100644 mysql-test/suite/clone/t/local_basic_gtid_compression-master.opt create mode 100644 mysql-test/suite/clone/t/local_basic_gtid_compression.test create mode 100644 mysql-test/suite/rpl_gtid/r/rpl_gtid_binlogless_slave_compressor_validation.result create mode 100644 mysql-test/suite/rpl_gtid/r/rpl_gtid_slave_persister_validation.result create mode 100644 mysql-test/suite/rpl_gtid/t/rpl_gtid_binlogless_slave_compressor_validation-slave.opt create mode 100644 mysql-test/suite/rpl_gtid/t/rpl_gtid_binlogless_slave_compressor_validation.test create mode 100644 mysql-test/suite/rpl_gtid/t/rpl_gtid_slave_persister_validation.test diff --git a/mysql-test/suite/binlog_gtid/r/binlog_gtid_compressor_validation.result b/mysql-test/suite/binlog_gtid/r/binlog_gtid_compressor_validation.result new file mode 100644 index 00000000000..cf9566b9fac --- /dev/null +++ b/mysql-test/suite/binlog_gtid/r/binlog_gtid_compressor_validation.result @@ -0,0 +1,28 @@ +RESET MASTER; +# +# 1. Create an InnoDB table and insert 2 rows. +# Wait till each entry appears individually in the table. +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE = INNODB; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +# +# 2. Assert that table is not compressed. +include/assert.inc [COMMITTED_GTIDS SERVER_UUID:1-3] +include/assert.inc [The mysql gtid table should have 3 rows.] +# +# 3. Enable debug points to enable force compression and to print the +# compression info for validation purpose. +# Adding debug point 'simulate_force_compress' to @@GLOBAL.debug +# Adding debug point 'print_gtid_compression_info' to @@GLOBAL.debug +# +# 4. Insert third row and assert that table is compressed. +INSERT INTO t1 VALUES (3); +include/assert.inc [The mysql gtid table should have 1 row.] + +# 5. Assert that the compression was done by the gtid compressor thread. +include/assert_grep.inc [GTID table compression is done by compressor thread.] +# +# 6. Cleanup +# Removing debug point 'simulate_force_compress' from @@GLOBAL.debug +# Removing debug point 'print_gtid_compression_info' from @@GLOBAL.debug +DROP TABLE t1; diff --git a/mysql-test/suite/binlog_gtid/r/binlog_gtid_persister_validation.result b/mysql-test/suite/binlog_gtid/r/binlog_gtid_persister_validation.result new file mode 100644 index 00000000000..2b08d58b62a --- /dev/null +++ b/mysql-test/suite/binlog_gtid/r/binlog_gtid_persister_validation.result @@ -0,0 +1,25 @@ +RESET MASTER; +# +# 1. Create an InnoDB table and insert 2 rows. +# Wait till each entry appears individually in the table. +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE = INNODB; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +# +# 2. Assert that table is not compressed. +include/assert.inc [COMMITTED_GTIDS SERVER_UUID:1-3] +include/assert.inc [The mysql gtid table should have 3 rows.] +# +# 3. Enable debug point to print the compression info for validation purpose. +# Adding debug point 'print_gtid_compression_info' to @@GLOBAL.debug +# +# 4. Execute FLUSH ENGINE LOGS and assert that table is compressed. +FLUSH ENGINE LOGS; +include/assert.inc [The mysql gtid table should have 1 row.] + +# 5. Assert that the compression was done by the gtid persister thread. +include/assert_grep.inc [GTID table compression is done by persister thread.] +# +# 6. Cleanup +# Removing debug point 'print_gtid_compression_info' from @@GLOBAL.debug +DROP TABLE t1; diff --git a/mysql-test/suite/binlog_gtid/t/binlog_gtid_compressor_validation.test b/mysql-test/suite/binlog_gtid/t/binlog_gtid_compressor_validation.test new file mode 100644 index 00000000000..f353d0f8dfc --- /dev/null +++ b/mysql-test/suite/binlog_gtid/t/binlog_gtid_compressor_validation.test @@ -0,0 +1,97 @@ +# ==== Purpose ==== +# +# This test verifies that compression of mysql.gtid_executed table shall be +# done only by the gtid compressor thread. +# +# ==== Implementation ==== +# +# 0. This test requires only one server. +# 1. Create an InnoDB table and insert 2 rows. +# Wait till each entry appears individually in the mysql.gtid_executed table. +# 2. Assert that table is not compressed. +# 3. Enable debug points to enable force compression and to print the +# compression info for validation purpose. +# 4. Insert third row and assert that table is compressed. +# 5. Assert that the compression was done by the gtid compressor thread. +# 6. Cleanup +# +# ==== References ==== +# +# Bug#100118 Server doesn't restart because of too many gaps in the +# mysql.gtid_executed table + +# This test requires debug binaries. +--source include/have_debug.inc +# This test is binlog format agnostic +--source include/have_binlog_format_row.inc + +--let $server_uuid = `SELECT @@GLOBAL.SERVER_UUID` + +# Clean the table before starting the test. +RESET MASTER; + +--echo # +--echo # 1. Create an InnoDB table and insert 2 rows. +--echo # Wait till each entry appears individually in the table. + +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE = INNODB; + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 1 AND interval_end = 1 +--source include/wait_condition.inc + +INSERT INTO t1 VALUES (1); + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 2 AND interval_end = 2 +--source include/wait_condition.inc + +INSERT INTO t1 VALUES (2); + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 3 AND interval_end = 3 +--source include/wait_condition.inc + +--echo # +--echo # 2. Assert that table is not compressed. + +--let $assert_text = COMMITTED_GTIDS SERVER_UUID:1-3 +--let $assert_cond = "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$server_uuid:1-3" +--source include/assert.inc + +--let $assert_cond = COUNT(*) = 3 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table should have 3 rows. +--source include/assert.inc + +--echo # +--echo # 3. Enable debug points to enable force compression and to print the +--echo # compression info for validation purpose. +--let $debug_point = simulate_force_compress +--source include/add_debug_point.inc +--let $debug_point = print_gtid_compression_info +--source include/add_debug_point.inc + +--echo # +--echo # 4. Insert third row and assert that table is compressed. +INSERT INTO t1 VALUES (3); + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 1 AND interval_end = 4 +--source include/wait_condition.inc + +--let $assert_cond = COUNT(*) = 1 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table should have 1 row. +--source include/assert.inc + +--echo +--echo # 5. Assert that the compression was done by the gtid compressor thread. + +--let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.1.err +--let $assert_match= .*Compression done by compressor thread, first gapless row = 1-4.* +--let $assert_select= Compression done by compressor thread, first gapless row = 1-4 +--let $assert_text= GTID table compression is done by compressor thread. +--source include/assert_grep.inc + +--echo # +--echo # 6. Cleanup +--let $debug_point = simulate_force_compress +--source include/remove_debug_point.inc +--let $debug_point = print_gtid_compression_info +--source include/remove_debug_point.inc +DROP TABLE t1; diff --git a/mysql-test/suite/binlog_gtid/t/binlog_gtid_persister_validation.test b/mysql-test/suite/binlog_gtid/t/binlog_gtid_persister_validation.test new file mode 100644 index 00000000000..b9a64be06b6 --- /dev/null +++ b/mysql-test/suite/binlog_gtid/t/binlog_gtid_persister_validation.test @@ -0,0 +1,91 @@ +# ==== Purpose ==== +# +# This test verifies that compression of mysql.gtid_executed table shall be +# done only by the gtid persister thread when binlogging is enabled. +# +# ==== Implementation ==== +# +# 0. This test requires only one server. +# 1. Create an InnoDB table and insert 2 rows. +# Wait till each entry appears individually in the mysql.gtid_executed table. +# 2. Assert that table is not compressed. +# 3. Enable debug point to print the compression info for validation purpose. +# 4. Execute FLUSH ENGINE LOGS and assert that table is compressed. +# 5. Assert that the compression was done by the gtid persister thread. +# 6. Cleanup +# +# ==== References ==== +# +# Bug #100118 Server doesn't restart because of too many gaps in the +# mysql.gtid_executed table + +# This test requires debug binaries. +--source include/have_debug.inc +# This test is binlog format agnostic +--source include/have_binlog_format_row.inc + +--let $server_uuid = `SELECT @@GLOBAL.SERVER_UUID` + +# Clean the table before starting the test. +RESET MASTER; + +--echo # +--echo # 1. Create an InnoDB table and insert 2 rows. +--echo # Wait till each entry appears individually in the table. + +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE = INNODB; + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 1 AND interval_end = 1 +--source include/wait_condition.inc + +INSERT INTO t1 VALUES (1); + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 2 AND interval_end = 2 +--source include/wait_condition.inc + +INSERT INTO t1 VALUES (2); + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 3 AND interval_end = 3 +--source include/wait_condition.inc + +--echo # +--echo # 2. Assert that table is not compressed. + +--let $assert_text = COMMITTED_GTIDS SERVER_UUID:1-3 +--let $assert_cond = "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$server_uuid:1-3" +--source include/assert.inc + +--let $assert_cond = COUNT(*) = 3 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table should have 3 rows. +--source include/assert.inc + +--echo # +--echo # 3. Enable debug point to print the compression info for validation purpose. +--let $debug_point = print_gtid_compression_info +--source include/add_debug_point.inc + +--echo # +--echo # 4. Execute FLUSH ENGINE LOGS and assert that table is compressed. +FLUSH ENGINE LOGS; + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 1 AND interval_end = 3 +--source include/wait_condition.inc + +--let $assert_cond = COUNT(*) = 1 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table should have 1 row. +--source include/assert.inc + +--echo +--echo # 5. Assert that the compression was done by the gtid persister thread. + +--let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.1.err +--let $assert_match= .*Compression done by persister thread, first gapless row = 1-3.* +--let $assert_select= Compression done by persister thread, first gapless row = 1-3 +--let $assert_text= GTID table compression is done by persister thread. +--source include/assert_grep.inc + +--echo # +--echo # 6. Cleanup +--let $debug_point = print_gtid_compression_info +--source include/remove_debug_point.inc +DROP TABLE t1; diff --git a/mysql-test/suite/clone/r/local_basic_gtid_compression.result b/mysql-test/suite/clone/r/local_basic_gtid_compression.result new file mode 100644 index 00000000000..30d2c0e8a14 --- /dev/null +++ b/mysql-test/suite/clone/r/local_basic_gtid_compression.result @@ -0,0 +1,45 @@ +RESET MASTER; +# +# 1. Create InnoDB and MyISAM tables and insert 2 rows. +CREATE TABLE t1(col1 INT PRIMARY KEY, col2 char(64)) ENGINE = INNODB; +CREATE TABLE t2(col1 INT PRIMARY KEY, col2 char(64)) ENGINE = MyISAM; +INSERT INTO t2 VALUES(10, 'myisam row 1'); +INSERT INTO t1 VALUES(10, 'innodb row 1'); +INSERT INTO t2 VALUES(20, 'myisam row 2'); +INSERT INTO t1 VALUES(20, 'innodb row 2'); +# +# 2. Assert that mysql.gtid_executed table is not compressed and has gaps. +include/assert.inc [COMMITTED_GTIDS SERVER_UUID:1-6] +include/assert.inc [The mysql gtid table is not compressed and has gaps.] +# +# 3. Install Clone Plugin, enable debug point to print compression info +# and initiate Clone operation. +INSTALL PLUGIN clone SONAME 'CLONE_PLUGIN'; +# Adding debug point 'print_gtid_compression_info' to @@GLOBAL.debug +SET GLOBAL clone_autotune_concurrency = OFF; +SET GLOBAL clone_max_concurrency = 8; +CLONE LOCAL DATA DIRECTORY = 'CLONE_DATADIR'; +select ID, STATE, ERROR_NO from performance_schema.clone_status; +ID STATE ERROR_NO +1 Completed 0 +select ID, STAGE, STATE from performance_schema.clone_progress; +ID STAGE STATE +1 DROP DATA Completed +1 FILE COPY Completed +1 PAGE COPY Completed +1 REDO COPY Completed +1 FILE SYNC Completed +1 RESTART Not Started +1 RECOVERY Not Started +# +# 4. Assert that table is compressed after clone operation. +include/assert.inc [The mysql gtid table should have 1 row.] +# +# 5. Assert that the compression was done by the gtid persister thread. +include/assert_grep.inc [GTID table compression is done by persister thread.] +# +# 6. Cleanup +# Removing debug point 'print_gtid_compression_info' from @@GLOBAL.debug +DROP TABLE t1; +DROP TABLE t2; +UNINSTALL PLUGIN clone; diff --git a/mysql-test/suite/clone/t/local_basic_gtid_compression-master.opt b/mysql-test/suite/clone/t/local_basic_gtid_compression-master.opt new file mode 100644 index 00000000000..e7d9f84929c --- /dev/null +++ b/mysql-test/suite/clone/t/local_basic_gtid_compression-master.opt @@ -0,0 +1 @@ +--gtid_mode=ON --enforce_gtid_consistency diff --git a/mysql-test/suite/clone/t/local_basic_gtid_compression.test b/mysql-test/suite/clone/t/local_basic_gtid_compression.test new file mode 100644 index 00000000000..3dab2312226 --- /dev/null +++ b/mysql-test/suite/clone/t/local_basic_gtid_compression.test @@ -0,0 +1,124 @@ +# ==== Purpose ==== +# +# This test verifies that compression of mysql.gtid_executed table shall be +# done only by the gtid persister thread for clone operation. +# +# ==== Implementation ==== +# +# 0. This test requires only one server. +# 1. Create InnoDB and MyISAM tables and insert 2 rows. +# 2. Assert that mysql.gtid_executed table is not compressed and has gaps. +# 3. Install Clone Plugin, enable debug point to print compression info +# and initiate Clone operation. +# 4. Assert that table is compressed after Clone operation. +# 5. Assert that the compression was done by the gtid persister thread. +# 6. Cleanup +# +# ==== References ==== +# +# Bug #100118 Server doesn't restart because of too many gaps in the +# mysql.gtid_executed table + +# This test requires debug binaries +--source include/have_debug.inc +# This test requires binlogging. +--source include/have_log_bin.inc +--source include/count_sessions.inc +--let $server_uuid = `SELECT @@GLOBAL.SERVER_UUID` + +# Clean the table before starting the test. +RESET MASTER; + +# Test setup for clone test. +--source ../include/clone_connection_begin.inc +--let $CLONE_DATADIR = $MYSQL_TMP_DIR/data_new + +--echo # +--echo # 1. Create InnoDB and MyISAM tables and insert 2 rows. +CREATE TABLE t1(col1 INT PRIMARY KEY, col2 char(64)) ENGINE = INNODB; +CREATE TABLE t2(col1 INT PRIMARY KEY, col2 char(64)) ENGINE = MyISAM; + +INSERT INTO t2 VALUES(10, 'myisam row 1'); +INSERT INTO t1 VALUES(10, 'innodb row 1'); +INSERT INTO t2 VALUES(20, 'myisam row 2'); +INSERT INTO t1 VALUES(20, 'innodb row 2'); + +--echo # +--echo # 2. Assert that mysql.gtid_executed table is not compressed and has gaps. + +# Assert that 6 transactions have been committed in total. +--let $assert_text = COMMITTED_GTIDS SERVER_UUID:1-6 +--let $assert_cond = "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$server_uuid:1-6" +--source include/assert.inc + +# When binlog is enabled, GTIDs of InnoDB transactions are updated by gtid +# persister thread and GTIDs of other transactions are updated on binlog +# rotation. So, wait till the GTIDs of InnoDB transactions are updated by the +# gtid persister thread. +# +# i.e, wait till the below state is reached. +# +# SELECT * FROM mysql.gtid_executed; +# source_uuid interval_start interval_end +# ------------------------------------------ +# server_uuid 1 1 +# server_uuid 4 4 +# server_uuid 6 6 + +--let $wait_condition = SELECT count(*) = 3 FROM mysql.gtid_executed +--source include/wait_condition.inc + +--let $assert_cond = COUNT(*) = 3 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table is not compressed and has gaps. +--source include/assert.inc + +--echo # +--echo # 3. Install Clone Plugin, enable debug point to print compression info +--echo # and initiate Clone operation. + +# Install Clone Plugin +--replace_result $CLONE_PLUGIN CLONE_PLUGIN +--eval INSTALL PLUGIN clone SONAME '$CLONE_PLUGIN' + +# Enable debug point to print the compression info for validation purpose. +--let $debug_point = print_gtid_compression_info +--source include/add_debug_point.inc + +# Clone data +--connection clone_conn_1 +--source ../include/clone_command.inc + +# During clone operation, persister thread, along with writing InnoDB GTIDs, it +# also writes non-InnoDB GTIDs and even compresses it by creating an explicit +# request. + +--echo # +--echo # 4. Assert that table is compressed after clone operation. +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 1 AND interval_end = 6 +--source include/wait_condition.inc + +--let $assert_cond = COUNT(*) = 1 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table should have 1 row. +--source include/assert.inc + +--echo # +--echo # 5. Assert that the compression was done by the gtid persister thread. + +# Find the thread that recently compressed the mysql.gtid_executed table and assert that it is clone_gtid_thread. +--let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.1.err +--let $assert_match= .*Compression done by persister thread, first gapless row = 1-6.* +--let $assert_select= Compression done by persister thread, first gapless row = 1-6 +--let $assert_text= GTID table compression is done by persister thread. +--source include/assert_grep.inc + +--echo # +--echo # 6. Cleanup +--let $debug_point = print_gtid_compression_info +--source include/remove_debug_point.inc +DROP TABLE t1; +DROP TABLE t2; +--force-rmdir $CLONE_DATADIR +--source ../include/clone_connection_end.inc + +UNINSTALL PLUGIN clone; +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/rpl_gtid/r/rpl_gtid_binlogless_slave_compressor_validation.result b/mysql-test/suite/rpl_gtid/r/rpl_gtid_binlogless_slave_compressor_validation.result new file mode 100644 index 00000000000..06622865147 --- /dev/null +++ b/mysql-test/suite/rpl_gtid/r/rpl_gtid_binlogless_slave_compressor_validation.result @@ -0,0 +1,41 @@ +include/master-slave.inc +Warnings: +Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. +Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. +[connection master] +# +# 1. Create an InnoDB table and insert 2 rows. +# Wait till each entry appears individually in the table. +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE = INNODB; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +# +# 2. Assert that table is not compressed on primary server. +include/assert.inc [COMMITTED_GTIDS SERVER_UUID:1-3] +include/assert.inc [The mysql gtid table should have 3 rows.] +include/sync_slave_sql_with_master.inc +include/stop_slave.inc +# +# 3. Assert that table is not compressed on replica server. +include/assert.inc [COMMITTED_GTIDS SERVER_UUID:1-3] +include/assert.inc [The mysql gtid table is not compressed.] +# +# 4. Enable debug point to print the compression info for validation purpose. +# Adding debug point 'print_gtid_compression_info' to @@GLOBAL.debug +# Adding debug point 'simulate_force_compress' to @@GLOBAL.debug +# +# 5. Execute FLUSH ENGINE LOGS and assert that table is compressed. +FLUSH ENGINE LOGS; +include/assert.inc [The mysql gtid table should have 1 row.] + +# 6. Assert that the compression was done by the gtid compressor thread. +include/assert_grep.inc [GTID table compression is done by compressor thread.] +CALL mtr.add_suppression("You need to use --log-bin to make --binlog-format work."); +# +# 7. Cleanup +# Removing debug point 'print_gtid_compression_info' from @@GLOBAL.debug +# Removing debug point 'simulate_force_compress' from @@GLOBAL.debug +include/start_slave.inc +[connection master] +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl_gtid/r/rpl_gtid_slave_persister_validation.result b/mysql-test/suite/rpl_gtid/r/rpl_gtid_slave_persister_validation.result new file mode 100644 index 00000000000..0a1aca225da --- /dev/null +++ b/mysql-test/suite/rpl_gtid/r/rpl_gtid_slave_persister_validation.result @@ -0,0 +1,38 @@ +include/master-slave.inc +Warnings: +Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. +Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. +[connection master] +# +# 1. Create an InnoDB table and insert 2 rows. +# Wait till each entry appears individually in the table. +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE = INNODB; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +# +# 2. Assert that table is not compressed on primary server. +include/assert.inc [COMMITTED_GTIDS SERVER_UUID:1-3] +include/assert.inc [The mysql gtid table should have 3 rows.] +include/sync_slave_sql_with_master.inc +include/stop_slave.inc +# +# 3. Assert that table is not compressed on replica server. +include/assert.inc [COMMITTED_GTIDS SERVER_UUID:1-3] +include/assert.inc [The mysql gtid table is not compressed.] +# +# 4. Enable debug point to print the compression info for validation purpose. +# Adding debug point 'print_gtid_compression_info' to @@GLOBAL.debug +# +# 5. Execute FLUSH ENGINE LOGS and assert that table is compressed. +FLUSH ENGINE LOGS; +include/assert.inc [The mysql gtid table should have 1 row.] + +# 6. Assert that the compression was done by the gtid persister thread. +include/assert_grep.inc [GTID table compression is done by persister thread.] +# +# 7. Cleanup +# Removing debug point 'print_gtid_compression_info' from @@GLOBAL.debug +include/start_slave.inc +[connection master] +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl_gtid/t/rpl_gtid_binlogless_slave_compressor_validation-slave.opt b/mysql-test/suite/rpl_gtid/t/rpl_gtid_binlogless_slave_compressor_validation-slave.opt new file mode 100644 index 00000000000..ccbd01c91d3 --- /dev/null +++ b/mysql-test/suite/rpl_gtid/t/rpl_gtid_binlogless_slave_compressor_validation-slave.opt @@ -0,0 +1 @@ +--disable-log-bin diff --git a/mysql-test/suite/rpl_gtid/t/rpl_gtid_binlogless_slave_compressor_validation.test b/mysql-test/suite/rpl_gtid/t/rpl_gtid_binlogless_slave_compressor_validation.test new file mode 100644 index 00000000000..b109d229a4a --- /dev/null +++ b/mysql-test/suite/rpl_gtid/t/rpl_gtid_binlogless_slave_compressor_validation.test @@ -0,0 +1,116 @@ +# ==== Purpose ==== +# +# This test verifies that compression of mysql.gtid_executed table shall be +# done only by the gtid compressor thread when binlogging is disabled. +# +# ==== Implementation ==== +# +# 0. This test requires two servers. Create a primary-replica setup. +# 1. Create an InnoDB table and insert 2 rows. +# Wait till each entry appears individually in the mysql.gtid_executed table. +# 2. Assert that table is not compressed on primary server. +# 3. Assert that table is not compressed on replica server. +# 4. Enable debug point to print the compression info for validation purpose. +# 5. Execute FLUSH ENGINE LOGS and assert that table is compressed. +# 6. Assert that the compression was done by the gtid compressor thread. +# 7. Cleanup +# +# ==== References ==== +# +# Bug #100118 Server doesn't restart because of too many gaps in the +# mysql.gtid_executed table + +# This test requires debug binaries. +--source include/have_debug.inc +# This test is binlog format agnostic +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +--let $server_uuid = `SELECT @@GLOBAL.SERVER_UUID` + +--echo # +--echo # 1. Create an InnoDB table and insert 2 rows. +--echo # Wait till each entry appears individually in the table. + +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE = INNODB; + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 1 AND interval_end = 1 +--source include/wait_condition.inc + +INSERT INTO t1 VALUES (1); + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 2 AND interval_end = 2 +--source include/wait_condition.inc + +INSERT INTO t1 VALUES (2); + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 3 AND interval_end = 3 +--source include/wait_condition.inc + +--echo # +--echo # 2. Assert that table is not compressed on primary server. + +--let $assert_text = COMMITTED_GTIDS SERVER_UUID:1-3 +--let $assert_cond = "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$server_uuid:1-3" +--source include/assert.inc + +--let $assert_cond = COUNT(*) = 3 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table should have 3 rows. +--source include/assert.inc + +# Sync replica with the primary. +--source include/sync_slave_sql_with_master.inc +--source include/stop_slave.inc +--echo # +--echo # 3. Assert that table is not compressed on replica server. + +--let $assert_text = COMMITTED_GTIDS SERVER_UUID:1-3 +--let $assert_cond = "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$server_uuid:1-3" +--source include/assert.inc + +--let $assert_cond = COUNT(*) > 1 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table is not compressed. +--source include/assert.inc + +--echo # +--echo # 4. Enable debug point to print the compression info for validation purpose. +--let $debug_point = print_gtid_compression_info +--source include/add_debug_point.inc +--let $debug_point = simulate_force_compress +--source include/add_debug_point.inc + +--echo # +--echo # 5. Execute FLUSH ENGINE LOGS and assert that table is compressed. +FLUSH ENGINE LOGS; + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 1 AND interval_end = 3 +--source include/wait_condition.inc + +--let $assert_cond = COUNT(*) = 1 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table should have 1 row. +--source include/assert.inc + +--echo +--echo # 6. Assert that the compression was done by the gtid compressor thread. + +--let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.2.err +--let $assert_match= .*Compression done by compressor thread, first gapless row = 1-3.* +--let $assert_select= Compression done by compressor thread, first gapless row = 1-3 +--let $assert_text= GTID table compression is done by compressor thread. +--source include/assert_grep.inc + +# Test Suppression +CALL mtr.add_suppression("You need to use --log-bin to make --binlog-format work."); + +--echo # +--echo # 7. Cleanup +--let $debug_point = print_gtid_compression_info +--source include/remove_debug_point.inc +--let $debug_point = simulate_force_compress +--source include/remove_debug_point.inc + +--source include/start_slave.inc +--source include/rpl_connection_master.inc +DROP TABLE t1; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl_gtid/t/rpl_gtid_slave_persister_validation.test b/mysql-test/suite/rpl_gtid/t/rpl_gtid_slave_persister_validation.test new file mode 100644 index 00000000000..5e15ea92da9 --- /dev/null +++ b/mysql-test/suite/rpl_gtid/t/rpl_gtid_slave_persister_validation.test @@ -0,0 +1,110 @@ +# ==== Purpose ==== +# +# This test verifies that compression of mysql.gtid_executed table shall be +# done only by the gtid persister thread when binlogging is enabled and +# log_slave_updates is set. +# +# ==== Implementation ==== +# +# 0. This test requires two servers. Create a primary-replica setup. +# 1. Create an InnoDB table and insert 2 rows. +# Wait till each entry appears individually in the mysql.gtid_executed table. +# 2. Assert that table is not compressed on primary server. +# 3. Assert that table is not compressed on replica server. +# 4. Enable debug point to print the compression info for validation purpose. +# 5. Execute FLUSH ENGINE LOGS and assert that table is compressed. +# 6. Assert that the compression was done by the gtid persister thread. +# 7. Cleanup +# +# ==== References ==== +# +# Bug #100118 Server doesn't restart because of too many gaps in the +# mysql.gtid_executed table + +# This test requires debug binaries. +--source include/have_debug.inc +# This test is binlog format agnostic +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +--let $server_uuid = `SELECT @@GLOBAL.SERVER_UUID` + +--echo # +--echo # 1. Create an InnoDB table and insert 2 rows. +--echo # Wait till each entry appears individually in the table. + +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE = INNODB; + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 1 AND interval_end = 1 +--source include/wait_condition.inc + +INSERT INTO t1 VALUES (1); + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 2 AND interval_end = 2 +--source include/wait_condition.inc + +INSERT INTO t1 VALUES (2); + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 3 AND interval_end = 3 +--source include/wait_condition.inc + +--echo # +--echo # 2. Assert that table is not compressed on primary server. + +--let $assert_text = COMMITTED_GTIDS SERVER_UUID:1-3 +--let $assert_cond = "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$server_uuid:1-3" +--source include/assert.inc + +--let $assert_cond = COUNT(*) = 3 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table should have 3 rows. +--source include/assert.inc + +# Sync replica with the primary. +--source include/sync_slave_sql_with_master.inc +--source include/stop_slave.inc +--echo # +--echo # 3. Assert that table is not compressed on replica server. + +--let $assert_text = COMMITTED_GTIDS SERVER_UUID:1-3 +--let $assert_cond = "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$server_uuid:1-3" +--source include/assert.inc + +--let $assert_cond = COUNT(*) > 1 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table is not compressed. +--source include/assert.inc + +--echo # +--echo # 4. Enable debug point to print the compression info for validation purpose. +--let $debug_point = print_gtid_compression_info +--source include/add_debug_point.inc + +--echo # +--echo # 5. Execute FLUSH ENGINE LOGS and assert that table is compressed. +FLUSH ENGINE LOGS; + +--let $wait_condition = SELECT count(*) = 1 FROM mysql.gtid_executed WHERE interval_start = 1 AND interval_end = 3 +--source include/wait_condition.inc + +--let $assert_cond = COUNT(*) = 1 FROM mysql.gtid_executed +--let $assert_text = The mysql gtid table should have 1 row. +--source include/assert.inc + +--echo +--echo # 6. Assert that the compression was done by the gtid persister thread. + +--let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.2.err +--let $assert_match= .*Compression done by persister thread, first gapless row = 1-3.* +--let $assert_select= Compression done by persister thread, first gapless row = 1-3 +--let $assert_text= GTID table compression is done by persister thread. +--source include/assert_grep.inc + +--echo # +--echo # 7. Cleanup +--let $debug_point = print_gtid_compression_info +--source include/remove_debug_point.inc + +--source include/start_slave.inc +--source include/rpl_connection_master.inc +DROP TABLE t1; + +--source include/rpl_end.inc diff --git a/sql/rpl_gtid_persist.cc b/sql/rpl_gtid_persist.cc index adcf8e3ebf6..ce43f77037d 100644 --- a/sql/rpl_gtid_persist.cc +++ b/sql/rpl_gtid_persist.cc @@ -365,10 +365,7 @@ end: uint32 count = (uint32)m_atomic_count++; if (count == gtid_executed_compression_period || DBUG_EVALUATE_IF("compress_gtid_table", 1, 0)) { - mysql_mutex_lock(&LOCK_compress_gtid_table); - should_compress = true; - mysql_cond_signal(&COND_compress_gtid_table); - mysql_mutex_unlock(&LOCK_compress_gtid_table); + set_compression_and_signal_compressor(); } } @@ -405,10 +402,7 @@ end: /* Notify compression thread to compress gtid_executed table. */ if (error == 0 && compress && DBUG_EVALUATE_IF("dont_compress_gtid_table", 0, 1)) { - mysql_mutex_lock(&LOCK_compress_gtid_table); - should_compress = true; - mysql_cond_signal(&COND_compress_gtid_table); - mysql_mutex_unlock(&LOCK_compress_gtid_table); + set_compression_and_signal_compressor(); } return ret; @@ -588,13 +582,20 @@ int Gtid_table_persistor::compress_first_consecutive_range(TABLE *table, if (err != HA_ERR_END_OF_FILE && err != 0) ret = -1; - else if (find_first_consecutive_gtids) + else if (find_first_consecutive_gtids) { + DBUG_EXECUTE_IF("print_gtid_compression_info", { + sql_print_information( + "Compression done by %s thread, first gapless row = %d-%d", + current_thd->thread_id() ? "compressor" : "persister", gno_start, + gno_end); + };); + /* Update the gno_end of the first consecutive gtid with the gno_end of the last consecutive gtid for the first consecutive range of gtids. */ ret = update_row(table, sid.c_str(), gno_start, gno_end); - + } return ret; } @@ -857,3 +858,10 @@ void terminate_compress_gtid_table_thread() { LogErr(WARNING_LEVEL, ER_FAILED_TO_JOIN_GTID_TABLE_COMPRESSION_THREAD, error); } + +void Gtid_table_persistor::set_compression_and_signal_compressor() { + mysql_mutex_lock(&LOCK_compress_gtid_table); + should_compress = true; + mysql_cond_signal(&COND_compress_gtid_table); + mysql_mutex_unlock(&LOCK_compress_gtid_table); +} diff --git a/sql/rpl_gtid_persist.h b/sql/rpl_gtid_persist.h index 0a41d5d1706..1d942c82b92 100644 --- a/sql/rpl_gtid_persist.h +++ b/sql/rpl_gtid_persist.h @@ -233,6 +233,11 @@ class Gtid_table_persistor { return 0; } + /** + Sets the should_compress flag and signals the compressor thread. + */ + void set_compression_and_signal_compressor(); + private: /* Count the append size of the table */ std::atomic m_atomic_count{0}; diff --git a/storage/innobase/clone/clone0repl.cc b/storage/innobase/clone/clone0repl.cc index ec980e74d45..ee1a20b6503 100644 --- a/storage/innobase/clone/clone0repl.cc +++ b/storage/innobase/clone/clone0repl.cc @@ -315,6 +315,7 @@ int Clone_persist_gtid::write_other_gtids() { } bool Clone_persist_gtid::check_compress() { + DBUG_EXECUTE_IF("simulate_force_compress", { return true; }); /* Check local threshold on number of flush. */ if (m_compression_counter >= s_compression_threshold) { return (true); @@ -473,7 +474,14 @@ void Clone_persist_gtid::flush_gtids(THD *thd) { m_compression_gtid_counter = 0; /* Write non-innodb GTIDs before compression. */ write_other_gtids(); - err = gtid_table_persistor->compress(thd); + + /* Compress the gtid_executed table if there is an explicit compression + request. Otherwise signal the gtid persister thread to perform + compression. */ + if (explicit_request) + err = gtid_table_persistor->compress(thd); + else + gtid_table_persistor->set_compression_and_signal_compressor(); } if (err != 0) { ib::error(ER_IB_CLONE_GTID_PERSIST) << "Error persisting GTIDs to table"; diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index d69b31cfdb1..4d1b4716a1b 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -65,6 +65,7 @@ this program; if not, write to the Free Software Foundation, Inc., #include "mysql/components/services/system_variable_source.h" #ifndef UNIV_HOTBACKUP +#include #include #include #include @@ -5182,10 +5183,16 @@ static bool innobase_flush_logs(handlerton *hton, bool binlog_group_flush) { return false; } - /* Signal and wait for all GTIDs to persist on disk. */ + /* Wait for all GTIDs to persist on disk and create an explicit request for + the compression of mysql.gtid_executed table if both binary logging and + log_slave_updates are enabled. This is required to avoid the + clone_gtid_thread getting stuck while scanning the gtid_executed table when + there is a mixed (transactional and non-transactional) workload on the + server. */ if (!binlog_group_flush) { + bool compress_gtid = mysql_bin_log.is_open() && opt_log_slave_updates; auto >id_persistor = clone_sys->get_gtid_persistor(); - gtid_persistor.wait_flush(true, true, true, nullptr); + gtid_persistor.wait_flush(true, compress_gtid, true, nullptr); } /* Flush the redo log buffer to the redo log file. -- 2.25.1