Amazon RDS for MySQL のパラメータ設定パート 2: レプリケーション関連のパラメータ

このシリーズの前回のブログ投稿では、Amazon RDS for MySQL のパフォーマンスを調整および最適化するために使用する MySQL パラメータと、それらに関連するベストプラクティスについて説明しました。今回の投稿では、RDS MySQL 環境でのレプリケーション構成とレプリケーションの最適化に使用される最も重要な MySQL パラメータについて説明します。

シングルスレッドおよびマルチスレッドの両方のレプリケーションに関連するパラメータ

以下に、シングルスレッドのレプリケーションとマルチスレッドのレプリケーションの両方に使用できるパラメータと、それぞれを設定するためのベストプラクティスの提案を示します。

sync_binlog

sync_binlog オプションは、MySQL がバイナリログをディスクにフラッシュする方法をコントロールします。

sync_binlog のデフォルト値は 1 です。マスターサーバで、sync_binlog が 0 に設定されているとディスクと同期しません。この場合、他のファイルと同様に、バイナリログの内容をフラッシュするタイミングはオペレーティングシステムに依存します。したがって、この設定は、MySQL がバイナリログ (binlog) をディスクにフラッシュしないようにすることでレプリケーションのパフォーマンスを向上させます。この方法で、最高のパフォーマンスが得られます。

ただし、MySQL がクラッシュした場合、いくつかのトランザクションが失われる可能性があります。通常、確実にレプリカをマスターと同期させるために、レプリカを再構築する必要があります。バックアップを無効にし、「チェーン接続された」リードレプリカがないRDS リードレプリカでは、binlog を同期する必要がないため、sync_binlog は適用されません。バイナリログが生成されないように、レプリカのバックアップ保存期間を 0 に設定することをお勧めします。

ただし、データの損失を最小限に抑えるには、レプリカソースで sync_binlog パラメータを 1 に設定する必要があります。設定する最善の値は、パフォーマンスと耐久性のどちらを優先するかによって異なります。

binlog_row_image

binlog_format パラメータを使用すると、バイナリログでサポートされている 2 つのイベントフォーマットを指定することができます。STATEMENT と ROW です。行ベースのフォーマットを使用すると、非決定性のクエリをログに記録することができるので、レプリカで一時テーブルは作成されません。一方、ステートメントベースのフォーマットは、行ベースのフォーマットよりもコンパクトです。

binlog_row_imag パラメータを使用して、行ベースのイベントについてバイナリログに記録される情報量をコントロールすることができます。行の状態は、バイナリログの「イメージ」で表されます。どの行ベースのイベントでも、変更前イメージと変更後イメージの 2 種類のイメージがあります。変更が行われる前の行は、変更前イメージによって表されます。変更が行われた後の行は、変更後イメージによって表されます。すべてのイベントに、変更の前後のイメージがあるわけではありません。

次の表は、さまざまな行ベースのイベントとそれらの使用可能なイメージをまとめたものです。INSERT ステートメントは Write_rows イベントを作成します。

イベントのタイプ	変更前イメージ	変更後イメージ
Write_rows		含まれる
Update_rows	含まれる	含まれる
Delete_rows	含まれる

次の例は、mysqlbinlog ユーティリティを使用してこれらのイメージを人間が読める形式で読み取ることで、これらのイメージを詳細に示しています。

mysql> SHOW CREATE TABLE test.table1\G
*************************** 1. row ***************************
       Table: table1
Create Table: CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL,
  `val1` int(10) unsigned NOT NULL,
  `val2` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> INSERT INTO test.table1 VALUES (1, 10, '');
Query OK, 1 row affected (0.03 sec)
mysql> UPDATE test.table1 SET val2 = UUID() WHERE id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> DELETE FROM test.table11 WHERE id = 1;
Query OK, 1 row affected (0.07 sec)

以下は、先行する INSERT、UPDATE、DELETE ステートメントによって生成されたイベントに対する mysqlbinlog --base64-output=decode-rows --verbose コマンドの出力です。where の後のセクションは、変更前イメージを表しています。SET の後のセクションは、変更後イメージを表しています。

