Bug #86692 dynamically changing gtid_executed_compression_period prevents gtid compression!

• Submitted: 14 Jun 2017 8:18
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 5.7.18
• OS: Any
• CPU Architecture: Any

[14 Jun 2017 8:18] Shane Bester

Description:
The gtid compression thread wakes up and compresses the mysql.gtid_executed table mostly reliably,  until you dynamically change the variable.  Then thread is never woken up again until server restart.

This is good :

mysql> select @@global.gtid_executed_compression_period p,now(),version(),count(*) from mysql.gtid_executed;
+------+---------------------+-----------+----------+
| p    | now()               | version() | count(*) |
+------+---------------------+-----------+----------+
| 1000 | 2017-06-14 10:10:45 | 5.7.20    |     1061 |
+------+---------------------+-----------+----------+
1 row in set (0.49 sec)

mysql> select @@global.gtid_executed_compression_period p,now(),version(),count(*) from mysql.gtid_executed;
+------+---------------------+-----------+----------+
| p    | now()               | version() | count(*) |
+------+---------------------+-----------+----------+
| 1000 | 2017-06-14 10:10:50 | 5.7.20    |      143 |
+------+---------------------+-----------+----------+
1 row in set (0.01 sec)

Now I do this :

mysql> set global gtid_executed_compression_period = 1;
Query OK, 0 rows affected (0.01 sec)

And monitor it ....  This is bad and gets worse to point of crippling slave:

mysql> select @@global.gtid_executed_compression_period p,now(),version(),count(*) from mysql.gtid_executed;
+------+---------------------+-----------+----------+
| p    | now()               | version() | count(*) |
+------+---------------------+-----------+----------+
|    1 | 2017-06-14 10:14:19 | 5.7.20    |     3604 |
+------+---------------------+-----------+----------+
1 row in set (0.01 sec)

mysql> select @@global.gtid_executed_compression_period p,now(),version(),count(*) from mysql.gtid_executed;
+------+---------------------+-----------+----------+
| p    | now()               | version() | count(*) |
+------+---------------------+-----------+----------+
|    1 | 2017-06-14 10:14:52 | 5.7.20    |     4750 |
+------+---------------------+-----------+----------+
1 row in set (0.02 sec)

How to repeat:
Setup a GTID master/slave with no binlogs on slave.

Suggested fix:
Pretty sure the "fix" for this broke the logic:

Bug#20104307 GTID_EXECUTED TABLE COMPRESSION THREAD MAY NOT WAKE UP
http://bugs.mysql.com/bug.php?id=75014

[14 Jun 2017 8:23] MySQL Verification Team

The "workload" is any number of small transactions. For example:

mysqlslap -hMASTER --query="replace into test.t(a) values(connection_id());delete from test.t where a=connection_id();" --pre-query="drop table if exists test.t;create table test.t(a tinyint unsigned not null primary key)engine=innodb;"  --create-schema=test  --concurrency=1 --iterations=1 --number-of-queries=9999999999999

[14 Jun 2017 8:32] MySQL Verification Team

I believe the replacing >= with = broke it:

https://github.com/mysql/mysql-server/commit/ece97f2b5ac74227ea5e473722b2ff963e2be082#diff...