Re: Speed up transaction completion faster after many relations are accessed in a transaction

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