Re: Deleting older versions in unique indexes to avoid page splits

пн, 4 янв. 2021 г. в 17:28, Victor Yegorov <vyegorov@gmail.com>:

> I created another setup (see attached testcases). Basically, I emulated
> queue operations(INSERT at the end and DELETE
>

Sorry, hit Send too early.

So, I emulated queue operations(INSERT at the end and DELETE from the
head). And also made 5-minute transactions
appear in the background for the whole duration of the test. 3 pgbench were
run in parallel on a scale 3000 bench database
with modifications (attached).

Master
------

        relname        |   nrows   | blk_before | mb_before | blk_after |
mb_after |  diff
-----------------------+-----------+------------+-----------+-----------+----------+--------
 pgbench_accounts      | 300000000 |    4918033 |   38422.1 |   5065575 |
 39574.8 |  +3.0%
 accounts_mtime        | 300000000 |    1155119 |    9024.4 |   1287656 |
 10059.8 | +11.5%
 fiver                 | 300000000 |     427039 |    3336.2 |    567755 |
4435.6 | +33.0%
 pgbench_accounts_pkey | 300000000 |     822573 |    6426.4 |   1033344 |
8073.0 | +25.6%
 score                 | 300000000 |     284022 |    2218.9 |    458502 |
3582.0 | +61.4%
 tenner                | 300000000 |     346050 |    2703.5 |    417985 |
3265.5 | +20.8%
(6 rows)

DB size: 65.2..72.3 (+7.1GB / +10.9%)
TPS: 2297 / 495

Patched
------
        relname        |   nrows   | blk_before | mb_before | blk_after |
mb_after |  diff
-----------------------+-----------+------------+-----------+-----------+----------+--------
 pgbench_accounts      | 300000000 |    4918033 |   38422.1 |   5067500 |
 39589.8 |  +3.0%
 accounts_mtime        | 300000000 |    1155119 |    9024.4 |   1283441 |
 10026.9 | +11.1%
 fiver                 | 300000000 |     427039 |    3336.2 |    429101 |
3352.4 |  +0.5%
 pgbench_accounts_pkey | 300000000 |     822573 |    6426.4 |    826056 |
6453.6 |  +0.4%
 score                 | 300000000 |     284022 |    2218.9 |    285465 |
2230.2 |  +0.5%
 tenner                | 300000000 |     346050 |    2703.5 |    347695 |
2716.4 |  +0.5%
(6 rows)

DB size: 65.2..67.5 (+2.3GB / +3.5%)
TPS: 2216 / 492

As you can see, TPS are very much similar, but the fact that we have no
bloat for the patched version makes me very happy!

On the graphs, you can clearly see extra write activity performed by the
backedns of the patched version.


-- 
Victor Yegorov