ApsaraDB for PostgreSQL執行個體CPU使用率高的排查及解決辦法 - ApsaraDB RDS

問題描述

阿里雲雲資料庫RDS PostgreSQL使用過程中，可能會遇到CPU使用率過高甚至達到100%的情況。本文將介紹造成該狀況的常見原因以及解決方案，並通過CPU使用率為100%的典型情境，來分析引起該狀況的排查及其相應的解決方案。

解決方案

CPU使用率到達100%，首先檢查是不是業務高峰活躍串連陡增，而資料庫預留的資源不足。需要查看問題發生時，活躍的串連數是否比平時多很多。對於RDS PostgreSQL，資料庫上的串連數變化，可以從控制台的監控資訊中看到。而當前活躍的串連數，可以直接連接資料庫，使用下列查詢語句得到。

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

追蹤慢SQL

如果活躍串連數的變化處於正常範圍，則可能是當時有效能很差的SQL被大量執行。由於RDS有慢SQL日誌，可以通過這個日誌，定位到當時比較耗時的SQL來進一步做分析。但通常問題發生時，整個系統都處於停滯狀態，所有SQL都慢下來，當時記錄的慢SQL可能非常多，並不容易找到目標。這裡介紹幾種追查慢SQL的方法。

第一種方法是使用pg_stat_statements外掛程式定位慢SQL，僅適用於PostgreSQL，步驟如下。
1. 如果沒有pg_stat_statements外掛程式，需要先手動建立。要利用外掛程式和資料庫系統裡面的計數資訊（如SQL執行時間累積等），而這些資訊是不斷累積的，包含了歷史資訊。為了更方便的排查當前的CPU過高問題，要先使用以下命令重設計數器。
```
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語句，查詢讀取Buffer次數最多的SQL，這些SQL可能由於所查詢的資料沒有索引，而導致了過多的Buffer讀，也同時大量消耗了CPU。
```
select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;
```

第二種方法是直接通過pg_stat_activity視圖，參考以下查詢SQL語句，查看當前長時間執行，一直不結束的SQL。這些SQL也可能造成CPU過高。

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;

第3種方法是從資料表上表掃描（Table Scan）的資訊開始查起，尋找缺失索引的表。資料表如果缺失索引，大部分熱資料又都在記憶體時（例如記憶體8G，熱資料6G），此時資料庫只能使用表掃描，並需要處理已在記憶體中的大量無關記錄，導致耗費大量CPU。特別是對於表記錄數超過100的表，一次表掃描佔用大量CPU（基本把一個CPU佔滿）和多個串連並發（例如上百串連）。
1. 參考以下SQL語句，查出使用表掃描最多的表。
```
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外掛程式定位涉及到這些表的查詢，如下所示。 select * from pg_stat_statements where query ilike '%<table>%'order by shared_blks_hit+shared_blks_read desc limit 3;

處理慢SQL

對於上面的方法查出來的慢SQL，如下所示，首先需要做的是結束掉它們，使業務先恢複。

select pg_cancel_backend(pid) from pg_stat_activity where  query like '%<query text>%' and pid != pg_backend_pid();
select pg_terminate_backend(pid) from pg_stat_activity where  query like '%<query text>%' and pid != pg_backend_pid();

如果這些SQL確實是業務上必需的，則需要對他們做如下最佳化。

對查詢涉及的表，執行ANALYZE [$Table]或VACUUM ANZLYZE [$Table]語句，更新表的統計資訊，使查詢計劃更準確。為避免對業務影響，最好在業務低峰執行。
說明
[$Table]為查詢涉及的表。
選擇一條如下SQL語句執行，查看SQL的執行計畫，第一條SQL語句不會實際執行SQL語句，第二條SQL語句會實際執行而且能得到詳細的執行資訊，對其中的Table Scan涉及的表，建立索引。
```
explain [$Query_Text]
explain (buffers true, analyze true, verbose true) [$Query_Text]
```
說明
[$Query_Text]為SQL檔案或語句。
重新編寫SQL語句，去除掉不必要的子查詢、改寫UNION ALL、使用JOIN CLAUSE固定串連順序等，都是進一步深度最佳化SQL語句的手段，這裡不再深入說明。

適用於

雲資料庫RDS PostgreSQL版