Introducing binlog I/O cache in Amazon Aurora MySQL to improve binlog performance

Binlog replication is a popular feature serving multiple use cases, including offloading transactional work from a source database, replicating changes to a separate dedicated system to run analytics, and streaming data into other systems, but the benefits don’t come for free.

Binary logging can limit database performance (higher commit latency and lower throughput) because the logging for binlog events is integrated as part of a critical path of transaction commits. The MySQL engine starts to write extra information (binlog events) into binlog files. Usually, binlog files are written in local storage, but Aurora MySQL-Compatible Edition writes these binlog files in the Aurora storage engine. The binlog file can be a performance bottleneck when MySQL starts to replicate these binlog events to other MySQL databases because MySQL not only writes binlog events to a binlog file, but also reads the binlog events from the same file.

The Aurora MySQL 2.10 release introduces a new feature named binlog I/O cache to improve performance for this use case, in which Aurora MySQL turns on binary logging and actively replicates its binlog events to the same file.

In this post, we show the performance improvement of the new binlog I/O cache and discuss its related monitoring metrics and status variables.

Binlog I/O cache overview

The binlog I/O cache minimizes read I/O from the Aurora storage engine by keeping the most recent binlog events in its circular cache. The binlog I/O cache is enabled for most Aurora MySQL instances except for db.t2 and db.t3 instance classes.

In the following graph, we measured the average writes per second by turning the binlog I/O cache on (blue line) and off (orange line). This shows how much throughput improvement we can gain with the binlog I/O cache.

In the graph, we also plotted the throughput of the “without replica” setup (gray line), which shows the optimal performance without the I/O contention from read replicas. This shows that when binlog I/O cache is enabled, the source instance can scale with the increase in write throughput (blue) to the optimal write throughput as if there were no replica attached (gray).

Before we released the binlog I/O cache, we implemented related binlog performance enhancement using the parameter aurora_binlog_replication_max_yield_seconds, which is released in 5.7 (2.04.5 and later) and 5.6 (1.17.6 and later). This parameter instructs binlog dump threads to wait up to “aurora_binlog_replication_max_yield_seconds” seconds. This “yielding” of the dump threads allows binlog writer threads to write to an active binlog file with no contention from the binlog dump threads but can have potentially high replica lag.

Although no additional configuration is necessary to use the binlog I/O cache, we recommend resetting the existing max yield feature aurora_binlog_replication_max_yield_seconds back to 0 to take full advantage of the feature.

Monitoring the binlog I/O cache

With this feature, we introduced two status variables (aurora_binlog_io_cache_reads and aurora_binlog_io_cache_read_requests) and new log messages saying [Dump thread metrics].

The variable aurora_binlog_io_cache_read_requests shows the total read I/O counts for any binlog files, and aurora_binlog_io_cache_reads shows the total read I/O counts that can be read from the cache. The following code shows an example query that calculates the hit ratio of the binlog I/O cache:

mysql> SELECT(SELECT VARIABLE_VALUE 
              FROM INFORMATION_SCHEMA.GLOBAL_STATUS 
              WHERE VARIABLE_NAME='aurora_binlog_io_cache_reads')/
             (SELECT VARIABLE_VALUE
              FROM INFORMATION_SCHEMA.GLOBAL_STATUS
              WHERE VARIABLE_NAME='aurora_binlog_io_cache_read_requests')*100 
              as binlog_io_cache_hit_ratio;
+-------------------------+
| binlog_io_cache_hit_ratio |
+-------------------------+
|       99.99847949080622 |
+-------------------------+
1 row in set, 2 warnings (0.00 sec)

In addition, you can monitor the distance (in bytes) of the binlog replica from the latest binlog event written in the source. This information is important because we can analyze whether the latest binlog events that read replica fetched from the source are served from the binlog I/O cache. You can filter the metrics by using the search keywords “Dump thread metrics.” The name “dump thread” came from binary log dump thread in the MySQL documentation.

This metric is logged when a replica is connected to the source instance, and emits every minute. Aurora MySQL only generates the messages if log_error_verbosity is 3, which is the default value. The log_error_verbosity value is configurable through the DB parameter group or DB cluster parameter group in Aurora MySQL.

Summary

Binlog replication can limit database performance because the logging for binlog events is integrated as part of a critical path of transaction commits and the same file for the binlog events are simultaneously read for replication. We introduced the new binlog I/O cache for Aurora MySQL and the cache showed more than 5 times throughput improvement in a binlog-replicated setup. The feature also introduced two status variables (aurora_binlog_io_cache_reads and aurora_binlog_io_cache_read_requests) and new dump thread metrics that appear in the log every minute when a replica is connected to the source cluster.

About the author

Jae M. Kim is a software development engineer at Amazon Web Services. He has worked for the Aurora MySQL team since 2019. He proposed and developed the binlog I/O cache for Aurora MySQL.