MySQL 5.7 でブロックポジショニング DDL の問題を解決する

MySQL 5.7 でブロックポジショニング DDL の問題を解決する

前回の記事「MySQL テーブル構造の変更、メタデータ ロックを知っておく必要があります」では、MDL 導入の背景と基本概念を紹介し、「道」レベルから MDL とは何かを学びました。次に、「技術的な」観点から MDL 関連の問題を特定する方法を見てみましょう。

MySQL 5.7 では、MDL に新しいテーブル performance_schema.metadata_locks が導入されました。このテーブルには、オブジェクト、タイプ、保留待機ステータスなど、MDL の関連情報を表示できます。

MDLインストルメンテーションを有効にする

ただし、関連するインストルメントが有効になっていません (MySQL 8.0 はデフォルトで有効になっています)。有効にするには、次の 2 つの方法があります。

一時的な効果

performance_schema.setup_instrume nts表変更されますが、インスタンスを再起動するとデフォルト値に復元されます。

performance_schema.setup_instruments を更新し、ENABLED = 'YES'、TIMED = 'YES' に設定します。
WHERE NAME = 'wait/lock/metadata/sql/mdl';

永続

設定ファイルで設定

[mysqld]
パフォーマンス スキーマ インストルメント = 'wait/lock/metadata/sql/mdl=ON'

テストシナリオ

以下は、MySQL 5.7 で DDL 操作のブロッキング問題を特定する方法を示した簡単なデモです。

セッション1> 開始;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
session1> slowtech.t1 から id=2 を削除します。
クエリは正常、1 行が影響を受けました (0.00 秒)
session1> slowtech.t1 から * を選択します。
+------+------+
| ID | 名前 |
+------+------+
| 1 | へ |
+------+------+
セット内の 1 行 (0.00 秒)
session1> slowtech.t1 を更新し、name='c' を設定し、id=1 とします。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0
session2> alter table slowtech.t1 add c1 int; ##ブロックされました session3> show processlist;
+----+------+-----------+--------+-------+-------+---------------------------------+------------------------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+----+------+-----------+--------+-------+-------+---------------------------------+------------------------------------+
| 2 | ルート | ローカルホスト | NULL | スリープ | 51 | | NULL |
| 3 | root | localhost | NULL | クエリ | 0 | 開始 | プロセスリストを表示 |
| 4 | root | localhost | NULL | クエリ | 9 | テーブル メタデータ ロックを待機中 | alter table slowtech.t1 add c1 int |
+----+------+-----------+--------+-------+-------+---------------------------------+------------------------------------+
セット内の 3 行 (0.00 秒)
セッション3> performance_schema.metadata_locks から object_type、object_schema、object_name、lock_type、lock_duration、lock_status、owner_thread_id を選択します。
+-------------+--------------------+---------------------+---------------+------------+----------------+
| オブジェクト タイプ | オブジェクト スキーマ | オブジェクト名 | ロック タイプ | ロック期間 | ロック ステータス | 所有者スレッド ID |
+-------------+--------------------+---------------------+---------------+------------+----------------+
| テーブル | slowtech | t1 | SHARED_WRITE | トランザクション | 許可 | 27 |
| グローバル | NULL | NULL | INTENTION_EXCLUSIVE | ステートメント | 許可 | 29 |
| スキーマ | slowtech | NULL | INTENTION_EXCLUSIVE | トランザクション | 許可 | 29 |
| テーブル | slowtech | t1 | SHARED_UPGRADABLE | トランザクション | 許可 | 29 |
| テーブル | slowtech | t1 | 排他 | トランザクション | 保留中 | 29 |
| テーブル | パフォーマンス スキーマ | メタデータ ロック | 共有読み取り | トランザクション | 許可 | 28 |
+-------------+--------------------+---------------------+---------------+------------+----------------+
セット内の 6 行 (0.00 秒)

ここで、lock_status に注目してください。「PENDING」はスレッドが MDL を待機していることを意味し、「GRANTED」はスレッドが MDL を保持していることを意味します。

ブロックの原因となっているセッションを見つける方法

owner_thread_id と組み合わせると、スレッド 29 がスレッド 27 の MDL を待機していることがわかります。この時点で、スレッド 52 は強制終了できます。

ただし、owner_thread_id はスレッド ID のみを提供し、show processlist の ID は提供しないことに注意してください。スレッドに対応するプロセスリスト ID を検索する場合は、performance_schema.threads テーブルをクエリする必要があります。

