このエントリーは ANDPAD Advent Calendar 2024 23日目の記事です。

こんにちは！DBRE の Infra を担当しているまみーです。
当社では Aurora 環境でレプリケーションを用いた参照専用環境を構築しています。
日々データが増えていく中で、レプリケーション遅延が発生 するようになりました。

本記事では、レプリケーション遅延を解消するために実施した「マルチスレッドレプリカ」の具体的な設定項目、設定方法、確認方法をご紹介します。

なお、記事執筆時点では効果測定が不十分なため、効果は後日測定し加筆修正いたします。
現時点では「具体的な設定項目、設定方法、確認方法」までをご紹介します。

前置き

サービス利用の Aurora MySQL のデータを、サービスに影響なく Redash などからリアルタイム参照すべく、別クラスターにレプリケーションを行なっています。
当社ではそのレプリカクラスターを「外部レプリカ」と呼びます。

本エントリーでは、外部レプリカ運用時に徐々に増えていったレプリケーション遅延を改善するために行ったマルチスレッドレプリカ設定についてご紹介します。

なお、カスタムエンドポイントを使えばいいのに、というご意見もあるかと思いますが、外部レプリカを使うには理由があることを申し添えておきます。 ¹

起きていた問題

主に分析用途のため Redash から外部レプリカを参照しているのですが、たびたびレプリケーション遅延が発生し、commit の同期が 10 分、20 分と遅れ、まれに数時間単位で遅れが発生していました。

各種分析やアラートなど、重要だけれど重めなクエリを伴う調査には一定以上のリアルタイム性が求められるため、改善が必要でした。

レプリケーション遅延

主な原因は Source 側クラスターの commit 数増加によるレプリケーション遅延でした。 ²
成長していくサービス、増える commit 数、サービス側のクラスターがスケールアップした影響もあったと思います。
2023 年はレプリケーション遅延がほとんどなく業務に影響することはなかったのですが、2024 年の夏ごろからたびたびレプリケーション遅延が観測され、利用チームから DBRE に問い合わせをいただくようになりました。

シングルスレッドからマルチスレッドへ

Aurora v2 (MySQL 5.7系) 時代に設置した外部レプリカでしたが、当時はシングルスレッドで動作しており、Aurora v3 (MySQL 8.0系) にバージョンアップ後もそのまま設定を引き継いで運用しておりました。³

そこで、シングルスレッドからマルチスレッドへ設定を変更し、レプリケーション遅延の改善を試みます。
設定変更対象は、Aurora のクラスターパラメーターグループです。

注意点

小さな単位のトランザクションをマルチスレッドで処理することにより、パフォーマンス改善を行い遅延を少なくすることが今回の目的です。
もともと大きなトランザクション（数十秒レベル）は1つのスレッドで処理されるため、改善は見込めないケースがあることに注意が必要です。

マルチスレッドレプリカ設定後、大きなトランザクションが問題になるようであれば、今後の課題として対策をしていく想定です。

手順と各種パラメーター解説

以下の手順で設定を進めます。パラメーターも解説していきます。

Source 側のパラメーター変更
Replica 側のパラメーター変更
performance_schema 有効化のため外部レプリカ再起動
- 有効になっているならばこの手順は不要です
レプリケーション停止、再実行
マルチスレッドレプリカ動作確認（ performance_schema.threads テーブル確認）
統計情報の確認

Source 側のパラメーター変更

レプリケーション元となる Source 側の設定変更パラメーターです。
Dynamic のため再起動することなく反映されます。

Replica 側のパラメーター変更

対象パラメーターグループ
- クラスター
パラメーターの内容
- Dynamic
  - replica_preserve_commit_order=ON
    - MySQL 公式のパラメーターの記述
      
      レプリカのリレーログに表示されるのと同じ順序でトランザクションがレプリカ上で実行され、コミットされます。これにより、レプリカのリレーログから実行されたトランザクションのシーケンスにギャップが生じなくなり、レプリカ上でソースと同じトランザクション履歴が保持されます
      - 整合性を損なわず、全てのスレッドのレプリケーションが並行して実行される
  - replica_parallel_type=LOGICAL_CLOCK
    - MySQL 公式のパラメーターの記述
      レプリカで並列実行できるトランザクションを決定するために使用されるポリシーを指定します
      
      LOGICAL_CLOCK: トランザクションは、レプリケーションソースがバイナリログに書き込むタイムスタンプに基づいて、レプリカに並列に適用されます。トランザクション間の依存関係は、タイムスタンプに基づいて追跡され、可能な場合は追加の並列化が提供されます。
      - binlog に書き込まれたタイムスタンプに基づいて、レプリカでトランザクションが並列実行される
  - replica_parallel_workers=4
    - MySQL 公式のパラメーターの記述
      
      レプリケーショントランザクションを並列で実行するためのスレッドの数を設定します
      - 並列実行ワーカー数
        
        お使いのクラスターの性能や環境に合わせた値を設定してください。今回はデフォルト値である 4 を使用します
