Azure Database for MySQL - フレキシブルサーバーのレプリケーション待機時間のトラブルシューティング

[アーティクル]
11/27/2024

Note

この記事には、Microsoft が使用しなくなった "スレーブ" という用語への言及が含まれています。ソフトウェアからこの用語が削除された時点で、この記事から削除します。

読み取りレプリカ機能を使用すると、Azure Database for MySQL サーバーから、読み取り専用のレプリカサーバーにデータをレプリケートできます。読み取りおよびレポートクエリをアプリケーションから複数のレプリカサーバーにルーティングすることによって、ワークロードをスケールアウトできます。このセットアップにより、ソースサーバーの負荷が軽減され、アプリケーションのスケーリングに合わせて全体的なパフォーマンスと待機時間が改善されます。

レプリカは、MySQL エンジンのネイティブなバイナリログ (binlog) ファイルの位置に基づくレプリケーションテクノロジを使用して、非同期で更新されます。詳細については、MySQL の binlog ファイルの位置に基づくレプリケーションの構成の概要に関するページを参照してください。

セカンダリ読み取りレプリカでのレプリケーションのラグは、いくつかの要因に依存します。このような要因には以下のものが含まれますが、これらだけではありません。

ネットワーク待機時間。
ソースサーバーでのトランザクションの量。
ソースサーバーとセカンダリ読み取りレプリカサーバーのコンピューティングレベル。
ソースサーバーとセカンダリサーバーで実行されているクエリ。

この記事では、Azure Database for MySQL でレプリケーション待機時間のトラブルシューティングを行う方法について説明します。レプリカサーバーでレプリケーション待機時間が長くなるいくつかの一般的な原因についても理解を深めることができます。

レプリケーションの概念

バイナリログが有効になっている場合、コミットされたトランザクションはソースサーバーによってバイナリログに書き込まれます。バイナリログはレプリケーションに使用されます。それは、新しくプロビジョニングされるサーバーのうち、サポートされるストレージが 16 TB までのもののすべてにおいて既定で有効になります。各レプリカサーバーで 2 つのスレッドが実行されます。 1 つのスレッドは "IO スレッド" で、もう 1 つは "SQL スレッド" です。

IO スレッドにより、ソースサーバーへの接続と、更新されたバイナリログの要求が行われます。このスレッドは、バイナリログの更新を受け取ります。それらの更新は、レプリカサーバーの "リレーログ" と呼ばれるローカルログに保存されます。
SQL スレッドによって、リレーログが読み取られた後、レプリカサーバーにデータの変更が適用されます。

レプリケーション待機時間の監視

Azure Database for MySQL により、Azure Monitor でレプリケーションラグ (秒単位) のメトリックが提供されます。このメトリックは、読み取りレプリカサーバーでのみ使用できます。それは、MySQL で使用できる seconds_behind_master メトリックによって計算されます。

レプリケーション待機時間が増加する原因を理解するには、MySQL Workbench または Azure Cloud Shell を使用してレプリカサーバーに接続します。そして、次のコマンドを実行します。

Note

コード内で、例の値を実際のレプリカサーバー名と管理ユーザー名に置き換えます。管理者ユーザー名には、Azure Database for MySQL の @\<servername> が必要です。

mysql --host=myreplicademoserver.mysql.database.azure.com --user=myadmin@mydemoserver -p

Cloud Shell ターミナルでのエクスペリエンスは次のようになります。

Requesting a Cloud Shell.Succeeded.
Connecting terminal...

Welcome to Azure Cloud Shell

Type "az" to use Azure CLI
Type "help" to learn about Cloud Shell

user@Azure:~$mysql -h myreplicademoserver.mysql.database.azure.com -u myadmin@mydemoserver -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64796
Server version: 5.6.42.0 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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>

同じ Cloud Shell ターミナルで、次のコマンドを実行します。

mysql> SHOW SLAVE STATUS;