session3> performance_schema.threads から * を選択 where thread_id in (27,29)\G
************************** 1. 行 ****************************
   スレッドID: 27
    名前: スレッド/sql/one_connection
    タイプ: フォアグラウンド
  プロセスリストID: 2
 PROCESSLIST_USER: ルート
 PROCESSLIST_HOST: ローカルホスト
  PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: スリープ
 プロセスリスト_時間: 214
 プロセスリスト状態: NULL
 PROCESSLIST_INFO: NULL
 親スレッドID: 1
    役割: NULL
  計測済み: はい
   履歴: はい
 CONNECTION_TYPE: ソケット
  スレッド_OS_ID: 9800
************************** 2. 行 ****************************
   スレッドID: 29
    名前: スレッド/sql/one_connection
    タイプ: フォアグラウンド
  プロセスリストID: 4
 PROCESSLIST_USER: ルート
 PROCESSLIST_HOST: ローカルホスト
  PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: クエリ
 プロセスリスト_時間: 172
 PROCESSLIST_STATE: テーブルメタデータのロックを待機しています
 PROCESSLIST_INFO: テーブル slowtech.t1 を変更し、c1 int を追加します
 親スレッドID: 1
    役割: NULL
  計測済み: はい
   履歴: はい
 CONNECTION_TYPE: ソケット
  スレッド_OS_ID: 9907
セット内の 2 行 (0.00 秒)

これら 2 つのテーブルを組み合わせて sys.innodb_lock_waits の出力を参照することで、MDL の待機関係を実際に直感的に表すことができます。

選択
 a.OBJECT_SCHEMA AS ロックされたスキーマ、
 a.OBJECT_NAME AS ロックテーブル、
 「メタデータ ロック」AS locked_type、
 c.PROCESSLIST_ID は、waiting_processlist_id として、
 c.PROCESSLIST_TIME は待機時間として、
 c.PROCESSLIST_INFO AS 待機クエリ、
 c.PROCESSLIST_STATE AS 待機状態、
 d.PROCESSLIST_ID AS ブロッキングプロセスリストID、
 d.PROCESSLIST_TIME AS ブロッキング年齢、
 d.PROCESSLIST_INFO AS ブロッキングクエリ、
 concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
から
 performance_schema.metadata_locks a
performance_schema.metadata_locks b を a.OBJECT_SCHEMA = b.OBJECT_SCHEMA に結合します。
かつ、a.OBJECT_NAME = b.OBJECT_NAME
かつ a.lock_status = 'PENDING'
b.lock_status = 'GRANTED' かつ
かつ、a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
かつ、a.lock_type = 'EXCLUSIVE'
performance_schema.threads c を a.OWNER_THREAD_ID = c.THREAD_ID に結合します。
performance_schema.threads d を b.OWNER_THREAD_ID = d.THREAD_ID\G に結合します。

************************** 1. 行 ****************************
    ロックされたスキーマ: slowtech
    ロックされたテーブル: t1
     ロックタイプ: メタデータロック
  待機プロセスリスト ID: 4
     待機年齢: 259
    待機クエリ: テーブル slowtech.t1 を変更し、c1 int を追加します
    waiting_state: テーブルメタデータのロックを待機中
  ブロッキングプロセスリストID: 2
    ブロッキング年齢: 301
    ブロッキングクエリ: NULL
sql_kill_blocking_connection: 2 を強制終了する
セット内の 1 行 (0.00 秒)

出力は一目でわかります。DDL 操作の MDL を取得する場合は、kill 2 を実行します。

公式 sys.schematablelock_waits

実際、MySQL 5.7 でも sys ライブラリに同様の機能が統合されています。同じシナリオでは、出力は次のようになります。

mysql> sys.schema_table_lock_waits\G から * を選択します
************************** 1. 行 ****************************
    オブジェクトスキーマ: slowtech
     オブジェクト名: t1
   待機スレッドID: 29
     待機中のpid: 4
    待機アカウント: root@localhost
   待機ロックタイプ: 排他的
  待機ロック期間: トランザクション
    待機クエリ: テーブル slowtech.t1 を変更し、c1 int を追加します
   待機クエリ秒数: 446
 影響を受けるクエリ行の待機: 0
 検査済みのクエリ行を待機中: 0
   ブロックスレッドID: 27
    ブロッキングpid: 2
   ブロッキングアカウント: root@localhost
   ブロッキングロックタイプ: SHARED_READ
  ブロックロック期間: トランザクション
  sql_kill_blocking_query: クエリ 2 を強制終了する
