On Mon, Jul 24, 2017 at 6:37 AM, Sokolov Yura <funny.fal...@postgrespro.ru> wrote: > Good day, Claudio > > > On 2017-07-22 00:27, Claudio Freire wrote: >> >> On Fri, Jul 21, 2017 at 2:41 PM, Sokolov Yura >> <funny.fal...@postgrespro.ru> wrote: >>> >>> >>> My friend noticed, that I didn't said why I bother with autovacuum. >>> Our customers suffers from table bloating. I've made synthetic >>> bloating test, and started experiments with modifying micro- and >>> auto-vacuum. My first attempts were to update FSM early (both in >>> micro and autovacuum) and update it upto root, not only low level. >> >> >> This FSM thing is probably not a bad idea as well. >> >> We're forced to run regular manual vacuums because for some tables >> autovacuums seems to never be enough, no matter how it's configured, >> mostly because it gets canceled all the time. These are high-churn, >> huge tables, so vacuuming them takes hours or days, there's always >> someone with a conflicting lock at some point that ends up canceling >> the autovacuum task. >> >> The above paragraph triggered me to go check, and it seems in those >> cases the FSM never gets vacuumed. That's probably not a good thing, >> but I don't see how to vacuum the FSM after a cancel. So vacuuming the >> FSM from time to time during long-running vacuums seems like a good >> idea at this point. > > > Attached patch changes fsm update: instead of updating only lowest > level, it propagates space increase up to root. > > It slows autovacuum a bit, so that I didn't propose it together with > ring buffer increase.
I was mostly thinking about something like the attached patch. Simple, unintrusive, and shouldn't cause any noticeable slowdown.
From 5da264507058175e614f6ce7c77d2bd0491b1416 Mon Sep 17 00:00:00 2001 From: Claudio Freire <klaussfre...@gmail.com> Date: Mon, 24 Jul 2017 13:09:10 -0300 Subject: [PATCH] Vacuum FSM after each index pass This prevents concurrent writes from accumulating bloat due to recently freed space being invisible in the FSM yet. When vacuum can run for hours or days, this can make a huge difference. --- src/backend/commands/vacuumlazy.c | 3 +++ 1 file changed, 3 insertions(+) diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index fabb2f8d52..4d8d90e833 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -735,6 +735,9 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats, /* Remove tuples from heap */ lazy_vacuum_heap(onerel, vacrelstats); + /* Vacuum the Free Space Map */ + FreeSpaceMapVacuum(onerel); + /* * Forget the now-vacuumed tuples, and press on, but be careful * not to reset latestRemovedXid since we want that value to be -- 2.12.0
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
