Global temporary tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
57 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik


On 16.08.2019 9:25, Craig Ringer wrote:
On Tue, 13 Aug 2019 at 21:50, Konstantin Knizhnik <[hidden email]> wrote:
As far as I understand relpages and reltuples are set only when you perform "analyze" of the table.

Also autovacuum's autoanalyze.

When it happen?
I have created normal table, populated it with some data and then wait several hours but pg_class was not updated for this table.



heap_vacuum_rel() in src/backend/access/heap/vacuumlazy.c below

     * Update statistics in pg_class.

which I'm pretty sure is common to explicit vacuum and autovacuum. I haven't run up a test to verify 100% but most DBs would never have relpages etc set if autovac didn't do it since most aren't explicitly VACUUMed at all.

Sorry, I already understood it myself.
But to make vacuum process the table it is necessary to remove or update some rows in it.
It seems to be yet another Postgres problem, which was noticed by Darafei Praliaskouski some time ago: append-only tables are never proceeded by autovacuum.



I thought it was done when autovac ran an analyze, but it looks like it's all autovac. Try setting very aggressive autovac thresholds and inserting + deleting a bunch of tuples maybe.

I attach to this mail slightly refactored versions of this patches with fixes of issues reported in your review.

Thanks.

Did you have a chance to consider my questions too? I see a couple of things where there's no patch change, which is fine, but I'd be interested in your thoughts on the question/issue in those cases.

Sorry, may be I didn't notice some your questions. I have a filling that I have replied on all your comments/questions.
Right now I reread all this thread and see two open issues:

1. Statistic for global temporary tables (including number of tuples, pages and all visible flag).
My position is the following: while in most cases it should not be a problem, because users rarely create indexes or do analyze for temporary tables,
there can be situations when differences in data sets of global temporary tables in different backends can really be a problem.
Unfortunately I can not propose good solution for this problem. It is certainly possible to create some private (per-backend) cache for this metadata.
But it seems to requires changes in many places.

2. Your concerns about performance penalty of global temp tables accessed through shared buffers comparing with local temp tables access through local buffers.
I think that this concern is not  actual any more because there is implementation of global temp tables using local buffers.
But my experiments doesn't show significant difference in access speed of shared and local buffers. As far as shared buffers are used to be much larger than local buffers,
there are more chances to hold all temp relation in memory without spilling it to the disk. In this case access to global temp table will be much faster comparing with access to
local temp tables. But the fact is that right now in the most frequent scenario of temp table usage:

    SELECT ... FROM PersistentTable INTO TempTable WHERE ...;
    SELECT * FROM TempTable;

local temp table are more efficient than global temp table access through shared buffer.
I think it is explained by caching and eviction policies.
In case of pulling all content of temp table in memory (pg_prewarm) global temp table with shared buffers becomes faster.


I forget or do not notice some of your questions, would you be so kind as to repeat them?


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

Craig Ringer-3
On Fri, 16 Aug 2019 at 15:30, Konstantin Knizhnik <[hidden email]> wrote:
 

1. Statistic for global temporary tables (including number of tuples, pages and all visible flag).
My position is the following: while in most cases it should not be a problem, because users rarely create indexes or do analyze for temporary tables,
there can be situations when differences in data sets of global temporary tables in different backends can really be a problem.
Unfortunately I can not propose good solution for this problem. It is certainly possible to create some private (per-backend) cache for this metadata.
But it seems to requires changes in many places.

Yeah. I don't really like just sharing them but it's not that bad either.
 
2. Your concerns about performance penalty of global temp tables accessed through shared buffers comparing with local temp tables access through local buffers.
I think that this concern is not  actual any more because there is implementation of global temp tables using local buffers.
But my experiments doesn't show significant difference in access speed of shared and local buffers. As far as shared buffers are used to be much larger than local buffers,
there are more chances to hold all temp relation in memory without spilling it to the disk. In this case access to global temp table will be much faster comparing with access to
local temp tables.

You ignore the costs of evicting non-temporary data from shared_buffers, i.e. contention for space. Also increased chance of backends being forced to do direct write-out due to lack of s_b space for dirty buffers.
 
> In case of pulling all content of temp table in memory (pg_prewarm) global temp table with shared buffers becomes faster.

Who would ever do that?

I forget or do not notice some of your questions, would you be so kind as to repeat them?
 
 

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 2ndQuadrant - PostgreSQL Solutions for the Enterprise
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

Craig Ringer-3