- Static (再起動で反映)
  - performance_schema=1
    - マルチスレッドレプリカ設定後の動作確認時のスレッド確認に必要 ( performance_schema.threads テーブルにスレッド状態を保存するため有効化が必要)

確認手順

Dynamic の各種パラメーターは即時反映のため、設定変更したのちに別途コンソールなどから以下のように確認すると確実です。

replica_parallel_workers を確認する場合

mysql> SHOW VARIABLES LIKE 'replica_parallel_workers';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| replica_parallel_workers | 4     |
+--------------------------+-------+
1 row in set (0.01 sec)

ただし、即時反映といっても実際の反映（パラメーターグループ上での変更から実際のインスタンスで有効になる）までに長くて数十秒かかる場合があるため、変更されたことが確認できるまで何度か実行するといいと思います。

performance_schema 有効化のため外部レプリカ再起動

外部レプリカのみ performance_schema が無効化されていたため、AWS コンソールよりインスタンス再起動を行います。
手順は割愛しますが、performance_schema が有効化されているならば不要な手順です。
また、performance_schema を無効化するメリットはないので有効にしておくことを推奨します。

レプリケーション停止、再実行

一度停止して再実行することにより、パラメーターグループの設定変更が反映され、マルチスレッドレプリカが動作するようになります。
Aurora の場合、ストアドプロシージャにて行います。

-- レプリケーション停止。MySQL および RDS の場合、「STOP REPLICA」
mysql> CALL mysql.rds_stop_replication;
+-----------------------------+
| Message                     |
+-----------------------------+
| Replica is down or disabled |
+-----------------------------+
1 row in set (1.05 sec)

Query OK, 0 rows affected (1.05 sec)

-- レプリケーション再実行。MySQL および RDS の場合、「START REPLICA」
mysql> CALL mysql.rds_start_replication;
+---------------------------+
| Message                   |
+---------------------------+
| Replica running normally. |
+---------------------------+
1 row in set (2.17 sec)

Query OK, 0 rows affected (2.17 sec)

レプリケーションの状態も確認する

リスタートした際に、何かの原因でレプリケーションがエラーとなるかもしれません。
以下のコマンドでレプリケーションのステータスを表示し、問題がないことを確認しましょう。

mysql> SHOW REPLICA STATUS\G

SHOW REPLICA STATUS 実行例

Replica_IO_State: Waiting for source to send event
- この例では、Source からの更新を待っている状態
Replica_IO_Running: Yes
- IO スレッドが動作している状態
Replica_SQL_Running: Yes
- SQL スレッドが動作している状態
Last_Errno: 0
- エラーなし
Last_Error: (ブランク)
- エラーなし
Seconds_Behind_Source: 0
- Source と比べてビハインドしているクエリの数。大きな値ではないこと（大きい場合は遅延が発生している）。通常ならば数値があってもだんだんと減っていく
Last_IO_Errno: 0
- エラーなし
Last_IO_Error: (ブランク)
- エラーなし
Last_SQL_Errno: 0
- エラーなし
Last_SQL_Error: (ブランク)
- エラーなし

*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: hogehoge.cluster-xxxx.ap-northeast-1.rds.amazonaws.com
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin-changelog.xxxxxx
          Read_Source_Log_Pos: 157
               Relay_Log_File: relaylog.xxxxxx
                Relay_Log_Pos: 279
        Relay_Source_Log_File: mysql-bin-changelog.xxxxxx
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: mysql.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 157
              Relay_Log_Space: 604
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: (省略)
                  Source_UUID: (省略)
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

マルチスレッドレプリカ動作確認（performance_schema.threads テーブル確認）

シングルスレッドからマルチスレッドになったことを実際に確認するには、performance_schema.threads テーブル確認にて、スレッドを確認します。（他に確認手段をご存知の方おられたら教えてください）
以下のクエリで、4スレッド動作していることが確認でき、問題なくマルチスレッドレプリカが動作していることがわかります。

mysql> SELECT * FROM performance_schema.threads WHERE NAME='thread/sql/replica_worker'\G
*************************** 1. row ***************************
          THREAD_ID: 166095
               NAME: thread/sql/replica_worker
               TYPE: FOREGROUND
     PROCESSLIST_ID: 164211
   PROCESSLIST_USER: user_name
   PROCESSLIST_HOST: xxx.xxx.xxx.xxx
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
   PROCESSLIST_TIME: 11
  PROCESSLIST_STATE: Waiting for an event from Coordinator
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 166094
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 2317
     RESOURCE_GROUP: NULL

(後略)

mysql> SELECT COUNT(1) FROM performance_schema.threads WHERE NAME='thread/sql/replica_worker'\G
*************************** 1. row ***************************
         count(1): 4
