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

問題の説明

ApsaraDB RDS for PostgreSQLを使用すると、CPU使用率が高い場合や100% 場合があります。この記事では、CPU使用率が100% する典型的なシナリオを分析して、一般的な原因と解決策について説明します。

解決策

説明

以下の点にご注意ください。

Alibaba Cloudインスタンスの設定やデータの変更などのリスクの高い操作を実行する前に、データのセキュリティを確保するために、インスタンスのディザスタリカバリ機能とフォールトトレランス機能を確認することを推奨します。
ECSインスタンスやApsaraDB RDSインスタンスなど、Alibaba Cloudインスタンスの設定とデータを変更できます。インスタンス設定またはデータを変更する前に、スナップショットを作成するか、RDSログバックアップを有効にすることを推奨します。
ユーザーに権限を付与した場合、またはAlibaba Cloud管理コンソールでログオンアカウントやパスワードなどの機密情報を送信した場合は、情報をタイムリーに変更することを推奨します。

CPU使用率が100% 場合は、業務のピーク時にアクティブな接続が急増し、データベースの予約リソースが不足しているかどうかを確認します。問題が発生したときに、アクティブな接続の数が通常よりもはるかに多いことを確認する必要があります。 ApsaraDB RDS For PostgreSQLインスタンスの場合、ApsaraDB RDSコンソールのモニタリングおよびアラートセクションでデータベースへの接続変更を表示できます。現在アクティブな接続では、データベースに接続し、次のステートメントを実行してクエリを実行できます。

選択count( * ) from pg_stat_activity where state not like '% idle';

低速SQL文の追跡

アクティブな接続の数が正常な範囲で変化した場合、原因は多数の低速SQL文である可能性があります。この場合、原因をさらに分析するために、ApsaraDB RDSのスロークエリログを使用して、時間のかかるステートメントを特定できます。ただし、この問題が発生すると、システム全体が静止し、すべてのSQL文の速度が低下します。したがって、ログに大量のスロー文が記録されている可能性があるため、問題の原因となっているスロー文を特定することは困難です。次のセクションでは、低速SQL文を追跡する方法について説明します。

方法1: pg_stat_statementsプラグインを使用して、ApsaraDB RDS for PostgreSQLにのみ適用可能な低速SQLステートメントを識別します。以下の手順を実行します。
1. データベースにプラグインがない場合は、プラグインを作成します。このメソッドは、プラグインと、SQL実行時間の累積などのカウント情報を使用します。しかし、カウント情報は蓄積され続け、ステートメントの履歴情報を含む。したがって、高いCPU使用率をトラブルシューティングするには、次のコマンドを実行してカウンタをリセットする必要があります。
```
pg_stat_reset() を選択します。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_statements order by shared_blks_hit + shared_blks_read desc limit 5;
```

方法2: pg_stat_activityビューを使用し、次のステートメントを参照して、長期間実行されているSQLステートメントを照会します。これらのステートメントは、高いCUP使用を引き起こし得る。

select datname,
       usename,
       client_addr,
       application_name,
       状態、
       backend_start,
       xact_start,
       xact_stay,
       query_start,
       query_stay,
       replace(query, chr(10), ') as query
から
  (datnameとしてpgsa.datnameを選択し、
          enameをusenameとしてpgsa.usし、
          pgsa.client_addr client_addr,
          pgsa.application_nameをapplication_nameとして、
          pgsa.state as state,
          pgsa.backend_startをbackend_startとして、
          pgsa.xact_startをxact_startとして、
          extract (エポック)
                  (now() - pgsa.xact_start) からxact_stayとして、
          pgsa.query_startをquery_startとして、
          extract (エポック)
                  (now() - pgsa.query_start) からquery_stayとして、
          クエリとしてのpgsa.query
   pg_stat_activityからpgsaとして
   ここでpgsa.state! ='idle'
     とpgsa.state! ='idle in transaction'
     とpgsa.state! ='idle in transaction (aborted)') idleconnections
query_stay descによる注文
制限5;

方法3: テーブルスキャン機能によって記録された情報から始めて、インデックスがないテーブルを照会します。テーブルにインデックスがなく、ホットデータのほとんどがメモリに保存されている場合 (たとえば、8 GBのメモリと6 GBのホットデータ) 、データベースはテーブルスキャンのみを使用し、メモリ内の大量の無関係なレコードを処理できます。これにより、大量のCPUリソースが消費されます。特に100を超えるレコードを持つテーブルの場合、テーブルスキャンはCPUリソースのほとんどすべてを占有し、複数の接続、たとえば数百の接続を同時に開始します。
1. 次のSQL文を参照して、テーブルスキャンを最も使用するテーブルを照会します。
```
select * from pg_stat_user_tables where n_live_tup > 100000および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秒 ';
```
  説明
  注: pg_stat_statementsプラグインを使用して、次のステートメントを実行して、テーブルに関連するクエリを見つけることもできます。 select * from pg_statements where query ilike '%<table>%' order by shared_blks_hit + shared_blks_read desc limit 3;

スローSQL文の処理

次の例に示すように、上記の方法を使用して取得した低速SQL文の場合は、それらを終了してビジネスを復元する必要があります。

pg_stat_activityからpg_cancel_backend(pid) を選択し、'%<query text>%' やpid! =pg_backend_pid();
pg_stat_activityからpg_terminate_backend(pid) を選択すると、'%<query text>%' やpid! =pg_backend_pid();

ビジネスにスローSQLステートメントが必要な場合は、次の方法を使用して最適化する必要があります。

取得した低速SQLステートメントに関連するテーブルでANALYZE [$Table] またはVACUUM ANZLYZE [$Table] ステートメントを実行して、テーブルの統計を更新します。これにより、クエリ実行プランをより正確にすることができます。ビジネスへの影響を最小限に抑えるために、ビジネスのオフピーク時にステートメントを実行することをお勧めします。
説明
注: [$Table] は、低速SQLステートメントに関連するテーブルを表します。
次のいずれかのSQL文を選択して、SQL実行計画を実行および表示します。最初のSQL文は実際にはSQL文を実行しません。 2番目のSQL文が実行され、詳細な実行情報が取得されます。この場合、テーブルスキャン機能に関連するテーブルのインデックスが作成されます。
```
explain [$Query_Text]
explain (buffers true, analyze true, verbose true) [$Query_Text] 
```
説明
注: [$Query_Text] はSQLファイルまたはSQL文を表します。
低速SQL文を書き換えて、不要なサブクエリを削除します。ステートメントをさらに最適化するには、UNION ALLを書き直し、固定JOINシーケンスにJOIN句を使用します。詳細はここでは説明しない。

適用範囲

ApsaraDB RDS for PostgreSQL