PostgreSQL—如何查找TOP SQL

参考目录 隐藏

1) pg_stat_statements视图

2) 安装pg_stat_statements

阅读完需：约 12 分钟

数据版本为：postgres14 (不同版本之间的配置会有所变化)

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

该模块必须通过在postgresql.conf的shared_preload_libraries中增加pg_stat_statements来载入，因为它需要额外的共享内存。这意味着增加或移除该模块需要一次服务器重启。此外，必须启用查询ID计算器才能使模块处于active状态。如果compute_query_id参数设置为auto 或者 on，或者如果加载了计算查询 id 的第三方模块，则会自动完成此操作。

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

`pg_stat_statements`视图

由该模块收集的统计信息可以通过一个名为 pg_stat_statements的视图使用。这个视图的每一行都包含一个单独的数据库 ID、用户 ID 和查询 ID 以及它是否是顶级语句（最多到该模块可以追踪的可区分语句的数量）的组合。

名称	类型	描述
userid	oid (参考`pg_authid`.`oid`)	执行该语句的用户的 OID
dbid	oid (参考pg_authid.oid)	在其中执行该语句的数据库的 OID
toplevel	bool	如果查询作为顶级 SQL 语句执行则为True（`pg_stat_statements.track`如果设置为`top`则始终为真）
queryid	queryid	用于识别相同规范化查询的哈希码。
query	text	语句的文本形式
plans	bigint	计划语句的次数（如果启用了`pg_stat_statements.track_planning`，否则为零）
total_plan_time	double	计划语句所花费的总时间，以毫秒为单位（如果启用了`pg_stat_statements.track_planning`，否则为零）
min_plan_time	double	计划语句所花费的最短时间，以毫秒为单位（如果启用了`pg_stat_statements.track_planning`，否则为零）
max_plan_time	double	计划语句所花费的最长时间，以毫秒为单位（如果启用了`pg_stat_statements.track_planning`，否则为零）
mean_plan_time	double	计划语句所花费的平均时间，以毫秒为单位（如果启用了`pg_stat_statements.track_planning`，否则为零）
stddev_plan_time	double	计划语句花费的时间的总体标准偏差，以毫秒为单位（如果启用了`pg_stat_statements.track_planning`，否则为零）
calls	bigint	语句被执行的次数
total_exec_time	double	执行语句所花费的总时间，以毫秒为单位
min_exec_time	double	执行语句所花费的最短时间，以毫秒为单位
max_exec_time	double	执行语句所花费的最长时间，以毫秒为单位
mean_exec_time	double	执行语句的平均时间，以毫秒为单位
stddev_exec_time	double	执行语句花费的时间的总体标准偏差，以毫秒为单位
rows	bigint	语句检索或影响的总行数
shared_blks_hit	bigint	语句的共享块缓存命中总数
shared_blks_read	bigint	语句读取的共享块总数
shared_blks_dirtied	bigint	被语句弄脏的共享块总数
shared_blks_written	bigint	语句写入的共享块总数
local_blks_hit	bigint	语句的本地块缓存命中总数
local_blks_read	bigint	语句读取的本地块总数
local_blks_dirtied	bigint	被语句弄脏的本地块总数
local_blks_written	bigint	语句写入的本地块总数
temp_blks_read	bigint	语句读取的临时块总数
temp_blks_written	bigint	语句写入的临时块总数
blk_read_time	double	语句读取块所花费的总时间，以毫秒为单位（如果启用了track_io_timing，否则为零）
blk_write_time	double	语句写入块所花费的总时间，以毫秒为单位（如果启用了track_io_timing，否则为零）
wal_records	bigint	语句生成的 WAL 记录总数
wal_fpi	bigint	语句生成的 WAL 整页图像总数
wal_bytes	wal_bytes	SQL 语句生成的 WAL 总量（以字节为单位）

由于安全性原因，只有超级用户和pg_read_all_stats 角色的成员被允许看到其他用户执行的查询的 SQL 文本或者queryid。不过，如果该视图被安装在其他用户的数据库中，那么他们就能够看见统计信息。

安装`pg_stat_statements`

修改postgresql.conf中的shared_preload_libraries，然后重启PostgreSQL。

shared_preload_libraries = 'pg_stat_statements'

