MySQL 5.7: Store GTIDs in table

 

Overview
========

In 5.7, GTID feature works fine without running the binlog on the slave as GTID information can be stored in a table.
In 5.6, GTID feature requires that the binlog is enabled on the slave, since GTIDs are only store in the binary logs.

There are two USE CASES which benefit from the new feature
=====================================================

USE CASE 1: A passive slave can use GTIDs with binary logs disabled
A slave that is only used for read scale-out and is never going to become a master may not have any use for the transactions in the binary log, it can use GTIDs for auto positioning with binary logs disabled.

USE CASE 2: GTIDs are more resilient to unexpected deletion of binary logs
– If the DBA/user can close the server normally after deleting all binlogs / the most recent binlog manually, GTID history is still preserved.
– If the DBA/user manually deletes all binlogs / the most recent binlog after normal server shutdown, GTID history is still preserved.

Introduce a MySQL internal table named ‘gtid_executed’
=================================================

A row in the table can contain an interval of several GTIDs, all having the same source_uuid and an interval of consecutive numbers as following:
CREATE TABLE gtid_executed(
source_uuid CHAR(36) NOT NULL,              — uuid of the source where the transaction was originally executed
interval_start BIGINT NOT NULL,             — First number of interval
interval_end BIGINT NOT NULL,               — Last number of interval
PRIMARY KEY(source_uuid, interval_start)    — PK on source_uuid and interval_start
);

How to store GTIDs in table?
========================

If gtid_mode is enabled, store GTIDs into the gtid_executed table always as below.
– If binlog is disabled, store transaction owned GTID (which is explicitly specified through SET GTID_NEXT by user client or slave SQL thread/works) into the table within each transaction.
For example, a transaction with two statements is executed as following:
BEGIN
STMT1;
STMT2;
INSERT INTO gtid_executed VALUES (SID, GNO, GNO); // the implicit INSERT STMT inserts the transaction owned GTID into the table right before transaction commit.
COMMIT
– If binlog is enabled, store set of GTIDs of the last binlog into the table on each binlog rotation and on server shutdown.
For example, the set of GTIDs of the last binlog are stored into the table on FLUSH LOGS as following:
INSERT INTO gtid_executed VALUES (SID1, GNO_start, GNO_end); // the row contains an interval of several GTIDs
INSERT INTO gtid_executed VALUES (SID2, GNO_start, GNO_end); // the row for a different SID.
ROTATE BINLOG;
– If log_slave_updates is disabled, slave SQL thread/worker stores transaction owned GTID in table within each transaction.
For example, a transaction with two statements is executed as following:
BEGIN
STMT1;
STMT2;
INSERT INTO gtid_executed VALUES (SID, GNO, GNO); // transaction owned GTID contains SID and GNO
COMMIT

Introduce a compression thread to compress the table
===============================================

The ‘gtid_executed’ table is filled with singleton interval as we can not use UPDATE instead of INSERT, because any concurrently executing transactions would conflict on the update. So introduce a thread to compress the ‘gtid_executed’ table regularly.
A example with 1000 executed thransactions’ GTIDs on master before compression:
SELECT * FROM mysql.gtid_executed;
source_uuid     interval_start      interval_end
MASTER_UUID      1                       1
MASTER_UUID      2                       2
MASTER_UUID      3                       3
MASTER_UUID      …                      …
MASTER_UUID      1000                 1000

How to control compression thread to compress the table?
===================================================

Introduce an option ‘executed_gtids_compression_period’ to control how often a thread is woken up to compress the ‘gtid_executed’ table. When binlog is disabled, to the option ‘executed_gtids_compression_period’ (it will be renamed to be ‘gtid_executed_compression_period’ later):
if value is 0, the compression thread never wakes up.
if value is 1, wakes up to compress gtid table every transaction.
if value is N, wakes up to compress gtid table every N transactions (N > 1)
The single slave SQL thread can get a good performance with the default value (1000), user can tune it to affect performance.
Note: When binlog is enabled, the thread is woken up to compress the ‘gtid_executed’ table on each binlog rotation.
A example with 1000 executed thransactions’ GTIDs after compression:
SELECT * FROM mysql.gtid_executed;
source_uuid     interval_start  interval_end
MASTER_UUID       1                 1000

Summary
=========
This feature is very useful to above two USE CASES and available in MySQL 5.7.5 release. Please try it out and let us know the feedback.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter. * Time limit is exhausted. Please reload CAPTCHA.