Mysql の読み取り/書き込み分離期限切れに対する一般的な解決策

MySQLの読み書き分離の落とし穴

読み取りと書き込みの分離の主な目的は、メイン データベースの負荷を分散することであり、クライアントはクエリ用にバックエンド データベースを選択します。もう 1 つのアーキテクチャは、MYSQL とクライアントの間に中間プロキシ層が存在するというものです。クライアントはプロキシに接続し、プロキシはリクエストの種類とコンテキストに基づいてリクエストの配布ルートを決定します。

• クライアント直接接続ソリューション: プロキシ転送のレイヤーが 1 つ少なくなるため、クエリのパフォーマンスがわずかに向上し、全体的なアーキテクチャがシンプルになり、問題のトラブルシューティングが容易になります。ただし、このソリューションではバックエンドのデプロイメントの詳細を理解する必要があるため、マスターとスレーブの切り替えやデータベースの移行などの操作が発生すると、クライアントはそれを認識し、データベース接続情報を調整する必要があります。
• プロキシ アーキテクチャを使用すると、クライアントにとってより使いやすくなります。クライアントはバックエンドの詳細に注意を払う必要はありません。接続の維持、バックエンド情報の維持、その他のタスクはすべてプロキシによって完了されます。しかし、この場合、バックエンドメンテナンスチームに対する要件は高くなります。
  

使用されるアーキテクチャに関係なく、マスターとスレーブ間の遅延が発生する可能性があるため、クライアントが更新トランザクションの実行直後にクエリを開始し、クエリがスレーブを選択した場合、トランザクションの更新前のステータスを読み取ることができます。この「スレーブ ライブラリ上でシステムの期限切れ状態を読み取る」という現象を、ここでは「期限切れ読み取り」と呼ぶことにします。

解決策1: メインデータベースソリューションを強制する

クエリ要求は次の 2 つのカテゴリに分けられます。

• 最新の結果を取得する必要があるリクエストは、強制的にメイン データベースに送信されます。たとえば、取引プラットフォームでは、販売者が商品を公開した後、すぐにメインページに戻って商品が正常に公開されたかどうかを確認する必要があります。次に、このリクエストで最新の結果を取得する必要がある場合は、メイン データベースを通過する必要があります。
• 古いデータを読み取ることができる要求のみがスレーブ データベースに送信されます。この取引プラットフォームでは、購入者がストアページを閲覧する際に、最新の商品が数秒遅れて表示されても問題ありません。その後、そのようなリクエストはライブラリを経由できるようになります。このソリューションの最大の問題は、金融サービスなど、すべてのクエリが「期限切れの読み取り」ではないという要件に遭遇する可能性があることです。この場合、読み取りと書き込みの分離を放棄する必要があり、すべての負荷がメイン データベースにかかります。以下のスキームが採用されました。

解決策2: 睡眠ソリューション

マスターデータベースが更新された後、select sleep (1) コマンドを実行するのと同様に、スレーブデータベースを読み取る前にスリープが実行されます。このソリューションの前提は、ほとんどの場合、マスタースレーブの遅延は 1 秒以内であり、スリープによって最新のデータを取得できる可能性が非常に高いということです。

販売者が商品を公開する例を挙げると、商品が公開された後、実際にデータベースを照会するのではなく、Ajax を使用して、クライアントが入力したコンテンツを「新商品」としてページに直接表示します。このように、販売者はこの表示を使用して、製品が正常にリリースされたことを確認できます。販売者がページを更新して商品を確認するまでに、実際にいくらかの時間が経過しているため、スリープの目的が達成され、期限切れの読書の問題が解決されます。

解決策 3: マスターとバックアップに遅延がないかどうかを確認します。

最初の方法: まず、show slave status 結果の seconds_behind_master パラメータの値を使用して、マスターとスレーブ間の遅延の長さを測定します。まず、パラメータ値が 0 かどうかを判断します。0 でない場合は、リクエストを実行する前にパラメータが 0 になるまで待つ必要があります。

