From: | Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> |
---|---|
To: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
Cc: | Sokolov Yura <funny(dot)falcon(at)postgrespro(dot)ru>, Robert Haas <robertmhaas(at)gmail(dot)com>, PostgreSQL-Dev <pgsql-hackers(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers-owner(at)postgresql(dot)org |
Subject: | Re: Increase Vacuum ring buffer. |
Date: | 2017-07-27 08:30:51 |
Message-ID: | CAD21AoAZb2dK6EutyV-4MGT1m=r-PtAwWpA4X91KT3XobGB-gQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sokolov Yura | 2017-07-27 08:48:29 | Re: Increase Vacuum ring buffer. |
Previous Message | Aleksander Alekseev | 2017-07-27 08:27:29 | Re: A couple of postgresql.conf.sample discrepancies |