Re: [HACKERS] vacuum process size

Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom, you already handled this, right?

Someone committed it, not sure if it was me.

I was worried whether vacuum's other expandable lists needed the same
treatment, but Hiroshi and/or Tatsuo seemed to think it wasn't worth the
trouble to change them. So I guess the item is closed.

regards, tom lane

>>>> Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
>>>>> Just for a testing I made a huge table (>2GB and it has 10000000
>>>>> tuples). copy 10000000 tuples took 23 minutes. This is not so
>>>>> bad. Vacuum analyze took 11 minutes, not too bad. After this I created
>>>>> an index on int4 column. It took 9 minutes. Next I deleted 5000000
>>>>> tuples to see how long delete took. I found it was 6
>>>>> minutes. Good. Then I ran into a problem. After that I did vacuum
>>>>> analyze, and seemed it took forever! (actually took 47 minutes). The
>>>>> biggest problem was postgres's process size. It was 478MB! This is not
>>>>> acceptable for me. Any idea?
>>>>
>>>> Yeah, I've complained about that before --- it seems that vacuum takes
>>>> a really unreasonable amount of time to remove dead tuples from an index.
>>>> It's been like that at least since 6.3.2, probably longer.
>>
>> Hiroshi came up with a work around for this(see included
>> patches). After applying it, the process size shrinked from 478MB to
>> 86MB! (the processing time did not descrease, however). According to
>> him, repalloc seems not very effective with large number of calls. The
>> patches probably descreases the number to 1/10.
>> --
>> Tatsuo Ishii
>>
>> -------------------------------------------------------------------------
>> *** vacuum.c.orig Sat Jul 3 09:32:40 1999
>> --- vacuum.c Thu Aug 19 17:34:18 1999
>> ***************
>> *** 2519,2530 ****
>> static void
>> vc_vpinsert(VPageList vpl, VPageDescr vpnew)
>> {
>>
>> /* allocate a VPageDescr entry if needed */
>> if (vpl->vpl_num_pages == 0)
>> ! vpl->vpl_pagedesc = (VPageDescr *) palloc(100 * sizeof(VPageDescr));
>> ! else if (vpl->vpl_num_pages % 100 == 0)
>> ! vpl->vpl_pagedesc = (VPageDescr *) repalloc(vpl->vpl_pagedesc, (vpl->vpl_num_pages + 100) * sizeof(VPageDescr));
vpl-> vpl_pagedesc[vpl->vpl_num_pages] = vpnew;
>> (vpl->vpl_num_pages)++;
>>
>> --- 2519,2531 ----
>> static void
>> vc_vpinsert(VPageList vpl, VPageDescr vpnew)
>> {
>> + #define PG_NPAGEDESC 1000
>>
>> /* allocate a VPageDescr entry if needed */
>> if (vpl->vpl_num_pages == 0)
>> ! vpl->vpl_pagedesc = (VPageDescr *) palloc(PG_NPAGEDESC * sizeof(VPageDescr));
>> ! else if (vpl->vpl_num_pages % PG_NPAGEDESC == 0)
>> ! vpl->vpl_pagedesc = (VPageDescr *) repalloc(vpl->vpl_pagedesc, (vpl->vpl_num_pages + PG_NPAGEDESC) * sizeof(VPageDescr));
vpl-> vpl_pagedesc[vpl->vpl_num_pages] = vpnew;
>> (vpl->vpl_num_pages)++;