Hello.
>- Which version of postgres is this? Newer versions avoid scanning
> unchanged parts of the heap even for freezing (9.6+, with additional
> smaller improvements in 11).
Oh, totally forgot about version and settings...
server_version 10.9 (Ubuntu 10.9-103)
So, "don't vacuum all-frozen pages" included.
> - have you increased the vacuum cost limits? Before PG 12 they're so low
> they're entirely unsuitable for larger databases, and even in 12 you
> should likely increase them for a multi-TB database
Current settings are:
autovacuum_max_workers 8
autovacuum_vacuum_cost_delay 5ms
autovacuum_vacuum_cost_limit 400
autovacuum_work_mem -1
vacuum_cost_page_dirty 40
vacuum_cost_page_hit 1
vacuum_cost_page_miss 10
"autovacuum_max_workers" set to 8 because server needs to process a lot of changing relations.
Settings were more aggressive previously (autovacuum_vacuum_cost_limit was 2800) but it leads to very high IO load causing issues with application performance and stability (even on SSD).
"vacuum_cost_page_dirty" was set to 40 few month ago. High IO write peaks were causing application requests to stuck into WALWriteLock.
After some investigations we found it was caused by WAL-logging peaks.
Such WAL-peaks are mostly consist of such records:
Type N(%) Record size (%) FPI size (%) Combined size (%)
------
Heap2/CLEAN 10520 ( 0.86) 623660 ( 0.21) 5317532 ( 0.53) 5941192 ( 0.46)
Heap2/FREEZE_PAGE 113419 ( 9.29) 6673877 ( 2.26) 635354048 ( 63.12) 642027925 ( 49.31)
another example:
Heap2/CLEAN 196707 ( 6.96) 12116527 ( 1.56) 292317231 ( 37.77) 304433758 ( 19.64)
Heap2/FREEZE_PAGE 1819 ( 0.06) 104012 ( 0.01) 13324269 ( 1.72) 13428281 ( 0.87)
Thanks,
Michail.
