On Thu, Jul 27, 2017 at 6:16 AM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Thu, Jul 27, 2017 at 5:48 PM, Sokolov Yura > <funny.fal...@postgrespro.ru> wrote: >> On 2017-07-27 11:30, Masahiko Sawada wrote: >>> >>> On Tue, Jul 25, 2017 at 2:27 AM, Claudio Freire <klaussfre...@gmail.com> >>> wrote: >>>> >>>> On Mon, Jul 24, 2017 at 2:20 PM, Claudio Freire <klaussfre...@gmail.com> >>>> wrote: >>>>> >>>>> On Mon, Jul 24, 2017 at 2:10 PM, Sokolov Yura >>>>> <funny.fal...@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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
