Re: Global temporary tables

On 19.08.2019 14:25, Pavel Stehule wrote:
>
>
> po 19. 8. 2019 v 13:16 odesílatel Konstantin Knizhnik 
> <k.knizhnik@postgrespro.ru <mailto:k.knizhnik@postgrespro.ru>> napsal:
>
>
>
>     On 19.08.2019 11:51, Konstantin Knizhnik wrote:
>>
>>
>>     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.
>>
>>
>
>     All previously reported results were produced at my desktop.
>     I also run this read-only test on huge IBM server (POWER9, 2 NUMA
>     nodes, 176 CPU, 1Tb RAM).
>
>     Here the difference between local and global tables is not so large:
>
>     Local temp:   739k TPS
>     Global temp:  924k TPS
>
>
> is not difference between local temp buffers and global temp buffers 
> by too low value of TEMP_BUFFERS?


Certainly, default (small) temp buffer size plays roles.
But it this IPC host this difference is not so important.
Result with local temp tables and temp_buffers = 1GB: 859k TPS.

> -- 

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