On 2017-07-24 19:11, Claudio Freire wrote:
> On Mon, Jul 24, 2017 at 6:37 AM, Sokolov Yura
> <funny.falcon@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.falcon@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