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-05 12:25:08
Message-ID: cacd3406-7503-3ffc-3dad-92a3e3bf27d7@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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 54.0 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Korotkov 2018-09-05 13:05:17 Re: [HACKERS] Bug in to_timestamp().
Previous Message amul sul 2018-09-05 12:09:57 Re: [HACKERS] Bug in to_timestamp().