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