The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/12/runtime-config-wal.html
Description:
The PostgreSQL manual contains the following paragraph
https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-TIMEOUT
about the archive_timeout:
> archive_timeout (integer)
> Note that archived files that are closed early due to a forced switch are
still the same length as completely full files. Therefore, it is unwise to
use a very short archive_timeout — it will bloat your archive storage.
archive_timeout settings of a minute or so are usually reasonable.
The description mentions that a short archive_timeout would cause storage
cost but miss the potential performance loss of a short archive_timeout.
Also, while it seems setting the archive_mode to off will disable the effect
of this parameter, it is not the case. Therefore, I think it would be great
to elaborate this description further:
> "A short archive_timeout would also lead to performance penalties if the
wal_sync_method is open_sync. Turning the archive_mode to off would not
mitigate the performance loss"
Rationale:
I test the archive_timeout on PostgreSQL 11 with a small table (50MB) on
Ubuntu 18.04. I run the sysbench with an insert-intensive workload. I tweak
the configuration parameters below:
- wal_sync_method = open_sync
- archive_mode = on
- archive_command = 'cp %p /path-to-data/pg_archive/%f'
- archive_timeout = 1/5/10/30/60
When the archive_timeout is 1, the average latency is 35.61 ms. When the
archive_timeout is 5, the average latency is 8.05ms. When the
archive_timeout is 10, the average latency is 6.8 ms. When the
archive_timeout is 30, the average latency is 5.3 ms. When the
archive_timeout is 60, the average latency is 4.77 ms. When the
archive_timeout is 0, the average latency is 4.71 ms. The result shows that
if the archive_timeout is short and the wal_sync_method is open_sync, the
system will suffer significant performance degradation. The performance
regression will not be mitigated by closing the archive_mode. If I change
the wal_sync_method to other options, the average latency doesn't change
significantly when I change the archive_timeout.
Some guidance books like PostgreSQL 9 Administration Cookbook suggests a
relative short value like 30 seconds. It seems that some users in practice
will use a low timeout value like 5 seconds: e.g.,
https://dba.stackexchange.com/questions/194629/file-creation-dates-of-archived-wal-files-not-in-line-with-archive-timeout-in-po,
perhaps because they are not worried much about the storage cost.
Thus, I think it would be helpful to include the caveat about the
performance impact in the documentation as well.