在需要查询TOP SQL的数据库中，创建extension

create extension pg_stat_statements;

如果不修改postgresql.conf，创建的时候会报错

ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

查看版本

SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

在postgresql.conf中设置的参数还有

pg_stat_statements.max (integer)

pg_stat_statements.max是由该模块跟踪的语句的最大数目（即pg_stat_statements视图中行的最大数量）。如果观测到的可区分的语句超过这个数量，最少被执行的语句的信息将会被丢弃。可以在pg_stat_statements_info视图中看到此类信息被丢弃的次数。默认值为 5000。这个参数只能在服务器启动时设置。

pg_stat_statements.track (enum)

pg_stat_statements.track控制哪些语句会被该模块计数。指定top可以跟踪顶层语句（那些直接由客户端发出的语句），指定all还可以跟踪嵌套的语句（例如在函数中调用的语句），指定none可以禁用语句统计信息收集。默认值是top。只有超级用户能够改变这个设置。

pg_stat_statements.track_utility (boolean)

pg_stat_statements.track_utility控制该模块是否会跟踪工具命令。工具命令是除了SELECT、INSERT、 UPDATE和DELETE之外所有的其他命令。默认值是on。只有超级用户能够改变这个设置。

pg_stat_statements.track_planning (boolean)

pg_stat_statements.track_planning控制模块是否跟踪计划操作和持续时间。启用此参数可能会导致明显的性能损失，尤其是当具有相同查询结构的 SQL 语句由许多竞争更新少数pg_stat_statements条目的并发连接执行时。默认值为off。只有超级用户才能更改此设置。

pg_stat_statements.save (boolean)

pg_stat_statements.save指定是否在服务器关闭之后还保存语句统计信息。如果被设置为off，那么关闭后不保存统计信息并且在服务器启动时也不会重新载入统计信息。默认值为on。这个参数只能在postgresql.conf文件中或者在服务器命令行上设置。

该模块要求与pg_stat_statements.max成比例的额外共享内存。注意只要该模块被载入就会消耗这么多的内存，即便pg_stat_statements.track被设置为none。

典型的用法可能是：

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

compute_query_id = on
pg_stat_statements.max = 10000      # 在pg_stat_statements中最多保留多少条统计信息，通过LRU算法，覆盖老的记录。
pg_stat_statements.track = all      # all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)
pg_stat_statements.save = on        # 重启后是否保留统计信息

pg_stat_statements视图包含了一些重要的信息，例如：

SQL的调用次数，总的耗时，最快执行时间，最慢执行时间，平均执行时间，执行时间的方差（看出抖动），总共扫描或返回或处理了多少行；
shared buffer的使用情况，命中，未命中，产生脏块，驱逐脏块。
local buffer的使用情况，命中，未命中，产生脏块，驱逐脏块。
temp buffer的使用情况，读了多少脏块，驱逐脏块。
数据块的读写时间。

分析TOP SQL

最耗IO SQL

如果要跟踪IO消耗的时间，还需要打开如下参数

track_io_timing = on

单次调用最耗IO SQL TOP 10

SELECT
	userid :: regrole,
	dbid,
	query 
FROM
	pg_stat_statements 
ORDER BY
	( blk_read_time + blk_write_time ) / calls DESC 
	LIMIT 10;

总最耗IO SQL TOP 10

SELECT
	userid :: regrole,
	dbid,
	query 
FROM
	pg_stat_statements 
ORDER BY
	( blk_read_time + blk_write_time ) DESC 
	LIMIT 10;

最耗时 SQL

单次调用最耗时 SQL TOP 10

SELECT
	userid :: regrole,
	dbid,
	query,
	mean_exec_time 
FROM
	pg_stat_statements 
ORDER BY
	mean_exec_time DESC 
	LIMIT 10;

总最耗时 SQL TOP 5(最需要关注的是这个)

SELECT
	userid :: regrole,
	dbid,
	query,
	total_exec_time 
FROM
	pg_stat_statements 
ORDER BY
	total_exec_time DESC 
	LIMIT 10;

响应时间抖动最严重 SQL

SELECT
	userid :: regrole,
	dbid,
	query,
	stddev_exec_time 
FROM
	pg_stat_statements 
ORDER BY
	stddev_exec_time DESC 
	LIMIT 10;

