快捷導航

PostgreSQL慢SQL的定位排查方法

更新時間：2024年07月18日 08:46:13 作者：云掣YUNCHE

所謂慢SQL 是指在數(shù)據(jù)庫中執(zhí)行時間超過指定閾值的語句,慢查詢太多,對于業(yè)務(wù)而言,是有很大風險的,可能隨時都會因為某種原因而被觸發(fā),本篇文章將介紹 PostgreSQL 慢 SQL 如何定位排查,需要的朋友可以參考下

前言

所謂慢SQL 是指在數(shù)據(jù)庫中執(zhí)行時間超過指定閾值的語句。慢查詢太多，對于業(yè)務(wù)而言，是有很大風險的，可能隨時都會因為某種原因而被觸發(fā)，并且根據(jù)我們的經(jīng)驗，數(shù)據(jù)庫最常出現(xiàn)的問題，都是因為慢查詢導致數(shù)據(jù)庫慢了，進而導致整個實例 “雪崩” 從而導致線上故障。本篇文章將介紹 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 和錯誤日志放到一個文件中，個人覺得不太好看，尤其是內(nèi)容很多的時候，這里如果有人有其他好方法或使用經(jīng)驗，可以評論區(qū)發(fā)表下。

2. pg_stat_statements 插件

pg_stat_statements 模塊提供一種跟蹤執(zhí)行統(tǒng)計服務(wù)器執(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

可以看到一些關(guān)于 pg_stat_statements 文件信息，表示插件已被安裝完成。如果看到相關(guān)文件表示插件沒有被編譯，需要編譯后才能使用。

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ù)中調(diào)用的語句）指定 none 可以禁用語句統(tǒng)計信息收集。
pg_stat_statements.track = all

# 控制模塊 是否 跟蹤除了 “增刪改查” 之外的命令，默認為 on
pg_stat_statements.track_utility = on

# 指定是否在服務(wù)器關(guān)閉之后還保存語句統(tǒng)計信息，默認為 on 關(guān)機前會持久化保存
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í)行時間
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;
-- 總調(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)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: