五月 4, 2024
摘要：在本教程中，您将学习如何在 PostgreSQL 中优化统计信息收集器。
目录
尽管 PostgreSQL 15 采用共享内存改进了累积统计信息系统，但了解如何在以前的版本中优化统计信息收集器仍然非常重要。
以前，累积统计数据是通过 UDP 数据包发送到统计信息收集器进程的，并且只有在通过文件系统持久化后才能被会话读取。PostgreSQL 15 和更高版本中不再有单独的统计信息收集器进程。
背景
如果设置了适当的日志记录级别，则可以在 PostgreSQL 14 的日志中看到如下消息：
2023-12-19 23:22:03.338 [14641] LOG: parameter "log_min_messages" changed to "debug3"
2023-12-19 23:22:03.341 [14644] DEBUG: checkpointer updated shared memory configuration values
2023-12-19 23:22:03.343 [14648] DEBUG: received inquiry for database 0
2023-12-19 23:22:03.343 [14648] DEBUG: writing stats file "pg_stat_tmp/global.stat"
2023-12-19 23:22:03.344 [14648] DEBUG: writing stats file "pg_stat_tmp/db_0.stat"
2023-12-19 23:23:03.406 [14648] DEBUG: received inquiry for database 0
2023-12-19 23:23:03.406 [14648] DEBUG: writing stats file "pg_stat_tmp/global.stat"
2023-12-19 23:23:03.406 [14648] DEBUG: writing stats file "pg_stat_tmp/db_0.stat"
2023-12-19 23:23:03.418 [16112] DEBUG: InitPostgres
2023-12-19 23:23:03.419 [16112] DEBUG: autovacuum: processing database "postgres"
2023-12-19 23:23:03.419 [14648] DEBUG: received inquiry for database 14486
2023-12-19 23:23:03.419 [14648] DEBUG: writing stats file "pg_stat_tmp/global.stat"
2023-12-19 23:23:03.419 [14648] DEBUG: writing stats file "pg_stat_tmp/db_14486.stat"
2023-12-19 23:23:03.419 [14648] DEBUG: writing stats file "pg_stat_tmp/db_0.stat"
上述消息表明，统计信息收集器进程将统计数据写入临时文件，产生了大量 I/O 开销，因为它需要频繁收集信息。在业务高负载期间，可能会出现消息：“LOG: using stale statistics instead of current ones because stats collector is not responding”。
PostgreSQL 14 及更早版本的选项
简单地说，将产生统计数据文件的目录（stats_temp_directory 参数），移动到 RAMFS 或 TMPFS（基于内存的文件系统）中的某个位置，可以降低 IO 开销，同时使统计信息收集器更加高效和准确，然后 autovacuum 工作进程也可以获得更多新的统计信息，以更高效地进行工作。
在大多数系统上，stats_temp_directory的默认位置只是在数据目录中。
PostgreSQL 文档也正式地建议这样做，以减少影响。
来自 PostgreSQL 文档
为了获得更好的性能，可以将
stats_temp_directory指向基于内存的文件系统，从而降低物理 I/O 的需要。当服务器完全关闭时，会在pg_stat子目录中存储一份统计数据的持久化副本，以便在服务器重新启动后可以重用统计数据。
需要多少空间？
可以通过检查stats_temp_directory的值，来检查生成统计信息文件的当前位置。
在 Red Hat 系列的系统上，默认位置将位于数据目录中。
postgres=# show stats_temp_directory ;
stats_temp_directory
----------------------
pg_stat_tmp
在 Debian/Ubuntu 上，它会在/var/run/postgresql中，例如：
postgres=# show stats_temp_directory ;
stats_temp_directory
-----------------------------------------
/var/run/postgresql/14-main.pg_stat_tmp
一旦确定了位置，只需使用du -sh来检查该目录的大小即可。
通常，它不会高于几百 MB。大小取决于这些实例中的数据库和对象（表和索引）的数量。更重要的是，收集了哪些统计数据，这些统计数据由 PostgreSQL 文档中提到的track_activities、track_activity_query_size、track_commit_timestamp、track_counts、track_functions和track_io_timing等参数控制。
Ramfs 还是 tmpfs？
有两种主要的基于内存的文件系统：ramfs 和 tmpfs。
ramfs 可以使用/etc/fstab配置项进行挂载，如
ramfs /var/lib/pgsql_stats_ram ramfs size=1G,uid=postgres,gid=postgres 0 0
然而，有几个缺点。即使我们指定了uid和gid，如上所示，ramfs 也会以 root 账号进行挂载，我们需要一个脚本或方法来更改所有权或授予权限，给运行 PostgreSQL 的 “postgres” 账号。
ramfs 的另一个问题是，我们无法使用df命令查看大小。但它也有一个优点：ramfs 是动态可调整大小的，并且可以根据需要动态增长。这消除了估计所需大小的问题。但是，ramfs 不能按需使用 swap，因此存在系统挂起的轻微风险，特别是在那些存在内存限制的系统上。
考虑到这些风险和缺点，ramfs 不太受欢迎，用户通常会使用 tmpfs。
下面是 tmpfs 的一个/etc/fstab配置样例：
tmpfs /var/lib/pgsql_stats_tmp tmpfs size=1G,uid=postgres,gid=postgres 0 0
挂载该文件系统后，它会显示为一个常规的文件系统，这与 ramfs 不同：
$ df -h
Filesystem Size Used Avail Use% Mounted on
tmpfs 1.0G 0 1.0G 0% /var/lib/pgsql_stats_tmp
...
现在要做的是，通过stats_temp_directory来指示 PostgreSQL 使用这个位置。请记住，这是一个动态参数，不需要 PostgreSQL 进行重启。
ALTER SYSTEM SET stats_temp_directory = '/var/lib/pgsql_stats_tmp';
我们只需要向 PostgreSQL 发出信号（SIGHUP），以重新加载新的配置。
select pg_reload_conf();
另一个选择是使用/dev/shm。但是，建议维护一个由 Postgres 用户帐户拥有的子目录，并设置严格的权限控制。
附加说明
如果我们计划在同一台主机上运行多个实例，那么将stats_temp_directory设置到数据目录以外，还需要额外的注意。每个 PostgreSQL 实例/集群都需要有自己单独的stats_temp_directory。