Aurora MySQL のバイナリログ利用に関して調べたことのメモ

はじめに

Aurora MySQL の BG デプロイを試そうとしたところ、バイナリログの有効化が必要でした。しかし、Aurora MySQL ではデフォルトでバイナリログがオフになっています。では有効化すれば良いのではと思いましたが、パフォーマンスに影響が出る可能性も示唆されていて、安易に有効化して良いものか、と思ったので少し調べてみました。

バイナリログ

MySQL :: MySQL 8.0 Reference Manual :: 7.4.4 The Binary Log
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

For replication, the binary log on a replication source server provides a record of the data changes to be sent to replicas. The source sends the information contained in its binary log to its replicas, which reproduce those transactions to make the same data changes that were made on the source. See Section 17.2, “Replication Implementation”.

Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.5, “Point-in-Time (Incremental) Recovery”.

上記に書いてある通り、バイナリログには、テーブル作成操作やテーブルデータへの変更などのデータベース変更を記述する「イベント」が格納される、とあります。データを更新した各ステートメントに要した時間に関する情報や、行ベース(binlog_format=ROW)以外の場合、(一致する行のない DELETE などの) 潜在的に変更を行おうとしたステートメントについてのイベントも格納されるようです。バイナリログは、レプリケーションやデータリカバリに利用されます。

Aurora MySQL におけるバイナリログの利用

以下の2つの利用パターンがあるのではないかと思います。

クラッシュ時

ドキュメントによると、Aurora MySQL の場合、クラッシュ時に関しては、バイナリログなしでもリカバリされる、と記載があります。

Amazon Aurora storage and reliability - Amazon Aurora
Aurora is designed to recover from an unplanned restart almost instantaneously and continue to serve your application data without the binary log. Aurora recovers asynchronously on parallel threads, so that your database is open and available immediately after an unplanned restart.

おそらくAurora MySQLの仕組み上REDO/UNDOログがあれば復旧できるため、ということなのかと思っていますが、障害でのデータリカバリにおいてはバイナリログなしでも良いという選択はできそうです。一方で、オペミスによるデータロスト、例えば謝って DELETE 文を実行して削除してしまった、と言った場合にはバイナリログがないと復旧は難しいかと思います。その障害ケースまで想定する場合は、バイナリログも出力する必要がありそうです。

Aurora / Aurora MySQL のバックアップ, 復元機能

Aurora MySQL でのデータ復元のための機能として、スナップショットの他に、バックトラックやPITRがあります。 PITRは Aurora DB クラスターの機能で、Aurora MySQL でも Aurora PostgreSQL でも使えますが、バックトラックは Aurora MySQL の機能になるようです。

PITRは特定の時点の状態で DB クラスターを新規に作成して起動することができます。バックトラックは既存DBクラスターを特定の時点まで巻き戻します(厳密にはバックアップ&復元ではない)。便利そうですがクラスター作成時(またはスナップショットからの復元時)にしか有効にすることができません。いずれも指定した時点に巻き戻すことができますが、それまでの更新データは失われてしまいます。バイナリログがあれば、それを適用することで追加のデータ復旧ができることになります。

Aurora クラスター間、またはMySQLへのレプリケーション

Aurora と MySQL との間、または Aurora と別の Aurora DB クラスターとの間のレプリケーションすることが必要になった場合は、レプリケーションのためにバイナリログが必要になります。最近利用可能になった Aurora MySQL での Blue Green Deployments では Blue 環境から Green 環境へのレプリケーションに、バイナリログを利用する必要があるため、バイナリログ出力を有効にして、バイナリログレプリケーションを行う必要があります。

新機能 – Amazon Aurora と Amazon RDS でのフルマネージド型 Blue/Green Deployments | Amazon Web Services ブログ

Aurora におけるバイナリログ有効化の場合の性能影響

Aurora MySQLでバイナリログを有効化した場合、以下のような影響があるとドキュメントには記載されています。