この INSERT の例には、変更後イメージがあります。

#170820  7:44:22 server id 582927608  end_log_pos 6985 CRC32 0x00499cfa         Table_map: 'test'.'table1' mapped to number 240
# at 6995
#170820  7:44:22 server id 582927608  end_log_pos 7030 CRC32 0xf0a5e5ad         Write_rows: table id 240 flags: STMT_END_F
### INSERT INTO 'test'.'table1'
### SET
###   @1=1
###   @2=10
###   @3=''

この UPDATE の例には、変更前イメージと変更後イメージの両方があります。

#170820  7:44:42 server id 582927608  end_log_pos 7402 CRC32 0x842b11e5         Update_rows: table id 240 flags: STMT_END_F
### UPDATE 'test'.'table1'
### WHERE
###   @1=1
###   @2=10
###   @3=''
### SET
###   @1=1
###   @2=10
###   @3='e3c27a9e-7e25-11e7-b749-08002715584a'

この DELETE の例には、変更前イメージだけがあります。

#170820  7:44:52 server id 582927608  end_log_pos 7750 CRC32 0x7f842c22         Delete_rows: table id 240 flags: STMT_END_F
### DELETE FROM 'test'.'table1'
### WHERE
###   @1=1
###   @2=10
###   @3='e3c27a9e-7e25-11e7-b749-08002715584a'

上記の例では、すべての列の詳細がデフォルト設定で記録されています。binlog_row_image パラメータは、先行するイベントに対してどの列とイメージを記録するかをコントロールするのに役立ちます。

このパラメータでサポートされている値は次のとおりです。

full – 変更前イメージと変更後イメージのすべての列をログに記録します。
minimal – 変更後イメージで変更された列だけと、変更前イメージで行を識別するために必要な列をログに記録します。
noblob – 変更されていない限り、BLOB 列と TEXT 列を除くすべての列をログに記録します。

以下のセクションでは、これらのフォーマット値について説明します。どのイメージフォーマットを使用するかを決定する際は、これらのフォーマットの利点と欠点、およびユースケースとワークロードへの適用方法を考慮します。

Full

full の値については、変更前イメージと変更後イメージの両方がある、前の例の update_row イベントの例を示します。

#170820  7:44:42 server id 582927608  end_log_pos 7402 CRC32 0x842b11e5         Update_rows: table id 230 flags: STMT_END_F
### UPDATE 'test'.'table1'
### WHERE
###   @1=1
###   @2=10
###   @3=''
### SET
###   @1=1
###   @2=10
###   @3='e3c27a9e-7e25-11e7-b749-08002715584a'

2 番目の列 val1 は更新されませんが、変更前イメージと変更後イメージに含まれます。

full イメージを使用する利点は次のとおりです。

すべてのストレージエンジンが、このフォーマットをサポートしています。
このフォーマットを使用すると、データ操作言語 (DML) のステートメントをロールバックすることができます。このフォーマットは、行を削除するイベントについて、行の変更前イメージのすべての列をログに記録します。したがって、変更前イメージに記録されている値を再挿入することで、行を再構築することができます。

ただし、binlog_row_image=full を使用することの欠点は、イベントを記録するためにバイナリログでより多くのスペースが必要になることです。これは、列に BLOB または TEXT の値がある更新ステートメントが多数ある場合に特に問題になります。

Minimal

minimal オプションは、変更を適用するのに必要な情報だけをログに記録します。

変更前イメージ – プライマリキーの値だけが、ログに記録されます。
変更後イメージ – 値が変更された列が、ログに記録されます。

上記の挿入、更新、削除の例で、minimal オプションを使用した場合、3 つのイベントは次のとおりです。

INSERT の例には、変更後イメージだけがあります。

#170820  10:20:22 server id 582927608  end_log_pos 8434 CRC32 0xdea26bc4         Write_rows: table id 240 flags: STMT_END_F
### INSERT INTO 'test'.'table1'
### SET
###   @1=1
###   @2=10
###   @3=''

UPDATE の例には、変更前イメージと変更後イメージの両方があります。