On Fri, 16 Aug 2019 at 15:30, Konstantin Knizhnik <[hidden email]> wrote:
 
I forget or do not notice some of your questions, would you be so kind as to repeat them?
 

Sent early by accident.

Repeating questions:


Why do you need to do all this indirection with changing RelFileNode to RelFileNodeBackend in the bufmgr, changing BufferGetTag etc? Similarly, your changes of RelFileNodeBackendIsTemp to RelFileNodeBackendIsLocalTemp . I'm guessing you did it the way you did instead to lay the groundwork for cross-backend sharing, but if so it should IMO be in your second patch that adds support for using shared_buffers for temp tables, not in the first patch that adds a minimal global temp tables implementation. Maybe my understanding of the existing temp table mechanics is just insufficient as I see RelFileNodeBackendIsTemp is already used in some aspects of existing temp relation handling.

Did you look into my suggestion of extending the relmapper so that global temp tables would have a relfilenode of 0 like pg_class etc, and use a backend-local map of oid-to-relfilenode mappings?

Similarly, TruncateSessionRelations probably shouldn't need to exist in this patch in its current form; there's no shared_buffers use to clean and the same file cleanup mechanism should handle both session-temp and local-temp relfilenodes.

Sequence initialization ignores sequence startval/firstval settings. Why?
+               value[SEQ_COL_LASTVAL-1] = Int64GetDatumFast(1); /* start sequence with 1 */



Doesn't this change the test outcome for RELPERSISTENCE_UNLOGGED?:
- else if (newrelpersistence == RELPERSISTENCE_PERMANENT)
+ else if (newrelpersistence != RELPERSISTENCE_TEMP)


--
 Craig Ringer                   http://www.2ndQuadrant.com/
 2ndQuadrant - PostgreSQL Solutions for the Enterprise
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik


On 16.08.2019 11:37, Craig Ringer wrote:

On Fri, 16 Aug 2019 at 15:30, Konstantin Knizhnik <[hidden email]> wrote:
 
I forget or do not notice some of your questions, would you be so kind as to repeat them?
 

Sent early by accident.

Repeating questions:

Sorry, but I have answered them (my e-mail from 13.08)!
Looks like you have looed at wrong version of the patch:
global_shared_temp-1.patch instead of global_private_temp-1.patch which implements global tables accessed through local buffers.



Why do you need to do all this indirection with changing RelFileNode to RelFileNodeBackend in the bufmgr, changing BufferGetTag etc? Similarly, your changes of RelFileNodeBackendIsTemp to RelFileNodeBackendIsLocalTemp . I'm guessing you did it the way you did instead to lay the groundwork for cross-backend sharing, but if so it should IMO be in your second patch that adds support for using shared_buffers for temp tables, not in the first patch that adds a minimal global temp tables implementation. Maybe my understanding of the existing temp table mechanics is just insufficient as I see RelFileNodeBackendIsTemp is already used in some aspects of existing temp relation handling.


Sorry, are you really speaking about global_private_temp-1.patch?
This patch doesn't change bufmgr file at all.
May be you looked at another patch - global_shared_temp-1.patch
which is accessing shared tables though shared buffers and so have to change buffer tag to include backend ID in it.


Did you look into my suggestion of extending the relmapper so that global temp tables would have a relfilenode of 0 like pg_class etc, and use a backend-local map of oid-to-relfilenode mappings?

Similarly, TruncateSessionRelations probably shouldn't need to exist in this patch in its current form; there's no shared_buffers use to clean and the same file cleanup mechanism should handle both session-temp and local-temp relfilenodes.

In global_private_temp-1.patch TruncateSessionRelations does nothing with shared buffers, it just delete relation files.




Sequence initialization ignores sequence startval/firstval settings. Why?
+               value[SEQ_COL_LASTVAL-1] = Int64GetDatumFast(1); /* start sequence with 1 */




I am handling only case of implicitly created sequences for SERIAL/BIGSERIAL columns.
Is it possible to explicitly specify initial value and step for them?
If so, this place should definitely be rewritten.


Doesn't this change the test outcome for RELPERSISTENCE_UNLOGGED?:
- else if (newrelpersistence == RELPERSISTENCE_PERMANENT)
+ else if (newrelpersistence != RELPERSISTENCE_TEMP)


RELPERSISTENCE_UNLOGGED case is handle in previous IF branch.
-
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik
In reply to this post by Craig Ringer-3


On 16.08.2019 11:32, Craig Ringer wrote:

You ignore the costs of evicting non-temporary data from shared_buffers, i.e. contention for space. Also increased chance of backends being forced to do direct write-out due to lack of s_b space for dirty buffers.
 
> In case of pulling all content of temp table in memory (pg_prewarm) global temp table with shared buffers becomes faster.

Who would ever do that?



I decided to redo my experiments and now get different results which illustrates advantages of global temp tables with shared buffer.
I performed the following test at my desktop with SSD and 16GB of RAM and Postgres with default configuration except shared-buffers increased to 1Gb.


postgres=# create table big(pk bigint primary key, val bigint);
CREATE TABLE
postgres=# insert into big values (generate_series(1,100000000),generate_series(1,100000000)/100);
INSERT 0 100000000
postgres=# select * from buffer_usage limit 3;
    relname     |  buffered  | buffer_percent | percent_of_relation
----------------+------------+----------------+---------------------
 big            | 678 MB     |           66.2 |                16.1
 big_pkey       | 344 MB     |           33.6 |                16.1
 pg_am          | 8192 bytes |            0.0 |                20.0

postgres=# create temp table lt(key bigint, count bigint);
postgres=# \timing
Timing is on.
postgres=# insert into lt (select count(*),val as key from big group by val);
INSERT 0 1000001
Time: 43265.491 ms (00:43.265)
postgres=# select sum(count) from lt;
     sum     
--------------
 500000500000
(1 row)

Time: 94.194 ms
postgres=# insert into gt (select count(*),val as key from big group by val);
INSERT 0 1000001
Time: 42952.671 ms (00:42.953)
postgres=# select sum(count) from gt;
     sum     
--------------
 500000500000
(1 row)

Time: 35.906 ms
postgres=# select * from buffer_usage limit 3;
 relname  | buffered | buffer_percent | percent_of_relation
----------+----------+----------------+---------------------
 big      | 679 MB   |           66.3 |                16.1
 big_pkey | 300 MB   |           29.3 |                14.0
 gt       | 42 MB    |            4.1 |               100.0


So time of storing result in global temp table is slightly smaller than time of storing it in local temp table and time of scanning global temp table is twice smaller!


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik
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.


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

Pavel Stehule


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




-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik


On 16.08.2019 20:17, Pavel Stehule wrote:


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

Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

Pavel Stehule


ne 18. 8. 2019 v 9:02 odesílatel Konstantin Knizhnik <[hidden email]> napsal:


On 16.08.2019 20:17, Pavel Stehule wrote:


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

Regards

Pavel
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik


On 18.08.2019 11:28, Pavel Stehule wrote:


ne 18. 8. 2019 v 9:02 odesílatel Konstantin Knizhnik <[hidden email]> napsal:


On 16.08.2019 20:17, Pavel Stehule wrote:


pá 16. 8. 2019 v 16:12 odesílatel Konstantin Knizhnik <[hidden email]> 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 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik


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 <[hidden email]> napsal:


On 16.08.2019 20:17, Pavel Stehule wrote:


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


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

Pavel Stehule


po 19. 8. 2019 v 13:16 odesílatel Konstantin Knizhnik <[hidden email]> 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 <[hidden email]> napsal:


On 16.08.2019 20:17, Pavel Stehule wrote:


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

Pavel



-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik


On 19.08.2019 14:25, Pavel Stehule wrote:


po 19. 8. 2019 v 13:16 odesílatel Konstantin Knizhnik <[hidden email]> 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 <[hidden email]> napsal:


On 16.08.2019 20:17, Pavel Stehule wrote:


pá 16. 8. 2019 v 16:12 odesílatel Konstantin Knizhnik <[hidden email]> 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 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

Pavel Stehule



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.

It is little bit unexpected result.I understand so it partially it is generic problem access to smaller dedicated caches versus access to bigger shared cache.

But it is hard to imagine so access to local cache is 10% slower than access to shared cache. Maybe there is some bottle neck - maybe our implementation of local buffers are suboptimal.

Using local buffers for global temporary tables can be interesting from another reason - it uses temporary files, and temporary files can be forwarded on ephemeral IO on Amazon cloud (with much better performance than persistent IO).


 

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik


On 19.08.2019 18:53, Pavel Stehule wrote:



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.

It is little bit unexpected result.I understand so it partially it is generic problem access to smaller dedicated caches versus access to bigger shared cache.

But it is hard to imagine so access to local cache is 10% slower than access to shared cache. Maybe there is some bottle neck - maybe our implementation of local buffers are suboptimal.