一般的な出力を次に示します。

出力には多数の情報が含まれています。通常は、次の表で説明する行だけに注目する必要があります。

メトリック	説明
Slave_IO_State	IO スレッドの現在の状態を表します。通常、ソース (マスター) サーバーが同期している場合、状態は "Waiting for master to send event" (マスターによるイベントの送信を待機中) です。 "Connecting to master" (マスターに接続中) などの状態は、レプリカがソースサーバーへの接続を失ったことを示します。ソースサーバーが実行されていることを確認するか、ファイアウォールによって接続がブロックされているかどうかを調べます。
Master_Log_File	ソースサーバーの書き込み先のバイナリログファイルを表します。
Read_Master_Log_Pos	ソースサーバーによって書き込みが行われているバイナリログファイル内の場所を示します。
Relay_Master_Log_File	レプリカサーバーによってソースサーバーから読み取りが行われているバイナリログファイルを表します。
Slave_IO_Running	IO スレッドが実行されているかどうかを示します。値は `Yes` である必要があります。値が `NO` の場合は、レプリケーションが中断されている可能性があります。
Slave_SQL_Running	SQL スレッドが実行されているかどうかを示します。値は `Yes` である必要があります。値が `NO` の場合は、レプリケーションが中断されている可能性があります。
Exec_Master_Log_Pos	レプリカによって適用されている Relay_Master_Log_File の位置を示します。待機時間がある場合、この位置シーケンスが Read_Master_Log_Pos より小さくなるはずです。
Relay_Log_Space	既存のすべてのリレーログファイルの合計サイズを示します。 `relay_log_space_limit` のように `SHOW GLOBAL VARIABLES` のクエリを実行することによって、上限サイズを確認できます。
Seconds_Behind_Master	レプリケーション待機時間を秒単位で表示します。
Last_IO_Errno	IO スレッドのエラーコードを表示します (存在する場合)。これらのコードの詳細については、MySQL サーバーのエラーメッセージリファレンスに関するページを参照してください。
Last_IO_Error	IO スレッドのエラーメッセージを表示します (存在する場合)。
Last_SQL_Errno	SQL スレッドのエラーコードを表示します (存在する場合)。これらのコードの詳細については、MySQL サーバーのエラーメッセージリファレンスに関するページを参照してください。
Last_SQL_Error	SQL スレッドのエラーメッセージを表示します (存在する場合)。
Slave_SQL_Running_State	現在の SQL スレッドの状態を示します。この状態では、`System lock` は正常です。また、`Waiting for dependent transaction to commit` という状態が表示される場合も正常です。この状態は、レプリカが、他の SQL ワーカースレッドでコミットされたトランザクションが更新されるのを待機していることを示します。

Slave_IO_Running が Yes で、Slave_SQL_Running が Yes の場合、レプリケーションは正常に実行されています。

次に、Last_IO_Errno、Last_IO_Error、Last_SQL_Errno、Last_SQL_Error を確認します。これらのフィールドには、SQL スレッドの停止の原因となった最新のエラーのエラー番号とエラーメッセージが表示されます。エラー番号が 0 でメッセージが空の場合は、エラーがないことを意味します。 MySQL サーバーのエラーメッセージリファレンスに関するページでエラーコードを調べることにより、0 以外のエラー値を調査します。

レプリケーション待機時間が長い場合の一般的なシナリオ

以下のセクションでは、レプリケーションの待機時間が長くなることがよくあるシナリオについて説明します。

ソースサーバーでのネットワーク待機時間または高い CPU 使用率

次のような値が表示される場合、レプリケーション待機時間の原因は、長いネットワーク待機時間、またはソースサーバーでの高い CPU 使用率である可能性があります。

Slave_IO_State: Waiting for master to send event
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller than Master_Log_File, e.g. mysql-bin.00010

