Re: Global temporary tables

On 18.08.2019 11:28, Pavel Stehule wrote:
>
>
> ne 18. 8. 2019 v 9:02 odesílatel Konstantin Knizhnik 
> <k.knizhnik@postgrespro.ru <mailto:k.knizhnik@postgrespro.ru>> napsal:
>
>
>
>     On 16.08.2019 20:17, Pavel Stehule wrote:
>>
>>
>>     pá 16. 8. 2019 v 16:12 odesílatel Konstantin Knizhnik
>>     <k.knizhnik@postgrespro.ru <mailto:k.knizhnik@postgrespro.ru>>
>>     napsal:
>>
>>         I did more investigations of performance of global temp
>>         tables with shared buffers vs. vanilla (local) temp tables.
>>
>>         1. Combination of persistent and temporary tables in the same
>>         query.
>>
>>         Preparation:
>>         create table big(pk bigint primary key, val bigint);
>>         insert into big values
>>         (generate_series(1,100000000),generate_series(1,100000000));
>>         create temp table lt(key bigint, count bigint);
>>         create global temp table gt(key bigint, count bigint);
>>
>>         Size of table is about 6Gb, I run this test on desktop with
>>         16GB of RAM and postgres with 1Gb shared buffers.
>>         I run two queries:
>>
>>         insert into T (select count(*),pk/P as key from big group by
>>         key);
>>         select sum(count) from T;
>>
>>         where P is (100,10,1) and T is name of temp table (lt or gt).
>>         The table below contains times of both queries in msec:
>>
>>         Percent of selected data
>>         	1%
>>         	10%
>>         	100%
>>         Local temp table
>>         	44610
>>         90
>>         	47920
>>         891
>>         	63414
>>         21612
>>         Global temp table
>>         	44669
>>         35
>>         	47939
>>         298
>>         	59159
>>         26015
>>
>>
>>         As you can see, time of insertion in temporary table is
>>         almost the same
>>         and time of traversal of temporary table is about twice
>>         smaller for global temp table
>>         when it fits in RAM together with persistent table and
>>         slightly worser when it doesn't fit.
>>
>>
>>
>>         2. Temporary table only access.
>>         The same system, but Postgres is configured with
>>         shared_buffers=10GB, max_parallel_workers = 4,
>>         max_parallel_workers_per_gather = 4
>>
>>         Local temp tables:
>>         create temp table local_temp(x1 bigint, x2 bigint, x3 bigint,
>>         x4 bigint, x5 bigint, x6 bigint, x7 bigint, x8 bigint, x9
>>         bigint);
>>         insert into local_temp values
>>         (generate_series(1,100000000),0,0,0,0,0,0,0,0);
>>         select sum(x1) from local_temp;
>>
>>         Global temp tables:
>>         create global temporary table global_temp(x1 bigint, x2
>>         bigint, x3 bigint, x4 bigint, x5 bigint, x6 bigint, x7
>>         bigint, x8 bigint, x9 bigint);
>>         insert into global_temp values
>>         (generate_series(1,100000000),0,0,0,0,0,0,0,0);
>>         select sum(x1) from global_temp;
>>
>>         Results (msec):
>>
>>         	Insert
>>         	Select
>>         Local temp table 	37489
>>         	48322
>>         Global temp table 	44358
>>         	3003
>>
>>
>>         So insertion in local temp table is performed slightly faster
>>         but select is 16 times slower!
>>
>>         Conclusion:
>>         In the assumption then temp table fits in memory, global temp
>>         tables with shared buffers provides better performance than
>>         local temp table.
>>         I didn't consider here global temp tables with local buffers
>>         because for them results should be similar with local temp
>>         tables.
>>
>>
>>     Probably there is not a reason why shared buffers should be
>>     slower than local buffers when system is under low load.
>>
>>     access to shared memory is protected by spin locks (are cheap for
>>     few processes), so tests in one or few process are not too
>>     important (or it is just one side of space)
>>
>>     another topic can be performance on MS Sys - there are stories
>>     about not perfect performance of shared memory there.
>>
>>     Regards
>>
>>     Pavel
>>
>      One more test which is used to simulate access to temp tables
>     under high load.
>     I am using "upsert" into temp table in multiple connections.
>
>     create global temp table gtemp (x integer primary key, y bigint);
>
>     upsert.sql:
>     insert into gtemp values (random() * 1000000, 0) on conflict(x) do
>     update set y=gtemp.y+1;
>
>     pgbench -c 10 -M prepared -T 100 -P 1 -n -f upsert.sql postgres
>
>
>     I failed to find some standard way in pgbech to perform
>     per-session initialization to create local temp table,
>     so I just insert this code in pgbench code:
>
>     diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
>     index 570cf33..af6a431 100644
>     --- a/src/bin/pgbench/pgbench.c
>     +++ b/src/bin/pgbench/pgbench.c
>     @@ -5994,6 +5994,7 @@ threadRun(void *arg)
>                     {
>                             if ((state[i].con = doConnect()) == NULL)
>                                     goto done;
>     +                       executeStatement(state[i].con, "create
>     temp table ltemp(x integer primary key, y bigint)");
>                     }
>             }
>
>
>     Results are the following:
>     Global temp table: 117526 TPS
>     Local temp table:   107802 TPS
>
>
>     So even for this workload global temp table with shared buffers
>     are a little bit faster.
>     I will be pleased if you can propose some other testing scenario.
>
>
> please, try to increase number of connections.

With 20 connections and 4 pgbench threads results are similar: 119k TPS 
for global temp tables and 115k TPS for local temp tables.

I have tried yet another scenario: read-only access to temp tables:

\set id random(1,10000000)
select sum(y) from ltemp where x=:id;

Tables are created and initialized in pgbench session startup:

knizhnik@knizhnik:~/postgresql$ git diff
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 570cf33..95295b0 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -5994,6 +5994,8 @@ threadRun(void *arg)
                 {
                         if ((state[i].con = doConnect()) == NULL)
                                 goto done;
+                       executeStatement(state[i].con, "create temp 
table ltemp(x integer primary key, y bigint)");
+                       executeStatement(state[i].con, "insert into 
ltemp values (generate_series(1,1000000), generate_series(1,1000000))");
                 }
         }


Results for 10 connections with 10 million inserted records per table 
and 100 connections with 1 million inserted record per table :

#connections:
	10
	100
local temp
	68k
	90k
global temp, shared_buffers=1G
	63k
	61k
global temp, shared_buffers=10G 	150k
	150k



So temporary tables with local buffers are slightly faster when data 
doesn't fit in shared buffers, but significantly slower when it fits.



-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company