Configuring Aurora MySQL binary logging - Amazon Aurora
Important

Setting the binary logging format to row-based can result in very large binary log files. Large binary log files reduce the amount of storage available for a DB cluster and can increase the amount of time to perform a restore operation of a DB cluster.

Statement-based replication can cause inconsistencies between the source DB cluster and a read replica. For more information, see Determination of safe and unsafe statements in binary logging in the MySQL documentation.

Enabling binary logging increases the number of write disk I/O operations to the DB cluster. You can monitor IOPS usage with the VolumeWriteIOPs CloudWatch metric.

バイナリログは、デフォルトだと通常のデータと同様にAuroraのストレージ基盤に保存されるようで、それによってIO性能に影響が出る可能性があるようです。

MySQL(Aurora MySQL) は、バイナリログのイベントをバイナリログファイルに書き込むだけでなく、書き込まれて生成されたファイルからバイナリログイベントを読み取ることも発生するため、MySQL(Aurora MySQL) がこれらのバイナリログを読み込む処理が実行された場合、パフォーマンス劣化の可能性があるようです。(このファイルサイズが大きかったりする場合など)

例えば、他の MySQL(Aurora MySQL)データベースに対してバイナリログを用いたレプリケーションを実行し始めると、IOボトルネックになる可能性があるようです。

そのため、 VolumeWriteIOPs, VolumeReadIOPs メトリクス等を見て、影響の有無をチェックしておく必要があります。また、昨年(2022年5月)下記のようなリリースがあったので、パフォーマンス改善の仕組みも準備されているようです。

Introducing Amazon Aurora MySQL enhanced binary log (binlog) | AWS Database Blog

また、バイナリログのリカバリプロセスにより、再起動時のエンジンの起動時間が長くなる、とあります。

Amazon Aurora storage and reliability - Amazon Aurora
Enabling binary logging on Aurora directly affects the recovery time after an unplanned restart, because it forces the DB instance to perform binary log recovery.

バイナリログが無くともAuroraによるリカバリプロセスで復旧可能な形にはなっていると思いますが、バイナリログがある場合は DB インスタンスで強制的にバイナリログ復旧が実行されるためのようです。おそらくこの時にバイナリログファイルの読み込みが発生することでのIO処理と復元処理で時間がかかる事になるのかと思いました。

バイナリログ出力時の性能検証

現時点(Aurora MySQL v3)で binlog のOFF/ONでどのくらいのパフォーマンス影響があるか、sysbench を使って検証してみます。バイナリログレプリケーションは実行してませんので、sysbench の処理によるバイナリログの出力が行われた場合の性能を見てみる、ということになります。

$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=22.04
DISTRIB_CODENAME=jammy
DISTRIB_DESCRIPTION="Ubuntu 22.04.3 LTS"

$ sysbench --version
sysbench 1.0.20

mysql> show variables like '%version';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| admin_tls_version        | TLSv1.2,TLSv1.3 |
| aurora_version           | 3.04.0          |
| immediate_server_version | 999999          |
| innodb_version           | 8.0.28          |
| original_server_version  | 999999          |
| protocol_version         | 10              |
| tls_version              | TLSv1.2,TLSv1.3 |
| version                  | 8.0.28          |
+--------------------------+-----------------+
8 rows in set (0.01 sec)

sysbench インストール

sysbench をインストールします。

$ sudo apt update

$ sudo apt install -y sysbench

sysbench用のデータベースとユーザー作成

Aurora MySQL へ sysbench 用のデータベースとユーザーを作成します。

$ mysql -u admin -h ams-sample-amazon-aurora-mysql-stack.cluster-********.ap-northeast-1.rds.amazonaws.com -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 533
Server version: 8.0.28 Source distribution

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> CREATE DATABASE sbtest;
Query OK, 1 row affected (0.00 sec)


mysql> CREATE USER sbtest@'%' IDENTIFIED BY 'Password1!';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON sbtest.* TO sbtest@'%'
    -> ;
