pg pg_stat_statements监控模块

转载

mb5fdcadbf25fde 2021-07-23 20:19:00

文章标签 sql postgresql 服务器数据库重启 文章分类 后端开发

一、pg_stat_statements模块简介

pg_stat_statements模块提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息，可以用于统计数据库的资源开销，分析TOP 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 为特定数据库启用它们。

二、安装使用pg_stat_statements

pg_stat_statements是PostgreSQL的核心插件之一。

可以在编译PostgreSQL时安装，也可以单独安装。

编译时安装

make world

make install-world

单独安装

cd src/contrib/pg_stat_statements/

make; make install

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

1.修改配置参数

vi $PGDATA/postgresql.conf

------

shared_preload_libraries='pg_stat_statements'

#加载pg_stat_statements模块

track_io_timing = on

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

track_activity_query_size = 2048

#设置单条SQL的最长长度，超过被截断显示（可选）

#以下配置pg_stat_statements采样参数

pg_stat_statements.max = 10000

# 在pg_stat_statements中最多保留多少条统计信息，通过LRU算法，覆盖老的记录。

pg_stat_statements.track = all

# all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)

pg_stat_statements.track_utility = off

# 是否跟踪非DML语句 (例如DDL，DCL)，on表示跟踪, off表示不跟踪

pg_stat_statements.save = on

# 重启后是否保留统计信息

------

重启数据库

pg_ctl restart -m fast

2.创建pg_stat_statements extension

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

create extension pg_stat_statements;

三、分析TOP SQL

查询pg_stat_statements视图，可以得到统计信息

SQL语句中的一些过滤条件在pg_stat_statements中会被替换成变量，减少重复显示的问题。

pg_stat_statements视图包含了一些重要的信息。

例如：

1. SQL的调用次数，总的耗时，最快执行时间，最慢执行时间，平均执行时间，执行时间的方差（看出抖动），总共扫描或返回或处理了多少行；

2. shared buffer的使用情况，命中，未命中，产生脏块，驱逐脏块；

3. local buffer的使用情况，命中，未命中，产生脏块，驱逐脏块；

4. temp buffer的使用情况，读了多少脏块，驱逐脏块；

5. 数据块的读写时间。

常用的统计sql参考

最耗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

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

最耗共享内存 SQL

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

最耗临时空间 SQL

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

重置统计信息

pg_stat_statements是累积的统计，如果要查看某个时间段的统计，需要打快照

用户也可以定期清理历史的统计信息，通过调用如下SQL

select pg_stat_statements_reset();

四、手册提供的示例验证过程：

修改相关配置参数

# postgresql.conf

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000

pg_stat_statements.track = all

重启数据库，使参数生效

pg_ctl restart -m fast

[postgres@hgdb01 ~]$ createdb bench

[postgres@hgdb01 ~]$ pgbench -i bench

NOTICE: table "pgbench_history" does not exist, skipping

NOTICE: table "pgbench_tellers" does not exist, skipping

NOTICE: table "pgbench_accounts" does not exist, skipping

NOTICE: table "pgbench_branches" does not exist, skipping

creating tables...

100000 of 100000 tuples (100%) done (elapsed 2.84 s, remaining 0.00 s)

vacuum...

set primary keys...

done.

[postgres@hgdb01 ~]$ pgbench -c10 -t300 bench

starting vacuum...end.

transaction type: TPC-B (sort of)

scaling factor: 1

query mode: simple

number of clients: 10

number of threads: 1

number of transactions per client: 300

number of transactions actually processed: 3000/3000

latency average: 16.183 ms

tps = 617.923361 (including connections establishing)

tps = 625.803049 (excluding connections establishing)

[postgres@hgdb01 data]$ psql bench

psql (9.5.7)

Type "help" for help.

bench=# \x

Expanded display is on.

bench=# 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;

-[ RECORD 1 ]-------------------------------------------------------------------

