PostgreSQL 教程: 优化统计信息收集器

五月 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_activitiestrack_activity_query_sizetrack_commit_timestamptrack_countstrack_functionstrack_io_timing等参数控制。

Ramfs 还是 tmpfs?

有两种主要的基于内存的文件系统:ramfstmpfs

ramfs 可以使用/etc/fstab配置项进行挂载,如

ramfs /var/lib/pgsql_stats_ram ramfs size=1G,uid=postgres,gid=postgres 0 0

然而,有几个缺点。即使我们指定了uidgid,如上所示,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