MySQL での長時間実行クエリの管理

長時間実行されるクエリは、MySQL データベースのパフォーマンスにとって深刻な問題となる可能性があり、応答時間の遅さからすべてのユーザーに影響を及ぼす本格的なボトルネックまで、あらゆる原因を引き起こします。これらの厄介なクエリを処理し、クエリの内容、発生理由、管理方法を理解することが、データベースをスムーズに実行し続けるための鍵となります。

早期に発見することも、進行中に阻止することも、自動的に対処する方法を設定することも、このガイドでカバーできます。

長時間実行クエリとは何ですか?

MySQL の長時間実行クエリとは、実行に異常に長い時間がかかるクエリです。
クエリを「長時間実行」として分類する具体的な期間は、アプリケーションのパフォーマンス基準によって異なります。一般に、クエリの実行時間が通常より長くなり、データベースの速度が低下し始める場合、クエリは長時間実行されているとみなされます。

長時間実行されるクエリの根本原因

長時間実行されるクエリの原因はさまざまです。

• 適切なインデックス作成の欠如 – 適切なインデックス作成がないと、MySQL は必要なデータを取得するためにテーブル全体をスキャンする必要があります。このプロセスは、かなりの時間とリソースを消費するため、特に大きなテーブルの場合、非常に非効率的です。

• 高負荷状況 – サーバーが大量のクエリを処理したり、いくつかの複雑なクエリを同時に処理したりすると、利用可能なリソース (CPU やメモリなど) が限界に達します。このリソースの競合によりクエリの実行が遅れ、特に使用量のピーク時に実行時間が長くなる可能性があります。

• ロック競合 – これは、複数のトランザクションが同じデータに同時にアクセスする必要があるが、他の操作が必要なロックを保持しているためにブロックされた場合に発生します。たとえば、あるトランザクションが行を更新している場合、同じ行の読み取りまたは更新を希望する別のトランザクションは、最初のトランザクションが完了してロックが解放されるまで待つ必要があります。

• 不適切な正規化 – 正規化はデータの冗長性を回避し、データの整合性を向上させるのに役立ちますが、データベースが過剰に正規化されると、複数の結合を含む複雑なクエリが発生する可能性があります。これらによりパフォーマンスが低下する可能性があります。逆に、正規化が不十分だとデータの重複が過剰になり、テーブルが大きくなり、クエリが遅くなる可能性があります。

• 大規模結合 – 大規模なテーブルの結合を伴うクエリ、特に適切なインデックスがない場合、速度が低下する可能性があります。データベースは結合条件に基づいてテーブル全体の行を照合する必要がありますが、効率的なインデックス作成を行わないと、このプロセスはリソースを大量に消費し、時間がかかる可能性があります。

長時間実行されるクエリの特定

長時間実行されるクエリを効果的に管理するには、まずクエリを特定する必要があります。以下にいくつかの方法を示します:

1. SHOW PROCESSLIST の使用

ショープロセスリスト;コマンドは、サーバー上で実行されているすべてのアクティブなクエリのスナップショットを取得する簡単な方法です。このコマンドは、各クエリの実行時間など、いくつかの重要な情報とともに各クエリを表示します。 「時間」値が高いものは、実行時間が長いクエリである可能性があります。このコマンドの使用方法は次のとおりです:

完全なプロセスリストを表示;

このコマンドは、現在のプロセスをすべてリストし、誰がプロセスを開始したか、どのような種類のコマンドを実行しているか、そして重要なことに、どれくらいの時間プロセスが実行されているかを示します。異常に長時間実行されているクエリを見つけた場合、それは長時間実行されているクエリです。その後、それらをさらに深く最適化するか、システムのパフォーマンスを低下させている場合は単純に停止するかを決定できます。

2. スロークエリログ

遅いクエリ ログを設定することは、問題のあるクエリを捕捉するためのもう 1 つの優れた戦略です。この便利な MySQL 機能は、特定のしきい値よりも実行に時間がかかるクエリをログに記録します。これは、長時間実行されているクエリを捕捉するだけではなく、インデックスを効率的に使用していないクエリを特定するのにも役立ちます。

スロークエリログを起動して実行するには、MySQL 構成ファイル (my.cnf または my.ini) でいくつかの設定を調整する必要があります。

• Slow_query_log – ログを有効にするには、これを 1 に設定します。
• Slow_query_log_file – ログを保存するファイル パスを指定します。
• Long_query_time – クエリをログに記録するための最小実行時間 (秒単位) を設定します。 たとえば、「2」に設定すると、実行に 2 秒以上かかるクエリがログに記録されます。

3. パフォーマンススキーマ

MySQL のパフォーマンス スキーマは、より詳細な調査に非常に役立ちます。このツールは、サーバー イベントを監視し、パフォーマンス メトリクスを追跡するように設計されており、クエリの実行と全体的なシステム パフォーマンスをより明確に把握できます。

次の行を追加して、MySQL 構成で有効になっていることを確認します。

[mysqld]
パフォーマンススキーマ = ON

アクティブ化すると、さまざまなパフォーマンス スキーマ テーブルを探索してクエリのパフォーマンスを分析できます。たとえば、長時間実行されるクエリを特定したい場合は、events_statements_history_long テーブルを調べるとよいでしょう。クエリを実行する方法は次のとおりです:

SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS '期間 (秒)'
FROM Performance_schema.events_statements_history_long
WHERE TIMER_WAIT > 10000000000;

このクエリは、10 秒以上実行されているクエリを見つけるのに役立ちます。 SQL テキストや各クエリの実行時間などの詳細が表示されます。

