Re: [HACKERS] Increase Vacuum ring buffer.

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.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center