MySQL のデッドロックとデータベースおよびテーブル シャーディングの問題の詳細な説明

MySQL 運用上の問題点を記録します。

ビジネスシナリオと問題の説明

外部インターフェースをリクエストする場合、1日あたりのリクエスト数は約900万件です。

依頼項目と受領項目の2つに分かれています。リクエストは外部インターフェースを呼び出すために使用され、受信は受信と送信のためのインターフェースです。

リクエストを送信する前にデータがデータベースに挿入されます。

リクエスト後、インターフェースが呼び出し失敗を返すと、データベースのステータスは失敗に更新されます。

送信が成功した場合、アップストリームが受信メッセージを送信するまで待機し、データベースのステータスを更新します。

本番運用中、MySQL に起因する MQ コンシューマー蓄積の問題が半年間で 2 回発生しました。

問題分析

異なる理由によって発生した 2 つの製造上の問題を記録し、原因を分析します。

MySQL デッドロック問題

MQ集約プラットフォームのTPSを表示する
実稼働環境では、MQ データが蓄積され、増加していることがわかります。しかし、TPSは30程度で、上がっていません。

これにより、MQ の消費が遅くなり、継続的な蓄積が発生します。 MQ が継続的に蓄積される具体的な理由については、さらに調査する必要があります。

運用サーバーのログを表示する

本番サーバーのログを確認すると、デッドロック エラーが見つかりました。

MySQLConnection からのエラー応答 [node=24、id=277499、threadId=2735941、state=borrowed、closed=false、autocommit=true、host=10.1.10.74、port=3306、database=sep_4、localPort=27744、isClose:false、toBeClose:false、MySQLVersion:5.7.25]、err: ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。コード: 1213

具体的なSQLは次のとおりです。

更新ステージで、status = 'success'、reply_time = '2021-03-07 10:40:11'、code = '000123'、create_time > '2021-03-03 00:00:00' を設定します。

つまり、サービスの実行時にデッドロックが発生しました。

実稼働サーバー上でエントリの正確な数と所要時間を確認するのは直感的ではないため、DBA に遅い SQL ステートメントと所要時間を調べるように依頼しました。

確認したところ、最も遅い SQL は 7780 ミリ秒もかかっていることがわかりました。

よく見ると、SQL は同じ ID が実行中であり、もう一方はロック待機状態にあることがわかります。

そして、この低速の SQL には、ロック待機状態が多数存在します。

デッドロックの原因は何ですか?

mysql で使用されるデータベース エンジンは InnoDB です。まずデッドロックとは何かを理解しましょう。

デッドロック: 2 つ以上のプロセスが実行中であることを意味します。
資源を奪い合うことで起こる、お互いを待つ現象。外部からの力がなければ、前に進めない。
このとき、システムはデッドロック状態にある、またはシステムがデッドロックを起こしていると言われ、常にお互いを待ち合わせているこれらのプロセスをデッドロックプロセスと呼びます。

上記の調査から、デッドロックの問題は次のようになります。

行データを更新するSQLを実行すると、行データがロックされます。実行が完了すると行ロックが解除され、実行されていないSQLはロック待機状態になります。

プログラム上、送信前後や受信時にデータベースの操作が頻繁に行われ、同じデータが同時に操作される可能性があるためです。

そのため、実行中にロック待機が発生します。

シャーディングキーはシャーディングテーブルに含まれていません

最初の警告は、stage_prod ライブラリの CPU が 85% に急上昇したというものです。

データベーススレッドの数はいっぱいですか?

データベース接続ステータスを確認すると、データベース接続数がいっぱいではないことがわかります。

遅いSQLと時間の消費を検出する

検出された問題SQL:

更新ステージで、status = 'success'、reply_time = '2021-03-07 10:40:11' に設定し、create_time > '2021-03-03 00:00:00' にします。

SQL を確認すると、この SQL にはシャード キー コード フィールドが含まれていないことがわかります。この SQL ステートメントは、領収書を受け取ったときに実行されます。

本番サーバーのログを確認する

コードには判断があります。コード値が空でない場合、SQL はコード値を持ちます。持参しなかった場合は、なぜ持参しなかったのか確認する必要があります。

コードを見ると、コードは redis から取得され、送信時に redis に設定されていることがわかります。でも、設定されていないのはおかしいですね。

当初は Redis の問題ではないかと疑っていましたが、Redis メンテナンス プラットフォームとやり取りした結果、確かに Redis の障害が原因の問題であることがわかりました。

シャードキーがないと CPU が急上昇するのはなぜですか?

まず、同社では1日の入力量が約900万件、1つのテーブルに数億件のデータが含まれているため、hotdbを使用してデータベースとテーブルを分割しています。

インデックスだけを使用すると、要件を満たせません。

hotdb データベースとテーブルを複数の部分に分割し、コード値に基づいてハッシュ シャーディングを実行し、合計 64 個のシャードを作成しました。つまり、64 個のデータベースが 8 台のサーバー上の 16 個のインスタンスに分散されます。

これにより、シャード間でのデータの不均一な分散を回避でき、理論的には特定のシャードへの過度の集中を回避できます。

SQL にシャード キー コードが含まれていない場合、すべての DML 操作は実行のためにすべての基礎ライブラリに送信され、これはライブラリを 1 回走査することと同じです。

これにより、CPU が 99% まで急上昇したり、サーバーがクラッシュしたりする可能性があり、非常に恐ろしいです。

解決

緊急処置: データベース操作を減らすために、まずいくつかのサービスを停止します

データが継続的に蓄積されると、データ処理速度に影響します。次に、まず操作の速度を下げる必要があります。最も速い方法は、サービスを停止し、データベース操作の頻度を減らすことです。

データベースのデッドロックを回避するためにデータベース操作を減らす

デッドロックは通常、DML 操作の送信がプログラムによって適切に制御されず、時間内に送信されないために発生します。

同じデータに対する繰り返し操作を減らします。バッチ操作中に各バッチ内の DML の数を減らして、送信を高速化し、長いトランザクションを回避し、DML の繰り返し送信を回避します。

では、どうやって操作を減らすのでしょうか?

マージSQL

送信前の挿入と送信失敗時の更新を 1 つの SQL ステートメントにマージし、同じデータに対する複数の操作を回避します。

バッチ実行中の長いトランザクションとエントリ数を削減

実行中に、一度に 200 個の SQL ステートメントを実行するよりも、毎回 20 個の SQL ステートメントをバッチで実行する方が効率的であることがわかりました。

したがって、この問題をできるだけ回避するようにしてください。

各 SQL ステートメントには、各データベースまたはテーブルのシャーディング キーが必要です。

1 つのデータによってデータベース全体の動作速度が低下しないようにすることが原則です。

シャード キーを含める必要があります。含まれていない場合は、エラーがスローされます。

開閉間隔の時間間隔を長くする

コードをシャード キーとして使用し、createTime をパーティションとして使用します。そして、コードの存在を保証しながら、開始間隔と終了間隔を記述して実行効率を向上させることができます。

より良い解決策: SQL の順次実行

このソリューションにより、実行される SQL ステートメントが順番に実行されるようになり、デッドロックを回避できます。

ただし、これはビジネス シナリオに応じて区別する必要があります。

レビュー

MySQL のデッドロック問題を解決するには、同じデータに対する頻繁な操作を避け、長いトランザクションを避けてください。
シャーディングの問題については、シャーディング キーを必ず含めてください。
監視メカニズムは不可欠です。

要約する

これで、MySQL のデッドロックとシャーディングに関するこの記事は終了です。MySQL のデッドロックとシャーディングの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。