On 2017-07-24 19:11, Claudio Freire wrote:
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.
Your change is small, clear, and currently useful for huge tables under
high update load (until "allowing vacuum to use more than 1GB memory"
is merged).
But it still delays updating fsm until whole first batch of dead tuples
cleared (ie all indices scanned, and all heap pages cleared), and on
such
huge table it will be hours.
On the other hand, if "dead" tuples consumes all useful item pointer (
MaxHeapTuplesPerPage ~ 290 on 8k page), then space, that actually exists
on a page, could not be used until "dead" tuples are converted into
"unused" tuples.
With my patch I've seen that writing FSM until dead tuples cleared
helps a little: while bloating is slowed a little by this change, it
is stopped only after final cleaning of dead tuples.
--
Sokolov Yura aka funny_falcon
Postgres Professional: https://postgrespro.ru
The Russian Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