It may be caused by system memory allocator - in case of using shared buffers we do not need to ask OS to allocate more memory.


Using local buffers for global temporary tables can be interesting from another reason - it uses temporary files, and temporary files can be forwarded on ephemeral IO on Amazon cloud (with much better performance than persistent IO).



My assumption is that temporary tables almost always fit in memory. So in most cases there is on need to write data to file at all.


As I wrote at the beginning of this thread, one of the problems with temporary table sis that it is not possible to use them at replica.
Global temp tables allows to share metadata between master and replica.
I perform small investigation: how difficult it will be to support inserts in temp tables at replica.
First my impression was that it can be done in tricky but simple way.

By making small changes changing just three places:
1.  Prohibit non-select statements in read-only transactions
2. Xid assignment (return FrozenTransactionId)
3. Transaction commit/abort

I managed to provide normal work with global temp tables at replica.
But there is one problem with this approach: it is not possible to undo changes in temp tables so rollback doesn't work.

I tried another solution, but assigning some dummy Xids to standby transactions.
But this approach require much more changes:
- Initialize page for such transaction in CLOG
- Mark transaction as committed/aborted in XCLOG
- Change snapshot check in visibility function

And still I didn't find safe way to cleanup CLOG space.
Alternative solution is to implement "local CLOG" for such transactions.
The straightforward solution is to use hashtable. But it may cause memory overflow if we have long living backend which performs huge number of transactions.
Also in this case we need to change visibility check functions.

So I have implemented simplest solution with frozen xid and force backend termination in case of transaction rollback (so user will no see inconsistent behavior).
Attached please find global_private_temp_replica.patch which implements this approach.
It will be nice if somebody can suggest better solution for temporary tables at replica.







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