Query OK, 0 rows affected (0.00 sec)


mysql> exit
Bye

~$ sysbench --mysql-host=ams-sample-amazon-aurora-mysql-stack.cluster-********.ap-northeast-1.rds.amazonaws.com  --mysql-user=sbtest --mysql-password='Password1!' --mysql-db=sbtest --tables=3 --table_size=10000 oltp_common prepare
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 10000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 10000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...

sysbench 実行

sysbenchで書き込みのテストoltp_write_onlyを実行します。まずbinlogなし(binlog_format=OFF)の状態で実行します。この場合、binlog_formatはROWとなっていました。

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

$ sysbench --db-driver=mysql --mysql-host=ams-sample-amazon-aurora-mysql-stack.cluster-********.ap-northeast-1.rds.amazonaws.com  --mysql-user=sbtest --mysql-password='Password1!' --mysql-db=sbtest oltp_write_only --threads=64 --time=300 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 64
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            0
        write:                           1576438
        other:                           789415
        total:                           2365853
    transactions:                        392789 (1309.05 per sec.)
    queries:                             2365853 (7884.72 per sec.)
    ignored errors:                      3837   (12.79 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0539s
    total number of events:              392789

Latency (ms):
         min:                                    8.69
         avg:                                   48.88
         max:                                  515.66
         95th percentile:                       89.16
         sum:                             19200860.56

Threads fairness:
    events (avg/stddev):           6137.3281/110.83
    execution time (avg/stddev):   300.0134/0.01

続いて、binlog あり(binlog_format=MIXED)の状態で実行します。 2023/12時点では、binlog_format=MIXEDがAurora MySQLの推奨のようなのでMIXEDにしています。

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)

$ sysbench --db-driver=mysql --mysql-host=ams-sample-amazon-aurora-mysql-stack.cluster-********.ap-northeast-1.rds.amazonaws.com  --mysql-user=sbtest --mysql-password='Password1!' --mysql-db=sbtest oltp_write_only --threads=64 --time=300 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 64
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            0
        write:                           1530155
        other:                           766233
        total:                           2296388
    transactions:                        381247 (1270.62 per sec.)
    queries:                             2296388 (7653.41 per sec.)
    ignored errors:                      3739   (12.46 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0462s
    total number of events:              381247

Latency (ms):
         min:                                   10.67
         avg:                                   50.36
         max:                                  524.95
         95th percentile:                       90.78
         sum:                             19201314.51

Threads fairness:
    events (avg/stddev):           5956.9844/100.13
    execution time (avg/stddev):   300.0205/0.01

両方ともで ignored errors がそれなりに発生しており、これについては何故か確認できてません。 binlog OFFの場合の方が、全体的には数値が良さそうに見えました。アクセスパターンや、バイナリログレプリケーションを行うと結果は変わるかと思いますが、バイナリログを出力するだけでもパフォーマンス影響は多少はあるように思いました。

まとめ

Aurora MySQLでバイナリログを有効化して良いものか迷ったので、どんな影響がありそうか調べてみました。性能面では、簡単に sysbench を試した限りでは、ONの場合の方が性能は多少(2-3%程度)は落ちるようですが、これはあくまで参考値として、各環境における本番ワークロードでどの程度影響があるかは念の為見ておいた方が良さそうです。また、クラッシュ時のリカバリにかかる時間については検証できていません。

ただ、binlog I/O cache や、 Aurora MySQL 3 では enhanced binlog という機能もリリースされており、バイナリログを別ストレージノードに書き込むことで性能向上を図る機能もあるようなので(ただし制約もあり)、これらを組み合わせることでバイナリログレプリケーションの性能はこれまでより向上させることができそうです。

バイナリログはデフォルトでAuroraストレージに保存されるようなので、出力する場合は料金面にも多少影響しそうなので、保存期間等も検討する必要がありそうです。

dshimizu/blog/alpha

とりとめのないITブログ