From: | Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> |
---|---|
To: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
Cc: | Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>, Anastasia Lubennikova <lubennikovaav(at)gmail(dot)com>, PostgreSQL-Dev <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Vacuum: allow usage of more than 1GB of work mem |
Date: | 2017-01-20 09:24:58 |
Message-ID: | CAD21AoD7hipPA1Od=FqOyrKMGcEGkHPQqOjAPACVnqTb2Tm1mg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Jan 19, 2017 at 8:31 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Thu, Jan 19, 2017 at 6:33 AM, Anastasia Lubennikova
> <a(dot)lubennikova(at)postgrespro(dot)ru> wrote:
>> 28.12.2016 23:43, Claudio Freire:
>>
>> Attached v4 patches with the requested fixes.
>>
>>
>> Sorry for being late, but the tests took a lot of time.
>
> I know. Takes me several days to run my test scripts once.
>
>> create table t1 as select i, md5(random()::text) from
>> generate_series(0,400000000) as i;
>> create index md5_idx ON t1(md5);
>> update t1 set md5 = md5((random() * (100 + 500))::text);
>> vacuum;
>>
>> Patched vacuum used 2.9Gb of memory and vacuumed the index in one pass,
>> while for old version it took three passes (1GB+1GB+0.9GB).
>> Vacuum duration results:
>>
>> vanilla:
>> LOG: duration: 4359006.327 ms statement: vacuum verbose t1;
>> patched:
>> LOG: duration: 3076827.378 ms statement: vacuum verbose t1;
>>
>> We can see 30% vacuum speedup. I should note that this case can be
>> considered
>> as favorable to vanilla vacuum: the table is not that big, it has just one
>> index
>> and disk used is a fast fusionIO. We can expect even more gain on slower
>> disks.
>>
>> Thank you again for the patch. Hope to see it in 10.0.
>
> Cool. Thanks for the review and the tests.
>
I encountered a bug with following scenario.
1. Create table and disable autovacuum on that table.
2. Make about 200000 dead tuples on the table.
3. SET maintenance_work_mem TO 1024
4. VACUUM
@@ -729,7 +759,7 @@ lazy_scan_heap(Relation onerel, int options,
LVRelStats *vacrelstats,
* not to reset latestRemovedXid since we want
that value to be
* valid.
*/
- vacrelstats->num_dead_tuples = 0;
+ lazy_clear_dead_tuples(vacrelstats);
vacrelstats->num_index_scans++;
/* Report that we are once again scanning the heap */
I think that we should do vacrelstats->dead_tuples.num_entries = 0 as
well in lazy_clear_dead_tuples(). Once the amount of dead tuples
reached to maintenance_work_mem, lazy_scan_heap can never finish.
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2017-01-20 10:08:49 | Re: Parallel Index Scans |
Previous Message | Ashutosh Sharma | 2017-01-20 09:18:10 | Re: Microvacuum support for Hash Index |