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(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)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(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)ru>>
>>> napsal:
>>>
>>>
>>>
>>> On 16.08.2019 20:17, Pavel Stehule wrote:
>>>>
>>>>
>>>> pá 16. 8. 2019 v 16:12 odesílatel Konstantin Knizhnik
>>>> <k(dot)knizhnik(at)postgrespro(dot)ru
>>>> <mailto:k(dot)knizhnik(at)postgrespro(dot)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(at)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