Replication Performance Enhancements in MySQL 8
Replication Performance Enhancements in MySQL 8
Here's your first look at some of the new replication enhancements Oracle has released in MySQL 8, and a tutorial in how to use them.
Join the DZone community and get the full member experience.Join For Free
Although it feels like it was only yesterday that Oracle released version 5.7 of their acclaimed MySQL Community Server, version 8 is already available as a development milestone release (DMR). No, you didn’t sleep through a bunch of releases; MySQL is jumping several versions in its numbering due to 6.0 being dropped and 7.0 being reserved for the clustering version of MySQL. This new version boasts numerous changes (and bug fixes), one of the most exciting of which are replication enhancements. This blog will provide an overview of the new replication enhancements, including new replication timestamps, additional information reported by performance schema tables, and how replication delay has been reduced by updating the relationship between replication threads to make them more efficient.
New Replication Timestamps
The most common task when managing a replication process is to ensure that replication is, in fact, taking place and that there were no errors between the slave and the master. The primary statement for this is
SHOW SLAVE STATUS, which provides status information on essential parameters of the slave threads. Hence, you must execute it on each slave. Here’s some sample output:
mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 13000 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 1307 Relay_Log_File: slave-relay-bin.000003 Relay_Log_Pos: 1508 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes / * / * / * / Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: ETC...
One of the many output fields is the
Seconds_Behind_Master. While perfectly suitable for a simple master-slave setup, this metric is insufficient for more complex replication scenarios. The
Seconds_Behind_Mastermetric has four main drawbacks:
- It only reports the delay between the slave and the top-most master. For instance, in a chained replication setup, the
Seconds_Behind_Masterreports the delay relative to the original master and does not provide any information regarding the lag between the slave and its nearest – i.e. immediate – master.
- It is relative to the original master’s time zone. As a result, server replication across time zones cause the measured delay to be offset by the time zone difference between the two servers.
- Lag is measured on a per-event basis, based on the statement’s execution start time. A more insightful measure would be per-transaction, from the time the transaction was actually committed on the master.
- The timestamp used to measure the replication lag offers a precision only up to the nearest second.
MySQL 8 introduces two new timestamps that complement the
Seconds_Behind_Mastermetric in circumventing the above issues. These are associated with the global transaction identifier (GTID) of each transaction (as opposed to each event), written to the binary log. A GTID is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated but across all servers in a given replication setup. Being associated to a transaction, there is a 1-to-1 mapping between all transactions and all GTIDs.
The two new timestamps are:
- original commit timestamp (OCT): the number of microseconds since epoch (i.e. POSIX time/ UNIX time/January 1, 1970/1970-01-01T00:00:00Z) when the transaction was written to the binary log of the original master
- immediate commit timestamp (ICT): the number of microseconds since epoch when the transaction was written to the binary log of the immediate master
The output of
mysqlbinlog displays the new timestamps in two formats:
- microseconds from epoch, and
- TIMESTAMP format in the user time zone (for better readability)
This snippet from a slave’s binary log shows both timestamps:
#170404 10:48:05 server id 1 end_log_pos 233 CRC32 0x016ce647 GTID last_committed=0 sequence_number=1 original_committed_timestamp=1491299285661130 immediate_commit_timestamp=1491299285843771 # original_commit_timestamp=1491299285661130 (2018-01-04 10:48:05.661130 WEST) # immediate_commit_timestamp=1491299285843771 (2018-01-04 10:48:05.843771 WEST) /*!80001 SET @@session.original_commit_timestamp=1491299285661130*//*!*/; SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1'/*!*/; # at 288
New Information Reported by Performance Schema Tables
MySQL 8.0 added a few changes to the Performance Schema resulting in better performance and more metrics:
- It can instrument server errors
- It now supports indexes
- It adds new fields to the existing performance schema replication status tables.
Let’s explore each of these in more detail.
Instrumentation of Server Errors
MySQL 8 saw the introduction of five new summary tables to assist in the in the instrumentation of server errors. These include:
- The error statistics are aggregated by error in all of the above tables. Moreover, each table, with the exception of events_errors_summary_global_by_error, stores errors related to a particular user, host, account, or thread; events_errors_summary_global_by_error contains errors for the entire server.
Each table contains the following fields:
+-------------------+---------------------+------+-----+---------------------+ | Field | Type | Null | Key | Default | +-------------------+---------------------+------+-----+---------------------+ | ERROR_NUMBER | int(11) | YES | | NULL | | ERROR_NAME | varchar(64) | YES | | NULL | | SQL_STATE | varchar(5) | YES | | NULL | | SUM_ERROR_RAISED | bigint(20) unsigned | NO | | NULL | | SUM_ERROR_HANDLED | bigint(20) unsigned | NO | | NULL | | FIRST_SEEN | timestamp | YES | | 0000-00-00 00:00:00 | | LAST_SEEN | timestamp | YES | | 0000-00-00 00:00:00 | +-------------------+---------------------+------+-----+---------------------+
Published at DZone with permission of Shree Nair . See the original article here.
Opinions expressed by DZone contributors are their own.