pg_stat_statements 属于一个 Extension 模块,用来记录数据库所有的SQL语句的运行信息,这个模块需要设置参数 shared_preload_libraries 的值为 “pg_stat_statements” , 并且需要重启 pg 服务。
pg_stat_statements 模块用于记录 SQL 的运行信息,这个和 Oracle 的视图 v$sql 类似,v$sql 里详细记录了数据库 SQL的运行状态,包括SQL的执行时间,执行次数,逻辑读,解析等信息, 下面介绍下 pg_stat_statements 模块的下载和使用。
pg_stat_statements 视图
从上面看出, pg_stat_statements 视图提供了语句所涉及的行数,执行时间shared_buffer 命中的数据块等信息。
加载 pg_stat_statements 模块
设置 postgresql.conf 模块并重启 PG 服务
1 |
shared_preload_libraries = 'pg_stat_statements' |
备注:参数 shared_preload_libraries 修改后需要重启PG服务.
加载 pg_stat_statements 模块
1 |
skytf=# create extension pg_stat_statements; |
备注:9.1 版本以后,需要使用 “CREATE EXTENSION” 加载外部模块。
使用 pgbench 执行SQL
1 |
pgbench -c 10 -T 30 -n -M prepared -d skytf -U skytf -f script_1.sql |
这里用的是默认的 pgbench 脚本,关于 pgbench 的使用,可以参考之前写的一篇 blog : https://postgres.fun/20110820103909.html
查询执行时间 TOP SQL
查询执行总时间排前五位的 SQL 语句信息
1 |
skytf=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / |
关于 pg_stat_statements_reset() 函数
pg_stat_statements_reset()
pg_stat_statements_reset discards all statistics gathered so far by pg_stat_statements. By default, this function can only be executed by superusers.
备注:上面的解释很明白, pg_stat_statements_reset()函数将会丢弃所有的语句统计信息。
pg_stat_statements_reset 函数测试
1 |
skytf=# select pg_stat_statements_reset(); |
备注:在运行了函数 pg_stat_statements_reset() 之后,视图 pg_stat_statements 的信息被清空了。
总结
- pg_stat_statements 用于分析语句的执行状态,在比较繁忙的生产库上应用对性能分析会有很大帮助;
- pg_stat_statements 模块加载会消耗部分内存,可以通过 pg_stat_statements.max * track_activity_query_size
来计算。这个值是比较小的, 假如 pg_stat_statements.max 值为 10000, 也就消耗了 10 M内存。 - 加载 pg_stat_statements 模块需要重启 PG服务,这点需要注意。
附一: pg_stat_statements 配置参数
pg_stat_statements.max (integer)
pg_stat_statements.max is the maximum number of statements tracked by the module (i.e., the maximum number of rows in the pg_stat_statements view). If more distinct statements than that are observed, information about the least-executed statements is discarded. The default value is 1000. This parameter can only be set at server start.
pg_stat_statements.track (enum)
pg_stat_statements.track controls which statements are counted by the module. Specify top to track top-level statements (those issued directly by clients), all to also track nested statements (such as statements invoked within functions), or none to disable. The default value is top. Only superusers can change this setting.
pg_stat_statements.track_utility (boolean)
pg_stat_statements.track_utility controls whether utility commands are tracked by the module. Utility commands are all those other than SELECT, INSERT, UPDATE and DELETE. The default value is on. Only superusers can change this setting.
pg_stat_statements.save (boolean)
pg_stat_statements.save specifies whether to save statement statistics across server shutdowns. If it is off then statistics are not saved at shutdown nor reloaded at server start. The default value is on. This parameter can only be set in the postgresql.conf file or on the server command line.
原创文章,作者:carmelaweatherly,如若转载,请注明出处:https://blog.ytso.com/tech/database/236419.html