この場合、IO スレッドは実行中であり、ソースサーバーを待機しています。ソースサーバーは既に、番号が 20 のバイナリログファイルに書き込んでいます。レプリカは、ファイル番号 10 までしか受信していません。このシナリオでレプリケーション待機時間が長くなる主な要因は、ネットワークの速度またはソースサーバーでの高い CPU 使用率です。

Azure においては、通常、リージョン内のネットワーク待機時間はミリ秒単位で測定されます。リージョン間の場合の待機時間の範囲は、ミリ秒から秒です。

ほとんどの場合、IO スレッドとソースサーバーの間の接続遅延の原因は、ソースサーバーでの高い CPU 使用率です。 IO スレッドの処理が遅くなっています。この問題は、Azure Monitor を使用して、ソースサーバーでの CPU の使用率とコンカレント接続の数を調べることにより検出できます。

ソースサーバーの CPU 使用率が高くない場合、問題はネットワーク待機時間である可能性があります。ネットワーク待機時間が突然異常に高くなった場合は、Azure の状態ページで既知の問題や停止を確認します。

ソースサーバーでのトランザクションの大量バースト

次のような値が表示される場合、レプリケーション待機時間の原因として可能性が高いのは、ソースサーバーでのトランザクションの大量バーストです。

Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller then Master_Log_File, e.g. mysql-bin.00010

レプリカはソースサーバーより遅れてバイナリログを取得できることが出力で示されています。しかし、レプリカの IO スレッドは、リレーログ領域が既にいっぱいであることを示しています。

ネットワークの速度は遅延の原因ではありません。レプリカは追い付こうとしています。しかし、更新されたバイナリログのサイズがリレーログ領域の上限を超えています。

この問題のトラブルシューティングを行うには、ソースサーバーで低速クエリログを有効にします。低速クエリログを使用して、ソースサーバーで長い時間実行されているトランザクションを特定します。その後、特定されたクエリを調整して、サーバーでの待機時間を短縮します。

この種のレプリケーション待機時間は、通常、ソースサーバーでのデータの読み込みが原因で発生します。ソースサーバーで週単位または月単位のデータの読み込みがある場合、レプリケーション待機時間は避けられません。ソースサーバーでデータの読み込みが完了した後、レプリカサーバーは最終的に追い付きます。

レプリカサーバーのパフォーマンスの低下

次のような値が表示される場合は、レプリカサーバーで問題が発生している可能性があります。

Slave_IO_State: Waiting for master to send event
Master_Log_File: The binary log file sequence equals to Relay_Master_Log_File, e.g. mysql-bin.000191
Read_Master_Log_Pos: The position of master server written to the above file is larger than Relay_Log_Pos, e.g. 103978138
Relay_Master_Log_File: mysql-bin.000191
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: The position of slave reads from master binary log file is smaller than Read_Master_Log_Pos, e.g. 13468882
Seconds_Behind_Master: There is latency and the value here is greater than 0

このシナリオでは、IO スレッドと SQL スレッドの両方が正常に実行されていることが出力で示されます。ソースサーバーで書き込まれているのと同じバイナリログファイルが、レプリカによって読み取られています。ただし、レプリカサーバーでの多少の待機時間は、ソースサーバーからの同じトランザクションを反映しています。

以下のセクションでは、この種の待機時間の一般的な原因について説明します。

テーブルに主キーまたは一意キーがない

Azure Database for MySQL では行ベースのレプリケーションが使用されます。ソースサーバーにより、バイナリログにイベントが書き込まれて、個々のテーブル行での変更が記録されます。その後、SQL スレッドにより、それらの変更が、レプリカサーバー上の対応するテーブル行にレプリケートされます。テーブルに主キーまたは一意キーがない場合、変更を適用するために、SQL スレッドによってターゲットテーブルのすべての行がスキャンされます。このスキャンにより、レプリケーション待機時間が発生する可能性があります。

