PostgreSQL之使用pg_stat_statements

PostgreSQL运维技术 2021-06-30

10420

1、什么是pg_stat_statements?

是pg的一个扩展，通常用于统计数据库的资源开销，分析TOP SQL 。

对于pg数据库来说，性能调优并不仅仅意味着正确调整postgresql.conf或者内核参数，还意味着我们需要找到性能瓶颈，找出慢查询，并理解系统当下正在做什么。而借助pg_stat_statements，就可以帮助我们确定哪些查询导致了性能低下，以及这些慢查询它们的执行频率等信息。

2、安装pg_stat_statements？

修改postgresql.conf中的shared_preload_libraries，然后重启PostgreSQL。
```
shared_preload_libraries = ‘pg_stat_statements
```

创建扩展

test=# CREATE EXTENSION pg_stat_statements;

查看版本

SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

3、什么是shared_preload_libraries？

参考：https://developer.aliyun.com/article/8235

Pg支持通过动态库的方式来扩展pg的功能，在调用动态库涉及的函数时会自动加载这些库，但是某些动态库需要预加载。比如pg_stat_statements。

shared_preload_libraries就是指定在服务器启动时预加载一个或多个shared libraries。它包含一个以逗号分隔的库名称列表。条目之间的空白将被忽略，如果需要在名称中包含空格或逗号，则使用双引号包含库名。此参数只能在服务器启动时设置，注意，如果没有找到指定的库, 服务器将无法启动。

此外，还有两个相关参数：local_preload_libraries和session_preload_libraries

local_preload_libraries：这个变量用于指定一个或者多个要在连接开始时预加载的共享库。它包含一个由逗号分隔的库名列表。任何用户都能设置这个选项。能够被这样载入的库只限定于$libdir/plugins下面的so文件。可以使用local_preload_libraries显式指定这个目录。这个特性的目的是允许非特权用户将调试或性能测量库加载到特定的会话中，而不需要显式的load命令。可以使用ALTER ROLE set设置该参数。


postgres=> alter role test set local_preload_libraries='pg_hint_plan';

ALTER ROLE

postgres=> alter role test set local_preload_libraries='$libdir/plugins/pg_hint_plan';

ALTER ROLE

session_preload_libraries：这个变量指定一个或者多个要在连接开始时预载入的共享库，只有超级用户可以更改这个设置。它能动态加载所有目录下面的so文件，如果未指定相对目录，自动到dynamic_library_path指定的目录中搜索so。

alter role all set session_preload_libraries='pg_hint_plan';  
 或   
alter role all set session_preload_libraries='$libdir/pg_hint_plan';

除了预加载(启动数据库时加载，或者连接数据库时加载)，PostgreSQL还有一种方法加载so，即使用LOAD语句。

Load命令将一个共享库文件加载到PostgreSQL服务器的地址空间中，如果文件已经加载，则该命令不执行任何操作。当调用C函数时，包含C函数的共享库文件将自动加载。

对于load命令，普通用户只能动态加载$libdir/plugins下面的so文件。如果未指定相对目录，自动到$libdir/plugins/中搜索so。

超级用户能动态加载所有目录下面的so文件，如果未指定相对目录，自动到dynamic_library_path指定的目录中搜索so。

4、pg_stat_statements视图字段信息？

