Replication Performance Enhancements in MySQL 8

by Jan 22, 2018

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 STATUSG

*************************** 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_Master metric 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_Master reports 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_Master metric 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_HANDLED column lists the number of times a particular error is handled.

All errors which were handled in stored programs are counted/aggregated under SUM_ERROR_HANDLED. Meanwhile, SUM_ERROR_RAISED is the number of all other remaining errors which were raised but not handled. Hence, to see the number of times a particular error is encountered on the server, we could do the following:

-- select from an unknown table:

select * from mydb.unknown_table;

ERROR 1146 (42S02): Table 'mydb.unknown_table' doesn't exist


-- look up the error

SELECT * from performance_schema.events_errors_summary_global_by_error where ERROR_NAME = 'ER_NO_SUCH_TABLE';


+--------------+------------------+-----------+------------------+-------------------+---------------------+--------------------+
| ERROR_NUMBER | ERROR_NAME       | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+
|         1146 | ER_NO_SUCH_TABLE | 42S02     |                1 |                 0 | 2018-01-15 15:15:21 | 2018-01-15 15:15:21 |
+--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+

Although this example is querying the global table, these error’s statistics could be retrieved aggregated by user/host/account/thread from their respective tables as well for more fine-grained statistics.

Index Support

Since its inclusion in MySQL 5.5, the Performance Schema has grown to 93 tables, some of which store a large amount of data. The added index support greatly increases the efficiency the performance_schema, resulting in a dramatic speed boost for many monitoring queries.

The performance improvements from indexes can be very easily seen in many of the sys schema queries. For instance, with 1000 idle threads, the query "SELECT * FROM sys.session drops from 34.70 seconds down to 1.01 seconds – a 30x improvement!

A total of 115 indexes have been added in the performance schema. Unlike the INFORMATION_SCHEMA, the performance schema exposes the data as a storage engine, rather than temporary tables. Whereas the latter are not able to expose indexes that may be utilized by the optimizer, storage engines can. Data access to the performance schema also uses the same (SQL) interface as regular tables, so that it is able to benefit from future improvements to the query optimizer.

New Fields Added to Existing Performance Schema Replication Status Tables

Beyond the new server error tables, existing performance_schema tables are also getting some extra fields to help detect and diagnose lag at several points. Each potential lag point in the replication stream maps to its own table:

Lag Point Performance Schema Replication Status Table
The connection thread that retrieves transactions from the master and queues them in the relay log. replication_connection_status: current status of the connection to the master
The coordinator thread that reads the transactions from the relay log and schedules them to a worker queue (when multi-threaded slave mode [MTS] is enabled). replication_applier_status_by_coordinator: current status of the coordinator thread that only displays information when MTS is enabled
The worker threads applying the transactions. replication_applier_status_by_worker: current status of the thread(s) applying transactions received from the master

Nine additional fields were added to each table that store information about the last transaction, the corresponding thread processed, and the transaction that thread is currently processing. This information includes:

    1. the transaction’s GTID
    2. its OCT and ICT (retrieved from the slave’s relay log)
    3. the time the thread started processing it and
    4. in case of the last processed transaction, the time the thread finished processing it.

New replication_connection_status Fields

The new replication_connection_status fields report information on the last and current transaction the connection thread queued into the relay log. This includes when it:

  • started queuing the transaction, i.e., when it read the first event from the master and queued it in the relay log
  • for LAST_QUEUED_TRANSACTION, when it finished queuing the last event of the transaction in the relay log

Here are the fields, along with sample data:

Field                                                                                                                                                                      Sample Data
————————————————————————————————————————————————————————–
LAST_QUEUED_TRANSACTION 				      aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1

LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP             2018-01-04 12:48:05.661130

LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP            2018-01-04 12:48:05.661130

LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP   	      2018-01-04 12:48:05.674003

LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP     	      2018-01-04 12:48:05.697760

QUEUEING_TRANSACTION    

QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP  	      0000-00-00 00:00:00.000000

QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP 	      0000-00-00 00:00:00.000000

QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP      	      0000-00-00 00:00:00.000000

New replication_applier_status_by_coordinator Fields

When MTS is enabled, the replication_applier_status_by_coordinator table also reports which was the last transaction buffered by the coordinator thread to a worker’s queue, as well as the transaction it is currently buffering. The start timestamp refers to when this thread read the first event of the transaction from the relay log to buffer it to a worker’s queue, while the end timestamp refers to when the last event finished buffering to the worker’s queue.