MySQL の主キーは、NULL 値を含むことができないために高速のクエリパフォーマンスが保証される、関連付けられたインデックスです。 InnoDB ストレージエンジンを使用している場合、テーブルデータは、主キーに基づいて超高速な検索と並べ替えを行えるよう物理的に編成されます。

レプリカサーバーを作成する前に、ソースサーバーのテーブルに主キーを追加することをお勧めします。ソースサーバーに主キーを追加した後、レプリケーション待機時間が向上するように、読み取りレプリカを再作成します。

ソースサーバーで主キーがないテーブルを調べるには、次のクエリを使用します。

select tab.table_schema as database_name, tab.table_name 
from information_schema.tables tab left join 
information_schema.table_constraints tco 
on tab.table_schema = tco.table_schema 
and tab.table_name = tco.table_name 
and tco.constraint_type = 'PRIMARY KEY' 
where tco.constraint_type is null 
and tab.table_schema not in('mysql', 'information_schema', 'performance_schema', 'sys') 
and tab.table_type = 'BASE TABLE' 
order by tab.table_schema, tab.table_name;

レプリカサーバーでの実行時間の長いクエリ

レプリカサーバーでのワークロードにより、SQL スレッドが IO スレッドより遅れることがあります。レプリカサーバー上の実行時間の長いクエリは、レプリケーション待機時間が長くなる一般的な原因の 1 つです。この問題のトラブルシューティングを行うには、レプリカサーバーで低速クエリログを有効にします。

低速クエリにより、リソースの消費が増加したり、サーバーの速度が低下することがあり、そのためにレプリカはソースサーバーに追いつけなくなります。このシナリオでは、低速のクエリを調整します。クエリを高速化すると、SQL スレッドをブロックするものがなくなり、レプリケーション待機時間が大幅に短縮されます。

ソースサーバーでの DDL クエリ

ソースサーバーで ALTER TABLE のようなデータ定義言語 (DDL) コマンドを実行すると、時間がかかる場合があります。 DDL コマンドの実行中に、他の何千ものクエリがソースサーバーで並列に実行される可能性があります。

DDL がレプリケートされるときは、データベースの整合性を維持するため、MySQL エンジンによって単一のレプリケーションスレッドで DDL が実行されます。このタスクが実行されている間、他のすべてのレプリケートされたクエリはブロックされ、レプリカサーバーで DDL 操作が完了するまで待機する必要があります。オンライン DDL 操作でも、この遅延が発生します。 DDL 操作により、レプリケーション待機時間が増加します。

ソースサーバーで低速クエリログを有効にした場合、ソースサーバーで実行された DDL コマンドを調べることで、この待機時間の問題を検出できます。インデックスの削除、名前変更、作成を行うときは、ALTER TABLE に INPLACE アルゴリズムを使用できます。テーブルのデータをコピーし、テーブルを再構築することが、必要になる場合があります。

通常、INPLACE アルゴリズムに対してはコンカレント DML がサポートされています。ただし、操作を準備して実行するときに、テーブルに対する排他的なメタデータロックを簡単に取得できます。したがって、CREATE INDEX ステートメントの場合、ALGORITHM 句と LOCK 句を使用して、テーブルをコピーする方法と、読み取りと書き込みのコンカレンシーのレベルに、影響を与えることができます。その場合でも、FULLTEXT インデックスまたは SPATIAL インデックスを追加することにより、DML 操作を防ぐことができます。

次の例では、ALGORITHM 句と LOCK 句を使用してインデックスを作成します。

ALTER TABLE table_name ADD INDEX index_name (column), ALGORITHM=INPLACE, LOCK=NONE;

残念ながら、ロックを必要とする DDL ステートメントの場合、レプリケーション待機時間を回避することはできません。可能性のある影響を軽くするには、夜間など、ピーク以外の時間帯にこれらの種類の DDL 操作を実行します。

ダウングレードされたレプリカサーバー