sql_kill_blocking_connection: 2 を強制終了する
************************** 2. 行 ****************************
    オブジェクトスキーマ: slowtech
     オブジェクト名: t1
   待機スレッドID: 29
     待機中のpid: 4
    待機アカウント: root@localhost
   待機ロックタイプ: 排他的
  待機ロック期間: トランザクション
    待機クエリ: テーブル slowtech.t1 を変更し、c1 int を追加します
   待機クエリ秒数: 446
 影響を受けるクエリ行の待機: 0
 検査済みのクエリ行を待機中: 0
   ブロッキングスレッドID: 29
    ブロッキングpid: 4
   ブロッキングアカウント: root@localhost
   ブロッキングロックタイプ: SHARED_UPGRADABLE
  ブロックロック期間: トランザクション
  sql_kill_blocking_query: クエリ 4 を強制終了する
sql_kill_blocking_connection: 4 を強制終了する
セット内の 2 行 (0.00 秒)

公式出力を具体的に分析すると、

alter table 操作は 1 つだけですが、2 つのレコードが生成され、2 つのレコードの kill オブジェクトが異なります。テーブル構造に精通しておらず、レコードの内容を注意深く読み取らないと、間違ったオブジェクトを kill してしまうことになります。

さらに、N 個のクエリが DDL 操作によってブロックされた場合、N * 2 個のレコードが生成されます。ブロック操作が多数ある場合、これらの N * 2 レコードは完全にノイズになります。

以前の SQL では、ブロックされた操作の数に関係なく、alter table 操作によって出力されたレコードは 1 つだけでした。

ブロックセッションによって実行された操作を表示する方法

しかし、上記の SQL にも残念な点があり、blocking_query が NULL であり、セッション 1 では明らかに 3 つの SQL が実行されています。

これは performance_schema.threads (show processlist と同様) に関連しており、現在実行中の SQL のみを出力します。実際には、すでに実行された SQL を確認することは不可能です。

しかし、オンラインでは、殺害は注意を要する操作です。結局のところ、殺害がビジネス上重要な操作であるかどうかを知ることは困難です。それともバッチ更新操作ですか?では、トランザクションの前に操作をキャプチャする方法はあるのでしょうか?

答えはイエスです。

これは、パフォーマンススキーマ内のステートメントイベント(操作イベント)を記録するテーブルです。これには、

events_statements_current、events_statements_history、events_statements_history_long、prepared_statements_instances。

最初の 3 つがよく使用されます。

3つのテーブル構造は全く同じです。このうちevents_statements_historyにはevents_statements_currentの操作も含まれているので、ここではevents_statements_historyを使用します。

最終的なSQLは次のようになります。

選択
 ロックされたスキーマ、
 ロックされたテーブル、
 ロックタイプ、
 待機プロセスリストID、
 待機年齢、
 待機クエリ、
 待機状態、
 ブロッキングプロセスリストID、
 ブロック年齢、
 substring_index(sql_text,"transaction_begin;" ,-1) AS ブロッキングクエリ、
 sql_kill_blocking_connection
から
 (
  選択
   b.OWNER_THREAD_IDはgranted_thread_idとして、
   a.OBJECT_SCHEMA AS ロックされたスキーマ、
   a.OBJECT_NAME AS ロックテーブル、
   「メタデータ ロック」AS locked_type、
   c.PROCESSLIST_ID は、waiting_processlist_id として、
   c.PROCESSLIST_TIME は待機時間として、
   c.PROCESSLIST_INFO AS 待機クエリ、
   c.PROCESSLIST_STATE AS 待機状態、
   d.PROCESSLIST_ID AS ブロッキングプロセスリストID、
   d.PROCESSLIST_TIME AS ブロッキング年齢、
   d.PROCESSLIST_INFO AS ブロッキングクエリ、
   concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
  から
   performance_schema.metadata_locks a
  performance_schema.metadata_locks b を a.OBJECT_SCHEMA = b.OBJECT_SCHEMA に結合します。
  かつ、a.OBJECT_NAME = b.OBJECT_NAME
  かつ a.lock_status = 'PENDING'
  b.lock_status = 'GRANTED' かつ
  かつ、a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
  かつ、a.lock_type = 'EXCLUSIVE'
  performance_schema.threads c を a.OWNER_THREAD_ID = c.THREAD_ID に結合します。
  performance_schema.threads d を b.OWNER_THREAD_ID = d.THREAD_ID に結合します。
 ) t1,
 (
  選択
   スレッドID、
   group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
  から
   パフォーマンス スキーマ イベント ステートメント 履歴
  スレッドIDでグループ化
 ) t2
どこ
 t1.許可されたスレッドID = t2.スレッドID \G
************************** 1. 行 ****************************
    ロックされたスキーマ: slowtech
    ロックされたテーブル: t1
     ロックタイプ: メタデータロック
  待機プロセスリスト ID: 4
     待機年齢: 294
    待機クエリ: テーブル slowtech.t1 を変更し、c1 int を追加します
    waiting_state: テーブルメタデータのロックを待機中
  ブロッキングプロセスリストID: 2
    ブロック年齢: 336
    ブロッキングクエリ: slowtech.t1 から id=2 を削除し、slowtech.t1 から * を選択し、slowtech.t1 を更新して、id=1 で name='c' を設定します。
