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:
- 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.
- 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_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:
- 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:
- events_errors_summary_by_account_by_error
- events_errors_summary_by_host_by_error
- events_errors_summary_by_thread_by_error
- events_errors_summary_by_user_by_error
- events_errors_summary_global_by_error
- 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:
-
- the transaction’s GTID
- its OCT and ICT (retrieved from the slave’s relay log)
- the time the thread started processing it and
- 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):
- the connection: handles the connection with the master, retrieving the events and queuing them on the relay log.
- 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.