1 row in set (0.00 sec)

統計情報のチェック

マルチスレッドレプリカが動作すると、Aurora のエラーログに以下の統計情報が出力されます。（可読性のため改行を入れています）

今回の実行では値として問題ないため確認に留めました。
統計情報にはエラーも出力されるため、検知したい場合は別途 CloudWatch Logs に送り監視を考えるなどしましょう。

2024-12-09T10:09:05.141969Z 19885601 [Note] [MY-010559] 
[Repl] Multi-threaded slave statistics for channel '': 
seconds elapsed = 120; 
events assigned = 6952961; 
worker queues filled over overrun level = 0; 
waited due a Worker queue full = 0; 
waited due the total size = 0; 
waited at clock conflicts = 2294075742600 
waited (count) when Workers occupied = 21446 
waited when Workers occupied = 14096163500
 (rpl_replica.cc:4625)

簡単な解説

詳しくは MySQL 公式を参照ください。ここでは簡単な意訳を記載します。

seconds elapsed = 120
- 統計情報が出力された間隔
events assigned = 6952961
- キューに入れたスレッドの数
worker queues filled over overrun level = 0
- ワーカーキューがオーバーランレベルを超えた数。ゼロの場合はレプリケーション対象のデータがないなどでレプリカ側にキューがない状態など、能力の上限で動作していないことを示す。この値が異常に増える場合はワーカースレッドで受けられるサイズである。replica_pending_jobs_size_max の数値 (デフォルト 128MB) を以下を守った数値で調整する必要がある
  - (source) max_allowed_packet <= replica_pending_jobs_size_max <= (replica) max_allowed_packet
  - 設定時の各種数値から、まずは問題なさそうなことがわかる
    - (source) max_allowed_packet
      - 64 MB
    - replica_pending_jobs_size_max
      - 128 MB
    - replica_max_allowed_packet
      - 1 GB
waited due a Worker queue full = 0
- スレッドが満杯で待機中のワーカーキュー数
waited due the total size = 0
- 総待機サイズ
waited at clock conflicts = 2294075742600
- イベントを待機したナノ秒
waited (count) when Workers occupied = 21446
- スレッドが短時間スリープした回数
waited when Workers occupied = 14096163500
- 空のスレッドを待機したナノ秒数

効果

当ブログ執筆時点ではまだ十分な測定結果が得られておりません…
年始早々に測定結果を掲載しますので、それまでお待ちくださいませ

教訓

performance_schema は有効化しておきましょう。無効化しておくメリットはないと言っていいと思います。

今後のアクション

設定はしましたが効果の測定がまだです。また、測定の結果わかってくるだろうことも考慮し、以下のアクションを想定しています。

レプリケーション遅延の改善の測定
- 外部レプリカのレプリケーション遅延はリアルタイム通知で監視していますので、今後どのように推移していくか測定を続け、グラフとして過去と比較することで、改善されたかどうかを判断していきます
大きなトランザクションがあった場合の対処方法の検討
- マルチスレッドで並行処理が可能となっても、もともとのトランザクションが大きな場合は分割できないため並行処理の恩恵を受けません。問題となるような場合はトランザクション処理を分割できないかなど、今後対処方法を検討する必要があるかもしれません

おわりに

MySQL のレプリケーションを深く知るのは初めて設定したころから実に7年半ぶりでした。わからない設定や出力項目があり、当社の DB 技術顧問に相談しながら進めてきました。
最初は「Dynamic だしサクッと設定すればええか！」と軽く考えていたのですが、思いの外やることが多く、また学びも多かったです。

個人的にも非常に楽しかったです。
今後のアクション含め、引き続き追跡してデータベースのパフォーマンスに寄与していこうと気持ちを新たにした一件でした。

今まで「フェイルオーバーなどを回避し最大限の安全を実現」し「万が一でも事故を起こさない」ポリシーで運用してきた歴史があります。明確に便利でより安全な運用方法があったとしても、まずは既存のルールを受け入れ運用した上で、改善できる可能性のメリットデメリットを丁寧に示していくことこそが、今いる組織の一員として振る舞う最善だと思うので、今はマルチスレッドレプリカの設定を行うのです。 ↩
また、Source と Replica のインスタンスクラスの差が大きかったことによる commit 処理の速度差も原因のひとつだったかもしれません。（例: Source が 16xlarge、Replica が 4xlarge など）。この観点での分析はしていませんが、マルチスレッドレプリカでの改善度合いが低ければ、分析・検証することになるかと思います。 ↩
Aurora v1 (MySQL 5.6) のパラメーターを引き継いでおり、マルチスレッドレプリカ設定は OFF でした。v2 -> v3 と同様にパラメーターを引き継いだため今回の設定に至っています。なお、MySQL 8.0.27 でマルチスレッドレプリカはデフォルト ON となりました。 ↩