postgresql模块——pg_stat_statements详解和安装测试

网友投稿 4096 2022-05-29

其实很简单,最近可能需要对postgresql进行监控,所以接触了很多相关的监控命令和工具,这边文章主要是记录下工作过程,怕之后会忘记。

转载注明出处:http://blog.csdn.net/lengzijian/article/details/8133471

我想要的功能:记录每条sql的执行时间,能够查询每天执行最慢的top10。

下面先介绍下pg_stat_statements:(翻译)

引文原文地址:http://www.postgresql.org/docs/9.0/interactive/pgstatstatements.html

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

这个模块必须改写配置文件postgresql.conf中的shared_preload_libraries变量(之后讲解如何配置),这是因为他需要额外的共享内存。同时也意味着需要重启服务。

1-先看下pg_stat_statements视图

字段名

类型

引用

说明

userid

oid

pg_authid.oid

执行者id

dbid

oid

pg_database.oid

执行数据库id

query

text

执行的语句

calls

bigint

执行次数

total_time

double precision

执行总时间 (平均值=total_time/calls )

rows

bigint

影响的总行数

shared_blks_hit

bigint

共享块命中数量

shared_blks_read

bigint

共享块读数量

shared_blks_written

bigint

共享块写数量

local_blks_hit

bigint

本地块命中数量

local_blks_read

bigint

本地块读数量

local_blks_written

bigint

本地块写数量

如上视图和函数pg_stat_statements_reset只有在数据库已经正确安装,并且已经执行pg_stat_statements.sql脚本后才会生效。只要pg_stat_statements成功添加,就会跟踪服务器上所有的数据库操作。

处于安全的原因,普通用户不允许查看其他用户执行的语句信息(query),如果视图安装到他的数据库,那么就可以查看相关的统计信息(子健做的实验实在超级用户下:postgres用户postgres库)

注意,如果语句信息(query)一样,不论任何out-of-line变量的值被使用,都会认为这几条声明是相同的。使用out-of-line变量有助于组织语句并且可能回事统计数据更加有用

2­­-函数

pg_stat_statements_reset() returns void

pg_stat_statements_reset丢弃目前由pg_stat_statements统计的所有信息,默认情况下,这个函数只能运行在超级用户下。

3-配置变量

pg_stat_statements.max(integer)

pg_stat_statements.max是最大追踪的统计数据数量(即,视图中的最大行数)。如果数据量大于最大值,那么执行最少的语句将会被丢弃(本人测试,如果语句执行次数都为1时,其次是时间久的数据被丢弃),这个值默认是1000,这个变量在服务启动前设置。

pg_stat_statements.track(enum)

pg_stat_statements.track控制统计数据规则,top用于追踪top-level statement(直接由客户端方发送的),all还会追踪嵌套的statements(例如在函数中调用的statements)

pg_stat_statements.track_utility(boolen)

pg_stat_statements.track_utility控制是否跟踪公共程序命令(utility commands),公共程序命令是SELECT/INSERT/UPDATE/DELETE以外的命令,默认值是开启,只有超级用户可以更改此设置。

pg_stat_statements.save(boolean)

pg_stat_statements.save指定在服务器关闭时,是否保存统计信息。如果设置off,服务关闭时,统计信息将不会保存。默认值是on。这个值只能够在postgresql.conf中或者命令行设置。

该模块需要额外的共享内存,内存大小大致为pg_stat_statements.max* track_activity_query_size。要注意的是,一旦模块被加载,即使pg_stat_statements.track设置为none,共享内存都会被消耗。

上面的都是一些需要掌握的知识,下面开始真正配置pg_stat_statements并且运行

首先要编写postgresql.conf

#postgresql.conf

#------------------------------------------------------------------------------

# PG_STAT_STATEMENTS OPTIONS

#------------------------------------------------------------------------------

shared_preload_libraries = 'pg_stat_statements'

custom_variable_classes = 'pg_stat_statements'

pg_stat_statements.max = 1000

pg_stat_statements.track = all

4-编译安装pg_stat_statements模块

进入postgresql的源码目录:

cd /home/proxy_pg/postgresql-9.1.3/contrib/pg_stat_statements

make

make install

#如果$pgpath/share/extension目录下存在pg_stat_statements--1.0.sql,说明安装成功了

5-加载pg_stat_statements模块

#启动postgresql服务

bin/pg_ctl start -D stat_date/

#加载sql文件

[postgres@slave2 pgsql]$ bin/psql -f share/extension/pg_stat_statements--1.0.sql -p 5499

Use "CREATE EXTENSION pg_stat_statements" to load this file.

#进入数据库做,如下操作:

[postgres@slave2 pgsql]$ bin/psql -p 5499

postgresql模块——pg_stat_statements详解和安装测试

psql (9.1.3)

Type "help" for help.

postgres=# create extension pg_stat_statements;         #创建pg_stat_statements

CREATE EXTENSION

postgres=# SELECT pg_stat_statements_reset();          #清空pg_stat_statements(可以不做)

pg_stat_statements_reset

--------------------------

(1 row)

#我们手动插入10条数据:(这里是自己写的脚本,可以通过http://blog.csdn.net/lengzijian/article/details/7729465下载)

./a.out 1 10

#执行如下命令

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

#可以看到最耗时的5条数据,最后一列表示命中率

虽然成功了,但这也是监控系统中很小的一部分,接下来我的挑战是把各种监控系统融合在意思,做出可视化界面,使监控更友好。

最耗 IO SQL

执行如下命令,查询单次调用最耗 IO SQL TOP 5。

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

执行如下命令,查询总最耗 IO SQL TOP 5。

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

最耗时 SQL

执行如下命令,查询单次调用最耗时 SQL TOP 5。

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

执行如下命令,查询总最耗时 SQL TOP 5。

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

响应时间抖动最严重 SQL

执行如下命令,查询响应时间抖动最严重 SQL。

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

最耗共享内存 SQL

执行如下命令,查询最耗共享内存 SQL。

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

最耗临时空间 SQL

执行如下命令,查询最耗临时空间 SQL。

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

重置统计信息

pg_stat_statements 是累积的统计,如果要查看某个时间段的统计,需要打快照,建议您参见文档《PostgreSQL AWR报告(for 阿里云ApsaraDB PgSQL)》。

您也可以通过执行如下命令,来定期清理历史统计信息。

select pg_stat_statements_reset();

参考文档

PostgreSQL 9.6.2 Documentation — F.29. pg_stat_statements

PostgreSQL SQL

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:如何理解写文档这件事情 ?
下一篇:十大至简规则,用Jupyter Notebook写代码应该这样来
相关文章