Binary logging optimizations in Amazon Aurora MySQL version 3

The binary log (binlog) in MySQL is used to capture database modifications on a MySQL server in a logical format known as “events”. These database modifications can include DCL statements (such as CREATE USER or GRANT), DDL statements (CREATE TABLE, ALTER TABLE) and DML statements (INSERT, UPDATE, DELETE). When such a modification is committed in MySQL, the server persists the transaction’s binary log events atomically with the storage engine commit using a 2-phase commit(2PC). This guarantee of ACID (atomic, consistent, isolated, and durable) compliance and logging of database changes allows MySQL to use this log to facilitate logical replication to other MySQL servers (read replicas), assist in the database recovery process, and provide the ability to restore a DB instance to a point in time through reapplying logical changes on top of a full database backup.

However, enforcing this ACID compliance can come at a price, including amplification of writes, longer database recovery times, and lock contention under high concurrency. For point in time restore(PITR) and horizontal scaling using read replicas, the binary log must be logically replayed on database servers. This can cause lag and an increased Recovery Time Objective (RTO) under heavy write workloads. For example, when a DDL statement needs to be replicated to read replicas or standbys, this can block the application of other log events until completion.

With the release of Amazon Aurora MySQL-Compatible Edition in 2015, customers no longer needed to rely on binary logging to satisfy these requirements. In the custom-built Amazon Aurora storage architecture, replication, recovery, and PITR are all handled transparently at the storage layer without the need to enable binary logging. These innovations enable you to scale highly concurrent workloads to hundreds of thousands of writes per second, with milliseconds of replication lag across Availability Zones and AWS Regions, while not sacrificing recovery times or ACID compliance.

In this post, we discuss use cases for binary logging in Amazon Aurora MySQL, improved binary logging capabilities that have been added to Amazon Aurora MySQL over the years, and additional support for MySQL native binary logging features.

Binary logging use cases

Despite not needing binary logging in Amazon Aurora MySQL for high availability or horizontal scalability, there are use cases that can take advantage of this database change log, such as the following:

• When migrating to Amazon Aurora MySQL, binary log replication can be utilized to facilitate minimal downtime migrations by configuring Amazon Aurora MySQL as a binary log read replica of a MySQL database instance. You can find more information in Migrating data to an Amazon Aurora MySQL DB cluster.
• With Amazon RDS Blue/Green deployments, you can facilitate minimal downtime changes to production systems, such as major version upgrades, using logical binary log replication.
• You can use Amazon Aurora zero-ETL integration with Amazon Redshift or tools such as Maxwell and Debezium to stream database changes from your Aurora MySQL database cluster to other sources, such as a cache, data warehouse or datalake.
• With online schema change tools such as Spirit and gh-ost, you can deploy schema changes to production systems with minimal impact to applications.

Improved binary log capabilities

Based on customer use cases and feedback over the years, the Amazon Aurora MySQL team has been adding to the binary log capabilities through support for additional community features, and optimizing the implementation of binary logging in Amazon Aurora MySQL to take advantage of our highly scalable distributed Aurora storage. In each of the following releases, incremental changes have been made to improve the efficiency and performance of binary logging in four main areas.

Binary log replication consumer threads

In Amazon Aurora MySQL version 2.10, we introduced the binary log I/O cache. The binary log I/O cache aims to minimize read I/O from the Aurora storage layer by keeping the most recent binary log change events in its circular cache on your writer DB instance. This improvement in I/O latency helps improve the rate at which replication consumer threads can fetch change log events and reduce lock contention, especially under highly concurrent write workloads where foreground transactions and binary log consumers can compete for locks on the active binary log file. For more information on these improvements, see Introducing binlog I/O cache in Amazon Aurora MySQL to improve binlog performance.

As mentioned previously one of the common use cases for binary logging is to stream database changes from your Aurora MySQL database cluster to other sources, such as data warehouses. In Amazon Aurora MySQL version 3.05, we introduced the Amazon Aurora zero-ETL integration with Amazon Redshift. Amazon Aurora zero-ETL integration with Amazon Redshift enables near real-time analytics and machine learning (ML) using Amazon Redshift on petabytes of transactional data. Within seconds of transactional data being written into Aurora, zero-ETL makes the data available in Amazon Redshift, removing the need to build and maintain complex data pipelines that perform extract, transform, and load (ETL) operations. Using Amazon Aurora zero-ETL integration with Amazon Redshift, you no longer need to setup and configure change data capture infrastructure using tools such as Maxwell and Debezium. Amazon Aurora MySQL will automatically setup, configure and manage the change data capture infrastructure, streaming changes directly from the Aurora storage layer to your Amazon Redshift data warehouse.

Binary log replication applier threads

In Amazon Aurora MySQL version 3.05, we also introduced the in-memory relay log cache for Amazon Aurora MySQL binary log replicas. This improvement can help achieve up to a 40% increase in binary log replication throughput vs. database clusters without relay log cache enabled. This enhancement is enabled automatically when using single-threaded binary log replication or when using multi-threaded replication with GTID auto-positioning enabled.

