AnalyticDB for PostgreSQLの遅いクエリ診断 - AnalyticDB - Alibaba Cloud ドキュメントセンター

AnalyticDB for PostgreSQLには、スロークエリの診断、分析、最適化に役立つスロークエリ診断機能があります。スロークエリ診断機能を使用する前に、スロークエリ機能を有効にする必要があります。スロークエリ機能を有効にすると、1秒 (デフォルトのしきい値) を超えるSQLクエリが自動的に記録されます。 postgresデータベースにログインして、SQLクエリを照会および分析できます。

前提条件

低速クエリ診断機能は、エラスティックストレージモードおよびマイナーエンジンバージョンV7.0.5.0以降のAnalyticDB for PostgreSQL V7.0インスタンスでのみ使用できます。マイナーエンジンバージョンを表示および更新する方法の詳細については、「インスタンスのマイナーバージョンの表示」および「インスタンスのマイナーバージョンの更新」をご参照ください。

注意

デフォルトでは、スロークエリログは過去7日間のデータを保持しますが、失敗したクエリに関するデータは記録されません。スロークエリログには、実行時間がGrand Unified Configuration (GUC) パラメーターslow_query_min_durationの値を超えるすべてのSQL文が記録されます。このパラメータのデフォルト値は1秒です。つまり、1秒以上を消費するすべてのSQLステートメントとすべてのデータ定義言語 (DDL) ステートメントが低速クエリログに記録されます。システムの安定性を確保するため、このパラメーターのデフォルト設定を変更しないことを推奨します。
スロークエリログには、長さが1024バイトを超えるSQL文は記録されません。 SQL文の余分な部分は切り捨てられます。

低速クエリ診断機能の有効化または無効化

--ON: enables the slow query diagnostics feature. OFF: disables the slow query diagnostics feature.
SHOW adbpg_feature_enable_query_monitor;
-- Enable the slow query diagnostics feature for a business database.
ALTER database {Business database name} SET adbpg_feature_enable_query_monitor to ON;

例

デフォルトでは、システムは1秒以上を消費するすべてのSQL文とすべてのDDL文に関する統計を収集します。スロークエリログはpostgresデータベースに保存されます。現在のインスタンスの低速クエリをクエリする場合は、postgresデータベースに切り替えて、qmonitor.instance_slow_queries (インスタンスレベル) およびqmonitor.host_slow_queries (ノードレベル) ビューを使用して低速クエリを診断できます。次のセクションでは、低速クエリを診断する一般的なシナリオについて説明します。

説明

関連フィールドの詳細については、このトピックの付録を参照してください。

指定された時間範囲内に実行されるすべてのスロークエリステートメントを照会します。

過去30分など、指定された期間内に実行されたすべてのスロークエリステートメントを照会します。ビジネス要件に基づいて期間を変更し、指定した期間内に長時間を消費する低速クエリをクエリすることもできます。
```
SELECT 
       query_start as "Start time",
       query_end AS "End time",
       query_duration_ms AS "Time consumed, in milliseconds",
       query_id AS "Query ID",
       query AS "SQL statement"
 FROM qmonitor.instance_slow_queries
 WHERE query_start >= now() - interval '30 min';
```

2024年2月26日に実行されたすべてのスロークエリを照会します。デフォルトでは、スロークエリログは過去7日間のデータを保持します。

SELECT 
       query_start as "Start time",
       query_end AS "End time",
       query_duration_ms AS "Time consumed, in milliseconds",
       query_id AS "Query ID",
       query AS "SQL statement"
 FROM qmonitor.instance_slow_queries
 WHERE query_start >= '2024-02-26 00:00:00'
 AND query_end <= '2024-02-27 00:00:00';

指定された時間範囲内で、インスタンスレベルのリソース (CPU使用率、メモリ使用量、またはファイルサイズ) ごとに上位N個のスロークエリを照会します。
- 指定された期間 (30分など) 内にインスタンスのCPU使用率が高い上位20個のスロークエリを照会します。ビジネス要件に基づいて期間を変更し、指定した期間内にCPU使用率が高い低速クエリをクエリすることもできます。
```
SELECT 
       (cpu_time_ms/1000)::text || ' s' AS "CPU time",
       query_start as "Start time",
       query_end AS "End time",
       query_duration_ms AS "Time consumed, in milliseconds",
       query_id AS "Query ID",
       query AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min'
ORDER BY cpu_time_ms DESC
LIMIT 20;
```
- 指定された期間 (30分など) にインスタンスのメモリ使用量が多い上位20個のスロークエリを照会します。ビジネス要件に基づいて期間を変更し、指定した期間内にメモリ使用量が多い低速クエリをクエリすることもできます。
```
SELECT 
       pg_size_pretty(memory_bytes) AS "Memory consumed",
       query_start as "Start time",
       query_end AS "End time",
       query_duration_ms AS "Time consumed, in milliseconds",
       query_id AS "Query ID",
       query AS "SQL statement"
 FROM qmonitor.instance_slow_queries
 WHERE query_start >= now() - interval '30 min'
 ORDER BY memory_bytes DESC
 LIMIT 20;
```
  説明
  統計収集時にメモリ使用量を考慮する必要があります。メモリ使用量はクエリ中に変動します。したがって、クエリ中に収集されるメモリ使用量のピーク値は、メモリ使用量を大まかに反映できますが、正確な値ではありません。
