Re: [HACKERS] Increase Vacuum ring buffer. - Mailing list pgsql-hackers

On Thu, Jul 27, 2017 at 6:16 AM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> On Thu, Jul 27, 2017 at 5:48 PM, Sokolov Yura
> <funny.falcon@postgrespro.ru> wrote:
>> 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.
>>
>
> The vacuuming the very large table with no index could also take a
> long time, and it scans and vacuums blocks one by one. So I imagined
> that we can vacuum the FSM once vacuumed a certain amount of blocks.
> And that can avoid bloating table during the long-time vacuum.

Could do that. I'll see about doing something of the sort and
submitting it as a separate patch.