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

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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL の意図的な共有ロック、意図的な排他ロック、デッドロックを 1 つの記事で学習します。
  • Mysql ロック機構の行ロック、テーブルロック、デッドロックの実装
  • Ali インタビュー MySQL デッドロック問題の処理
  • RC レベルでの MySQL デッドロック問題の解決
  • MySQLでデッドロックログを出力する方法
  • MySQL のロック待機とデッドロック問題の分析
  • MySQLオンラインデッドロック分析練習
  • MySQL デッドロック問題の超詳細な説明

<<:  ウェブデザインと制作の一般的な原則をまとめる

>>:  XHTML タグのネスト規則の分析

推薦する

MySQL の詳細な単一テーブルの追加、削除、変更、クエリの CRUD ステートメント

MySQL の追加、削除、変更、クエリステートメント1. 練習シートを作成するここでの練習表は3つの...

MySQL例外に対する一般的な解決策をいくつか分析する

目次序文1. コードによって設定されたデータベース名またはパスワードがローカルデータベースと一致して...

チェックボックスとラジオボタンの配置を実装する方法

ブラウザによって動作が異なるだけでなく、フォントやテキスト サイズによっても動作が異なります。フォー...

NginxはGzipアルゴリズムを使用してメッセージを圧縮します

HTTP圧縮とは場合によっては、比較的大きなメッセージ データがクライアントとサーバー間で送信され、...

MySQL実行計画を学ぶ

目次1. 実施計画の概要2. 実行計画の実践id:選択タイプ:テーブル:タイプ:可能なキー:鍵:キー...

CSS は、小さな鋭角のチャット ダイアログ ボックスで鋭角の吹き出し効果を実現します。

1. CSS を使用して、小さな尖った角のチャット ダイアログ ボックスと尖った角の吹き出しを描画...

MYSQL(電話番号、IDカード)データ非感応化の実装

1. データ感度低下の説明日常の開発ニーズでは、データの感度低下が頻繁に発生します。たとえば、ID ...

MySQL Null は 5 つの問題を引き起こす可能性があります (すべて致命的)

目次1. カウントデータが失われる解決2. 明確なデータ損失3.データ損失を選択解決4. Nullポ...

Vueは指定された日付間のカウントダウンを実装します

この記事では、指定された日付間のカウントダウンを実装するためのVueの具体的なコードを例として紹介し...

WeChatアプレットが9マスグリッド効果を実現

この記事では、WeChatアプレットの9マスグリッド効果を実現するための具体的なコードを参考までに紹...

JSに関する7つの面接の質問、あなたはいくつ正しく答えられますか

序文JavaScript では、これは関数呼び出しコンテキストです。この動作が非常に複雑であるからこ...

MySQL 8.0.12 解凍版インストールチュートリアル個人テスト!

Mysql8.0.12 解凍版のインストール方法をテストしましたので、ご参考までに1. ダウンロー...

MySQL 8.0.12 簡単インストールチュートリアル

この記事では、MySQL 8.0.12のインストールチュートリアルを参考までに紹介します。具体的な内...

CenOS6.7 mysql 8.0.22 のインストールと設定方法のグラフィックチュートリアル

CenOS6.7 は MySQL8.0.22 (推奨コレクション) をインストールします1. MyS...

テキストまたはJSONを返すようにnginxを設定する方法

特定のインターフェースをリクエストするときに、指定されたテキスト文字列または JSON 文字列を返す...