- 2024年2月26日の00:00から12:00までの期間に、CPU使用率の高い上位10のスロークエリを照会します。
```
SELECT 
       (cpu_time_ms/1000)::text || ' s' AS "CPU time",
       query_start as "Start time",
       query_end AS "End time",
       query_duration_ms AS "Time consumed, in milliseconds",
       query_id AS "Query ID",
       query AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= '2024-02-26 00:00:00'
AND query_end <= '2024-02-26 12:00:00'
ORDER BY cpu_time_ms DESC
LIMIT 10;
```

指定された時間範囲内のノードレベルのリソース (CPU使用率、メモリ使用率、またはファイルサイズ) ごとに上位N個のスロークエリを照会します。

指定された期間 (30分など) に、ノードのCPU使用率が高い上位20個の低速クエリを照会します。ビジネス要件に基づいて期間を変更し、指定した期間内にCPU使用率が高い低速クエリをクエリすることもできます。

SELECT 
        (host_cpu_time_ms/1000)::text || ' s' as "CPU time",
       query_start as "Start time",
       query_end as "End time",
       query_duration_ms as "Time consumed, in milliseconds",
       query_id as "Query ID",
       query as "SQL statement"
FROM qmonitor.host_slow_queries
WHERE hostname ='Node hostname' 
AND query_start >= now() - interval '30 min'
ORDER BY host_cpu_time_ms DESC
LIMIT 20;

指定された期間 (30分など) にノードのメモリ使用量が多い上位20個の低速クエリを照会します。ビジネス要件に基づいて期間を変更し、指定した期間内にメモリ使用量が多い低速クエリをクエリすることもできます。

SELECT 
        pg_size_pretty(host_mem_bytes) as "Memory consumed",
       query_start as "Start time",
       query_end as "End time",
       query_duration_ms as "Time consumed, in milliseconds",
       query_id as "Query ID",
       query as "SQL statement"
 FROM qmonitor.host_slow_queries
 WHERE hostname ='Node hostname'
 AND query_start >= now() - interval '30 min'
 ORDER BY host_mem_bytes DESC
 LIMIT 20;

スロークエリログの特定の情報を照会します。

SELECT * FROM qmonitor.instance_slow_queries WHERE query_id = '<Query ID>';

指定した時間範囲内の各ユーザーの低速クエリを照会します。
- 指定された時間範囲 (10分など) 内の各ユーザーの低速クエリを照会します。ビジネス要件に基づいて時間範囲を変更し、指定した時間範囲内の各ユーザーの低速クエリをクエリすることもできます。
```
SELECT user_name AS "User name",
COUNT(1) AS "Number of queries"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '10 min'
GROUP BY user_name
ORDER BY COUNT(1) DESC;
```

スロークエリログのエクスポート

AnalyticDB for PostgreSQLを使用すると、SELECTステートメントを実行して、qmonitor.instance_slow_queriesまたはqmonitor.host_slow_queriesビューからカスタム内部テーブルまたはObject Storage Service (OSS) バケットやMaxComputeテーブルなどの外部テーブルにデータをエクスポートできます。詳細については、「Data Lake Analysis」をご参照ください。スロークエリログのデータを正しく効率的にエクスポートするには、次の項目に注意してください。

AnalyticDB for PostgreSQLでは、スロークエリログ用のインデックスが作成され、パーティションテーブルフィールドはquery_startパラメーターに基づいて設定されます。時間範囲ごとにデータをエクスポートする場合は、クエリ条件にquery_start列を含めることで、パフォーマンスを向上させ、リソース消費を減らすことができます。たとえば、2024年2月26日の13:00から16:00の期間に生成された低速クエリログをエクスポートする場合、クエリ条件query_start >= '2024-02-26 13:00:00 'およびquery_start <= '2024-02-26 16:00:00' を追加できます。

設定アイテム

低速クエリの既定のしきい値設定項目を変更して、さまざまな条件下で低速クエリログをクエリできます。

説明

これらの設定項目を変更できるのはスーパーユーザーのみです。

slow_query_min_duration
デフォルトでは、この設定項目で指定されたしきい値は1秒です。この設定項目を変更して、1秒未満の低速クエリを収集することもできます。低速クエリで消費された時間がこの構成項目の値以上の場合、SQL文、実行時間、およびその他の関連情報が収集されます。この設定項目の値が-1に設定されている場合、低速クエリは収集されません。
例：
1. 現在のデータベースで5秒以上かかる低速クエリを収集するには、特権ユーザーとして次のステートメントを実行します。
```
ALTER DATABASE '<Database name>' SET slow_query_min_duration = '5s';
```
2. 現在のセッションで5秒以上かかる低速クエリを収集するには、通常のユーザーとして次のステートメントを実行します。
```
SET slow_query_min_duration = '5s';
```