長時間実行クエリを手動で強制終了する

時間がかかりすぎてシステムのリソースに負担をかけているクエリを特定した場合、手動でクエリを終了するオプションがあります。これは、KILL コマンドの後にクエリの特定のプロセス ID を指定して実行します。

プロセス ID を確認するには、SHOW PROCESSLIST コマンドを実行すると、現在実行中のすべてのプロセスとそれぞれの ID が表示されます。リスト内で、実行時間の長さを示す「時間」値が高いクエリがないか調べてください。

問題のあるクエリを特定し、そのプロセス ID をメモしたら、KILL コマンドを使用してクエリを終了できます。

KILL [プロセス ID];

[プロセス ID] を SHOW PROCESSLIST 出力の実際の番号に置き換えます。

このアプローチには注意してください。クエリを突然停止すると、クエリが情報の書き込みまたは更新中にデータが不整合な状態のままになるなど、問題が発生することがあります。

長時間実行クエリの強制終了を自動化する方法

長時間実行されるクエリを処理するように自動化を設定すると、これらの遅いクエリや最適化されていないクエリがデータベース リソースを使い果たし、システム全体の速度が低下したり、さらにはロックアップしたりすることを防ぎ、真の救世主となる可能性があります。ただし、慎重に行ってください。適切なチェックを行わずにこのツールを使用すると、注意が必要な深刻なパフォーマンスの問題が実際に隠れてしまう可能性があります。

強制終了されたクエリがアプリケーションに及ぼす影響を分析するために、包括的なログ記録と監視が適切に行われていることを常に確認し、単に自動的に強制終了するのではなく、それらのクエリを改善することを検討してください。自動終了は、すべてを解決する解決策としてではなく、パフォーマンスを最適化するためのより大きな戦略の一部として考えてください。

1. イベントスケジューラを有効にする

まず、デフォルトでは無効になっている MySQL イベント スケジューラを有効にする必要があります。イベント スケジューラを使用すると、事前定義された時間にサーバーで自動的に実行するタスクを作成およびスケジュールできます。次のコマンドを実行します:

グローバルevent_scheduler = ONを設定します;

2. 長時間実行されるクエリを強制終了するイベントを作成する

スケジューラーを有効にしたら、次のステップは、長時間実行されるクエリを監視して強制終了する実際のイベントを定義することです。このイベントは 1 分ごとに実行され、指定されたしきい値 (たとえば 60 秒) を超えて実行されているクエリがないかチェックされます。識別されると、これらのクエリは自動的に強制終了されます。このイベントを設定する SQL コードの内訳は次のとおりです:

`CREATE EVENT kill_long_running_queries
ON SCHEDULE EVERY 1 MINUTE -- イベントを実行する頻度を指定します
してください
始めてください
完了したことを宣言します。 INT DEFAULT FALSE;
proc_id INT を宣言します。 -- 各クエリのプロセス ID を格納する変数
information_schema.processlist
から ID を選択するための cur1 CURSOR を宣言します WHERE コマンド = 'クエリ' AND 時間 > 60; -- 「60」を秒単位のしきい値に変更します
NOT FOUND SET の CONTINUE ハンドラを宣言 = TRUE;

cur1 を開く;

read_loop: LOOP
cur1 INTO proc_id;
をフェッチします 完了したら
read_loop を終了します;
END IF;
KILL proc_id; -- proc_id
で識別されるプロセスを強制終了します。 ループを終了;

cur1 を閉じる;
終わり;`

3. MySQL での最大クエリ実行時間を設定する

クエリの最大実行時間を制御すると、実行時間が長すぎるクエリによってデータベースが拘束されるのを防ぐことができます。これは、MySQL 5.7.8 以降のバージョンの max_execution_time システム変数を使用して、すべての読み取り専用 SELECT クエリに対してシステム全体の実行時間制限を設定することで行われます。

SET GLOBAL max_execution_time = 2000;

これにより、制限が 2000 ミリ秒 (2 秒) に設定されます

この設定はストアド プロシージャ、関数、トリガーには適用されず、MySQL 構成ファイルに追加しない限り、サーバーの再起動時にデフォルトにリセットされることに注意してください。

[mysqld]
最大実行時間 = 2000

4. MariaDB での最大ステートメント時間を設定する

MariaDB は、MySQL からフォークされたものですが、クエリの実行時間を管理するために、似ていますが異なるアプローチを提供します。 MariaDB 10.1.1 以降では、この目的で max_statement_time システム変数を使用できます。

SET GLOBAL max_statement_time = 2;

これにより、すべてのクエリの実行時間が 2 秒に制限されます。

サーバーの再起動を通じて永続的な構成を行うには、MariaDB 構成ファイルに次の行を追加します。

[mysqld]
max_statement_time = 2

Releem のクエリ分析を使用して長時間実行クエリを特定して修正する

Releem のクエリ分析ツールは、データベースのパフォーマンスを監視および最適化する方法に革命をもたらします。上位 100 件のクエリに関する詳細情報を自動的に収集し、平均実行時間や各クエリがデータベースの運用効率に及ぼす全体的な影響などの重要な指標を提供します。

Releem を使用すると、パフォーマンスの低いクエリを特定するために PROCESSLIST 出力を手動で調べたり、低速クエリ ログを調べたりする必要がありません。このツールには直感的なダッシュボードが備わっており、遅延しているクエリや過度に時間がかかっているクエリを簡単に並べ替えて特定できます。この即時の洞察は、ボトルネックをすぐに特定して解決するのに役立ちます。

以上がMySQL での長時間実行クエリの管理の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。