#170820  10:40:42 server id 582927608  end_log_pos 8500 CRC32 0xecc1bb1f         Update_rows: table id 240 flags: STMT_END_F
### UPDATE 'test'.'table1'
### WHERE
###   @1=1
### SET
###   @3='e3c27a9e-7e25-11e7-b749-08002715584a'

DELETE の例には、変更前イメージだけがあります。

#170820  10:44:52 server id 582927608  end_log_pos 8555 CRC32 0x5850de79         Delete_rows: table id 240 flags: STMT_END_F
### DELETE FROM 'test'.'table1'
### WHERE
###   @1=1

上記の例から分かるように、INSERT ステートメントの性質上、Write_rows イベントの full オプションと minimal オプションの間に違いはありません。ただし、行の更新イベントおよび行の削除イベントに対応する UPDATE および DELETE のステートメントで記録されるイメージのサイズは、full オプションよりも minimal オプションの方がはるかに小さくなります。

minimal イメージフォーマットを使用する利点は次のとおりです。

バイナリログイベントは、UPDATE と DELETE の方が小さくなります。ほとんどの更新が単一の列であり、varchar、char、text、blob、および同様のデータ型を持つ大規模な列が多数ある場合は、かなりの節約になる可能性があります。
バイナリログが小さいと、スペースを節約するだけでなく、レプリケーションのためのディスク I/O とネットワークトラフィックも少なくなります。

minimal イメージフォーマットを使用する欠点は次のとおりです。

このアプローチが機能するのは、明示的なプライマリキーまたは NULL 以外の一意のインデックスを持つテーブルに対してだけです。
すべての列が変更後イメージに記録されるのではなく、変更された列だけが記録されるため、レプリカインスタンスでの列定義の要件が厳しくなります。以下が要件です。
- 両方のテーブルが同じプライマリキーである。
- データ型が同じである。
- 列の順序が同じである。
- マスターとレプリカに同じ列が必要である。それ以外の場合、レプリカは更新イベントのために通知なしで同期しなくなる可能性があります。

noblob

noblob イメージは、列が必須または変更された場合にだけ BLOB またはテキスト列が変更後イメージに含まれる点を除いて、full イメージと同じです。

slave_pending_jobs_size_max

このパラメータは、まだ適用されていないイベントを保持するレプリカワーカーのキューに割り当てられるメモリの最大量を定義します。デフォルト値は 16 MB です。この値は、マスターの max_allowed_packet の値より小さくしてはなりません (下記参照)。小さくすると、処理しなければならないイベントがマスターから来ているのに、レプリカワーカーのキューが満杯になる原因となります。

max_allowed_packet

マスターとリードレプリカの両方で、max_allowed_packet に同じ値が設定されていることを確認します。そうでないと、レプリカの max_allowed_packet の値が低いことによる制約で、レプリケーションの遅延が発生する可能性があります。このトピックについては、接続とタイムアウトに関連するパラメーターについてのブログシリーズの今後の部分で詳しく説明します。

innodb_rollback_on_timeout

このパラメータが指定されていると、トランザクションのタイムアウトにより InnoDB が中断し、実行が成功した最後のクエリだけでなく、トランザクション全体をロールバックします。こうすることで、重複エントリに関連するさまざまなレプリケーションエラーを回避できます。デフォルト値は OFF であり、機能を無効にします。

innodb_flush_log_at_trx_commit

パフォーマンスに関連するパラメータについてのこのブログシリーズのパート 1 で説明したように、レプリカインスタンスで innodb_flush_log_at_trx_commit を 0 または 2 に設定すると、ログバッファがディスクにフラッシュされる頻度が少なくなります。これにより、ディスク書き込みによるパフォーマンスへの影響が少なくなります。ただし、前述のように、これを実行すると、クラッシュした場合に一部のトランザクションが失われる可能性があるというトレードオフが伴います。

query_cache settings

リードレプリカのクエリキャッシュを無効にすることをお勧めします。お勧めする理由は、リードレプリカがデータベースに書き込むときは、ほとんどの場合クエリキャッシュを無効にする必要があるからです。

クエリキャッシュを無効にするには、query_cache_type を 0 に、query_cache_size を 0 に設定します。クエリキャッシュの操作方法の詳細については、このブログシリーズのパート 1 を参照してください。

