Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hzFCb-0008T1-5F for pgsql-hackers@arkaria.postgresql.org; Sun, 18 Aug 2019 07:02:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hzFCZ-0007mR-Sf for pgsql-hackers@arkaria.postgresql.org; Sun, 18 Aug 2019 07:02:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hzFCZ-0007kJ-4R for pgsql-hackers@lists.postgresql.org; Sun, 18 Aug 2019 07:02:07 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1hzFCV-0003yq-IN for pgsql-hackers@postgresql.org; Sun, 18 Aug 2019 07:02:05 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 08A7621C2121; Sun, 18 Aug 2019 10:02:00 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.28.74] (unknown [192.168.28.74]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 7862F21C1FFE; Sun, 18 Aug 2019 10:01:59 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1566111719; bh=kdT0QOiPOkGy60sShORHG+jvtFipQfJXpwz1xQO6USI=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=A6qvAgmeGTkEPyQlg6BMM+GbMWNHfJs6BPV2Phq5xxrH3YWaluuYTjJ8ZM3aP3HKQ I9+CJJXdwVy1dCD7MscGWp+hyOCePiYDuwoyhvhvAgDO6n/7vlC0jD3caYdnPwM+R8 HMNVXHxaI/uOB5IZ5qqtkCn+W6jcdNgbgTRxZG20= Subject: Re: Global temporary tables To: Pavel Stehule Cc: Craig Ringer , PostgreSQL Hackers References: <73954ab7-44d3-b37b-81a3-69bdcbb446f7@postgrespro.ru> <614c1e9f-c0e6-332c-ba2b-85a7e1efb956@postgrespro.ru> <5a711f75-e999-a809-60e1-c74c8c9e7915@postgrespro.ru> <21f047d6-bb2d-950c-2aff-2fee301d5851@postgrespro.ru> <91de2094-165a-04f1-9b3d-348473394345@postgrespro.ru> <758e5293-3ccd-3a6a-8464-6373bb441994@postgrespro.ru> From: Konstantin Knizhnik Message-ID: <0bc85c39-67a2-3bb4-d6fc-bf78862147a5@postgrespro.ru> Date: Sun, 18 Aug 2019 10:01:58 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.7.2 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------71F04927250A641DEFC9CAAF" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------71F04927250A641DEFC9CAAF Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 16.08.2019 20:17, Pavel Stehule wrote: > > > pá 16. 8. 2019 v 16:12 odesílatel Konstantin Knizhnik > > 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. --------------71F04927250A641DEFC9CAAF Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 16.08.2019 20:17, Pavel Stehule wrote:


pá 16. 8. 2019 v 16:12 odesílatel Konstantin Knizhnik <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.

--------------71F04927250A641DEFC9CAAF--