PostgreSQL CPU滿(100%)效能分析及最佳化

標籤：大量   live   3.1   資源   1.2   建立   過多   nat   ams   

PostgreSQL CPU滿(100%)效能分析及最佳化

轉自：https://help.aliyun.com/knowledge_detail/43562.html

 

  

在資料庫營運當中，一個DBA比較常遇到又比較緊急的問題，就是突發的CPU滿（CPU利用率達到100%），導致業務停滯。遇到CPU滿，往往需要從後端資料庫開始排查，追溯到具體SQL，最終定位到業務層。下面是這個問題具體的處理方法。

查看串連數變化

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

1. select count( * ) from pg_stat_activity where state not like ‘%idle‘;

追蹤慢SQL

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

1. 第一種方法是使用pg_stat_statements外掛程式定位慢SQL，步驟如下。

1.1. 如果沒有建立這個外掛程式，需要手動建立。我們要利用外掛程式和資料庫系統裡面的計數資訊（如SQL執行時間累積等），而這些資訊是不斷累積的，包含了曆史資訊。為了更方便的排查當前的CPU滿問題，我們要先重設計數器。

1. create extension pg_stat_statements;
2. select pg_stat_reset();
3. select pg_stat_statements_reset();

1.2. 等待一段時間（例如1分鐘），使計數器積累足夠的資訊。

1.3. 查詢最耗時的SQL（一般就是導致問題的直接原因）。

1. select * from pg_stat_statements order by total_time desc limit 5;

1.4. 查詢讀取Buffer次數最多的SQL，這些SQL可能由於所查詢的資料沒有索引，而導致了過多的Buffer讀，也同時大量消耗了CPU。

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

2. 第二種方法是，直接通過pg_stat_activity視圖，利用下面的查詢，查看當前長時間執行，一直不結束的SQL。這些SQL對應造成CPU滿，也有直接嫌疑。

1. 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. 第3種方法，是從資料表上表掃描（Table Scan）的資訊開始查起，尋找缺失索引的表。資料表如果缺失索引，大部分熱資料又都在記憶體時（例如記憶體8G，熱資料6G），此時資料庫只能使用表掃描，並需要處理已在記憶體中的大量的無關記錄，而耗費大量CPU。特別是對於表記錄數超100的表，一次表掃描佔用大量CPU（基本把一個CPU佔滿），多個串連並發（例如上百串連），把所有CPU佔滿。

3.1. 通過下面的查詢，查出使用表掃描最多的表：

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

3.2. 查詢當前正在啟動並執行訪問到上述表的慢查詢：

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

3.3. 也可以通過pg_stat_statements外掛程式定位涉及到這些表的查詢：

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

處理慢SQL

對於上面的方法查出來的慢SQL，首先需要做的可能是Cancel或Kill掉他們，使業務先恢複：

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

如果這些SQL確實是業務上必需的，則需要對他們做最佳化。這方面有“三板斧”：

1. 對查詢涉及的表，執行ANALYZE <table>或VACUUM ANZLYZE <table>，更新表的統計資訊，使查詢計劃更準確。注意，為避免對業務影響，最好在業務低峰執行。

2. 執行explain <query text>或explain (buffers true, analyze true, verbose true) <query text>命令，查看SQL的執行計畫（注意，前者不會實際執行SQL，後者會實際執行而且能得到詳細的執行資訊），對其中的Table Scan涉及的表，建立索引。

3. 重新編寫SQL，去除掉不必要的子查詢、改寫UNION ALL、使用JOIN CLAUSE固定串連順序等到，都是進一步深度最佳化SQL的手段，這裡不再深入說明。

PostgreSQL CPU滿(100%)效能分析及最佳化（轉）