read_only

このパラメータを使用すると、クライアントからリードレプリカへの更新を許可することができます。デフォルト値は TrueIfReplica です。レプリカインスタンスの場合、TrueIfReplica は値を ON (1) に設定し、クライアントからの書き込みアクティビティを無効にします。マスター/ライターインスタンスの場合、TrueIfReplica は値を OFF (0) に設定し、マスター/ライターインスタンスのクライアントからの書き込みアクティビティを有効にします。

この機能を有効にするには、パラメータグループで read_only を 1 に設定します。read_only を 0 に設定してこのパラメータを無効にすると、リードレプリカは書き込み可能になります。ただし、RDS MySQL リードレプリカのベストプラクティスとして、この方法でリードレプリカを長期間書き込み可能に変更することはお勧めしません。この設定は、レプリケーションエラーやデータの整合性に関する問題を引き起こす可能性があります。

log_bin_trust_function_creators

バイナリロギングが有効になっているときに関数、プロシージャ、トリガーを有効にするには MySQL SUPER 権限が必要です。この権限は、RDS MySQL DB インスタンスでは制限されています。ただし、バイナリログが有効になっている場合、RDS MySQL インスタンスで関数、プロシージャ、トリガーを有効にできます。そのためには、log_bin_trust_function_creators パラメータ値を 1 に設定します。そうしないと、次のようなエラーが発生する可能性があります。

「You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable).」

デフォルト値は 0 です。

マルチスレッドレプリケーションだけに関連するパラメータ

以下に、マルチスレッドレプリケーションに関連するパラメータと、それぞれを設定するためのベストプラクティスの提案を示します。

slave_parallel_type

このパラメータは、MySQL バージョン 5.7 から利用可能です。その値は、マルチスレッドレプリケーションが有効になっているときにどのトランザクションを並列実行するかを決定するポリシーを定義します。そのためには、slave_parallel_workers パラメータにゼロ以外の値を使用します。

このパラメータは、LOGICAL_CLOCK と DATABASE の 2 つの値を持つことができます。デフォルト値は、DATABASE です。LOGICAL_CLOCK が設定されていると、マスターで同じバイナリロググループコミットの一部であるトランザクションが、レプリカで並列実行されます。さらに並列化を提供するために、トランザクション間の依存関係をタイムスタンプを使用して追跡します。

値が DATABASE に設定されている場合、異なるデータベースで実行されているトランザクションは並列に適用されますが、この値ではスキーマ内の並列化は不可能です。この値を適用できるのは、異なるデータベースのデータがパーティション化されていて、マスターで同時に独立して更新される場合です。DATABASE を使用するには、データベース間の制約があってはなりません。

このパラメータに使用する値を決定するには、前述の制約と、スキーマ内かスキーマ間のどちらで並列化するかを検討します。スキーマ内の並列化では、LOGICAL_CLOCK が唯一のオプションです。

slave_preserve_commit_order=1 (後述) を使用するには、slave_parallel_type に LOGICAL_CLOCK を使用する必要があります。

slave_parallel_type が LOGICAL_CLOCK であり、slave_preserve_commit_order が有効になっていると、マルチスレッドスレーブ (MTS) ワーカーが innodb_lock_wait タイムアウトに等しい秒数の間ハングする可能性があるという既知の問題があります。この問題は MySQL 5.6.3、MySQL Bug 82400 および MySQL Bug 25082593 です。これを回避するには、トランザクション分離レベル READ COMMITTED を使用するようにレプリカを設定します。これを行うには、tx_isolation パラメータを使用します。

slave_parallel_workers

このパラメータは、トランザクションを並列実行しているレプリカインスタンスのワーカースレッドの数を設定します。デフォルト値は 0 であり、その値はワーカースレッドの並列実行を無効にします。可能な値は、0 〜 1,024 です。

