thoughts on "prevent wraparound" vacuum
Hello.
Currently I am working a lot with cluster consist a few of big tables.
About 2-3 TB. These tables are heavily updated, some rows are removed, new
rows are inserted... Kind of typical OLTP workload.
Physical table size keeps mostly stable while regular VACUUM is working. It
is fast enough to clean some place from removed rows.
But time to time "to prevent wraparound" comes. And it works like 8-9 days.
During that time relation size starting to expand quickly. Freezing all
blocks in such table takes a lot of time and bloat is generated much more
quickly.
Of course after aggressive vacuum finishes table are not shrink back (some
kind of repacking required). And even after repacking - relation shrinking
causes all cluster to stuck for some time (due exclusive locking, see (1)).
So, I was thinking about it and I saw two possible solutions:
1. Track two block pointers for aggressive vacuum. One is to freeze all
blocks and other is to perform regular vacuum on non-all-visible blocks.
Second one is circular (could process table multiple times while first one
is moving from start to end of the table). And some parameters to spread
resources between pointers is required.
2. Separate "to prevent wraparound" from regular Vacuum to allow them run
concurrently. But it seems to be much more work here.
Could you please share some thoughts on it? Is it worth to be implemented?
Thanks.
[1]: /messages/by-id/c9374921e50a5e8fb1ecf04eb8c6ebc3@postgrespro.ru
/messages/by-id/c9374921e50a5e8fb1ecf04eb8c6ebc3@postgrespro.ru
Hi,
On 2019-07-20 15:35:57 +0300, Michail Nikolaev wrote:
Currently I am working a lot with cluster consist a few of big tables.
About 2-3 TB. These tables are heavily updated, some rows are removed, new
rows are inserted... Kind of typical OLTP workload.Physical table size keeps mostly stable while regular VACUUM is working. It
is fast enough to clean some place from removed rows.But time to time "to prevent wraparound" comes. And it works like 8-9 days.
During that time relation size starting to expand quickly. Freezing all
blocks in such table takes a lot of time and bloat is generated much more
quickly.
Several questions:
- 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).
- 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
Unfortunately even if those are fixed the indexes are still likely going
to be scanned in their entirety - but most of the time not modified
much, so that's not as bad.
Greetings,
Andres Freund
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.