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 | Resend email |
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 |
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(). |