Azure Database for MySQL の場合、ソースサーバーと同じサーバー構成が読み取りレプリカで使用されます。作成された後で、レプリカサーバーの構成を変更できます。

レプリカサーバーをダウングレードすると、ワークロードによるリソースの消費量が増え、その結果、レプリケーション待機時間が発生する可能性があります。この問題を検出するには、Azure Monitor を使用して、レプリカサーバーの CPU とメモリの消費量を調べます。

このシナリオの場合、レプリカサーバーの構成を、ソースサーバーの値以上の値に維持することをお勧めします。このように構成すると、レプリカはソースサーバーより遅れることがなくなります。

ソースサーバーのパラメーターの調整によるレプリケーション待機時間の改善

Azure Database for MySQL の場合、レプリケーションは既定で、レプリカ上の並列スレッドで実行されるように最適化されます。ソースサーバーでの高コンカレンシーワークロードのためにレプリカサーバーが遅れる場合、ソースサーバーでパラメーター binlog_group_commit_sync_delay を構成することにより、レプリケーション待機時間を改善できます。

binlog_group_commit_sync_delay パラメーターにより、バイナリログファイルを同期する前にバイナリログコミットが待機するマイクロ秒数が制御されます。このパラメーターの利点は、トランザクションがコミットされるたびに直ちに適用されるのではなく、ソースサーバーからバイナリログの更新が一括送信されることです。この遅延により、レプリカでの IO が減少し、パフォーマンスを向上させることができます。

binlog_group_commit_sync_delay パラメーターを 1000 程度に設定すると効果がある場合があります。その後、レプリケーション待機時間を監視します。このパラメーターを設定するときは慎重に行い、高コンカレンシーワークロードに対してのみ使用します。

重要

レプリカサーバーでは、binlog_group_commit_sync_delay パラメーターを 0 にすることをお勧めします。これが推奨されるのは、ソースサーバーとは異なり、レプリカサーバーの同時実行性が高くないためであり、レプリカサーバーで binlog_group_commit_sync_delay の値を増やすと、レプリケーションのラグが誤って増加する可能性があります。

多数のシングルトントランザクションが含まれる低コンカレンシーのワークロードの場合は、binlog_group_commit_sync_delay を設定すると待機時間が長くなる可能性があります。コミットされるトランザクションが少ない場合でも、一括バイナリログ更新に対する IO スレッドの待機により、待機時間が増加する可能性があります。

高度なトラブルシューティングオプション

show slave status コマンドを使用してもレプリケーション待機時間のトラブルシューティングに十分な情報が提供されない場合は、アクティブまたは待機中のプロセスについて理解するために次のようなその他のオプションの表示を試してみてください。

スレッドテーブルを表示する

performance_schema.threads テーブルはプロセスの状態を示します。 "lock_type ロックを待機中" という状態になっているプロセスは、いずれかのテーブルにロックがあることを示しており、レプリケーションスレッドがテーブルを更新することはできません。

SELECT name, processlist_state, processlist_time FROM performance_schema.threads WHERE name LIKE '%slave%';

詳細については、「一般的なスレッドの状態」をご覧ください。

replication_connection_status テーブルを表示する

performance_schema.replication_connection_status テーブルには、ソースへのレプリカの接続を処理するレプリケーション I/O スレッドの現在の状態が表示され、頻繁に変更されます。このテーブルには、接続中に変化する値が含まれています。

SELECT * FROM performance_schema.replication_connection_status;

replication_applier_status_by_worker テーブルを表示する

performance_schema.replication_applier_status_by_worker テーブルには、ワーカースレッドの状態、最後に表示されたトランザクション、および最後のエラー番号とメッセージが表示されます。これは、問題が発生しているトランザクションを見つけ、根本原因を特定するのに役立ちます。

データインレプリケーションで次のコマンドを実行して、エラーまたはトランザクションをスキップできます。

az_replication_skip_counter

または