sql_kill_blocking_connection: 2 を強制終了する
セットに 1 行、警告 1 件 (0.00 秒)

上記の出力から、blocking_query にはセッション 1 の現在のトランザクションのすべての操作が含まれており、実行順に出力されていることがわかります。

デフォルトでは、events_statements_history は各スレッドの最新の 10 件の操作のみを保持することに注意してください。トランザクションに多数の操作がある場合、実際にはそれらすべてをキャプチャすることは不可能です。

要約する

上記は、MySQL 5.7 で DDL がブロックされる問題を特定する方法について紹介したものです。お役に立てば幸いです。ご質問がある場合は、メッセージを残していただければ、すぐに返信いたします。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。

以下もご興味があるかもしれません:
  • MySQL 8.0 で列を素早く追加する方法
  • MySQLオンラインDDLの使用に関する詳細な説明
  • MySQL DDL による同期遅延を解決する方法
  • MySQL 8.0 アトミック DDL 構文の詳細な説明
  • MySQL オンライン DDL ツール gh-ost 原理分析
  • MySQL DDLステートメントの使用
  • 一般的なMysql DDL操作の概要
  • MySQL 8.0 の新機能の分析 - トランザクション データ ディクショナリとアトミック DDL
  • MySQL データ定義言語 DDL の基本ステートメント
  • MySQL 8.0 DDLアトミック機能と実装原則
  • MySQLオンラインDDL gh-ostの使用の概要
  • MySQL 8.0 の新機能: アトミック DDL ステートメントのサポート
  • MySQL がユーザー名とパスワードの漏洩を引き起こす可能性のある Riddle の脆弱性を公開
  • MySQL 8.0 オンライン DDL クイック列追加の概要

<<:  Node.jsがES6モジュールを処理する方法の詳細な説明

>>:  Linux の一般的なテキスト処理コマンドと vim テキストエディタ

推薦する

Vueのトグルボタンをクリックしてボタンを有効にし、無効にします。

実装方法は3つのステップに分かれています。テンプレートに 2 つのボタンを設定し、v-if と v-...

MySQL 最適化チュートリアル: 大規模なページングクエリ

目次背景制限の最適化最適化方法1. カバーインデックスを使用する2. サブクエリの最適化3. 遅延連...

YUMを使用してdockerをインストールする方法

次の図に示すように: Centos 7.0以上であれば問題ありません。現在のシステム カーネル バー...

nginx パニック問題の解決方法の詳細な説明

nginx パニック問題に関しては、まず nginx の起動プロセス中に、マスター プロセスが構成フ...

Nginx ロケーション設定(ロケーションのマッチング順序)の詳細な説明

ロケーションは「位置指定」を意味し、主にさまざまな位置指定のための URI に基づいています。これは...

Linuxブートサービスを起動する2つの方法

目次rc.local メソッドchkconfig メソッドrc.local メソッド1 まず自動的に...

Linux スケジュールタスクの関連操作の概要

皆様の参考と操作を容易にするために、様々な主要ウェブサイトを検索し、関連するスケジュールされたタスク...

MySQL データベースの 1045 エラーの解決方法

ローカル データベースがサーバー データベースに接続されているときに発生する 1045 の問題を解決...

vue-element-admin グローバル読み込み待機中

最近の要件:グローバルロード、すべてのインターフェースはロード待機機能を表示するかどうかを手動で制御...

CentOS7にsshをインストールして設定する

1. openssh-serverをインストールする yum インストール -y openssl o...

MySQL ビュー管理ビューの例の詳細説明 [追加、削除、変更、クエリ操作]

この記事では、例を使用して MySQL ビューの管理ビュー操作について説明します。ご参考までに、詳細...

MySQL thread_stack 接続スレッドの最適化

MySQL は、ネットワーク経由だけでなく、名前付きパイプ経由でも接続できます。MySQL への接続...

MySQL データベースの一般的な基本操作の分析 [データベースの作成、表示、変更、削除]

この記事では、例を使用して、MySQL データベースの一般的な基本操作について説明します。ご参考まで...

MySql テーブル、データベース、シャーディング、パーティショニングの知識ポイントの紹介

1. はじめにデータベース内のデータ量が一定レベルに達すると、システムパフォーマンスのボトルネックを...

MySQL が起動直後にシャットダウンする問題 (ibdata1 ファイルの破損が原因) に対する完璧な解決策

コンピュータ ルームのサーバー上の mysql がしばらく実行されていたのですが、突然、再起動しても...