[PATCH] Vacuum: Update FSM more frequently
Claudio Freire <klaussfreire@gmail.com>
From: Claudio Freire <klaussfreire@gmail.com>
To: PostgreSQL-Dev <pgsql-hackers@postgresql.org>
Date: 2017-07-29T00:42:08Z
Lists: pgsql-hackers
Attachments
- 0001-Vacuum-Update-FSM-more-frequently.patch (text/x-patch) patch 0001
As discussed in the vacuum ring buffer thread: Make Vacuum update the FSM more frequently, to avoid the case where autovacuum fails to reach the point where it updates the FSM in highly contended tables. Introduce a tree pruning threshold to FreeSpaceMapVacuum that avoids recursing into branches that already contain enough free space, to avoid having to traverse the whole FSM and thus induce quadratic costs. Intermediate FSM vacuums are only supposed to make enough free space visible to avoid extension until the final (non-partial) FSM vacuum. Run partial FSM vacuums after each index pass, which is a point at which whole ranges of the heap have been thorougly cleaned, and we can expect no further updates to those ranges of the FSM save for concurrent activity. When there are no indexes, and thus no index passes, run partial FSM vacuums every 8GB of dirtied pages or 1/8th of the relation, whichever is highest. This allows some partial work to be made visible without incurring quadratic cost. In any case, FSM are small in relation to the table, so even when quadratic cost is involved, it should not be problematic. Index passes already incur quadratic cost, and the addition of the FSM is unlikely to be measurable. Run a partial FSM vacuum with a low pruning threshold right at the beginning of the VACUUM to finish up any work left over from prior canceled vacuum runs, something that is common in highly contended tables when running only autovacuum. Autovacuum canceling is thus handled by updating the FSM first-thing when autovacuum retries vacuuming a relation. I attempted to add an autovacuum work item for performing the FSM update shortly after the cancel, but that had some issues so I abandoned the approach. For one, it would sometimes crash when adding the work item from inside autovacuum itself. I didn't find the cause of the crash, but I suspect AutoVacuumRequestWork was being called in a context where it was not safe. In any case, the way work items work didn't seem like it would have worked for our purposes anyway, since they will only ever be processed after processing all tables in the database, something that could take ages, the work items are limited to 256, which would make the approach troublesome for databases with more than 256 tables that trigger this case, and it required de-duplicating work items which had quadratic cost without major refactoring of the feature. So, patch attached. I'll add it to the next CF as well.
Commits
-
Do index FSM vacuuming sooner.
- c79f6df75dd3 11.0 landed
-
Remove UpdateFreeSpaceMap(), use FreeSpaceMapVacuumRange() instead.
- a063baaced27 11.0 landed
-
While vacuuming a large table, update upper-level FSM data every so often.
- 851a26e26637 11.0 landed
-
Simplify autovacuum work-item implementation
- 31ae1638ce35 11.0 cited