Re: [WIP] [B-Tree] Retail IndexTuple deletion

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: PostgreSQL-Dev <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [WIP] [B-Tree] Retail IndexTuple deletion
Date: 2018-09-06 10:26:47
Message-ID: 709434ae-b211-a936-4eec-73ce09095793@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

And background worker patch in attachment.

05.09.2018 15:25, Andrey Lepikhov пишет:
> Hi,
>
> I prepared next version of Background worker (cleaner) based on a retail
> indextuple deletion patch.
> This version shows stable behavior on regression tests and pgbench
> workloads.
>
> In this version:
> 1. Only AccessShareLock are acquired on a cleanup of heap and index
> relations.
> 2. Some 'aggressive' cleanup strategy introduced - conditional cleanup
> locks not used.
> 3. Cleanup only an in-memory blocks.
> 4. The Cleaner calls heap_page_prune() before cleanup a block.
>
> Benchmarks
> ---------
>
> Two factors were evaluated: performance (tps) and relations blowing.
>
> Before each test some rarefaction of pgbench_accounts was modeled by
> deletion 10% of tuples at each block.
> Also, I tested normal and Gaussian distribution of queries on
> pgbench_accounts relation.
> Autovacuum uses default settings.
>
> Script:
> pgbench -i -s 10
> psql -c $"DELETE FROM pgbench_accounts WHERE (random() < 0.1);"
> psql -c $"VACUUM;"
> psql -c $"CREATE INDEX pgbench_accounts_ext ON public.pgbench_accounts
> USING btree (abalance);" &&
> pgbench -T 3600 -c 32 -j 8 -M prepared -P 600
>
> NORMAL distribution:
> average tps = 1045 (cleaner); = 1077 (autovacuum)
>
> Relations size at the end of test, MB:
> pgbench_accounts: 128 (cleaner); 128 (autovacuum)
> pgbench_branches: 0.1 (cleaner); 2.1 (autovacuum)
> pgbench_tellers: 0.4 (cleaner); 2.8 (autovacuum)
> pgbench_accounts_pkey: 21 (cleaner); 43 (autovacuum)
> pgbench_accounts_ext: 48 (cleaner); 56 (autovacuum)
>
> Gaussian distribution:
> average tps = 213 (cleaner); = 213 (autovacuum)
>
> Relations size at the end of test, MB:
> pgbench_accounts: 128 (cleaner); 128 (autovacuum)
> pgbench_accounts_ext: 22 (cleaner); 29 (autovacuum)
>
> Conclusions
> -----------
> 1. For retail indextuple deletion purposes i replaced ItemIdSetDead() by
> ItemIdMarkDead() in heap_page_prune_execute() operation. Hereupon in the
> case of 100% filling of each relation block we get a blowing HEAP and
> index , more or less. When the blocks already have free space, the
> cleaner can delay blowing the heap and index without a vacuum.
> 2. Cleaner works fine in the case of skewness of access frequency to
> relation blocks.
> 3. The cleaner does not cause a decrease of performance.
>

--
Andrey Lepikhov
Postgres Professional
https://postgrespro.com
The Russian Postgres Company

Attachment Content-Type Size
0005-Heap-and-Index-cleaner.patch text/x-patch 113.8 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2018-09-06 10:55:39 Re: [HACKERS] Restricting maximum keep segments by repslots
Previous Message Kyotaro HORIGUCHI 2018-09-06 09:57:15 Re: [HACKERS] PATCH: Keep one postmaster monitoring pipe per process