以前、「MySQL でコミットされていないトランザクション情報を検索する方法」というタイトルのブログ記事をまとめました。今では、この記事の知識ポイントや視点の多くは少し表面的であったり、十分に深くなかったり、結論の一部が間違っていたりするようです。このトピックをもう一度検討してみましょう。それでは先ほどの例で紹介しましょう。 --テスト環境データを用意する(実験環境はMySQL 8.0.18 Community Edition) mysql> テーブル kkk(id int 、 name varchar(12)) を作成します。 クエリは正常、影響を受けた行は 0 行 (0.34 秒) mysql> kkk 値に挿入します (1、'kerry')。 クエリは正常、1 行が影響を受けました (0.01 秒) mysql> kkk 値に挿入します (2、'jerry')。 クエリは正常、1 行が影響を受けました (0.00 秒) mysql> kkk 値に挿入します (3、'ken')。 クエリは正常、1 行が影響を受けました (0.00 秒) マイSQL> mysql> テーブル t(varchar(10)) を作成します。 クエリは正常、影響を受けた行は 0 行 (0.47 秒) mysql> t 値に挿入します('test'); クエリは正常、1 行が影響を受けました (0.00 秒) セッションウィンドウ(接続ID=38)で次のSQLを実行します。 mysql> デュアルから connection_id() を選択します。 +-----------------+ | 接続ID() | +-----------------+ | 38 | +-----------------+ セット内の 1 行 (0.00 秒) mysql> セッションの自動コミットを 0 に設定します。 クエリは正常、影響を受けた行は 0 行 (0.00 秒) mysql> id =1 の kkk から削除します。 クエリは正常、1 行が影響を受けました (0.00 秒) マイSQL> 別のセッションウィンドウ(接続ID=39)で、次のSQLを実行します。 mysql> t.trx_mysql_thread_idを選択します -> 、t.trx_id -> 、t.trx_state -> 、t.trx_tables_in_use -> 、t.trx_tables_locked -> 、t.trx_クエリ -> 、t.trx_rows_locked -> 、t.trx_rows_modified -> 、t.trx_lock_structs -> 、t.trx_started -> 、t.trx_isolation_level -> 、p.時間 -> 、p.ユーザー -> 、p.ホスト -> 、p.db -> 、p.コマンド -> information_schema.innodb_trx から -> INNER JOIN information_schema.processlist p -> ON t.trx_mysql_thread_id = p.id -> t.trx_state = 'RUNNING' の場合 -> かつ p.time > 4 -> AND p.command = 'Sleep'\G ************************** 1. 行 **************************** trx_mysql_スレッドID: 38 トランザクションID: 7981581 trx_state: 実行中 使用中のtrxテーブル: 0 trx_tables_locked: 1 trx_query: NULL ロックされた行数: 4 trx_rows_modified: 1 trx_lock_structs: 2 開始日時: 2020-12-03 15:39:37 trx_isolation_level: 繰り返し読み取り 時間: 23 ユーザー: ルート ホスト: ローカルホスト db: マイDB コマンド: スリープ セット内の 1 行 (0.00 秒) 上記の SQL ではトランザクションによって実行された SQL を見つけることができませんが、実際には、次のスクリプトを通じて、MySQL でコミットされていないトランザクションによって実行された最後の SQL を正確に見つけることができます。以下のように表示されます。 t.trx_mysql_thread_id を connection_id として選択します ,t.trx_id AS trx_id ,t.trx_state は trx_state として ,t.trx_started AS trx_started ,TIMESTAMPDIFF(SECOND,t.trx_started, now()) AS "trx_run_time(s)" ,t.trx_requested_lock_id AS trx_requested_lock_id ,t.trx_operation_state は trx_operation_state として ,t.trx_tables_in_use は trx_tables_in_use として ,t.trx_tables_locked は trx_tables_locked として ,t.trx_rows_locked は trx_rows_locked として ,t.trx_isolation_level AS trx_isolation_level ,t.trx_is_read_only は trx_is_read_only です ,t.trx_autocommit_non_locking は trx_autocommit_non_locking として ,e.イベント名 AS イベント名 ,e.timer_wait / 1000000000000 AS timer_wait ,e.sql_text information_schema.innodb_trx から、 performance_schema.events_statements_current e、 パフォーマンススキーマ.スレッド c ここで、t.trx_mysql_thread_id = c.processlist_id かつ、e.thread_id = c.thread_id\G; 次のスクリーンショットに示すように: セッション ウィンドウ (接続 ID = 38) で次の SQL を実行し続けます: "select * from t;"。 下記の通り mysql> セッションの自動コミットを 0 に設定します。 クエリは正常、影響を受けた行は 0 行 (0.01 秒) mysql> id =1 の kkk から削除します。 クエリは正常、1 行が影響を受けました (0.00 秒) mysql> t から * を選択します。 +------+ | ア | +------+ | テスト | +------+ セット内の 1 行 (0.00 秒) マイSQL> セッション ウィンドウ (接続 ID = 39) で次の SQL を実行し続けると、キャプチャされた SQL ステートメントがトランザクションによって実行された最後の SQL ステートメントであることがわかります。"select * from t" mysql> t.trx_mysql_thread_id を connection_id として選択します -> 、t.trx_id は trx_id として -> 、t.trx_state は trx_state として -> ,t.trx_started は trx_started として -> ,TIMESTAMPDIFF(SECOND,t.trx_started, now()) AS "trx_run_time(s)" -> 、t.trx_requested_lock_id は trx_requested_lock_id として -> 、t.trx_operation_state は trx_operation_state として -> 、t.trx_tables_in_use は trx_tables_in_use として -> 、t.trx_tables_locked は trx_tables_locked として -> 、t.trx_rows_locked は trx_rows_locked として -> 、t.trx_isolation_level は trx_isolation_level として -> 、t.trx_is_read_only は trx_is_read_only です -> 、t.trx_autocommit_non_locking は trx_autocommit_non_locking として -> ,e.event_name AS イベント名 -> 、e.timer_wait / 1000000000000 AS timer_wait -> ,e.sql_text -> information_schema.innodb_trx t から、 -> performance_schema.events_statements_current e、 -> performance_schema.threads c -> ここで t.trx_mysql_thread_id = c.processlist_id -> かつ e.thread_id = c.thread_id\G; ************************** 1. 行 **************************** 接続ID: 38 トランザクションID: 7981581 trx_state: 実行中 開始日時: 2020-12-03 15:39:37 trx_run_time(秒): 237 trx_requested_lock_id: NULL trx_operation_state: NULL 使用中のtrxテーブル: 0 trx_tables_locked: 1 ロックされた行数: 4 trx_isolation_level: 繰り返し読み取り trx_is_read_only: 0 trx_autocommit_non_locking: 0 イベント名: ステートメント/SQL/選択 タイマー待機: 0.0002 sql_text: t から * を選択 セット内の 1 行 (0.00 秒) エラー: クエリが指定されていません つまり、上記の SQL では、コミットされていないトランザクションで最後に実行された SQL 文しか取得できません。実稼働環境では、トランザクション内に複数の SQL 文が存在することが多く、複数の SQL 文の集合になります。コミットされていないトランザクションで実行されたすべての SQL ステートメントを調べたい場合はどうすればよいでしょうか?実は、MySQL には方法があります。次のSQL文は、 trx.trx_mysql_thread_id を processlist_id として選択します ,sc.スレッドID ,trx.trx_開始 、TO_SECONDS(now())-TO_SECONDS(trx_started) AS trx_last_time 、pc1.ユーザー ,pc1.ホスト ,pc1.db ,sc.SQL_TEXT を現在の SQL テキストとして ,sh.history_sql_test INFORMATION_SCHEMA.INNODB_TRX trx から INNER JOIN INFORMATION_SCHEMA.processlist pc1 ON trx.trx_mysql_thread_id=pc1.id th.processlist_id = trx.trx_mysql_thread_id の performance_schema.threads th の内部結合 内部結合 performance_schema.events_statements_current sc ON sc.THREAD_ID = th.THREAD_ID 内部結合( SELECT thread_id AS thread_id, GROUP_CONCAT(SQL_TEXT SEPARATOR ';') AS history_sql_test performance_schema.events_statements_history から スレッドIDでグループ化 ) sh ON sh.thread_id = th.thread_id ここで、trx_mysql_thread_id != connection_id() TO_SECONDS(now())-TO_SECONDS(trx_started) >= 0; です。 ただし、これら 2 つの SQL 文には問題があります。現在の接続の履歴で実行されたすべての SQL 文が検索されます (もちろん、これらの SQL 文が performance_schema.events_statements_history テーブルに保存されていることが前提です)。つまり、この SQL 文は、コミットされていないトランザクションによって実行されたすべてのスクリプトを照会するだけでなく、このセッション (接続) より前のトランザクションの SQL 文など、多くの履歴 SQL スクリプトも照会します。さらに、より厄介な問題があります。どの SQL 文がどのトランザクションに対応しているかを区別することが難しいのです。識別するには他の情報が必要です。それは時間がかかり、手間がかかります。下のスクリーンショットに示すように。 information_schema.innodb_trx システム テーブルにのみトランザクションの開始時刻 (trx_started) が含まれており、他のシステム テーブルにはトランザクション関連の時刻が含まれていないため、イベントの SQL の実行が開始される時刻を取得するには performance_schema.events_statements_history の TIMER_START フィールドのみを使用できます。この時刻は、対応するトランザクションの開始時刻 (trx_started) 以下である必要があります。この画期的な機能により、コミットされていないトランザクションのすべての SQL ステートメントを見つけることができます。次に、TIMER_START などのフィールドについて詳しく説明します。 TIMER_START、TIMER_END、TIMER_WAIT の紹介は次のとおりです。 TIMER_START、TIMER_END、TIMER_WAIT: イベントの時間情報。値はピコ秒(1兆分の1秒)単位です。 TIMER_START と TIMER_END の値は、イベントの開始時刻と終了時刻を表します。 TIMER_WAITはイベント実行に要した時間(期間)です。 イベントが完了していない場合、TIMER_END は現在の時刻、TIMER_WAIT はこれまで経過した時間 (TIMER_END - TIMER_START) になります。 監視機器設定テーブル setup_instruments 内の対応するモニターの TIMED フィールドが NO に設定されている場合、モニターの時間情報は収集されません。このイベントに対して収集された情報レコードでは、TIMER_START、TIMER_END、および TIMER_WAIT フィールドの値はすべて NULL です。 長い間テストと苦労を重ねた結果、ようやくほぼ完璧な SQL が完成しました。 @dt_ts を選択:=UNIX_TIMESTAMP(NOW()); 選択 @dt_timer:=MAX(sh.TIMER_START) performance_schema.threads から t へ 内部結合 performance_schema.events_statements_history AS sh ON t.`THREAD_ID`=sh.`THREAD_ID` ここで、t.PROCESSLIST_ID=CONNECTION_ID(); sh.current_schema AS database_name を選択 ,t.スレッドID ,it.trx_mysql_thread_id AS connection_id ,it.trx_id ,sh.イベントID ,it.trx_state ,REPLACE(REPLACE(REPLACE(sh.`SQL_TEXT`,'\n',' '),'\r',' '),'\t',' ') AS 実行されたSQL ,it.trx_started 、FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_START)/1000000000000 AS SIGNED)) AS start_time 、FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_END) /1000000000000 AS SIGNED)) AS end_time ,(sh.TIMER_END-sh.TIMER_START)/1000000000000 AS used_seconds ,sh.TIMER_WAIT/1000000000000 AS wait_seconds ,sh.LOCK_TIME/1000000000000 AS lock_seconds ,sh.ROWS_AFFECTED AS 影響を受ける行 ,sh.ROWS_SENT は send_rows として performance_schema.threads から t へ INNER JOIN information_schema.innodb_trx it ON it.trx_mysql_thread_id = t.processlist_id 内部結合 performance_schema.events_statements_history AS sh ON t.`THREAD_ID`=sh.`THREAD_ID` t.PROCESSLIST_ID IN ( ) のWHERE 選択 p.ID AS conn_id `information_schema`.`INNODB_TRX` から t INNER JOIN `information_schema`.`PROCESSLIST` p オン t.trx_mysql_thread_id=p.id t.trx_state='RUNNING' の場合 かつ p.COMMAND='スリープ' かつp.TIME>2 ) そして、sh.TIMER_START<@dt_timer AND FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_START)/1000000000000 AS SIGNED)) >=it.trx_started it.trx_id ASC、sh.TIMER_START ASC で順序付けします。 コミットされていないトランザクションのSQLを調べることができます。単純なテストであれば問題ありませんし、コミットされていないトランザクションのテストを複数同時に構築することも可能です。しかし、上記のSQLは3つのSQLで構成されており、いつも少しぎこちない感じがします。調べてみると、次のSQLに変換できることが分かりました。 sh.current_schema AS database_name を選択 ,t.スレッドID ,it.trx_mysql_thread_id AS connection_id ,it.trx_id ,sh.イベントID ,it.trx_state ,REPLACE(REPLACE(REPLACE(sh.`SQL_TEXT`,'\n',' '),'\r',' '),'\t',' ') AS 実行されたSQL ,it.trx_started ,DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - sh.TIMER_START*10e-13 秒) AS start_time ,DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - sh.TIMER_END*10e-13 秒) AS end_time ,(sh.TIMER_END-sh.TIMER_START)/1000000000000 AS used_seconds ,sh.TIMER_WAIT/1000000000000 AS wait_seconds ,sh.LOCK_TIME/1000000000000 AS lock_seconds ,sh.ROWS_AFFECTED AS 影響を受ける行 ,sh.ROWS_SENT は send_rows として performance_schema.threads から t へ INNER JOIN information_schema.innodb_trx it ON it.trx_mysql_thread_id = t.processlist_id 内部結合 performance_schema.events_statements_history AS sh ON t.`THREAD_ID`=sh.`THREAD_ID` t.PROCESSLIST_ID IN ( ) のWHERE 選択 p.ID AS conn_id `information_schema`.`INNODB_TRX` から t INNER JOIN `information_schema`.`PROCESSLIST` p オン t.trx_mysql_thread_id=p.id t.trx_state='RUNNING' の場合 かつ p.COMMAND='スリープ' かつp.TIME>2 ) AND sh.TIMER_START<(SELECT VARIABLE_VALUE*10000000000000 FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') AND DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - sh.TIMER_START*10e-13 秒) >= it.trx_started it.trx_id ASC、sh.TIMER_START ASC で順序付けします。 注: performance_schema.global_status は MySQL 5.7 で導入されました。データベースが MySQL 5.6 の場合は、SQL の performance_schema.global_status を INFORMATION_SCHEMA.GLOBAL_STATUS に置き換えることができます。 では、この SQL は必ずしも完璧なのでしょうか? ネットユーザーMSSQL123は、テスト環境では、上記のSQLはFROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_START)/1000000000000 AS SIGNED)) >=it.trx_startedがデータをフィルタリングするため、データを見つけられなかったと報告しました。対応するtrx_started値はすべてstart_timeより大きいことがわかりました。 -------------------------------------------------------------------------補足情報---------------------------------------------------------------------------------------------------------------- そして、同じテスト環境で、翌日テストしたところ、上記の最初のSQL文は正常であるものの、2番目のSQL文は書き込み方法の違いによりstart_timeに若干の差があり、クエリ結果が全く異なることが突然判明しました(2番目のSQL文はミリ秒単位の精度で、比較中に偏差によりデータが一括で除外されました) ------------------------------------------------------------------------------------------------------------------------------------------------ 関連ドキュメントを検索したところ、TIMER_START フィールドの値が変動したり逸脱したりする可能性があり、この変動や逸脱がクエリ結果に影響を与える可能性があることを知りました。次のコンテンツは、http://porthos.ist.utl.pt/docs/mySQL/performance-schema.html から引用したものです。
一節は次のように翻訳されています。 イベントのピコ秒値は概算です。それらの精度は、ある単位から別の単位に変換するときに発生する一般的な誤差の影響を受けます。 CYCLE タイマーが使用され、プロセッサ速度が変化すると、偏差が発生する可能性があります。これらの理由から、イベントの TIMER_START 値を、サーバーの起動から経過した時間を正確に測定するものと見なすことは合理的ではありません。一方、イベントを開始時刻または期間で並べ替えるには、ORDER BY 句で TIMER_START または TIMER_WAIT 値を使用するのが合理的です。 私たちは問題を一撃で解決したいと考えることが多いのですが、複雑な環境といくつかの制御できない要因により、現実は「特効薬はない」というほど残酷であることがよくあります。 TIMER_START の変動や偏差がクエリ結果に影響する場合は、前回の記事の SQL を通じて大量の SQL を見つけ、他のフィールドや情報を通じてどの SQL がコミットされていないトランザクションであるかを手動で識別する必要があります。 参考文献: https://stackoverflow.com/questions/25607249/mysql-performance-schema-how-to-get-event-time-from-events-statements-current-ta パフォーマンススキーマ https://dev.mysql.com/doc/refman/5.7/en/パフォーマンススキーマタイミング.html https://dev.mysql.com/doc/refman/8.0/en/パフォーマンススキーマタイミング.html これで、MySQL でコミットされていないトランザクション SQL を見つける方法に関するこの記事は終了です。MySQL でコミットされていないトランザクション SQL を見つける方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。 以下もご興味があるかもしれません:
|
現実世界では、鍵は外の世界から身を隠したいときに使用するツールです。コンピュータでは、複数のプロセス...
コードをコピーコードは次のとおりです。 <!DOCTYPE HTML PUBLIC "...
Harbor は、Docker イメージを保存および配布するためのエンタープライズ レベルのレジスト...
ドキュメントの範囲この記事では、Firefox やその他の Gecko ベースのブラウザ、Safar...
Linux でディレクトリを切り替えるとなると、誰もが間違いなくcdコマンドを思い浮かべるでしょう。...
目次Nginx 負荷分散構成Nginx 負荷分散戦略ポーリング(デフォルト)重さip_ハッシュ公正(...
目次1. テーブル自動ソート2. ページング機能3.el-checkbox-group 複数選択ボッ...
dockerの登場により、多くのサービスが徐々にハードウェアアーキテクチャへの依存から脱却しました。...
メタタグは、HTML言語のヘッド領域にある補助タグです。HTML文書のヘッダーにあるヘッドタグとタイ...
目次1. はじめに2. 初期ビュー(I) Vueの概念を理解する(II) MVVMアーキテクチャ(I...
目次序文始める基本レイアウトデータバインディングイベントバインディング最適化ジッター問題を最適化する...
目次1. カスタム指示の登録1.1. グローバルカスタム指示1.2. ローカルカスタム指示2. カス...
MySQL バックアップコールドバックアップ:停止服務進行備份,即停止數據庫的寫入ホットバックアップ...
私はmysql ERROR 1045に遭遇し、この問題に長い時間を費やしました。私はそれを自分で書き...
私は通常、Tomcatや他のアプリケーションのリバースプロキシとしてnginxを使用しています。実際...