名字	类型	参考	描述
`userid`	`oid`	`pg_authid.oid`	执行该语句的用户的OID
`dbid`	`oid`	`pg_database.oid`	执行该语句的数据库的OID
`query`	`text`		有代表性的语句的文本 (多达 track_activity_query_size 字节)
`calls`	`bigint`		执行的次数
`total_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，否则为0）
`blk_write_time`	`double precision`		该语句写入块花费的总时间，以毫秒计（如果启用了track_io_timing，否则为0）

这个视图和函数pg_stat_statements_reset，在安装了pg_stat_statements扩展后可用。

我们可以看到该试图包含丰富的信息。

大致有：

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

5、使用pg_stat_statements?

我们可以通过这个插件获得最耗费资源的Top SQL，资源又分多个维度，比如CPU、内存、IO或者执行时间等了，那么，如何获取各个维度层面的Top SQL？

A.总执行时间最长的SQL

SELECT query,
      calls,
      round(total_time::numeric, 2) AS total_time,
      round(mean_time::numeric, 2) AS mean_time,
      round((100 * total_time  sum(total_time) OVER ())::numeric, 2) AS percentage
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

在上面的查询中，我们可以看到某个query的执行频率，以及测试到的总执行时间，同时还计算了每种查询类型的总运行时间的百分比。

B.最耗IO的SQL

SELECT query,
      calls,
      round(total_time::numeric, 2) AS total_time,
      round(blk_read_time::numeric, 2) AS io_read_time,
      round(blk_write_time::numeric, 2) AS io_write_time,
      round((100 * total_time  sum(total_time) OVER ())::numeric, 2) AS percentage
FROM pg_stat_statements
ORDER BY blk_read_time + blk_write_time DESC
LIMIT 10;

注意：如果要跟踪IO消耗的时间，还需要打开trace_io_timing参数。

track_io_timing = on

C.最耗共享内存 SQL

select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

D.查看QPS

with                                               
a as (select sum(calls) s, sum(case when ltrim(query,' ') ~* '^select' then calls else 0 end) q from pg_stat_statements),   
b as (select sum(calls) s, sum(case when ltrim(query,' ') ~* '^select' then calls else 0 end) q from pg_stat_statements , pg_sleep(1))   
select   
b.s-a.s,          -- QPS  
b.q-a.q,          -- 读QPS  
b.s-b.q-a.s+a.q   -- 写QPS  
from a,b;

参考：

https://developer.aliyun.com/article/74421?spm=a2c6h.14164896.0.0.5eb53f234QQUxH

6、使用pg_stat_statements的技巧？

我们可以创建一个表用来保存pg_stat_statements的快照。

这样的话我们就可以分析每个查询的“增量数据”，也就是所谓的“连续监控”。

CREATE TABLE stat_statements_snapshots AS 
SELECT now() AS ts, * FROM pg_stat_statements WHERE false;

b.将pg_stat_statements数据定期收集到快照表中，可以通过watch命令来完成。

INSERT INTO stat_statements_snapshots
  SELECT now(), * FROM pg_stat_statements;
\watch 60

c.当数据收集运行了一段时间并覆盖了我们的目标时间范围时，我们就可以以某种方式来分析数据。

小技巧：

我们可以使用UNLOGGED表修饰符来创建快照表，以绕过频繁插入而产生的事务日志，从而减少磁盘IO和网络负载。
Unlogged：PostgreSQL有一种介于正常表和临时表之间的类型表，称之为unlogged表，在该表新建的索引也属于unlogged，该表在写入数据时候并不将数据写入到持久的write-ahead log文件中，在数据库异常关机或者异常崩溃后该表的数据会被truncate掉，但是在写入性能上会比正常表快几倍。
我们可能不需要存储所有查询的每个间隔上的pg_stat_statement统计数据。因为可能只有一个子集在监控的时间范围内是“活动的”。所以我们可以添加一些WHERE过滤条件，查找特定查询的第一个条目，如果当前时间戳的执行计数器没有进展，则取消它。
另外，我们可以在快照表上添加索引，以减少cpu时间。

7、pg_stat_statements_reset？

它用于以定期清理历史的统计信息。

select pg_stat_statements_reset();

参考：

https://www.cybertec-postgresql.com/en/a-quick-pg_stat_statements-troubleshooting-hack/
https://developer.aliyun.com/article/74421?spm=a2c6h.14164896.0.0.5eb53f234QQUxH

postgresql

文章转载自PostgreSQL运维技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL之使用pg_stat_statements

C.最耗共享内存 SQL

评论