PostgreSQL 開(kāi)啟慢SQL捕獲在排查問(wèn)題時(shí)是個(gè)很有效的手段。根據(jù)慢SQL讓我在工作中真正解決了實(shí)際問(wèn)題，很有幫助。
PostgreSQL 日志支持的輸出格式有 stderr（默認(rèn)）、csvlog 、syslog
一般的錯(cuò)誤跟蹤，只需在配置文件 【postgresql.conf】簡(jiǎn)單設(shè)置幾個(gè)參數(shù)，當(dāng)然還有錯(cuò)誤級(jí)別等要設(shè)置。
logging_collector = on
log_destination = 'stderr'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
SELECT
name,
setting,
vartype,
boot_val,
reset_val
FROM pg_settings
where name in('logging_collector','log_destination','log_directory','log_filename');
默認(rèn)的跟蹤日志記錄在 pgdate/log 中，如 /usr/local/pgsql/data/log 。
其他幾個(gè)重要參數(shù)說(shuō)明：
log_rotation_age = 1440 #minute,多長(zhǎng)時(shí)間創(chuàng)建新的文件記錄日志。0 表示禁擴(kuò)展。
log_rotation_size = 10240 #kb,文件多大后創(chuàng)建新的文件記錄日志。0 表示禁擴(kuò)展。
log_truncate_on_rotation = on #可重用同名日志文件
當(dāng)需要跟蹤SQL語(yǔ)句或者慢語(yǔ)句，得需要設(shè)置以下參數(shù)：
log_statement = all #需設(shè)置跟蹤所有語(yǔ)句，否則只能跟蹤出錯(cuò)信息
log_min_duration_statement = 5000 #milliseconds,記錄執(zhí)行5秒及以上的語(yǔ)句
log_statement：
設(shè)置跟蹤的語(yǔ)句類型，有4種類型：none(默認(rèn)), ddl, mod, all。跟蹤所有語(yǔ)句時(shí)可設(shè)置為 "all"。
log_min_duration_statement：
跟蹤慢查詢語(yǔ)句，單位為毫秒。如設(shè)置 5000，表示日志將記錄執(zhí)行5秒以上的SQL語(yǔ)句。
當(dāng) log_statement=all 和 log_min_duration_statement 同時(shí)設(shè)置時(shí)，將跟蹤所有語(yǔ)句，忽略log_min_duration_statement 設(shè)置。所以需按情況設(shè)置其中一個(gè)或兩個(gè)值。
加載配置
select pg_reload_conf();
show log_min_duration_statement;
針對(duì)某個(gè)用戶或者某數(shù)據(jù)庫(kù)進(jìn)行設(shè)置
alter database test set log_min_duration_statement=5000;
捕獲正在查詢的慢SQL
select * from pg_stat_activity where state>'idle' and now()-query_start > interval '5 s' order by query_start ;
補(bǔ)充：PostgreSQL CPU占用100%性能分析及慢sql優(yōu)化
查看連接數(shù)變化
CPU利用率到達(dá)100%，首先懷疑，是不是業(yè)務(wù)高峰活躍連接陡增，而數(shù)據(jù)庫(kù)預(yù)留的資源不足造成的結(jié)果。我們需要查看下，問(wèn)題發(fā)生時(shí)，活躍的連接數(shù)是否比平時(shí)多很多。
對(duì)于RDS for PG，數(shù)據(jù)庫(kù)上的連接數(shù)變化，可以從控制臺(tái)的監(jiān)控信息中看到。而當(dāng)前活躍的連接數(shù)>可以直接連接數(shù)據(jù)庫(kù)，使用下列查詢語(yǔ)句得到：
select count( * ) from pg_stat_activity where state not like '%idle';
追蹤慢SQL
如果活躍連接數(shù)的變化處于正常范圍，則很大概率可能是當(dāng)時(shí)有性能很差的SQL被大量執(zhí)行導(dǎo)致。由于RDS有慢SQL日志，我們可以通過(guò)這個(gè)日志，定位到當(dāng)時(shí)比較耗時(shí)的SQL來(lái)進(jìn)一步做分析。但通常問(wèn)題發(fā)生時(shí)，整個(gè)系統(tǒng)都處于停滯狀態(tài)，所有SQL都慢下來(lái)，當(dāng)時(shí)記錄的>慢SQL可能非常多，并不容易排查罪魁禍?zhǔn)住＿@里我們介紹幾種在問(wèn)題發(fā)生時(shí)，即介入追查慢SQL的方法。
1、第一種方法是使用pg_stat_statements插件定位慢SQL，步驟如下。
1.1 如果沒(méi)有創(chuàng)建這個(gè)插件，需要手動(dòng)創(chuàng)建。我們要利用插件和數(shù)據(jù)庫(kù)系統(tǒng)里面的計(jì)數(shù)信息（如SQL執(zhí)行時(shí)間累積等），而這些信息是不斷累積的，包含了歷史信息。為了更方便的排查當(dāng)前的CPU滿問(wèn)題，我們要先重置計(jì)數(shù)器。
create extension pg_stat_statements;
select pg_stat_reset();
select pg_stat_statements_reset();
1.2 等待一段時(shí)間（例如1分鐘），使計(jì)數(shù)器積累足夠的信息。
1.3 查詢最耗時(shí)的SQL（一般就是導(dǎo)致問(wèn)題的直接原因）。
select * from pg_stat_statements order by total_time desc limit 5;
1.4 查詢讀取Buffer次數(shù)最多的SQL，這些SQL可能由于所查詢的數(shù)據(jù)沒(méi)有索引，而導(dǎo)致了過(guò)多的Buffer讀，也同時(shí)大量消耗了CPU。
select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;
2、第二種方法是，直接通過(guò)pg_stat_activity視圖，利用下面的查詢，查看當(dāng)前長(zhǎng)時(shí)間執(zhí)行，一直不結(jié)束的SQL。這些SQL對(duì)應(yīng)造成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;
datname | usename | client_addr | application_name | state | backend_start | xact_start | xact_stay | query_start | query_stay |
query
---------+-------------+---------------+--------------------------+--------+-------------------------------+-------------------------------+---------------+-------------------------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
denali | denaliadmin | 10.222.16.45 | pgAdmin III - Query Tool | active | 2018-02-26 22:36:05.603781+00 | 2018-02-26 22:36:13.054396+00 | 187614.245395 | 2018-02-26 22:36:13.054396+00 | 187614.245395 | select * from gen3_search_eu_17q2_20171115_epl.place_name \r where place_id not in (select place_id from gen3_search_eu_17q1_20170308_epl.place_name ) \r and name not in (select name from gen3_search_eu_17q1_20170308_epl.place_name)\r and lang = 'ENG'\r limit 50
denali | denaliadmin | 10.222.16.45 | pgAdmin III - Query Tool | active | 2018-02-26 23:46:24.442846+00 | 2018-02-26 23:46:34.920261+00 | 183392.37953 | 2018-02-26 23:46:34.920261+00 | 183392.37953 | select * from gen3_search_eu_17q2_20171115_epl.place_name \r where place_id not in (select place_id from gen3_search_eu_17q1_20170308_epl.place_name ) \r and name not in (select name from gen3_search_eu_17q1_20170308_epl.place_name)\r and lang = 'ENG'\r limit 50\r
denali | denaliadmin | 10.222.16.45 | pgAdmin III - Query Tool | active | 2018-02-27 01:19:53.83589+00 | 2018-02-27 01:20:01.519778+00 | 177785.780013 | 2018-02-27 01:20:01.519778+00 | 177785.780013 | select * from gen3_search_eu_17q2_20171115_epl.place_name \r where place_id not in (select place_id from gen3_search_eu_17q1_20170308_epl.place_name ) \r and name not in (select name from gen3_search_eu_17q1_20170308_epl.place_name)\r limit 50
denali | denaliadmin | 10.222.16.45 | pgAdmin III - Query Tool | active | 2018-02-27 01:46:05.207888+00 | 2018-02-27 01:47:52.039779+00 | 176115.260012 | 2018-02-27 01:47:52.039779+00 | 176115.260012 | select a.place_id, a.metadata_dictionary_id,a.value, a.lang, b.place_id, b.metadata_dictionary_id, b.value, b.lang\r from gen3_search_eu_17q1_20170308_epl.place_address a \r inner join gen3_search_eu_17q2_20171115_epl.place_address b\r on a.place_id = b.place_id \r where a.metadata_dictionary_id = b.metadata_dictionary_id and a.lang = b.lang and a.value!=b.value and b.place_id not in (select poi_id from gen3_search_eu_17q2_20171115_epl.place_embeded_ids)\r limit 100\r
denali | denaliadmin | 10.224.14.148 | pgAdmin III - Query Tool | active | 2018-02-27 05:05:39.903885+00 | 2018-02-27 05:05:48.827779+00 | 164238.472012 | 2018-02-27 05:05:48.827779+00 | 164238.472012 | select a.place_id, a.metadata_dictionary_id,a.value, a.lang, b.place_id, b.metadata_dictionary_id, b.value, b.lang\r from gen3_search_eu_17q1_20170308_epl.place_address a \r inner join gen3_search_eu_17q2_20171115_epl.place_address b\r on a.place_id = b.place_id \r where a.metadata_dictionary_id = b.metadata_dictionary_id and a.lang = b.lang and a.value!=b.value and b.place_id not in (select poi_id from gen3_search_eu_17q2_20171115_epl.place_embeded_ids)\r limit 100\r
(5 rows)
3、第3種方法，是從數(shù)據(jù)表上表掃描（Table Scan）的信息開(kāi)始查起，查找缺失索引的表。數(shù)據(jù)表如果缺失索引，大部分熱數(shù)據(jù)又都在內(nèi)存時(shí)（例如內(nèi)存8G，熱數(shù)據(jù)6G），此時(shí)數(shù)據(jù)庫(kù)只能使用表掃描，并需要處理已在內(nèi)存中的大量的無(wú)關(guān)記錄，而耗費(fèi)大量CPU。特別是對(duì)于表記錄數(shù)超100的表，一次表掃描占用大量CPU（基本把一個(gè)CPU占滿），多個(gè)連接并發(fā)（例如上百連接），把所有CPU占滿。
3.1 通過(guò)下面的查詢，查出使用表掃描最多的表：
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 查詢當(dāng)前正在運(yùn)行的訪問(wèn)到上述表的慢查詢：
select * from pg_stat_activity where query ilike '%table name>%' and query_start - now() > interval '10 seconds';
3.3 也可以通過(guò)pg_stat_statements插件定位涉及到這些表的查詢：
select * from pg_stat_statements where query ilike '%table>%'order by shared_blks_hit+shared_blks_read desc limit 3;
處理慢SQL
對(duì)于上面的方法查出來(lái)的慢SQL，首先需要做的可能是Cancel或Kill掉他們，使業(yè)務(wù)先恢復(fù)：
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確實(shí)是業(yè)務(wù)上必需的，則需要對(duì)他們做優(yōu)化。這方面有“三板斧”：
1、對(duì)查詢涉及的表，執(zhí)行ANALYZE table>或VACUUM ANZLYZE table>，更新表的統(tǒng)計(jì)信息，使查詢計(jì)劃更準(zhǔn)確。注意，為避免對(duì)業(yè)務(wù)影響，最好在業(yè)務(wù)低峰執(zhí)行。
2、執(zhí)行explain (query text)或explain (buffers true, analyze true, verbose true) (query text)命令，查看SQL的執(zhí)行計(jì)劃（注意，前者不會(huì)實(shí)際執(zhí)行SQL，后者會(huì)實(shí)際執(zhí)行而且能得到詳細(xì)的執(zhí)行信息），對(duì)其中的Table Scan涉及的表，建立索引。
3、重新編寫(xiě)SQL，去除掉不必要的子查詢、改寫(xiě)UNION ALL、使用JOIN CLAUSE固定連接順序等到，都是進(jìn)一步深度優(yōu)化SQL的手段，這里不再深入說(shuō)明。
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- postgresql 查詢字符串中是否包含某字符的操作
- PostgreSQL 實(shí)現(xiàn)子查詢返回多行的案例
- Visual Studio Code(VS Code)查詢PostgreSQL拓展安裝教程圖解
- PostgreSQL 實(shí)現(xiàn)給查詢列表增加序號(hào)操作
- 基于postgresql查詢某個(gè)字段屬于哪個(gè)表的說(shuō)明
- 基于postgreSql 常用查詢小結(jié)
