Re: [HACKERS] Increase Vacuum ring buffer.

On 2017-07-27 11:30, Masahiko Sawada wrote:
> On Tue, Jul 25, 2017 at 2:27 AM, Claudio Freire 
> <klaussfreire@gmail.com> wrote:
>> On Mon, Jul 24, 2017 at 2:20 PM, Claudio Freire 
>> <klaussfreire@gmail.com> wrote:
>>> On Mon, Jul 24, 2017 at 2:10 PM, Sokolov Yura
>>> <funny.falcon@postgrespro.ru> wrote:
>>>> On 2017-07-24 19:11, Claudio Freire wrote:
>>>>> 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).
>>> 
>>> In high-bloat conditions, it doesn't take long to accumulate 1GB of
>>> dead tuples (which is about 178M tuples, btw).
>>> 
>>> The index scan takes way longer than the heap scan in that case.
>>> 
>>>> 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.
>>> 
>>> So, true, it will get delayed considerably. But as you realized,
>>> there's not much point in trying to vacuum the FSM sooner, since it
>>> won't be accurate shortly afterwards anyway. Dead line pointers do 
>>> use
>>> up a fair bit of space, especially on narrow tables.
>>> 
>>> In a particular table I have that exhibits this problem, most of the
>>> time is spent scanning the index. It performs dozens of index scans
>>> before it's done, so it would vacuum the FSM quite often enough, even
>>> if I were to increase the mwm setting n-fold.
>> 
>> I hate to reply to myself, but I wanted to add: in any case, the case
>> I'm trying to avoid is the case where the FSM *never* gets vacuumed.
>> That's bad. But it may not be the phenomenon you're experiencing in
>> your tests.
>> 
> 
> I think the frequently vacuuming the FSM during long-time vacuum would
> be worth to have in order to avoid a table bloating. The patch
> triggers to vacuum the FSM after vacuumed the table and indexes but I
> think that we can have a similar mechanism for a table with no index.
> 
> Regards,
> 
> --
> Masahiko Sawada
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
> NTT Open Source Software Center

I could be wrong, but it looks like table without index doesn't
suffer that much. Since there is no indices, there is only one stage -
scanning heap, and no quadratic behavior cause of full dead-tuple array
and repeating index vacuuming.

-- 
Sokolov Yura aka funny_falcon
Postgres Professional: https://postgrespro.ru
The Russian Postgres Company