Here are its fields, along with sample data:

Field                                                                                                                                                                      Sample Data
————————————————————————————————————————————————————————–
LAST_PROCESSED_TRANSACTION      			     aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1

LAST_PROCESSED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP         2018-01-04 12:48:05.661130

LAST_PROCESSED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP        2018-01-04 12:48:05.661130

LAST_PROCESSED_TRANSACTION_START_BUFFER_TIMESTAMP            2018-01-04 12:48:05.674139

LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP              2018-01-04 12:48:05.819167

PROCESSING_TRANSACTION

PROCESSING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP             0000-00-00 00:00:00.000000

PROCESSING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP            0000-00-00 00:00:00.000000

PROCESSING_TRANSACTION_START_BUFFER_TIMESTAMP   	     0000-00-00 00:00:00.000000

New replication_applier_status_by_worker Fields

The table replication_applier_status_by_worker now contains details about the transactions applied by the applier thread, or, if MTS is enabled, by each worker.  The start timestamp refers to when the worker started applying the first event, whereas the end timestamp refers to when the last event of the transaction was applied.

Here are its nine new fields, along with sample data:

Field                                                                                                                                                                      Sample Data
————————————————————————————————————————————————————————–
LAST_PROCESSED_TRANSACTION      			    aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1

LAST_APPLIED_TRANSACTION        			    aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1

LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP          2018-01-04 12:48:05.661130

LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP         2018-01-04 12:48:05.661130

LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP  	    2018-01-04 12:48:05.822463

LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP    	    2018-01-04 12:48:05.948926

APPLYING_TRANSACTION    

APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP  	    0000-00-00 00:00:00.000000

APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP 	    0000-00-00 00:00:00.000000

APPLYING_TRANSACTION_START_APPLY_TIMESTAMP      	    0000-00-00 00:00:00.000000

More Efficient Relationship between Replication Threads

As of MySQL 8.0.1, updates to the replica server will make it more efficient than previous MySQL versions thanks to improvements in the relationship between the replication threads.  Although it’s still early to say with any precision just how the increased efficiency will translate into faster performance, preliminary testing showed a benefit of up to 65 percent.

The core of MySQL replication on the replica side is composed of two threads (sometimes more):

  1. the connection: handles the connection with the master, retrieving the events and queuing them on the relay log.
  2. the applier: reads the queued events from the relay log and applies them to the replica

The Old Arbitration Thread Model

The relationship between connection and applier was problematic when both threads were dealing with the same relay log file, due to “arbitration”.  The relay log file could only be accessed by one thread at a time, resulting in mutual exclusion of the replication threads.  Hence, when the connection was writing to the relay log file, the applier was unable to read content to be applied and had to wait. Likewise, when the applier was reading from the relay log file, the connection was unable to write new content to it and went into an idle state.  The arbitration was necessary to prevent the applier from sending events that were only partially written to the relay log to workers.

While sometimes beneficial on slaves with limited resources, this arbitration was also limiting the scalability of the multi-threaded slave (MTS) applier.

The New Thread Model

As of MySQL 8.0.1, the applier should almost never block the connection anymore, the exception being when the relay log has exceeded its size limit.   Likewise, the connection will not block the applier for already fully queued transaction parts.  To make this solution work, the connection thread keeps updated information about the position in the relay log file of the last fully queued event. The applier now reads from the log up to this position and waits for notification from the connection thread when it’s done writing to the relay log.

Conclusion

This blog provided an overview of MySQL 8.0’s exciting new replication enhancements, including:

  • new replication timestamps: MySQL 8 introduces two new timestamps that complement the Seconds_Behind_Master metric.  These are associated with the global transaction identifier (GTID) of each transaction written to the binary log.  
  • new information reported by performance schema tables: the Performance Schema in version 8.0 can now instrument server errors, supports indexes and adds new fields to the existing performance schema replication status tables.
  • more efficient relationship between replication threads: as of MySQL 8.0.1, updates to the replica server will make it more efficient than previous MySQL versions thanks to improvements in the relationship between the replication threads. Preliminary testing showed a benefit of up to 65 percent!

All of these changes will make for much-improved performance monitoring. Once MySQL 8 goes into production, we’ll get a chance to gauge just how much.