From: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
---|---|
To: | "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> |
Cc: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, "Imai, Yoshikazu" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andres Freund <andres(at)anarazel(dot)de>, Simon Riggs <simon(at)2ndquadrant(dot)com>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Speed up transaction completion faster after many relations are accessed in a transaction |
Date: | 2019-03-26 10:47:07 |
Message-ID: | CAKJS1f-bX2iQ24XofJDfckumqjAQ7+273ZcXCHNC=CQAKKtM2g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, 26 Mar 2019 at 21:55, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>
> On Tue, 26 Mar 2019 at 21:23, Tsunakawa, Takayuki
> <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> > Thank you David for explaining. Although I may not understand the effect of "speeding up planning with partitions" patch, this patch takes effect even without it. That is, perform the following in the same session:
> >
> > 1. SELECT count(*) FROM table; on a table with many partitions. That bloats the LocalLockHash.
> > 2. PREPARE a point query, e.g., SELECT * FROM table WHERE pkey = $1;
> > 3. EXECUTE the PREPAREd query repeatedly, with each EXECUTE in a separate transaction. Without the patch, each transaction's LockReleaseAll() has to scan the bloated large hash table.
>
> Oh. I think I see what you're saying. Really the table in #2 would
> have to be some completely different table that's not partitioned. I
> think in that case it should make a difference.
Here a benchmark doing that using pgbench's script weight feature.
I've set this up so the query that hits the partitioned table runs
once for every 10k times the other script runs. I picked that number
so the lock table was expanded fairly early on in the benchmark.
setup:
create table t1 (a int primary key);
create table hp (a int) partition by hash (a);
select 'create table hp'||x|| ' partition of hp for values with
(modulus 4096, remainder ' || x || ');' from generate_series(0,4095)
x;
\gexec
hp.sql
select count(*) from hp;
t1.sql
\set p 1
select a from t1 where a = :p;
Master = c8c885b7a5
Master:
$ pgbench -T 60 -M prepared -n -f hp(dot)sql(at)1 -f t1(dot)sql(at)10000 postgres
SQL script 2: t1.sql
- 1057306 transactions (100.0% of total, tps = 17621.756473)
- 1081905 transactions (100.0% of total, tps = 18021.449914)
- 1122420 transactions (100.0% of total, tps = 18690.804699)
Master + 0002-speed-up-LOCALLOCK-scan.patch
$ pgbench -T 60 -M prepared -n -f hp(dot)sql(at)1 -f t1(dot)sql(at)10000 postgres
SQL script 2: t1.sql
- 1277014 transactions (100.0% of total, tps = 21283.551615)
- 1184052 transactions (100.0% of total, tps = 19734.185872)
- 1188523 transactions (100.0% of total, tps = 19785.835662)
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2019-03-26 10:59:24 | Re: Reduce amount of WAL generated by CREATE INDEX for gist, gin and sp-gist |
Previous Message | Amit Kapila | 2019-03-26 10:07:57 | Re: Transaction commits VS Transaction commits (with parallel) VS query mean time |