PostgreSQL慢SQL的定位排查方法
更新時(shí)間：2024年07月18日 08:46:13 作者：云掣YUNCHE
所謂慢SQL 是指在數(shù)據(jù)庫中執(zhí)行時(shí)間超過指定閾值的語句,慢查詢太多,對于業(yè)務(wù)而言,是有很大風(fēng)險(xiǎn)的,可能隨時(shí)都會因?yàn)槟撤N原因而被觸發(fā),本篇文章將介紹 PostgreSQL 慢 SQL 如何定位排查,需要的朋友可以參考下
前言
所謂慢SQL 是指在數(shù)據(jù)庫中執(zhí)行時(shí)間超過指定閾值的語句。慢查詢太多，對于業(yè)務(wù)而言，是有很大風(fēng)險(xiǎn)的，可能隨時(shí)都會因?yàn)槟撤N原因而被觸發(fā)，并且根據(jù)我們的經(jīng)驗(yàn)，數(shù)據(jù)庫最常出現(xiàn)的問題，都是因?yàn)槁樵儗?dǎo)致數(shù)據(jù)庫慢了，進(jìn)而導(dǎo)致整個(gè)實(shí)例 “雪崩” 從而導(dǎo)致線上故障。 本篇文章將介紹 PostgreSQL 慢 SQL 如何定位排查。
1. 日志參數(shù)設(shè)置
MySQL 提供為用戶提供 slow_query_log 參數(shù)來設(shè)置慢日志存儲：
# 是否開啟慢日志 slow_query_log = 1 # 慢日志文件路徑 slow_query_log_file = '/logs/slow.log' # 慢日志閾值，取值范圍 [0.000000-3600.000000] long_query_time = 1 PostgreSQL 也提供相關(guān)參數(shù)： # -1 表示不記錄語句 log_min_duration_statement = 100ms # none, ddl, mod, all # none: 表示不記錄慢 SQL # ddl: ddl 記錄所有數(shù)據(jù)定義語句，例如 CREATE、ALTER 和 DROP 語句 # mod: DDL + INSERT, UPDATE、DELETE、TRUNCATE, 和 COPY FROM # all: 所有語句都會被記錄 log_statement = 'mod' 2023-09-14 14:07:46.695 CST [46385] LOG: statement: update pgbench_accounts set bid = 11 where abalance = 101; 2023-09-14 14:07:53.698 CST [46385] LOG: duration: 7003.518 ms
這是將慢 SQL 和錯(cuò)誤日志放到一個(gè)文件中，個(gè)人覺得不太好看，尤其是內(nèi)容很多的時(shí)候，這里如果有人有其他好方法或使用經(jīng)驗(yàn)，可以評論區(qū)發(fā)表下。
2. pg_stat_statements 插件
pg_stat_statements 模塊提供一種跟蹤執(zhí)行統(tǒng)計(jì)服務(wù)器執(zhí)行的所有 SQL 語句的手段。該模塊默認(rèn)是不開啟的，如果需要開啟需要我們手動對其進(jìn)進(jìn)行編譯安裝，修改配置文件并重啟數(shù)據(jù)庫，并在使用前手動載入該模塊。
2.1 確認(rèn)是否安裝插件
安裝插件之前，要先確認(rèn)插件是否已經(jīng)被編譯好了，可以到 PostgreSQL 安裝目錄查看：
ll ./share/extension 輸出： 總用量 52 -rw-r--r-- 1 root root 1246 9月 14 15:38 pg_stat_statements--1.0--1.1.sql -rw-r--r-- 1 root root 1336 9月 14 15:38 pg_stat_statements--1.1--1.2.sql -rw-r--r-- 1 root root 1454 9月 14 15:38 pg_stat_statements--1.2--1.3.sql -rw-r--r-- 1 root root 345 9月 14 15:38 pg_stat_statements--1.3--1.4.sql -rw-r--r-- 1 root root 305 9月 14 15:38 pg_stat_statements--1.4--1.5.sql -rw-r--r-- 1 root root 1427 9月 14 15:38 pg_stat_statements--1.4.sql -rw-r--r-- 1 root root 376 9月 14 15:38 pg_stat_statements--1.5--1.6.sql -rw-r--r-- 1 root root 806 9月 14 15:38 pg_stat_statements--1.6--1.7.sql -rw-r--r-- 1 root root 191 9月 14 15:38 pg_stat_statements.control -rw-r--r-- 1 root root 449 9月 14 15:38 pg_stat_statements--unpackaged--1.0.sql -rw-r--r-- 1 root root 310 9月 5 10:21 plpgsql--1.0.sql -rw-r--r-- 1 root root 179 9月 5 10:21 plpgsql.control -rw-r--r-- 1 root root 370 9月 5 10:21 plpgsql--unpackaged--1.0.sql
可以看到一些關(guān)于 pg_stat_statements 文件信息，表示插件已被安裝完成。如果看到相關(guān)文件表示插件沒有被編譯，需要編譯后才能使用。
2.2 編譯插件
進(jìn)入 PostgreSQL 源碼目錄，后執(zhí)行下方命令：
# 進(jìn)入插件目錄 cd contrib/pg_stat_statements/ # 編譯安裝 make && make install
2.3 載入插件
確認(rèn)編譯安裝成功后，就可以使用插件了，首先需要修改 postgresql.conf 配置文件：
# 加載動態(tài)庫
shared_preload_libraries='pg_stat_statements'
# 記錄語句的最大行數(shù)，默認(rèn) 5000
pg_stat_statements.max = 10000
# 控制哪些語句會被該模塊計(jì)數(shù)。指定 top 可以跟蹤頂層語句（那些直接由客戶端發(fā)出的語句）
# 指定 all 還可以跟蹤嵌套的語句（例如在函數(shù)中調(diào)用的語句）指定 none 可以禁用語句統(tǒng)計(jì)信息收集。
pg_stat_statements.track = all
# 控制模塊 是否 跟蹤除了 “增刪改查” 之外的命令，默認(rèn)為 on
pg_stat_statements.track_utility = on
# 指定是否在服務(wù)器關(guān)閉之后還保存語句統(tǒng)計(jì)信息，默認(rèn)為 on 關(guān)機(jī)前會持久化保存
pg_stat_statements.save = on
然后 重啟數(shù)據(jù)庫 后生效。
然后使用 psql 連接 PostgreSQL 服務(wù)，載入插件：
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
查詢插件狀態(tài)：
postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+-----------------------------------------------------------
pg_stat_statements | 1.7 | 1.7 | track execution statistics of all SQL statements executed
2.4 插件使用
直接查詢插件視圖，就可以看到 TOP SQL 情況：
postgres=# select * from pg_stat_statements limit 1; -[ RECORD 1 ]-------+------------------------------------------------------------------------ userid | 10 //用戶id dbid | 13547 //數(shù)據(jù)庫oid queryid | 1194713979 //查詢id query | SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' //查詢SQL calls | 1 //調(diào)用次數(shù) total_time | 53.363875 //SQL總共執(zhí)行時(shí)間 min_time | 53.363875 //SQL最小執(zhí)行時(shí)間 max_time | 53.363875 //SQL最大執(zhí)行時(shí)間 mean_time | 53.363875 //SQL平均執(zhí)行時(shí)間 stddev_time | 0 //SQL花費(fèi)時(shí)間的表中偏差 rows | 1 //SQL返回或者影響的行數(shù) shared_blks_hit | 1 //SQL在在shared_buffer中命中的塊數(shù) shared_blks_read | 0 //SQL從page cache或者磁盤中讀取的塊數(shù) shared_blks_dirtied | 0 //SQL語句弄臟的shared_buffer的塊數(shù) shared_blks_written | 0 //SQL語句寫入的塊數(shù) local_blks_hit | 0 //臨時(shí)表中命中的塊數(shù) local_blks_read | 0 //臨時(shí)表需要讀的塊數(shù) local_blks_dirtied | 0 //臨時(shí)表弄臟的塊數(shù) local_blks_written | 0 //臨時(shí)表寫入的塊數(shù) temp_blks_read | 0 //從臨時(shí)文件讀取的塊數(shù) temp_blks_written | 0 //從臨時(shí)文件寫入的數(shù)據(jù)塊數(shù) blk_read_time | 0 //從磁盤或者讀取花費(fèi)的時(shí)間 blk_write_time | 0 //從磁盤寫入花費(fèi)的時(shí)間
3. 慢 SQL 排查手段
3.1 查詢當(dāng)前會話
SELECT pgsa.datname AS database_name
, pgsa.usename AS user_name
, pgsa.client_addr AS 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_time, pgsa.query_start AS query_start
, extract(epoch FROM now() - pgsa.query_start) AS query_time
, pgsa.query AS query_sql
FROM pg_stat_activity pgsa
WHERE pgsa.state != 'idle'
AND pgsa.state != 'idle in transaction'
AND pgsa.state != 'idle in transaction (aborted)'
ORDER BY query_time DESC
LIMIT 5;
3.2 查看 TOP SQL
重啟并重置 pg_stat_statements 插件，讓插件重新收集當(dāng)前的數(shù)據(jù)：
create extension pg_stat_statements;
select pg_stat_reset();
select pg_stat_statements_reset();
查看 TOP SQL：
-- 總查詢時(shí)間 TOP select * from pg_stat_statements order by total_time desc limit 5; -- 總 IO 消耗 TOP select * from pg_stat_statements order by blk_read_time+blk_write_time desc limit 5; -- 總調(diào)用次數(shù) TOP select * from pg_stat_statements order by calls desc limit 5;
到此這篇關(guān)于PostgreSQL慢SQL的定位排查方法的文章就介紹到這了,更多相關(guān)PostgreSQL慢SQ排查L內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了postgresql SQL語句變量的使用說明，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgresql插入后返回id的操作，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
關(guān)于PostgreSQL JSONB的匹配和交集問題
這篇文章主要介紹了PostgreSQL JSONB的匹配和交集問題，本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-09-09
postgreSQL 非count方法算記錄數(shù)操作
這篇文章主要介紹了postgreSQL 非count方法算記錄數(shù)操作，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PGSQL 實(shí)現(xiàn)把字符串轉(zhuǎn)換成double類型(to_number())
這篇文章主要介紹了PGSQL 實(shí)現(xiàn)把字符串轉(zhuǎn)換成double類型(to_number())，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
postgresql 數(shù)據(jù)庫 與TimescaleDB 時(shí)序庫 join 在一起
這篇文章主要介紹了postgresql 數(shù)據(jù)庫 與TimescaleDB 時(shí)序庫 join 在一起,需要的朋友可以參考下2020-12-12
postgresql 實(shí)現(xiàn)啟動、狀態(tài)查看、關(guān)閉
這篇文章主要介紹了postgresql 實(shí)現(xiàn)啟動、狀態(tài)查看、關(guān)閉的操作，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgreSql分組統(tǒng)計(jì)數(shù)據(jù)的實(shí)現(xiàn)代碼
這篇文章給大家介紹postgreSql的監(jiān)控記錄表里多條不同時(shí)間的數(shù)據(jù)，只取最新的數(shù)據(jù)，并分組統(tǒng)計(jì)，本文通過實(shí)例代碼給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友參考下吧2020-12-12
PostgreSQL中使用數(shù)組改進(jìn)性能實(shí)例代碼
這篇文章主要給大家介紹了關(guān)于PostgreSQL中使用數(shù)組改進(jìn)性能的相關(guān)資料，文中通過示例代碼以及圖文介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-12-12
PostgreSQL中如何將對象oid和對象名相互轉(zhuǎn)換
文章介紹了在PostgreSQL中如何使用內(nèi)部數(shù)據(jù)類型將對象OID（對象標(biāo)識符）轉(zhuǎn)換為對象名,從而簡化系統(tǒng)視圖的關(guān)聯(lián)查詢,文章還提供了不同類型OID與對象名之間的轉(zhuǎn)換關(guān)系,并通過示例展示了如何將對象名轉(zhuǎn)換為OID2024-11-11