On Tue, 11 Feb 2020 at 11:31, Amit Kapila <[email protected]> wrote:
>
> On Wed, Feb 5, 2020 at 12:07 PM Masahiko Sawada <[email protected]> wrote:
> >
> >
> > Unfortunately the environment I used for performance verification is
> > no longer available.
> >
> > I agree to run this test in a different environment. I've attached the
> > rebased version patch. I'm measuring the performance with/without
> > patch, so will share the results.
> >
>
> Did you get a chance to run these tests? Lately, Mahendra has done a
> lot of performance testing of this patch and shared his results. I
> don't see much downside with the patch, rather there is a performance
> increase of 3-9% in various scenarios.
I've done performance tests on my laptop while changing the number of
partitions. 4 clients concurrently insert 32 tuples to randomly
selected partitions in a transaction. Therefore by changing the number
of partition the contention of relation extension lock would also be
changed. All tables are unlogged tables and N_RELEXTLOCK_ENTS is 1024.
Here is my test results:
* HEAD
nchilds = 64 tps = 33135
nchilds = 128 tps = 31249
nchilds = 256 tps = 29356
* Patched
nchilds = 64 tps = 32057
nchilds = 128 tps = 32426
nchilds = 256 tps = 29483
The performance has been slightly improved by the patch in two cases.
I've also attached the shell script I used to test.
When I set N_RELEXTLOCK_ENTS to 1 so that all relation locks conflicts
the result is:
nchilds = 64 tps = 30887
nchilds = 128 tps = 30015
nchilds = 256 tps = 27837
Regards,
--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
NDATA=32
NTRY=2
NCLIENTS=4
TIME=120
for childs in 64 128 256
do
pg_ctl stop -mi
rm -r $PGDATA
initdb -E UTF8 --no-locale
cat <<EOF >> $PGDATA/postgresql.conf
shared_buffers = 512MB
max_wal_size = 20GB
checkpoint_timeout = 1h
EOF
pg_ctl start
psql -c "create unlogged table parent (c int) partition by list(c)"
cat <<EOF | psql > /dev/null
select 'create unlogged table p' || i || ' partition of parent for values in (' || i || ')' from generate_series(0,$childs) i; \gexec
EOF
echo "insert into parent select ((random() * 1000)::int % $childs) from generate_series(1,$NDATA)" > data.sql
pgbench -i -n postgres
avg=0
total=0
for t in `seq 1 $NTRY`
do
tps=`bin/pgbench -T $TIME -c ${NCLIENTS} -f data.sql -n postgres | grep "excluding" | cut -d " " -f 3`
echo "CHILDS = $childs, TRIS = $t, TPS = $tps"
total=$(echo "$tps + $total" | bc)
done
avg=$(echo "$total / $NTRY" | bc)
echo "nchilds = $childs tps = $avg" >> result_${1}.txt
done
pg_ctl stop
