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

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