调整PostgreSQL的配置以便应对高负载的写操作

 

随着数据库的增长和从理论性验证到正式生产实例的扩展，数据库管理员和系统管理员总是会遇到各种各样的麻烦。

通常，Crunchy数据支持团队的工程师会帮助支持企业项目，这些项目从小的、理论验证系统开始，然后被推广到大规模生产用途。由于这些系统收到的流量负载超出了其原始的理论验证范围，因此在Postgres日志中可能会观察到以下问题：

?

1 2 3 4

LOG:  checkpoints are occurring too frequently (9 seconds apart) HINT:  Consider increasing the configuration parameter "max_wal_size". LOG:  checkpoints are occurring too frequently (2 seconds apart) HINT:  Consider increasing the configuration parameter "max_wal_size".

　

这是数据库的一个经典示例，它没有为高写负载进行适当的调优。在这篇文章中，我们将讨论这意味着什么，导致这个错误的一些可能的原因，以及解决这个问题的一些相对简单的方法。

系统设置

首先，查看系统设置并简要讨论下此错误的含义。

Postgres日志提到了两个特定的东西，检查点和max_wal_size。 调查Postgres实例以观察与这两项有关的任何设置，我们看到以下内容：

?

1 2 3 4 5 6 7 8 9 10 11

[local]:5433 user@exampledb=# select name, setting from pg_settings where name like '%wal_size%' or name like '%checkpoint%' order by name;              name             |  setting  ------------------------------+-----------  checkpoint_completion_target | 0.9  checkpoint_flush_after       | 32  checkpoint_timeout           | 300  checkpoint_warning           | 30  log_checkpoints              | off  max_wal_size                 | 1024  min_wal_size                 | 80 (7 rows)

max_wal_size设置自动检查点之间WAL日志可以增长的最大值。这是一个软限制。在特殊情况下，例如超负载、归档命令失败、或者wal_keep_segments设置太高，WAL大小可能会超过max_wal_size设定的值。

但也要注意，增加此参数可能会增加崩溃恢复所需的时间。默认值为1GB（1024 MB）。

如前几篇文章所述，PostgreSQL的默认配置值通常是保守的，因此在大型服务器上与在资源受限的小型开发机器上同样可以正常工作。因此，对于这里我们已经看到错误消息的系统，在此观察到的max_wal_size的默认值可能太低。

识别问题

接下来，让我们看看为什么max_wal_size低值可能与问题原因有关。

显然，此问题的确切原因因情况而异，但通常来说，当max_wal_size较低且数据库具有大量更新或快速插入时，生成WAL的速度往往会比其归档快，并且超出标准检查点进程的速度。

因此，如果你在Postgres实例上监视磁盘使用情况（应该这样做），你可能还会注意到pg_wal目录的大小急剧增加，因为保留了这些WAL文件。

小贴士：

max_wal_size还有一个对应参数，与之相反：min_wal_size。min_wal_size定义了WAL的最小值。只要在归档过程中WAL磁盘使用率保持在此设置以下，旧的WAL文件将始终被检查点回收以备将来使用，而不是删除。 这对于确保保留足够的WAL空间来处理WAL使用率的峰值很有用，例如在运行大的批量批处理作业时。缺省值为80 MB

如何解决

PostgreSQL在日志文件中帮助性地告知我们应该怎么做：增加max_wal_size。

因此，根据建议，编辑实例配置文件以增加max_wal_size值以匹配系统的工作负载。

对于大多数情况，理想的值是增加max_wal_size的值，以使其至少可以保存一小时的日志。 但是，这里有个警告，不要将此值设置得很高，因为它会增加崩溃恢复所需的时间。如果需要，也可以增加min_wal_size，以便系统可以处理批处理作业和其他异常情况下WAL使用量的峰值。在进行了适当的配置更改并重新加载Postgres之后，我们可以按预期验证新设置的效果：

?

1 2 3 4 5 6 7 8 9 10

name             |  setting  ------------------------------+-----------  checkpoint_completion_target | 0.9  checkpoint_flush_after       | 32  checkpoint_timeout           | 300  checkpoint_warning           | 30  log_checkpoints              | off  max_wal_size                 | 16384  min_wal_size                 | 4096 (7 rows)

有了这些新设置，并仔细监控日志文件和系统使用情况，将系统从开发环境扩展到成熟的生产实例所带来的痛苦将成为遥远的记忆。

 

 

 

https://info.crunchydata.com/blog/tuning-your-postgres-database-for-high-write-loads