ApsaraDB RDS:ApsaraDB RDS for PostgreSQL の CPU 使用率が高い時のトラブルシューティング方法

方法一：pg_stat_statements プラグインを使用する (ApsaraDB RDS for PostgreSQL のみ対応)

この方法では、カウント情報 (SQL の累計実行時間など) を参照します。データベースにプラグインがない場合は手動で作成してください。

1. 次のコマンドを実行し、カウンターをリセットします。

   create extension pg_stat_statements;
   select pg_stat_reset();
   select pg_stat_statements_reset();
   
   

2. カウンターに一定期間 (1 分間など) の情報が蓄積されるまで待ちます。

3. 次のコマンドを参照し、時間のかかる SQL (すなわち問題の原因) をクエリします。

   select * from pg_stat_statements order by total_time desc limit 5;

4. 次のコマンドを参照し、バッファ読み取り回数が一番大きい SQL をクエリします。クエリ対象データのインデックスがないと、バッファーの頻繁な読み取りが発生し、大量な CPU を消費することになります。

   select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

方法二：pg_stat_activity ビューを使用する

pg_stat_activity ビューを使用し、次のコマンドを参照して、長時間実行されている SQL をクエリします。

select datname,
       usename,
       client_addr,
       application_name,
       state,
       backend_start,
       xact_start,
       xact_stay,
       query_start,
       query_stay,
       replace(query, chr(10), ' ') as query
from
  (select pgsa.datname as datname,
          pgsa.usename as usename,
          pgsa.client_addr client_addr,
          pgsa.application_name as application_name,
          pgsa.state as state,
          pgsa.backend_start as backend_start,
          pgsa.xact_start as xact_start,
          extract(epoch
                  from (now() - pgsa.xact_start)) as xact_stay,
          pgsa.query_start as query_start,
          extract(epoch
                  from (now() - pgsa.query_start)) as query_stay,
          pgsa.query as query
   from pg_stat_activity as pgsa
   where pgsa.state != 'idle'
     and pgsa.state != 'idle in transaction'
     and pgsa.state != 'idle in transaction (aborted)') idleconnections
order by query_stay desc
limit 5;

方法三：テーブルスキャンの記録を参照する

この方法では、テーブルスキャンの記録から、インデックスのないテーブルをクエリします。

テーブルのインデックスがなく、ほとんどのホットデータがメモリに格納されている場合 (例えば、8 GB のメモリに 6 GB のホットデータ)、テーブルスキャンを実行してメモリ内の大量の無関係なレコードを処理することになるため、CPU 使用量が増加します。特に 100 レコードを超えるテーブルに対してテーブルスキャンを実行すると、CPU 使用量が 100% 近くになり、複数の接続 (数百など) が同時に開始します。

1. 次のコマンドを参照して、スキャンするレコード数の最も多いテーブルをクエリします。

   select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;

2. 次のコマンドを参照して、上記のテーブルにアクセスしている実行中のスロー SQL をクエリます。

   select * from pg_stat_activity where query ilike '%<table name>%' and query_start - now() > interval '10 seconds';

   説明

   pg_stat_statements プラグインを使用し、次のコマンドを参照して、上記のテーブルに関連している SQL をクエリすることもできます。

   select * from pg_stat_statements where query ilike '%<table>%'order by shared_blks_hit+shared_blks_read desc limit 3;