From: | Victor Yegorov <vyegorov(at)gmail(dot)com> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Deleting older versions in unique indexes to avoid page splits |
Date: | 2021-01-04 22:07:13 |
Message-ID: | CAGnEbogATZS1mWMVX8FzZHMXzuDEcb10AnVwwhCtXtiBpg3XLQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
пн, 4 янв. 2021 г. в 17:28, Victor Yegorov <vyegorov(at)gmail(dot)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
Attachment | Content-Type | Size |
---|---|---|
20210103-pg_bench-alterations.txt | text/plain | 560 bytes |
20210103-master-overview.png | image/png | 215.5 KB |
![]() |
image/png | 35.7 KB |
20210103-v12-overview.png | image/png | 242.7 KB |
![]() |
image/png | 33.6 KB |
20210103-postgresql.auto.conf | application/octet-stream | 715 bytes |
20210103-testcase1.pgbench | application/octet-stream | 278 bytes |
20210103-testcase2.pgbench | application/octet-stream | 353 bytes |
20210103-testcase3.pgbench | application/octet-stream | 114 bytes |
20210103-results-master.txt | text/plain | 3.3 KB |
20210103-results-patched.txt | text/plain | 2.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2021-01-04 22:21:07 | Re: Context diffs |
Previous Message | Peter Geoghegan | 2021-01-04 22:06:01 | Re: pg_waldump/heapdesc.c and struct field names |