From: | Pavel Borisov <pashkin(dot)elfe(at)gmail(dot)com> |
---|---|
To: | Alexander Korotkov <aekorotkov(at)gmail(dot)com> |
Cc: | Andres Freund <andres(at)anarazel(dot)de>, Aleksander Alekseev <aleksander(at)timescale(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Mason Sharp <masonlists(at)gmail(dot)com>, vignesh C <vignesh21(at)gmail(dot)com> |
Subject: | Re: POC: Lock updated tuples in tuple_update() and tuple_delete() |
Date: | 2023-03-02 10:28:56 |
Message-ID: | CALT9ZEHKdCF_jCoK2ErUuUtCuYPf82+Zr1XE5URzneSFxz3zqA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi, Alexander!
> Let's see the performance results for the patchset. I'll properly
> revise the comments if results will be good.
>
> Pavel, could you please re-run your tests over revised patchset?
Since last time I've improved the test to avoid significant series
differences due to AWS storage access variation that is seen in [1].
I.e. each series of tests is run on a tmpfs with newly inited pgbench
tables and vacuum. Also, I've added a test for low-concurrency updates
where the locking optimization isn't expected to improve performance,
just to make sure the patches don't make things worse.
The tests are as follows:
1. Heap updates with high tuple concurrency:
Prepare without pkeys (pgbench -d postgres -i -I dtGv -s 10 --unlogged-tables)
Update tellers 100 rows, 50 conns ( pgbench postgres -f
./update-only-tellers.sql -s 10 -P10 -M prepared -T 600 -j 5 -c 50 )
Result: Average of 5 series with patches (0001+0002) is around 5%
faster than both master and patch 0001. Still, there are some
fluctuations between different series of the measurements of the same
patch, but much less than in [1]
2. Heap updates with low tuple concurrency:
Prepare with pkeys (pgbench -d postgres -i -I dtGvp -s 300 --unlogged-tables)
Update 3*10^7 rows, 50 conns (pgbench postgres -f
./update-only-account.sql -s 300 -P10 -M prepared -T 600 -j 5 -c 50)
Result: Both patches and master are the same within a tolerance of
less than 0.7%.
Tests are run on the same 36-vcore AWS c5.9xlarge as [1]. The results
pictures are attached.
Using pkeys in low-concurrency cases is to make the index search of a
tuple to be updated. No pkeys in case of high concurrency is for
concurrent index updates not contribute to updates performance.
Common settings:
shared_memory 20Gb
max_worker_processes = 1024
max_parallel_workers = 1024
max_connections=10000
autovacuum_multixact_freeze_max_age=2000000000
autovacuum_freeze_max_age=2000000000
max_wal_senders=0
wal_level=minimal
max_wal_size = 10G
autovacuum = off
fsync = off
full_page_writes = off
Kind regards,
Pavel Borisov,
Supabase.
Attachment | Content-Type | Size |
---|---|---|
lo-concurrency.png | image/png | 241.4 KB |
hi-concurrency.png | image/png | 391.8 KB |
hi-concurrency.csv | text/csv | 8.1 KB |
low-concurrency.csv | text/csv | 8.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2023-03-02 10:33:01 | Re: [HACKERS] make async slave to wait for lsn to be replayed |
Previous Message | Amit Kapila | 2023-03-02 10:17:53 | Re: pg_upgrade and logical replication |