Re: [WIP] [B-Tree] Retail IndexTuple deletion - Mailing list pgsql-hackers

Hi,
I wrote a background worker (hcleaner) to demonstrate application of 
Retail IndexTuple deletion (see patch at attachment).
Like Autovacuum it utilizes concept of one launcher and many workers. 
But one worker correspond to one database.

Short description:
Backend collects dirty block numbers by a hash table at the point in 
code immediately after heap_page_prune() call. Backend send a package of 
dirty block numbers (not one-by-one!) by socket at the end of 
transaction or if hash table is full.
Launcher transfers block numbers to correspond workers.
Worker collects dead tuples from a block, clean index relations, clean 
heap block. It uses conditional locking with waiting list approach if 
heap block are busy.

hcleaner has experimental status, but make check-world passed
.
For benchmarking i used xiaomi notebook with intel Core i5 8gen processor.

BENCHMARK
----------

test: pgbench -i -s 100 && pgbench -c 25 -j 8 -M prepared -P 60 -T 3600
autovacuum = off

master:
-------
number of transactions actually processed: 6373215
latency average = 14.122 ms
latency stddev = 9.458 ms
tps = 1770.291436 (including connections establishing)
tps = 1770.293191 (excluding connections establishing)

VACUUM verbose pgbench_accounts:
--------------------------------
INFO: vacuuming "public.pgbench_accounts"
INFO: scanned index "pgbench_accounts_pkey" to remove 237496 row versions
DETAIL: CPU: user: 4.67 s, system: 0.27 s, elapsed: 8.05 s
INFO: "pgbench_accounts": removed 237496 row versions in 167652 pages
DETAIL: CPU: user: 7.54 s, system: 3.40 s, elapsed: 26.10 s
INFO: index "pgbench_accounts_pkey" now contains 10000000 row versions 
in 27422 pages
DETAIL: 237496 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "pgbench_accounts": found 165275 removable, 10000000 nonremovable 
row versions in 167840 out of 167840 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 6373796
There were 82282 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 20.33 s, system: 5.88 s, elapsed: 51.38 s.

patched:
--------
number of transactions actually processed: 6338593
latency average = 14.199 ms
latency stddev = 13.988 ms
tps = 1760.685922 (including connections establishing)
tps = 1760.688038 (excluding connections establishing)

VACUUM verbose pgbench_accounts:
--------------------------------
INFO: vacuuming "public.pgbench_accounts"
INFO: scanned index "pgbench_accounts_pkey" to remove 1804 row versions
DETAIL: CPU: user: 1.84 s, system: 0.05 s, elapsed: 3.34 s
INFO: "pgbench_accounts": removed 1804 row versions in 1468 pages
DETAIL: CPU: user: 0.06 s, system: 0.03 s, elapsed: 1.42 s
INFO: index "pgbench_accounts_pkey" now contains 10000000 row versions 
in 27422 pages
DETAIL: 1618 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "pgbench_accounts": found 168561 removable, 10000000 nonremovable 
row versions in 169466 out of 169466 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 6339174
There were 75478 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 12.27 s, system: 4.03 s, elapsed: 31.43 s.

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