az_replication_skip_gtid_transaction

SELECT * FROM performance_schema.replication_applier_status_by_worker;

SHOW RELAYLOG EVENTS ステートメントを表示する

show relaylog events ステートメントは、レプリカのリレーログ内のイベントを示します。

· GITD ベースのレプリケーション (読み取りレプリカ) の場合、ステートメントには GTID トランザクションおよび binlog ファイルとその位置が表示されます。mysqlbinlog を使用して、実行中の内容とステートメントを取得できます。 · MySQL binlog 位置レプリケーション (データインレプリケーションに使用) の場合、実行されているステートメントが表示されます。これは、実行されているテーブルトランザクションを把握するのに役立ちます

InnoDB 標準モニターとロックモニターの出力を確認する

InnoDB 標準モニターとロックモニターの出力を確認してみることもできます。これは、ロックとデッドロックを解決し、レプリケーションのラグを最小限に抑えるのに役立ちます。ロックモニターは、追加のロック情報が含まれている以外は、標準モニターと同じです。そのロックとデッドロックに関する追加情報を表示するには、show engine innodb status\G コマンドを実行します。

次の手順

MySQL binlog レプリケーションの概要を確認します。

次の方法で共有

Azure Database for MySQL - フレキシブルサーバーのレプリケーション待機時間のトラブルシューティング

レプリケーションの概念

レプリケーション待機時間の監視

レプリケーション待機時間が長い場合の一般的なシナリオ

ソースサーバーでのネットワーク待機時間または高い CPU 使用率

ソースサーバーでのトランザクションの大量バースト

レプリカサーバーのパフォーマンスの低下

テーブルに主キーまたは一意キーがない

レプリカサーバーでの実行時間の長いクエリ

ソースサーバーでの DDL クエリ

ダウングレードされたレプリカサーバー

ソースサーバーのパラメーターの調整によるレプリケーション待機時間の改善

高度なトラブルシューティングオプション

スレッドテーブルを表示する

replication_connection_status テーブルを表示する

replication_applier_status_by_worker テーブルを表示する

SHOW RELAYLOG EVENTS ステートメントを表示する

InnoDB 標準モニターとロックモニターの出力を確認する

次の手順

フィードバック

その他のリソース

次の方法で共有

Azure Database for MySQL - フレキシブル サーバーのレプリケーション待機時間のトラブルシューティング

レプリケーションの概念

レプリケーション待機時間の監視

レプリケーション待機時間が長い場合の一般的なシナリオ

ソース サーバーでのネットワーク待機時間または高い CPU 使用率

ソース サーバーでのトランザクションの大量バースト

レプリカ サーバーのパフォーマンスの低下

テーブルに主キーまたは一意キーがない

レプリカ サーバーでの実行時間の長いクエリ

ソース サーバーでの DDL クエリ

ダウングレードされたレプリカ サーバー

ソース サーバーのパラメーターの調整によるレプリケーション待機時間の改善

高度なトラブルシューティング オプション

スレッド テーブルを表示する

replication_connection_status テーブルを表示する

replication_applier_status_by_worker テーブルを表示する

SHOW RELAYLOG EVENTS ステートメントを表示する

InnoDB 標準モニターとロック モニターの出力を確認する

次の手順

フィードバック

その他のリソース

Azure Database for MySQL - フレキシブルサーバーのレプリケーション待機時間のトラブルシューティング

ソースサーバーでのネットワーク待機時間または高い CPU 使用率

ソースサーバーでのトランザクションの大量バースト

レプリカサーバーのパフォーマンスの低下

レプリカサーバーでの実行時間の長いクエリ

ソースサーバーでの DDL クエリ

ダウングレードされたレプリカサーバー

ソースサーバーのパラメーターの調整によるレプリケーション待機時間の改善

高度なトラブルシューティングオプション

スレッドテーブルを表示する

InnoDB 標準モニターとロックモニターの出力を確認する