PostgreSQL:SQL信息统计拓展

  1. 云栖社区>
  2. 袋鼠云技术团队>
  3. 博客>
  4. 正文

PostgreSQL:SQL信息统计拓展

云平_Stephen 2019-08-27 16:24:42 浏览1385
展开阅读全文

概述

pg_stat_statements是PostgreSQL数据库的一个拓展插件,用于收集数据库中的SQL运行信息,例如SQL的总执行时间,调用次数,共享内存命中率等信息。用于监控数据库的性能,是数据库性能监控的重要拓展模块
pg_stat_statements 默认在PG的源码包中就有,无需下载,但是需要手动配置安装一下,下面就来演示一次

下载安装

如果你是编译安装,直接去源码包中的contrib/pg_stat_statements目录,执行编译和安装动作即可
(如果你是yum安装,则需要下载源码包去找到拓展,最好是与你安装的数据库同版本的)
pg的源码包中有一些自带的功能插件

[root@stephen contrib]# pwd
/opt/postgresql-11.4/contrib
[root@stephen contrib]# ls pg_stat_statements
expected pg_stat_statements--1.3--1.4.sql pg_stat_statements.conf
Makefile pg_stat_statements--1.4--1.5.sql pg_stat_statements.control
pg_stat_statements--1.0--1.1.sql pg_stat_statements--1.4.sql pg_stat_statements--unpackaged--1.0.sql
pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.5--1.6.sql sql
pg_stat_statements--1.2--1.3.sql pg_stat_statements.c

编译安装

make && make install

创建拓展

postgres=# create extension pg_stat_statements ;
CREATE EXTENSION

设置参数
安装完成之后,在PG的配置文件中的postgres.conf文件中添加以下参数

shared_preload_libraries='pg_stat_statements'     
track_io_timing = on
track_activity_query_size = 2048
pg_stat_statements.max = 1000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

参数介绍

  • shared_preload_libraries:动态库的加载,可设置多个共享库,多个之间逗号风格。如果设置了不支持的共享库,数据库会重启报错且无法启动
  • pg_stat_statements.max :设置记录最大 的SQL数,默认5000条,如果达到设置值,执行频率最小的SQL会被丢弃
  • pg_stat_statements.track:设置哪类SQL记录,top指的是最外层的SQL,all包含函数中涉及的SQL
  • pg_stat_statements.track_utility:设置是否记录select,update,delete,insert以外的SQL命令
  • pg_stat_statements.save = on:当数据库关闭时是否将SQL信息记录到文件中,一般为on

全部安装完成之后,就在数据库下可以看到一个视图

postgres=# \d
List of relations
-[ RECORD 1 ]--------------
Schema | public
Name | pg_stat_statements
Type | view
Owner | postgres

这就是 pg_stat_statements 记录SQL执行情况的视图

使用

开启之后,任何执行在数据库中的语句都会被记录下来

如,我在数据库中做几次查询

stephen=# select * from datax_test limit 10;

然后就可以在pg_stat_statements这个视图中看到关于这个SQL执行的情况

stephen=# select * from pg_stat_statements where queryid = 2362179633810610809;
-[ RECORD 1 ]-------+----------------------------------
userid        | 10        #执行此SQL的用户oid
dbid        | 16384        #此SQL执行的数据库的oid
queryid        | 2362179633810610809        #此SQL的编号
query        | select * from datax_test limit $1        #此SQL的内容
calls        | 6        #此SQL的调用次数
total_time        | 17.551195        #执行总时间(ms)
min_time        | 0.030288        #最小时间        
max_time        | 17.390669        #最大时间
mean_time        | 2.92519916666667        #平均时间
stddev_time        | 6.46915489671277        #SQL花费时间的表中偏差
rows        | 60        #返回的行数
shared_blks_hit        | 5        #命中的共享内存数据块数
shared_blks_read        | 1        #共享内存度
shared_blks_dirtied        | 0        #产生的共享内存脏块数量
shared_blks_written        | 0        #写入的共享内存数据块数
local_blks_hit        | 0        #临时表命中的块数
local_blks_read        | 0        # 临时表需要读的块数
local_blks_dirtied        | 0        #临时表弄脏的块数
local_blks_written        | 0        #临时表写入的块数
temp_blks_read        | 0        #临时文件读取的块数
temp_blks_written        | 0        #临时文件写入的块数
blk_read_time        | 17.332569        #从磁盘读取花费时间
blk_write_time        | 0        #从磁盘写入花费时间

重置统计
随着数据库运行,统计的pg_stat_statements 视图会越来越大可以通过pg_stat_statements_reset函数来清理已经获取的SQL

stephen=# select count(*) from pg_stat_statements;
 count 
-------
   106
(1 row)

stephen=# select pg_stat_statements_reset();
 pg_stat_statements_reset 
--------------------------
 
(1 row)

stephen=# select count(*) from pg_stat_statements;
 count 
-------
     2
(1 row)

网友评论

登录后评论
0/500
评论
云平_Stephen
+ 关注
所属团队号: 袋鼠云技术团队