In Amazon Aurora MySQL version 3.06 and higher, we introduced an optimization to improve the performance for binary log replicas when replicating transactions for large tables with more than one secondary index. This feature introduces a pool of background threads to apply secondary index changes in parallel on Aurora MySQL binary log replicas, which supplements replica_parallel_workers already available to the replication applier threads. For more information on enabling this optimization, see Optimizing binary log replication.

DML throughput and latency

To optimize the binary log commit process, MySQL has implemented a number of optimizations, such as binary log group commit in an attempt to write changes to the binary log more efficiently, while not affecting the ordering of the events. However, this synchronization point can introduce an area of contention on DB instances with high write throughput workloads.

In Amazon Aurora MySQL version 3.03.1 we introduced Amazon Aurora MySQL enhanced binary log (Enhanced binary log). Amazon Aurora MySQL enhanced binary log allows the database engine to take full advantage of Aurora distributed storage to reduce this contention by offloading the ordering of binary log change events to the Aurora storage layer, without sacrificing the commit order or durability of the committed transactions. Based on testing outlined in Introducing Amazon Aurora MySQL enhanced binary log (binlog), these optimizations have helped provide up to 40% throughput increases on highly concurrent write workloads vs. database clusters without enhanced binary logging enabled.

Binary log recovery

On transaction commit, the binary log events must be written to and made durable in the correct commit order on the active binary log file. For a transaction which generates a large amount of binary log data, the binary log recovery process during startup involves scanning the entire binary log file to collect metadata about the transactions, and using this to ensure consistency with the storage engine (InnoDB) data. If the binary log file size is large, this can take several minutes or longer, which proportionately affects the binary log recovery times.

Amazon Aurora MySQL enhanced binary log discussed above also improves recovery times for the MySQL binary log recovery process. With Enhanced binary log, the time-consuming scan of the binary log file is avoided by optimizations in the Aurora distributed storage layer.

As a result of these improvements, binary log recovery time can be reduced by up to 99%, from up to several minutes down to several seconds. The following table summarizes these recovery times.

For more information, see Introducing Amazon Aurora MySQL enhanced binary log (binlog).

Additional MySQL support

In addition to the Amazon Aurora MySQL optimizations we’ve already discussed, support for native MySQL community functionality was added in addition to what was already available in versions 1 and 2 of Aurora MySQL.

In Amazon Aurora MySQL version 3.01.0, we added support for binary log replication filters. Replication filters allow you to configure what is written to a binary log file and what is applied on a binary log read replica. This functionality can be useful in use cases such as selectively replicating certain tables or databases to read replica DB instances. For more information on replication filters, see Configuring replication filters with Aurora MySQL.

With the addition of dynamic privileges in MySQL 8, some restricted session variables were made available under the SESSION_VARIABLES_ADMIN database privilege. In Amazon Aurora MySQL version 3, users with this privilege have the ability to do the following at the session level:

• You can now modify sql_log_bin. This is useful if you want to carry out operations you don’t want logged in the binary log, such as archival jobs, or DDL statements you don’t want replicated to binary log consumers. In Amazon Aurora MySQL version 2, you can’t natively set sql_log_bin, but in version 2.12, the mysql.rds_disable_session_binlog and mysql.rds_enable_session_binlog stored procedures were added to allow you to do so.
• Additionally, you can modify binlog_format at the session level. There are three binary log formats; ROW, MIXED and STATEMENT. For the majority of use cases, ROW-based logging is recommended, which will log a change event for each row change made on the MySQL server. However, in some cases, this can lead to bloat, such as when doing bulk UPDATE/DELETE operations during archival or data purging. Another popular use case is using open source tools such as pt-table-checksum, which requires setting the binlog_format to statement. With binlog_format, you can now natively change the binlog format at the session level for these operations. In Amazon Aurora MySQL version 2, you can’t natively set binlog_format at the session level, but in version 2.12, the mysql.rds_set_session_binlog_format stored procedure was added to allow you to do so.

In Amazon Aurora MySQL version 3.04, the mysql.rds_gtid_purged stored procedure was added. The gtid_purged system variable is a GTID set that consists of the GTIDs of all transactions that have been committed on the server but don’t exist in any binary log file on the server. This is commonly used when configuring binary log replication auto-positioning between two MySQL database servers, allowing users configure replication more easily. For more information on GTIDs in MySQL, see Replication with Global Transaction Identifiers.

Conclusion

In this post, we discussed some of the optimizations and improvements made to binary logging in Amazon Aurora MySQL over the past few years. These incremental changes have allowed customers unlock new use cases utilizing change data, and improve database performance and recovery times.

To find out more about new Amazon Aurora MySQL releases and features, refer to our release notes and subscribe to the RSS feed to be notified of new releases.

For more information on binary logging in Amazon Aurora MySQL, see Configuring Aurora MySQL binary logging.

For more information on upgrading to Amazon Aurora MySQL version 3, see Aurora MySQL version 3 compatible with MySQL 8.0 and Upgrade to Amazon Aurora MySQL version 3 (with MySQL 8.0 compatibility).

About the Author

Marc Reilly is a Senior Database engineer on the Amazon Aurora MySQL team.