PostgreSQL慢SQL的定位排查方法
更新時間：2024年07月18日 08:46:13 作者：云掣YUNCHE
所謂慢SQL 是指在數(shù)據(jù)庫中執(zhí)行時間超過指定閾值的語句,慢查詢太多,對于業(yè)務而言,是有很大風險的,可能隨時都會因為某種原因而被觸發(fā),本篇文章將介紹 PostgreSQL 慢 SQL 如何定位排查,需要的朋友可以參考下
前言
所謂慢SQL 是指在數(shù)據(jù)庫中執(zhí)行時間超過指定閾值的語句。慢查詢太多，對于業(yè)務而言，是有很大風險的，可能隨時都會因為某種原因而被觸發(fā)，并且根據(jù)我們的經(jīng)驗，數(shù)據(jù)庫最常出現(xiàn)的問題，都是因為慢查詢導致數(shù)據(jù)庫慢了，進而導致整個實例 “雪崩” 從而導致線上故障。 本篇文章將介紹 PostgreSQL 慢 SQL 如何定位排查。
1. 日志參數(shù)設置
MySQL 提供為用戶提供 slow_query_log 參數(shù)來設置慢日志存儲：
# 是否開啟慢日志 slow_query_log = 1 # 慢日志文件路徑 slow_query_log_file = '/logs/slow.log' # 慢日志閾值，取值范圍 [0.000000-3600.000000] long_query_time = 1 PostgreSQL 也提供相關參數(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 和錯誤日志放到一個文件中，個人覺得不太好看，尤其是內容很多的時候，這里如果有人有其他好方法或使用經(jīng)驗，可以評論區(qū)發(fā)表下。
2. pg_stat_statements 插件
pg_stat_statements 模塊提供一種跟蹤執(zhí)行統(tǒng)計服務器執(zhí)行的所有 SQL 語句的手段。該模塊默認是不開啟的，如果需要開啟需要我們手動對其進進行編譯安裝，修改配置文件并重啟數(shù)據(jù)庫，并在使用前手動載入該模塊。
2.1 確認是否安裝插件
安裝插件之前，要先確認插件是否已經(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
可以看到一些關于 pg_stat_statements 文件信息，表示插件已被安裝完成。如果看到相關文件表示插件沒有被編譯，需要編譯后才能使用。
2.2 編譯插件
進入 PostgreSQL 源碼目錄，后執(zhí)行下方命令：
# 進入插件目錄 cd contrib/pg_stat_statements/ # 編譯安裝 make && make install
2.3 載入插件
確認編譯安裝成功后，就可以使用插件了，首先需要修改 postgresql.conf 配置文件：
# 加載動態(tài)庫
shared_preload_libraries='pg_stat_statements'
# 記錄語句的最大行數(shù)，默認 5000
pg_stat_statements.max = 10000
# 控制哪些語句會被該模塊計數(shù)。指定 top 可以跟蹤頂層語句（那些直接由客戶端發(fā)出的語句）
# 指定 all 還可以跟蹤嵌套的語句（例如在函數(shù)中調用的語句）指定 none 可以禁用語句統(tǒng)計信息收集。
pg_stat_statements.track = all
# 控制模塊 是否 跟蹤除了 “增刪改查” 之外的命令，默認為 on
pg_stat_statements.track_utility = on
# 指定是否在服務器關閉之后還保存語句統(tǒng)計信息，默認為 on 關機前會持久化保存
pg_stat_statements.save = on
然后 重啟數(shù)據(jù)庫 后生效。
然后使用 psql 連接 PostgreSQL 服務，載入插件：
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 //調用次數(shù) total_time | 53.363875 //SQL總共執(zhí)行時間 min_time | 53.363875 //SQL最小執(zhí)行時間 max_time | 53.363875 //SQL最大執(zhí)行時間 mean_time | 53.363875 //SQL平均執(zhí)行時間 stddev_time | 0 //SQL花費時間的表中偏差 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ù) local_blks_read | 0 //臨時表需要讀的塊數(shù) local_blks_dirtied | 0 //臨時表弄臟的塊數(shù) local_blks_written | 0 //臨時表寫入的塊數(shù) temp_blks_read | 0 //從臨時文件讀取的塊數(shù) temp_blks_written | 0 //從臨時文件寫入的數(shù)據(jù)塊數(shù) blk_read_time | 0 //從磁盤或者讀取花費的時間 blk_write_time | 0 //從磁盤寫入花費的時間
3. 慢 SQL 排查手段
3.1 查詢當前會話
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 插件，讓插件重新收集當前的數(shù)據(jù)：
create extension pg_stat_statements;
select pg_stat_reset();
select pg_stat_statements_reset();
查看 TOP SQL：
-- 總查詢時間 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; -- 總調用次數(shù) TOP select * from pg_stat_statements order by calls desc limit 5;
到此這篇關于PostgreSQL慢SQL的定位排查方法的文章就介紹到這了,更多相關PostgreSQL慢SQ排查L內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了postgresql SQL語句變量的使用說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgresql插入后返回id的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了PostgreSQL JSONB的匹配和交集問題，本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-09-09
postgreSQL 非count方法算記錄數(shù)操作
這篇文章主要介紹了postgreSQL 非count方法算記錄數(shù)操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PGSQL 實現(xiàn)把字符串轉換成double類型(to_number())
這篇文章主要介紹了PGSQL 實現(xiàn)把字符串轉換成double類型(to_number())，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
postgresql 數(shù)據(jù)庫 與TimescaleDB 時序庫 join 在一起
這篇文章主要介紹了postgresql 數(shù)據(jù)庫 與TimescaleDB 時序庫 join 在一起,需要的朋友可以參考下2020-12-12
postgresql 實現(xiàn)啟動、狀態(tài)查看、關閉
這篇文章主要介紹了postgresql 實現(xiàn)啟動、狀態(tài)查看、關閉的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgreSql分組統(tǒng)計數(shù)據(jù)的實現(xiàn)代碼
這篇文章給大家介紹postgreSql的監(jiān)控記錄表里多條不同時間的數(shù)據(jù)，只取最新的數(shù)據(jù)，并分組統(tǒng)計，本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友參考下吧2020-12-12
這篇文章主要給大家介紹了關于PostgreSQL中使用數(shù)組改進性能的相關資料，文中通過示例代碼以及圖文介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2018-12-12
文章介紹了在PostgreSQL中如何使用內部數(shù)據(jù)類型將對象OID（對象標識符）轉換為對象名,從而簡化系統(tǒng)視圖的關聯(lián)查詢,文章還提供了不同類型OID與對象名之間的轉換關系,并通過示例展示了如何將對象名轉換為OID2024-11-11