PolarDB:CPU 使用率が高い場合のトラブルシューティング

実行時間の長い低速なクエリを検出する

pg_stat_statements 拡張機能は、最適化フェーズと実行フェーズ中にデータベースサーバー上のすべての SQL 文の統計情報を記録できます。

この拡張機能は共有メモリを使用するため、拡張機能名は shared_preload_libraries パラメーターに追加されます。

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

1. pg_stat_statements 拡張機能とデータベースは、統計情報の累積を続けます。CPU 使用率が高い問題をトラブルシューティングするには、データベースと拡張機能の既存の統計情報をすべてクリアしてから、統計情報の収集を開始する必要があります。

   -- 現在のデータベースの既存の統計情報をすべてクリアします。
   SELECT pg_stat_reset();
   -- pg_stat_statements 拡張機能によって収集された既存の統計情報をすべてクリアします。
   SELECT pg_stat_statements_reset();

2. データベースと拡張機能が十分な統計情報を収集するまで、1 ～ 2 分待ちます。

3. 統計情報が収集されたら、次の文を実行して、実行時間が上位 5 つの SQL 文をクエリします。

   SELECT * FROM pg_stat_statements ORDER BY total_plan_time DESC LIMIT 5;
   SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

バッファー読み取り回数が多い低速なクエリを検出する

テーブルにインデックスが作成されておらず、テーブルでポイントクエリが頻繁に実行される場合は、フルスキャンが必要になります。すべてのレコードは、指定された条件を使用してメモリ内でフィルタリングされるため、CPU 使用率が大幅に増加します。

次の文を実行して、pg_stat_statements 拡張機能の統計情報に基づいて、バッファー読み取り回数が多い上位 5 つの SQL 文をクエリします。

SELECT * FROM pg_stat_statements
ORDER BY shared_blks_hit + shared_blks_read DESC
LIMIT 5;

また、PolarDB for PostgreSQL の組み込みシステムビュー pg_stat_user_tables の統計情報に基づいて、フルスキャン回数が多いテーブルをクエリすることもできます。PolarDB for PostgreSQL

次の文を実行して、約 100,000 個のタプルを持つテーブルからフルスキャンで取得されたタプルの数が上位 5 つのテーブルをクエリします。

SELECT * FROM pg_stat_user_tables
WHERE n_live_tup > 100000 AND seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 5;

非常に長い時間が経過しても終了しない低速なクエリを検出する

組み込みビュー pg_stat_activity を使用して、非常に長い時間が経過しても終了しない SQL 文をクエリできます。これらの SQL 文は、CPU 使用率が高い原因となる可能性があります。

次の文を実行して、長時間続き終了しない上位 5 つの SQL 文をクエリします。

SELECT
    *,
    extract(epoch FROM (NOW() - xact_start)) AS xact_stay,
    extract(epoch FROM (NOW() - query_start)) AS query_stay
FROM pg_stat_activity
WHERE state NOT LIKE 'idle%'
ORDER BY query_stay DESC
LIMIT 5;

次の文を実行し、前述のフルスキャンが多いテーブルを考慮して、10 秒以上かかる低速クエリを取得します。

SELECT * FROM pg_stat_activity
WHERE
    state NOT LIKE 'idle%' AND
    query ILIKE '%Table name%' AND
    NOW() - query_start > interval '10s';