postgresql 查找慢sql之二: pg_stat_statements_可以使用pg_stat_statements去查询运行时间长的sql语句。

作者：羊村懒王 | 2024-02-23 09:17:25

踩

可以使用pg_stat_statements去查询运行时间长的sql语句。

pg_stat_statements模块提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息。

该模块必须通过在 postgresql.conf 的 shared_preload_libraries 中增加pg_stat_statements 来载入，因为它需要额外的共享内存。这意味着增加或移除该模块需要一次服务器重启。

当 pg_stat_statements 被载入时，它会跟踪该服务器的所有数据库的统计信息。该模块提供了一个视图 pg_stat_statements 以及函数 pg_stat_statements_reset 和pg_stat_statements 用于访问和操纵这些统计信息。这些视图和函数不是全局可用的，但是可以用 CREATE EXTENSION pg_stat_statements 为特定数据库启用它们。

apt 、yum 安装

需要安装相同版本的contrib包，以ubuntu为例。

# apt install postgresql-contrib-9.6
# dpkg -l |grep -i postgresql-contrib-9.6
ii  postgresql-contrib-9.6              9.6.8-1.pgdg16.04+1                        amd64        additional facilities for PostgreSQL

1
2
3
4

源码编译安装

编译安装时如果是如下方式，则已经安装了。

$ make world
$ make install-world
1
2

否则，需要进入到源码的 \contrib\pg_stat_statements\ 下单独编译安装

$ cd /tmp/postgresql-9.6.8\contrib\pg_stat_statements\
$ make 
$ make install
1
2
3

创建 pg_stat_statements

由于pg_stat_statements 既使用了hook，同时又是以 extension 形式被使用，所以需要做两件事情：

1、修改 postgrsql.conf 的 shared_preload_libraries 值后，重启postgresql。

$ vi postgresql.conf
shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)

# systemctl stop postgresql
# systemctl start postgresql

1
2
3
4
5
6

2、创建 extension
由于pg_stat_statements针对的是数据库级别，所以需要首先进入指定数据库

postgres=# \c peiybdb
peiybdb=# create extension pg_stat_statements；
1
2

创建好后，多出一个视图 pg_stat_statements 和两个函数 pg_stat_statements_reset;

peiybdb=# \df
                                                                                             List of functions
 Schema |           Name           | Result data type |                                                                                                                                                                                                                                                                                                                          Argument data types                                                                                                                                                                                                                                                                                                                           |  Type  
--------+--------------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------
 public | pg_stat_statements       | SETOF record     | showtext boolean, OUT userid oid, OUT dbid oid, OUT queryid bigint, OUT query text, OUT calls bigint, OUT total_time double precision, OUT min_time double precision, OUT max_time double precision, OUT mean_time double precision, OUT stddev_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision | normal
 public | pg_stat_statements_reset | void             |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | normal
(2 rows)

peiybdb=# \df+ pg_stat_statements 
                     List of functions
 Schema |        Name        | Result data type |                                                                                                                                                                                                                                                                                                                          Argument data types                                                                                                                                                                                                                                                                                                                           |  Type  | Volatility | Parallel |  Owner   | Security | Access privileges | Language |      Source code       | Description 
--------+--------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------+----------+----------+----------+-------------------+----------+------------------------+-------------
 public | pg_stat_statements | SETOF record     | showtext boolean, OUT userid oid, OUT dbid oid, OUT queryid bigint, OUT query text, OUT calls bigint, OUT total_time double precision, OUT min_time double precision, OUT max_time double precision, OUT mean_time double precision, OUT stddev_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision | normal | volatile   | safe     | postgres | invoker  |                   | c        | pg_stat_statements_1_3 | 
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

peiybdb=# \d
               List of relations
 Schema |        Name        | Type  |  Owner   
--------+--------------------+-------+----------
 public | pg_stat_statements | view  | postgres

peiybdb=# \d+ pg_stat_statements
                      View "public.pg_stat_statements"
       Column        |       Type       | Modifiers | Storage  | Description 
---------------------+------------------+-----------+----------+-------------
 userid              | oid              |           | plain    | 
 dbid                | oid              |           | plain    | 
 queryid             | bigint           |           | plain    | 
 query               | text             |           | extended | 
 calls               | bigint           |           | plain    | 
 total_time          | double precision |           | plain    | 
 min_time            | double precision |           | plain    | 
 max_time            | double precision |           | plain    | 
 mean_time           | double precision |           | plain    | 
 stddev_time         | double precision |           | plain    | 
 rows                | bigint           |           | plain    | 
 shared_blks_hit     | bigint           |           | plain    | 
 shared_blks_read    | bigint           |           | plain    | 
 shared_blks_dirtied | bigint           |           | plain    | 
 shared_blks_written | bigint           |           | plain    | 
 local_blks_hit      | bigint           |           | plain    | 
 local_blks_read     | bigint           |           | plain    | 
 local_blks_dirtied  | bigint           |           | plain    | 
 local_blks_written  | bigint           |           | plain    | 
 temp_blks_read      | bigint           |           | plain    | 
 temp_blks_written   | bigint           |           | plain    | 
 blk_read_time       | double precision |           | plain    | 
 blk_write_time      | double precision |           | plain    | 
View definition:
 SELECT pg_stat_statements.userid,
    pg_stat_statements.dbid,
    pg_stat_statements.queryid,
    pg_stat_statements.query,
    pg_stat_statements.calls,
    pg_stat_statements.total_time,
    pg_stat_statements.min_time,
    pg_stat_statements.max_time,
    pg_stat_statements.mean_time,
    pg_stat_statements.stddev_time,
    pg_stat_statements.rows,
    pg_stat_statements.shared_blks_hit,
    pg_stat_statements.shared_blks_read,
    pg_stat_statements.shared_blks_dirtied,
    pg_stat_statements.shared_blks_written,
    pg_stat_statements.local_blks_hit,
    pg_stat_statements.local_blks_read,
    pg_stat_statements.local_blks_dirtied,
    pg_stat_statements.local_blks_written,
    pg_stat_statements.temp_blks_read,
    pg_stat_statements.temp_blks_written,
    pg_stat_statements.blk_read_time,
    pg_stat_statements.blk_write_time
   FROM pg_stat_statements(true) pg_stat_statements(userid, dbid, queryid, query, calls, total_time, min_time, max_time, mean_time, stddev_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

可以使用 pg_stat_statements_reset() 函数来重置 pg_stat_statements，方便阶段性的分析慢sql，比如专项优化、大版本上线监控。

peiybdb=# select pg_stat_statements_reset();
 pg_stat_statements_reset 
--------------------------
 
(1 row)
peiybdb=#
peiybdb=#
peiybdb=# select count(1) from pg_stat_statements;
 count 
-------
     1
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12

查找top sql

top io sql

平均单次 io

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 20;

1
2

累计 io

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 20;

1
2

top time sql

平均 time

select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 20;

1
2

累计 time

select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 20;

1
2

不稳定，时快时慢

select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 20;

1
2

top shared memory

select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 20;

1
2

top temp memory

select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 20;

1
2

参考：
http://postgres.cn/docs/9.6/pgstatstatements.html
https://blog.csdn.net/ctypyb2002/article/details/77711802

本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】