最耗共享内存 SQL

SELECT
	userid :: regrole,
	dbid,
	query,
	shared_blks_hit,
	shared_blks_dirtied 
FROM
	pg_stat_statements 
ORDER BY
	( shared_blks_hit + shared_blks_dirtied ) DESC 
	LIMIT 10;

最耗临时空间 SQL

SELECT
	userid :: regrole,
	dbid,
	query 
FROM
	pg_stat_statements 
ORDER BY
	temp_blks_written DESC 
	LIMIT 10;

重置统计信息

pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void

pg_stat_statements_reset丢弃到目前为止与指定的userid， dbid和queryid相对应的pg_stat_statements收集的统计信息。如果有任何参数未被指定，那么将对这些参数使用默认值0（无效），并且将重置与其他参数匹配的统计信息。如果未指定任何参数，或者所有指定的参数均为0（无效），则它将丢弃所有统计信息。如果pg_stat_statements视图的所有统计信息都被丢弃，则pg_stat_statements_info视图的统计信息也将被重置。默认情况下，此功能只能由超级用户执行。可以使用GRANT授予其他人访问权限。

select pg_stat_statements_reset();

例如你想了解9点到10点之间有哪些TOP SQL, 那么可以在9点时执行select pg_stat_statements_reset(); 然后在10点查询最耗时SQL, 得到的就是9点到10点之间的TOP SQL

慢SQL到底慢在哪里？

如果要分析慢SQL到底慢在哪里，使用数据库命令explain (analyze,verbose,timing,costs,buffers,timing) SQL;就可以，再加上一些开关，可以看到更加详细的信息。

开关, 当前会话生效，打印更加详细的信息

set client_min_messages=debug5;
set log_checkpoints = on;
set log_error_verbosity = verbose ;
set log_lock_waits = on;                  
set log_replication_commands = off;
set log_temp_files = 0;
set track_activities = on;
set track_counts = on;
set track_io_timing = on;
set track_functions = 'all';
set trace_sort=on;
set log_statement_stats = off;
set log_parser_stats = on;
set log_planner_stats = on;
set log_executor_stats = on;
set log_autovacuum_min_duration=0;
set deadlock_timeout = '1s';
set debug_print_parse = off;
set debug_print_rewritten = off;
set debug_print_plan = off;
set debug_pretty_print = on;
 
如
explain (analyze,verbose,timing,costs,buffers) select count(*),relkind from pg_class group by relkind order by count(*) desc limit 1;

查看真实的执行计划

begin;  
set local lock_timeout='1s';  
set local statement_timeout=0;  
explain (analyze,verbose,timing,costs,buffers,timing) SQL;  -- SQL代替为要分析的SQL  
rollback;

例子

BEGIN;
	SET LOCAL lock_timeout = '1s';
	SET LOCAL statement_timeout = 0;
	EXPLAIN ( ANALYZE, VERBOSE, timing, costs, buffers, timing ) SELECT ID,
	NAME,
	sex,
	phone,
	account_id,
	ent_code,
	park_id,
	org_id,
	POSITION,
	politics_status,
	management_function,
	birthday,
	DEGREE,
	certificate,
	exist,
	create_time,
	update_time,
	job_code,
	station_name,
	last_grad_college,
	major,
	title,
	chemical_diploma_flag,
	registered_safety_engineer_qualification_flag,
	certificate_num,
	certificate_receive_time,
	certificate_start_time,
	certificate_end_time,
	person_type,
	affiliation_enterprise_code 
	FROM
	ucenter.person;
ROLLBACK;

输出结果

Seq Scan on ucenter.person  (cost=0.00..279.52 rows=5352 width=436) (actual time=0.023..8.656 rows=5352 loops=1)
  Output: id, name, sex, phone, account_id, ent_code, park_id, org_id, "position", politics_status, management_function, birthday, degree, certificate, exist, create_time, update_time, job_code, station_name, last_grad_college, major, title, chemical_diploma_flag, registered_safety_engineer_qualification_flag, certificate_num, certificate_receive_time, certificate_start_time, certificate_end_time, person_type, affiliation_enterprise_code
  Buffers: shared read=226
  I/O Timings: read=1.720
Query Identifier: 6292144838584029685
Planning Time: 0.080 ms
Execution Time: 9.383 ms