global_private_temp_replica.patch (56K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

Pavel Stehule


út 20. 8. 2019 v 16:51 odesílatel Konstantin Knizhnik <[hidden email]> napsal:


On 19.08.2019 18:53, Pavel Stehule wrote:



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.

It is little bit unexpected result.I understand so it partially it is generic problem access to smaller dedicated caches versus access to bigger shared cache.

But it is hard to imagine so access to local cache is 10% slower than access to shared cache. Maybe there is some bottle neck - maybe our implementation of local buffers are suboptimal.

It may be caused by system memory allocator - in case of using shared buffers we do not need to ask OS to allocate more memory.

maybe, but shared buffers you have a overhead with searching free buffers and some overhead with synchronization processes.


Using local buffers for global temporary tables can be interesting from another reason - it uses temporary files, and temporary files can be forwarded on ephemeral IO on Amazon cloud (with much better performance than persistent IO).



My assumption is that temporary tables almost always fit in memory. So in most cases there is on need to write data to file at all.


As I wrote at the beginning of this thread, one of the problems with temporary table sis that it is not possible to use them at replica.
Global temp tables allows to share metadata between master and replica.

I am not sure if I understand to last sentence. Global temp tables should be replicated on replica servers. But the content should not be replicated. This should be session specific.
 
I perform small investigation: how difficult it will be to support inserts in temp tables at replica.
First my impression was that it can be done in tricky but simple way.

By making small changes changing just three places:
1.  Prohibit non-select statements in read-only transactions
2. Xid assignment (return FrozenTransactionId)
3. Transaction commit/abort

I managed to provide normal work with global temp tables at replica.
But there is one problem with this approach: it is not possible to undo changes in temp tables so rollback doesn't work.

I tried another solution, but assigning some dummy Xids to standby transactions.
But this approach require much more changes:
- Initialize page for such transaction in CLOG
- Mark transaction as committed/aborted in XCLOG
- Change snapshot check in visibility function

And still I didn't find safe way to cleanup CLOG space.
Alternative solution is to implement "local CLOG" for such transactions.
The straightforward solution is to use hashtable. But it may cause memory overflow if we have long living backend which performs huge number of transactions.
Also in this case we need to change visibility check functions.

So I have implemented simplest solution with frozen xid and force backend termination in case of transaction rollback (so user will no see inconsistent behavior).
Attached please find global_private_temp_replica.patch which implements this approach.
It will be nice if somebody can suggest better solution for temporary tables at replica.

This is another hard issue. Probably backend temination should be acceptable solution. I don't understand well to this area, but if replica allows writing (to global temp tables), then replica have to have local CLOG.

CLOG for global temp tables can be more simple then standard CLOG. Data are not shared, and life of data (and number of transactions) can be low.

Another solution is wait on ZHeap storage and replica can to have own UNDO log.

 







-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik


On 20.08.2019 19:06, Pavel Stehule wrote:


As I wrote at the beginning of this thread, one of the problems with temporary table sis that it is not possible to use them at replica.
Global temp tables allows to share metadata between master and replica.

I am not sure if I understand to last sentence. Global temp tables should be replicated on replica servers. But the content should not be replicated. This should be session specific.

Obviously.
When we run OLAP queries at replica, it will be great if we can do

insert into temp_table (select ...);

With local temp tables it is not possible just because you can not create temp table at replica.
But global temp table can be created at master and populated with data at replica.

 
I perform small investigation: how difficult it will be to support inserts in temp tables at replica.
First my impression was that it can be done in tricky but simple way.

By making small changes changing just three places:
1.  Prohibit non-select statements in read-only transactions
2. Xid assignment (return FrozenTransactionId)
3. Transaction commit/abort

I managed to provide normal work with global temp tables at replica.
But there is one problem with this approach: it is not possible to undo changes in temp tables so rollback doesn't work.

I tried another solution, but assigning some dummy Xids to standby transactions.
But this approach require much more changes:
- Initialize page for such transaction in CLOG
- Mark transaction as committed/aborted in XCLOG
- Change snapshot check in visibility function

And still I didn't find safe way to cleanup CLOG space.
Alternative solution is to implement "local CLOG" for such transactions.
The straightforward solution is to use hashtable. But it may cause memory overflow if we have long living backend which performs huge number of transactions.
Also in this case we need to change visibility check functions.

So I have implemented simplest solution with frozen xid and force backend termination in case of transaction rollback (so user will no see inconsistent behavior).
Attached please find global_private_temp_replica.patch which implements this approach.
It will be nice if somebody can suggest better solution for temporary tables at replica.

This is another hard issue. Probably backend temination should be acceptable solution. I don't understand well to this area, but if replica allows writing (to global temp tables), then replica have to have local CLOG.

There are several problems:

1. How to choose XID for writing transaction at standby.  The simplest solution is to just add 0x7fffffff to the current XID.
It eliminates possibility of conflict with normal XIDs (received from master).
But requires changes in visibility functions. Visibility check function do not know OID of tuple owner, just XID stored in the tuple header. It should make a decision just based on this XID.

2. How to perform cleanup of not needed XIDs. Right now there is quite complex logic of how to free CLOG pages.

3. How to implement visibility rules to such XIDs.


CLOG for global temp tables can be more simple then standard CLOG. Data are not shared, and life of data (and number of transactions) can be low.

Another solution is wait on ZHeap storage and replica can to have own UNDO log.

I thought about implementation of special table access method for temporary tables.
I am trying to understand now if  it is the only possible approach or there are simpler solutions.


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

Pavel Stehule


út 20. 8. 2019 v 18:42 odesílatel Konstantin Knizhnik <[hidden email]> napsal:


On 20.08.2019 19:06, Pavel Stehule wrote:


As I wrote at the beginning of this thread, one of the problems with temporary table sis that it is not possible to use them at replica.
Global temp tables allows to share metadata between master and replica.

I am not sure if I understand to last sentence. Global temp tables should be replicated on replica servers. But the content should not be replicated. This should be session specific.

Obviously.
When we run OLAP queries at replica, it will be great if we can do

insert into temp_table (select ...);

With local temp tables it is not possible just because you can not create temp table at replica.
But global temp table can be created at master and populated with data at replica.

yes


 
I perform small investigation: how difficult it will be to support inserts in temp tables at replica.
First my impression was that it can be done in tricky but simple way.

By making small changes changing just three places:
1.  Prohibit non-select statements in read-only transactions
2. Xid assignment (return FrozenTransactionId)
3. Transaction commit/abort

I managed to provide normal work with global temp tables at replica.
But there is one problem with this approach: it is not possible to undo changes in temp tables so rollback doesn't work.

I tried another solution, but assigning some dummy Xids to standby transactions.
But this approach require much more changes:
- Initialize page for such transaction in CLOG
- Mark transaction as committed/aborted in XCLOG
- Change snapshot check in visibility function

And still I didn't find safe way to cleanup CLOG space.
Alternative solution is to implement "local CLOG" for such transactions.
The straightforward solution is to use hashtable. But it may cause memory overflow if we have long living backend which performs huge number of transactions.
Also in this case we need to change visibility check functions.

So I have implemented simplest solution with frozen xid and force backend termination in case of transaction rollback (so user will no see inconsistent behavior).
Attached please find global_private_temp_replica.patch which implements this approach.
It will be nice if somebody can suggest better solution for temporary tables at replica.

This is another hard issue. Probably backend temination should be acceptable solution. I don't understand well to this area, but if replica allows writing (to global temp tables), then replica have to have local CLOG.

There are several problems:

1. How to choose XID for writing transaction at standby.  The simplest solution is to just add 0x7fffffff to the current XID.
It eliminates possibility of conflict with normal XIDs (received from master).
But requires changes in visibility functions. Visibility check function do not know OID of tuple owner, just XID stored in the tuple header. It should make a decision just based on this XID.

2. How to perform cleanup of not needed XIDs. Right now there is quite complex logic of how to free CLOG pages.

3. How to implement visibility rules to such XIDs.

in theory every session can have own CLOG. When you finish session, you can truncate this file. 


CLOG for global temp tables can be more simple then standard CLOG. Data are not shared, and life of data (and number of transactions) can be low.

Another solution is wait on ZHeap storage and replica can to have own UNDO log.

I thought about implementation of special table access method for temporary tables.

+1
 
I am trying to understand now if  it is the only possible approach or there are simpler solutions.


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik


On 20.08.2019 20:01, Pavel Stehule wrote:
Another solution is wait on ZHeap storage and replica can to have own UNDO log.

I thought about implementation of special table access method for temporary tables.

+1
 

Unfortunately implementing special table access method for temporary tables doesn't solve all problems.
XID generation is not part of table access methods.
So we still need to assign some XID to write transaction at replica which will not conflict with XIDs received from master.
Actually only global temp tables can be updated at replica and so assigned XIDs can be stored only in tuples of such relations.
But still I am not sure that we can use arbitrary XID for such transactions at replica.

Also I upset by amount of functionality which has to be reimplemented for global temp tables if we really want to provide access method for them:

1. CLOG
2. vacuum
3. MVCC visibility

And still it is not possible to encapsulate all changes need to support writes to temp tables at replica inside table access method.
XID assignment, transaction commit and abort, subtransactions - all this places need to be patched.



-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: Global temporary tables

konstantin knizhnik


On 21.08.2019 11:54, Konstantin Knizhnik wrote:


On 20.08.2019 20:01, Pavel Stehule wrote:
Another solution is wait on ZHeap storage and replica can to have own UNDO log.

I thought about implementation of special table access method for temporary tables.

+1
 

Unfortunately implementing special table access method for temporary tables doesn't solve all problems.
XID generation is not part of table access methods.
So we still need to assign some XID to write transaction at replica which will not conflict with XIDs received from master.
Actually only global temp tables can be updated at replica and so assigned XIDs can be stored only in tuples of such relations.
But still I am not sure that we can use arbitrary XID for such transactions at replica.

Also I upset by amount of functionality which has to be reimplemented for global temp tables if we really want to provide access method for them:

1. CLOG
2. vacuum
3. MVCC visibility

And still it is not possible to encapsulate all changes need to support writes to temp tables at replica inside table access method.
XID assignment, transaction commit and abort, subtransactions - all this places need to be patched.


I was able to fully support work with global temp tables at replica (including subtransactions).
The patch is attached. Also you can find this version in https://github.com/postgrespro/postgresql.builtin_pool/tree/global_temp_hot

Right now transactions at replica updating global temp table are assigned special kind of GIDs which are not related with XIDs received from master.
So special visibility rules are used for such tables at replica. Also I have to patch TransactionIdIsInProgress, TransactionIdDidCommit, TransactionIdGetCurrent
functions to correctly handle such XIDs. In principle it is possible to implement global temp tables as special heap access method. But it will require copying a lot of code (heapam.c)
so I prefer to add few checks to existed functions.

There are still some limitations:
- Number of transactions at replica which update temp tables is limited by 2^32 (wraparound problem is not addressed).
- I have to maintain in-memory analog of CLOG for such transactions which is also not cropped. It means that for 2^32 transaction size of bitmap can grow up to  0.5Gb.

I try to understand what are the following steps in global temp tables support.
This is why I want to perform short survey - what people are expecting from global temp tables:

1. I do not need them at all.
2. Eliminate catalog bloating.
3. Mostly needed for compatibility with Oracle (simplify porting,...).
4. Parallel query execution.
5. Can be used at replica.
6. More efficient use of resources (first of all memory).

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

global_shared_temp_replica.patch (110K) Download Attachment
123