2 番目の方法: 比較サイトは、プライマリとバックアップに遅延がないことを確認します。

• Master_Log_File と Read_Master_Log_Pos は、マスター データベースの最新の読み取り位置を示します。
• Relay_Master_Log_File と Exec_Master_Log_Pos は、スタンバイ データベースの最新の実行位置を示します。

Master_Log_File、Relay_Master_Log_File、Read_Master_Log_Pos、Exec_Master_Log_Posの値が全く同じであれば、受信したログが同期されていることを意味します。

3番目の方法: GTID (グローバルトランザクションID)を比較して、マスターとスレーブ間の遅延がないことを確認する

• Auto_Position=1 は、このマスター/スレーブ関係に GTID プロトコルが使用されることを意味します。
• Retrieved_Gtid_Set は、スレーブ データベースによって受信されたすべてのログの GTID セットです。
• Executed_Gtid_Set は、スタンバイ データベースで実行されたすべての GTID のセットです。

2 つのセットが同じである場合、スタンバイ データベースによって受信されたログが同期されていることを意味します。

スキーム4: 均等マスターライブラリサイトスキーム

select master_pos_wait(file, pos[, timeout]);

このコマンドはスレーブ データベースで実行されます。パラメータ file と pos は、マスター データベース上のファイル名と位置を指します。タイムアウトは、この関数が最大 N 秒間待機することを意味します。

• このコマンドによって返される通常の結果は正の整数 M です。これは、コマンドの実行開始から、file と pos によって示される binlog 位置の適用が完了するまでに実行されたトランザクションの数を示します。
• スタンバイ データベース同期スレッドで例外が発生した場合は、null が返されます。
• 待機時間がN秒を超える場合は-1を返す
• 実行開始時に実行されたことが判明した場合は0を返す

図に示すように、まずtrx1を実行し、次にクエリ要求のロジックを実行します。正しいデータが見つかるかどうかを確認するには、

この論理

1. trx1 トランザクションが更新されたら、すぐに show master status を実行して、マスター データベースによって現在実行されているファイルと位置を取得します。

2. クエリ ステートメントを実行するスレーブ データベースを選択します。

3. スレーブ データベースで select master_pos_wait(File, Position, 1) を実行します。

4. 戻り値が正の整数 >= 0 の場合、このスレーブ データベースでクエリ ステートメントを実行します。

5. それ以外の場合は、メイン データベースに移動してクエリ ステートメントを実行します。

ここでは、この選択クエリがスレーブ データベースで最大 1 秒間待機すると想定します。次に、master_pos_wait が 1 秒以内に 0 以上の整数を返す場合、スレーブ データベースで実行されたクエリ結果に trx1 のデータが含まれていることが保証されます。

5 メイン データベースでクエリ ステートメントを実行することは、このタイプのソリューションの一般的な劣化メカニズムです。スレーブ データベースの遅延時間は制御不能であり、無期限に待機することはできないため、待機がタイムアウトした場合は、諦めてマスター データベースを確認する必要があります。期限切れの読み取りを許可しないという要件によると、オプションは 2 つしかありません。1 つはタイムアウト後に諦めること、もう 1 つはクエリのためにメイン データベースに切り替えることです。

同時接続と同時クエリ

innodb_thread_concurrency パラメータは、Innodb の同時スレッドの上限を制御します。この値を超えると、新しいリクエストは保留になります。

• show processlist に表示される数千の接続は同時接続ですが、現在実行されているステートメントは同時クエリです。同時接続はほとんど影響がなく、メモリを多く消費するだけですが、同時クエリは CPU キラーになります。
• スレッドがロック待機状態に入ると、同時実行スレッド数は減少します。つまり、行ロックを待機しているスレッドは同時実行クエリにカウントされません。待機してもCPUを消費しなくなるため

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。