Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Replication Performance Enhancements in MySQL 8

DZone's Guide to

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.

· Performance Zone ·
Free Resource

Sensu is an open source monitoring event pipeline. Try it today.

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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:

  1. microseconds from epoch, and
  2. 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:

  1. It can instrument server errors
  2. It now supports indexes
  3. 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:

  1. events_errors_summary_by_account_by_error
  2. events_errors_summary_by_host_by_error
  3. events_errors_summary_by_thread_by_error
  4. events_errors_summary_by_user_by_error
  5. events_errors_summary_global_by_error
  6. 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.

Table Structure

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 |
+-------------------+---------------------+------+-----+---------------------+

Note that:

  • The FIRST_SEEN/LAST_SEEN columns indicate the first and last time a particular error was seen.
  • The SUM_ERROR_RAISED column lists the number of times a particular error is raised.
  • The SUM_ERROR_

    Sensu: workflow automation for monitoring. Learn more—download the whitepaper.

    Topics:
    database

    Published at DZone with permission of

    Opinions expressed by DZone contributors are their own.

    {{ parent.title || parent.header.title}}

    {{ parent.tldr }}

    {{ parent.urlSource.name }}