Re: 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(at)gmail(dot)com> wrote:
>> On Mon, Jul 24, 2017 at 2:20 PM, Claudio Freire
>> <klaussfreire(at)gmail(dot)com> wrote:
>>> On Mon, Jul 24, 2017 at 2:10 PM, Sokolov Yura
>>> <funny(dot)falcon(at)postgrespro(dot)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