slow_query_plan_min_duration
この設定項目では、実行計画のコレクションに対する低速クエリの時間のしきい値を指定します。デフォルトでは、システムは10秒以上かかる低速クエリの実行計画を収集します。スロークエリに費やされた時間がこの設定項目の値以上の場合、スロークエリの実行計画が収集されます。ほとんどの場合、EXPLAINステートメントを実行して実行計画をリアルタイムで照会でき、そのようなデータを収集する必要はありません。この設定項目の値が-1に設定されている場合、スロークエリの実行計画は収集されません。
例：
1. 10秒以上かかる低速クエリの実行計画を収集するには、特権ユーザーとして次のステートメントを実行します。
```
ALTER DATABASE '<Database name>' SET slow_query_plan_min_duration = '10s';
```
2. 10秒を超えるスロークエリの実行計画を収集するには、次のステートメントを通常ユーザーとして実行します。
```
SET slow_query_plan_min_duration = '10s';
```

付録

qmonitor.instance_slow_queries (インスタンスレベル) ビューのフィールド

項目	データ型	説明
query_id	text	クエリの一意のID。
session_id	integer	クエリセッションのID。
db_name	character variable (128)	照会されるデータベースの名前。
user_name	character variable (128)	クエリを開始したユーザーの名前。
application_name	character variable (128)	クエリを開始したアプリケーションのタイプ。
client_hostname	character variable (128)	クエリを開始したクライアントのホスト名。
client_addr	character variable (128)	クエリを開始したクライアントのIPアドレス。
client_port	さまざまなキャラクター (32)	クエリを開始したクライアントのポート番号。
rsg_name	character variable (128)	クエリによってアクセスされるテーブルが属するリソースグループの名前。
query_start	timestamptz	クエリの開始時刻。
query_end	timestamptz	クエリの終了時刻。
query_duration_ms	bigint	クエリによって消費された合計時間。単位：ミリ秒。このフィールドの値は、次のフィールドの値の合計です。 optimizer_duration_ms lock_wait_time_ms queue_wait_time_ms executor_duration_ms
optimizer_duration_ms	bigint	実行プランの生成にかかる時間。単位：ミリ秒。複雑なSQL文の実行計画に時間がかかります。
lock_wait_time_ms	bigint	ロックの待ち時間。単位：ミリ秒。
queue_wait_time_ms	bigint	リソースキューの待機時間。単位：ミリ秒。
executor_duration_ms	bigint	実行エンジンでクエリを実行するために消費された時間。単位：ミリ秒。
query	text	クエリのテキスト。The text of the query.
is_plpgsql	Boolean	クエリがPL/pgSQLストアドプロシージャかどうかを指定します。
query_optimizer	さまざまなキャラクター (16)	クエリで使用されるオプティマイザ。有効な値: ORCAとPlanner。
access_tables	text	クエリによってアクセスされるテーブルの名前。
result_rows	bigint	クエリに対して返される行数。クエリでINSERT文が実行されると、挿入された行の数が返されます。
num_segments	integer	クエリが実行される計算ノードの数。
num_slices	integer	クエリ実行プランに対して生成されたスライスの数。
cpu_time_ms	numeric	合計CPU時間。単位：ミリ秒。このフィールドで指定する時間は、次の時間の合計です。コーディネーターノードで消費されたCPU時間。すべての計算ノードのタスクによって消費された合計CPU時間。
mem_bytes	numeric	各ノードのピークメモリ使用量の累積値。この値は、クエリのメモリ使用量を大まかに反映しますが、正確な値ではありません。
spill_bytes	numeric	コンピューティング中にメモリが不足しているため、データがディスクに保存される場合があります。クエリが実行される計算ノードのディスクに保存されるファイルの最大数の累積値。この値は、クエリによって消費されたディスク容量を大まかに反映しますが、正確な値ではありません。

qmonitor.host_slow_queries (ノードレベル) ビューのフィールド

項目	データ型	説明
hostname	character variable (128)	ノードのホスト名。
hostrole	text	ノードのロール。有効な値： master: coordinatorノード segment: 計算ノード
query_id	text	クエリの一意のID。
db_name	character variable (128)	照会されるデータベースの名前。
user_name	character variable (128)	クエリを開始したユーザーの名前。
query_start	timestamptz	クエリの開始時刻。
query_end	timestamptz	クエリの終了時刻。
query	text	クエリのテキスト。The text of the query.
query_duration_ms	bigint	クエリによって消費された時間。単位：ミリ秒。
optimizer_duration_ms	bigint	実行プランの生成にかかる時間。単位：ミリ秒。複雑なSQL文の実行計画に時間がかかります。
host_cpu_time_ms	numeric	ノードでのクエリのCPU時間。単位：ミリ秒。
host_mem_bytes	numeric	ノードでのクエリのピークメモリ使用量。
host_spill_bytes	numeric	ノード上のディスクに保存されるファイルの最大数の累積値。