合 PG监控插件之pg_stat_statements
Tags： PGPostgreSQL监控整理自网络插件pg_stat_statements
1、什么是pg_stat_statements?
是pg的一个扩展插件，通常用于统计数据库的资源开销，分析TOP SQL 。
对于pg数据库来说，性能调优并不仅仅意味着正确调整postgresql.conf或者内核参数，还意味着我们需要找到性能瓶颈，找出慢查询，并理解系统当下正在做什么。而借助pg_stat_statements，就可以帮助我们确定哪些查询导致了性能低下，以及这些慢查询它们的执行频率等信息。
2、安装pg_stat_statements？
安装
pg_stat_statements是PostgreSQL的核心插件之一。可以在编译PostgreSQL时安装，也可以单独安装。
编译时安装：
1 2 | make world make install-world |
单独安装：
1 2 | cd src/contrib/pg_stat_statements/ make; make install |
配置
- 修改postgresql.conf中的shared_preload_libraries，然后重启PostgreSQL。
1 | alter system set shared_preload_libraries = 'pg_stat_statements'; |
如果要跟踪IO消耗的时间，还需要打开如下参数
1 | alter system set track_io_timing = on; |
设置单条SQL的最长长度，超过被截断显示（可选），指定为每个活动会话跟踪当前正在执行的命令的字节数，用于pg_stat_activity.query字段。 默认值为1024，建议增大该值至 4096或更大。此参数只能在服务器启动时设置。
1 | alter system set track_activity_query_size = 40960 ; |
配置pg_stat_statements采样参数（可选）
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | vi $PGDATA/postgresql.conf pg_stat_statements.max = 100000 # 在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 # 重启后是否保留统计信息 cat >> postgresql.conf <<"EOF" pg_stat_statements.max = 100000 pg_stat_statements.track = all pg_stat_statements.track_utility = off pg_stat_statements.save = on EOF alter system set shared_preload_libraries = 'pg_stat_statements'; alter system set track_io_timing = on; alter system set track_activity_query_size = 40960 ; alter system set pg_stat_statements.max = 100000 ; alter system set pg_stat_statements.track = 'all'; alter system set pg_stat_statements.track_utility = on; alter system set pg_stat_statements.save = on ; |
配置完成后，需要重启PG。
1 | pg_ctl restart -m fast |
- 每个数据库都创建扩展
1 | CREATE EXTENSION pg_stat_statements; |
查看版本
1 | SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'; |
参考：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设置该参数。
1 2 3 4 | 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。
1 2 3 | 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视图字段信息？
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | postgres=# \d pg_stat_statements View "public.pg_stat_statements" Column | Type | Collation | Nullable | Default ---------------------+------------------+-----------+----------+--------- userid | oid | | | dbid | oid | | | queryid | bigint | | | query | text | | | plans | bigint | | | total_plan_time | double precision | | | min_plan_time | double precision | | | max_plan_time | double precision | | | mean_plan_time | double precision | | | stddev_plan_time | double precision | | | calls | bigint | | | total_exec_time | double precision | | | min_exec_time | double precision | | | max_exec_time | double precision | | | mean_exec_time | double precision | | | stddev_exec_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 | | | blk_write_time | double precision | | | wal_records | bigint | | | wal_fpi | bigint | | | wal_bytes | numeric | | | |
| 名字 | 类型 | 参考 | 描述 |
|---|---|---|---|
userid | oid | pg_authid.oid | 执行该语句的用户的OID |
dbid | oid | pg_database.oid | 执行该语句的数据库的OID |
query | text | 有代表性的语句的文本 (多达 track_activity_query_size 字节) | |
calls | bigint | 执行的次数 | |
total_exec_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扩展后可用。
我们可以看到该试图包含丰富的信息。
查询pg_stat_statements视图，可以得到统计信息
SQL语句中的一些过滤条件在pg_stat_statements中会被替换成变量，减少重复显示的问题。
