PostgreSQL的扩展（extensions）-常用的扩展之pg_stat_statements
基础信息
OS版本：Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本：16.2
pg软件目录：/home/pg16/soft
pg数据目录：/home/pg16/data
端口：5777
pg_stat_statements 是 PostgreSQL 中的一个非常有用的扩展，它用于跟踪和统计数据库中执行的所有SQL语句的性能。这个扩展可以帮助你识别最频繁运行的查询、哪些查询消耗的时间最长，以及系统的整体工作量，从而对性能瓶颈进行诊断和优化。
主要特性
- 查询统计：收集关于执行的SQL语句的统计信息，包括调用次数、总执行时间、行读取数、行写入数等。
- 性能分析：帮助识别最耗时的查询，以便进行查询优化。
- 系统监控：了解系统运行情况，哪些查询对系统资源消耗最大。
安装和启用
要使用 pg_stat_statements 扩展，你首先需要在 PostgreSQL 安装它，然后在数据库中启用它。
-
安装扩展：这一步通常在 PostgreSQL 的安装过程中就已经完成。如果未完成，你可能需要根据操作系统和 PostgreSQL 的安装方法进行手动安装。
-
启用扩展：在你的目标数据库中运行以下SQL命令来启用
pg_stat_statements扩展。CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
–创建
postgres=# SELECT * FROM pg_extension;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
14270 | plpgsql | 10 | 11 | f | 1.0 | |
(1 row)
postgres=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION
postgres=# SELECT * FROM pg_extension;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
14270 | plpgsql | 10 | 11 | f | 1.0 | |
16423 | pg_stat_statements | 10 | 2200 | t | 1.10 | |
(2 rows)
配置
你可能需要在 postgresql.conf 配置文件中进行一些配置来使用 pg_stat_statements。
-
shared_preload_libraries：需要将
pg_stat_statements添加到这个参数中，以便在 PostgreSQL 启动时加载该扩展。修改配置后，你需要重启 PostgreSQL 服务。shared_preload_libraries = 'pg_stat_statements'
postgres=# select * from pg_settings where name='shared_preload_libraries' and setting ='pg_stat_statements';
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
------+---------+------+----------+------------+------------+---------+---------+--------+---------+---------+----------+----------+-----------+------------+------------+-----------------
(0 rows)
postgres=# alter system set shared_preload_libraries=pg_stat_statements;
ALTER SYSTEM
postgres=# \q
[pg16@test ~]$ pg_ctl restart
waiting for server to shut down....2024-04-24 21:22:28.679 PDT [14806] DEBUG: logger shutting down
done
server stopped
waiting for server to start....2024-04-24 21:22:28.728 PDT [15113] DEBUG: registering background worker "logical replication launcher"
2024-04-24 21:22:28.729 PDT [15113] DEBUG: loaded library "pg_stat_statements"
2024-04-24 21:22:28.729 PDT [15113] DEBUG: mmap(153092096) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory
2024-04-24 21:22:28.750 PDT [15113] LOG: redirecting log output to logging collector process
2024-04-24 21:22:28.750 PDT [15113] HINT: Future log output will appear in directory "log".
done
server started
[pg16@test ~]$ psql -p 5777
psql (16.2)
Type "help" for help.
postgres=# select * from pg_settings where name='shared_preload_libraries' and setting ='pg_stat_statements';
name | setting | unit | category | short_desc | extra_desc | context |
vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
--------------------------+--------------------+------+--------------------------------------------------------+------------------------------------------------+------------+------------+-
--------+--------------------+---------+---------+----------+----------+--------------------+--------------------------------------+------------+-----------------
shared_preload_libraries | pg_stat_statements | | Client Connection Defaults / Shared Library Preloading | Lists shared libraries to preload into server. | | postmaster |
string | configuration file | | | | | pg_stat_statements | /home/pg16/data/postgresql.auto.conf | 3 | f
(1 row)
postgres=#
-
track 配置：你可以通过调整
pg_stat_statements.track参数来控制哪些SQL被统计（例如，仅统计*语句或所有语句）。
使用
启用和配置 pg_stat_statements 后，你可以开始查询收集到的数据。
postgres=# \d pg_stat_statements
View "public.pg_stat_statements"
Column | Type | Collation | Nullable | Default
------------------------+------------------+-----------+----------+---------
userid | oid | | |
dbid | oid | | |
toplevel | boolean | | |
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 | | |
temp_blk_read_time | double precision | | |
temp_blk_write_time | double precision | | |
wal_records | bigint | | |
wal_fpi | bigint | | |
wal_bytes | numeric | | |
jit_functions | bigint | | |
jit_generation_time | double precision | | |
jit_inlining_count | bigint | | |
jit_inlining_time | double precision | | |
jit_optimization_count | bigint | | |
jit_optimization_time | double precision | | |
jit_emission_count | bigint | | |
jit_emission_time | double precision | | |
postgres=#
SELECT query, calls, total_exec_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_exec_time DESC;
这个查询将返回数据库中消耗时间最长的查询，包括它们被调用的次数、总执行时间、返回的行数和缓存命中率。
注意
-
pg_stat_statements保存的统计信息是跨服务器重启累积的，但你可以通过调用pg_stat_statements_reset()函数来清除统计数据。 - 在某些情况下，过多的细节信息可能被参数化，为了获得更具体的查询信息，你可能需要调整
pg_stat_statements.max参数和其他相关配置。
pg_stat_statements 是 PostgreSQL 数据库性能监控和优化的重要工具之一，正确使用和解读它的数据可以大大帮助提高数据库的运行效率。
谨记：心存敬畏，行有所止。