调优PostgreSQL 14和更早版本的统计信息收集器

PostgreSQL 15的一项重大改进:PostgreSQL 15:统计收集器不见了？

虽然对这个即将到来的改进高兴，但我们可以在以前的版本中看到一些关于“效率低下”的评论。

这让我意识到，尽管调整stats collector的特性是官方文档和建议的一部分，而且过去有许多关于它的博客帖子，但我很少看到有人在实践中尝试调整它。所以我觉得值得再提醒一下。

PostgreSQL 14及更早版本的选项

简单地说，将生成stats文件的目录(stats_temp_directory)移动到RAMFS或TEMPFS(基于RAM的文件系统)中的某个位置，可以节省IO开销，同时使stats collector更加高效和准确。

PostgreSQL文档也正式建议这样做以减少影响。

为了更好的性能，stats_temp_directory可以指向基于RAM的文件系统，减少了物理I/O需求。当服务器完全关闭时，统计数据的永久副本存储在pg_stat子目录，以便可以在服务器重新启动后保留统计信息。

参考:PostgreSQL文档

需要多大的空间？

stats文件的当前位置可以通过检查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

确定了位置，stat文件一般大小不会高于几百MB。大小取决于数据库和数据库中对象(表和索引)的数量。更重要的是，收集了哪些统计数据，这些统计数据由以下参数控制 track_activities , track_activity_query_size , track_commit_timestamp , track_counts , track_functions  and track_io_timing  as mentioned in PostgreSQL Documentation.

Ramfs or tempfs?

有两种主要的基于RAM的文件系统:ramfs和tempfs。

ramfs可以使用/etc/fstab类似条目

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

然而，有几个缺点。即使我们指定了uid and gid,，如上所示ramfs将被挂载为root:(。我们需要一个脚本或方法来改变PostgreSQL将运行的“postgres”账户的所有权或授予其权限。

ramfs的另一个问题是我们无法使用df命令。但是也有一个优点:ramfs可以动态调整大小，并且可以根据需要动态增长。这消除了估计所需大小的问题。但是，如果需要的话，ramfs不能使用swap，所以存在系统挂起的轻微风险，特别是在那些内存约束高的系统上。

考虑到风险和缺点，ramfs不太受欢迎，tempfs是最常用的。

这是一个 tempfs 的 /etc/fstab 示例:

tmpfs /var/lib/pgsql_stats_tmp tmpfs size=1G,uid=postgres,gid=postgres 0 0

一旦挂载了这个文件系统，这与ramfs不同，它将显示为一个常规文件系统:

$ df -h| grep -E "(tmpfs|ramfs)"
Filesystem Size Used Avail Use% Mounted on...
tmpfs 1.0G 0 1.0G 0% /var/lib/pgsql_stats_tmp

现在的问题是指示PostgreSQL使用这个位置作为stats_temp_directory。请记住，这是一个动态参数，不需要PostgreSQL来反弹。

ALTER SYSTEM SET stats_temp_directory = '/var/lib/pgsql_stats_tmp';

我们只需要向PostgreSQL发送信号(SIGHUP)来重新加载新的配置。

select pg_reload_conf();

参考：

https://www.percona.com/blog/dont-forget-to-tune-stats-collector-for-postgresql-14-and-older/

https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-STATS-SETUP

https://www.percona.com/blog/postgresql-15-stats-collector-gone-whats-new/