レプリカで並列トランザクション実行を使用すると、レプリケーションのスケーラビリティが向上します。ただし、この方法は、マスターとレプリカの両方が少なくとも MySQL 5.6 以降である場合にのみ機能します。slave_parallel_workers が 0 より大きい値に設定されていると、トランザクションを再試行できず、slave_transaction_retries は 0 として扱われます。このパラメータにこの値を設定することは、MySQL 5.6.3 (bug 13334470) では必ずしも正しく受け入れられていませんでした。これは 5.6.4 で修正されています。また、このパラメータが有効になっていると、バグ 84415 が影響を与える可能性があります。

MySQL 5.6 では、パラレルレプリケーションはスキーマごとに 1 つのスレッドしか使用しないため、複数のデータベースがある場合は効果的に使用できます。5.7 では、このアプローチはスキーマ内のワークロードにも使用できます。

slave_parallel_workers の値を大きくしても、レプリケーションのパフォーマンスは直線的には向上しません。このパラメータの最適な値を推定する方法はワークロードに依存しており、実際のワークロードをシミュレートし、レプリケーションの遅れを監視することによってテストする必要があります。

トランザクションに関連するパフォーマンススキーマの計測を有効にして、実行済みトランザクションを記録することができます。次に、それぞれのレプリケーションスレッドによって実行されたトランザクション数を知るために、パフォーマンススキーマテーブル performance_schema.events_transactions_summary_by_thread_by_event_name および performance_schema.replication_applier_status_by_worker を確認します。そうすることは、すべてのスレッドが適切に使用されていることを確認するのに役立ち、必要に応じて slave_parallel_workers の値を調整することができます。詳細については、Percona のこちらのブログ投稿を参照してください。

この値を調整する方法は、ワークロードによって異なります。リードレプリカがどの読み取り操作にも使用されず、スタンバイレプリカとしてのみ使用されている場合は、この値をインスタンスの vCPU の数に設定できます。リードレプリカがスタンバイとしてではなく読み取りワークロードの処理に使用されている場合は、値を設定した後にワークロードをテストします。

slave_preserve_commit_order

このパラメータは、MySQL 5.7 で利用可能です。トランザクションは、レプリカのリレーログに表示されるのと同じ順序でレプリカで実行されます。デフォルト値は 0 であり、この機能を無効にします。

MySQL 5.7 で slave_parallel_type が LOGICAL_CLOCK に設定されている場合に、マルチスレッドレプリケーションの設定でレプリケーションギャップを回避するために、このパラメータをお勧めします。このパラメータは、決してレプリカがマスターがない状態にならないようにします。

このオプションを使用するには、前提条件として自動バックアップ (したがってバイナリロギング) と log_slave_updates パラメータを有効にする必要があります。log_slave_updates パラメータは、デフォルトで有効になっています。レプリケーションインスタンスでバイナリロギングを有効にすると、ストレージの使用率とパフォーマンスに影響する可能性があります。したがって、バイナリロギング (自動バックアップ) の有無にかかわらずレプリケーションをテストすることをお勧めします。

マルチスレッドスレーブオプションが有効になっている場合、トランザクションは並列実行できます。slave_preserve_commit_order パラメータが設定されている場合、実行中のレプリカは、前のトランザクションがすべてコミットされるまでトランザクションのコミットを待機します。レプリカスレッドが他のワーカースレッドが前のトランザクションをコミットするのを待っている場合、そのステータスは前のトランザクションのコミット待ちとして報告されます。

このオプションが有効になっていない場合、リプレイログから実行される一連のトランザクションにギャップが生じる可能性があります。デフォルト値は 0 であり、このパラメータを無効にします。ただし、この場合、Exec_master_log_pos は、SQL スレッドが読み取って実行した現在のマスターバイナリログファイルの位置より後ろにあるように見える場合があります。

結論

上記のパラメータは、RDS MySQL のレプリケーションのパフォーマンスと安定性に影響を与える可能性がある最も重要なパラメータです。これらのパラメータについて説明したベストプラクティスに従うことで、MySQL RDS レプリカを最小限の遅延でで実行し、他の運用上の問題を回避することができます。

著者について

Saikat Banjeree は、アマゾンウェブサービスのクラウドサポートエンジニアです。

Amazon Web Services ブログ

Amazon RDS for MySQL のパラメータ設定 パート 2: レプリケーション関連のパラメータ