PostgreSQL的扩展（extensions）-常用的扩展之pg_stat_statements

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
1
2
3
4
5
6

pg_stat_statements 是 PostgreSQL 中的一个非常有用的扩展，它用于跟踪和统计数据库中执行的所有SQL语句的性能。这个扩展可以帮助你识别最频繁运行的查询、哪些查询消耗的时间最长，以及系统的整体工作量，从而对性能瓶颈进行诊断和优化。

主要特性

• 查询统计：收集关于执行的SQL语句的统计信息，包括调用次数、总执行时间、行读取数、行写入数等。
• 性能分析：帮助识别最耗时的查询，以便进行查询优化。
• 系统监控：了解系统运行情况，哪些查询对系统资源消耗最大。

安装和启用

要使用 pg_stat_statements 扩展，你首先需要在 PostgreSQL 安装它，然后在数据库中启用它。

1. 安装扩展：这一步通常在 PostgreSQL 的安装过程中就已经完成。如果未完成，你可能需要根据操作系统和 PostgreSQL 的安装方法进行手动安装。

2. 启用扩展：在你的目标数据库中运行以下SQL命令来启用 pg_stat_statements 扩展。

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   1

–创建

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

配置

你可能需要在 postgresql.conf 配置文件中进行一些配置来使用 pg_stat_statements。

• shared_preload_libraries：需要将 pg_stat_statements 添加到这个参数中，以便在 PostgreSQL 启动时加载该扩展。修改配置后，你需要重启 PostgreSQL 服务。

  shared_preload_libraries = 'pg_stat_statements'
  1

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

• 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;
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

这个查询将返回数据库中消耗时间最长的查询，包括它们被调用的次数、总执行时间、返回的行数和缓存命中率。

注意

• pg_stat_statements 保存的统计信息是跨服务器重启累积的，但你可以通过调用 pg_stat_statements_reset() 函数来清除统计数据。
• 在某些情况下，过多的细节信息可能被参数化，为了获得更具体的查询信息，你可能需要调整 pg_stat_statements.max 参数和其他相关配置。

pg_stat_statements 是 PostgreSQL 数据库性能监控和优化的重要工具之一，正确使用和解读它的数据可以大大帮助提高数据库的运行效率。

谨记：心存敬畏，行有所止。