query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?;

calls | 3000

total_time | 19268.9709999999 #该语句执行总时间，毫秒！

rows | 3000 #检索或影响的行总数

hit_percent | 99.9816828849456211 #缓冲池命中率

-[ RECORD 2 ]-------------------------------------------------------------------

query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?;

calls | 3000

total_time | 13394.484

rows | 3000

hit_percent | 99.9780364594772677

-[ RECORD 3 ]-------------------------------------------------------------------

query | vacuum analyze pgbench_accounts

calls | 1

total_time | 2093.405

rows | 0

hit_percent | 99.9002991026919242

-[ RECORD 4 ]-------------------------------------------------------------------

query | alter table pgbench_accounts add primary key (aid)

calls | 1

total_time | 1159.96

rows | 0

hit_percent | 100.0000000000000000

-[ RECORD 5 ]-------------------------------------------------------------------

query | UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?;

calls | 3000

total_time | 1055.42399999999

rows | 3000

hit_percent | 98.4236453201970443

#调用函数清理历史的统计信息

bench=# SELECT pg_stat_statements_reset();

pg_stat_statements_reset

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

(1 row)

#此时收集信息已做清清理

bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /

bench-# nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent

bench-# FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

query | calls | total_time | rows | hit_percent

------------------------------------+-------+------------+------+-------------

SELECT pg_stat_statements_reset(); | 1 | 0.925 | 1 |

(1 row)

至此本实验结束！

附：pg_stat_statements视图

由该模块收集的统计信息可以通过一个名为 pg_stat_statements的视图使用。

这个视图为每一个可区分的数据库 ID、用户 ID 和查询 ID（最多到该模块可以追踪的可区分语句的数量）的组合都包含一行。

该视图的列如下表中所示。

名称

类型

引用

描述

userid

oid

pg_authid.oid

执行该语句的用户的 OID

dbid

oid

pg_database.oid

在其中执行该语句的数据库的 OID

queryid

bigint

内部哈希码，从语句的解析树计算得来

query

text

语句的文本形式

calls

bigint

被执行的次数

total_time

double precision

在该语句中花费的总时间，以毫秒计

min_time

double precision

在该语句中花费的最小时间，以毫秒计

max_time

double precision

在该语句中花费的最大时间，以毫秒计

mean_time

double precision

在该语句中花费的平均时间，以毫秒计

stddev_time

double precision

在该语句中花费时间的总体标准偏差，以毫秒计

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 precision

该语句花在读取块上的总时间，以毫秒计（如果track_io_timing被启用，否则为零）

blk_write_time

double precision

该语句花在写入块上的总时间，以毫秒计（如果track_io_timing被启用，否则为零）

F.29.2. 函数

pg_stat_statements_reset() 返回 void

pg_stat_statements_reset抛弃目前由pg_stat_statements收集的所有统计信息。默认情况下，这个函数只能被超级用户执行。

pg_stat_statements(showtext boolean) returns setof record

pg_stat_statements视图按照一个也叫 pg_stat_statements的函数来定义。客户端可以直接调用 pg_stat_statements函数，并且通过指定 showtext := false来忽略查询文本（即，对应于视图的 query列的OUT参数将返回空值）。这个特性是为了支持不想重复接收长度不定的查询文本的外部工具而设计的。这类工具可以转而自行缓存第一个观察到的查询文本，因为这就是 pg_stat_statements自己所做的全部工作，并且只在需要的时候检索查询文本。因为服务器会把查询文本存储在一个文件中，这种方法可以降低重复检查pg_stat_statements数据的物理 I/O。

F.29.3. 配置参数

pg_stat_statements.max (integer)

pg_stat_statements.max是由该模块跟踪的语句的最大数目（即pg_stat_statements视图中行的最大数量）。如果观测到的可区分的语句超过这个数量，最少被执行的语句的信息将会被丢弃。默认值为 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.save (boolean)