Обсуждение: BUG #15115: temporary AND unlogged tables for parallel db import

The following bug has been logged on the website:

Bug reference:      15115
Logged by:           ew
Email address:      ennowein@googlemail.com
PostgreSQL version: 10.3
Operating system:   Centos 7
Description:

For fresh (large-) database import, we would like to be able to use
temporary tables which are also unlogged, here is why:
- Source data is organized in files per month (several million entries each)
of which we have multiple years
- Each source (-month) is independent of all others
- Each source month is imported as its own session with multiple preparation
operations (4 tables + 2 indexes) 
- Each session is completely independent of all others including the main db
status until the final insertion/commit step
- All session data is deleted immediately after insertion into the main db

A nice way to implement this is to use gnu parallel to start worker sessions
per month-input-file on a parallel machine (or several, actually). For this,
we implemented an sql script that uses the fact that temp tables are visible
only locally (otherwise, we'd have to name them all specifically).
We can tune the system quite well by the number of workers/cpu/RAM/disk
performance. 

Utilizing this parallel capability, we can reduce the import time by an
order of magnitude - from several days to a few hours. 
The "safety" aspect here is irrelevant in this stage as in case of a crash,
we'd simply restart the process from scratch.

Specifically WAL logging and checkpointing of the temporary data is very
cumbersome, slows the process, makes it actually less "reliable" (as it can
lead to a quasi-deadlock by causing traffic jams and swapping which in turn
practically stops the process).

Ironically, we can gain a little bit of optimization by unlogging the main
db - which is the only data we are actually interested in (we turn it back
on right after import of course).

There are some forum notes that we found which claim that "temporary" and
"unlogged" share the functionality of disabling WAL but according to what we
see that doesn't seem to be the case, the parallel import sessions generate
a huge (and process limiting) amount of logging data despite being
"temporary".

We would gladly share all data with the developers.

>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:

 PG> For fresh (large-) database import, we would like to be able to use
 PG> temporary tables which are also unlogged,

All temporary tables are unlogged.

Whatever you're looking at that leads you to conclude otherwise, you are
misinterpreting. Also, your message is not about a bug, so please take
the issue to an appropriate forum (e.g. the -general or -performance
mailing list or the IRC channel, #postgresql on freenode.net).

-- 
Andrew (irc:RhodiumToad)

Yes, we found out by trial and error that temporary and unlogged are
mutually exclusive while the documentation does not point that out
explicitly.

This is an enhancement request to enable having tables which are both
temporary and unlogged.


On 3/16/18 07:03, Andrew Gierth wrote:
>>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:
> 
>  PG> For fresh (large-) database import, we would like to be able to use
>  PG> temporary tables which are also unlogged,
> 
> All temporary tables are unlogged.
> 
> Whatever you're looking at that leads you to conclude otherwise, you are
> misinterpreting. Also, your message is not about a bug, so please take
> the issue to an appropriate forum (e.g. the -general or -performance
> mailing list or the IRC channel, #postgresql on freenode.net).
> 

>>>>> "Enno" == Enno Wein <ennowein@yahoo.com> writes:

 Enno> This is an enhancement request to enable having tables which are
 Enno> both temporary and unlogged.

ALL TEMPORARY TABLES ARE ALREADY UNLOGGED.
==========================================

I don't know how to make this any clearer.

-- 
Andrew.

I think you made that clear :-)

However, it doesn't seem to work like that. Even with ONLY either
unlogged (the global ones) and temprorary tables (the local ones), the
system still produces a huge amount of WAL/checkpoint writes. In fact,
the "wal-writer" and "checkpoint" processes are the biggest writers in
the system. checkpoint process uses up to 9GB of Ram and wal produces
more writes than any other process.




On 3/16/18 07:31, Andrew Gierth wrote:
>>>>>> "Enno" == Enno Wein <ennowein@yahoo.com> writes:
> 
>  Enno> This is an enhancement request to enable having tables which are
>  Enno> both temporary and unlogged.
> 
> ALL TEMPORARY TABLES ARE ALREADY UNLOGGED.
> ==========================================
> 
> I don't know how to make this any clearer.
> 

>>>>> "Enno" == Enno Wein <ennowein@yahoo.com> writes:

 Enno> I think you made that clear :-)

Obviously not clear enough.

 Enno> However, it doesn't seem to work like that. Even with ONLY either
 Enno> unlogged (the global ones) and temprorary tables (the local ones), the
 Enno> system still produces a huge amount of WAL/checkpoint writes.

So what you just said above is that you get a lot of WAL/checkpoint
writes when using unlogged non-temporary tables. What this implies is
that you have something _else_ going on, involving persistent tables
(i.e. non-temporary non-unlogged tables - these are the only kind that
get their data changes WAL-logged).

So please take this to a more appropriate forum where you can ask for
help in determining where your WAL or checkpoint traffic is coming from,
rather than wasting your time and ours in asking for a feature that
already exists and which from your own description above has nothing to
do with your problem.

-- 
Andrew.

Greetings,

* Enno Wein (ennowein@yahoo.com) wrote:
> However, it doesn't seem to work like that. Even with ONLY either
> unlogged (the global ones) and temprorary tables (the local ones), the
> system still produces a huge amount of WAL/checkpoint writes. In fact,
> the "wal-writer" and "checkpoint" processes are the biggest writers in
> the system. checkpoint process uses up to 9GB of Ram and wal produces
> more writes than any other process.

"Doesn't seem to work like that" really isn't enough to go on.  If you'd
like for someone to look into this, posting a reproducible test case
which illustrates the bug you think you've found would be your best bet.

Thanks!

Stephen

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Enno" == Enno Wein <ennowein@yahoo.com> writes:
>  Enno> However, it doesn't seem to work like that. Even with ONLY either
>  Enno> unlogged (the global ones) and temprorary tables (the local ones), the
>  Enno> system still produces a huge amount of WAL/checkpoint writes.

> So what you just said above is that you get a lot of WAL/checkpoint
> writes when using unlogged non-temporary tables. What this implies is
> that you have something _else_ going on, involving persistent tables
> (i.e. non-temporary non-unlogged tables - these are the only kind that
> get their data changes WAL-logged).

One theory is that the OP's workload involves a huge number of short-lived
temp tables, and then the WAL traffic is coming from the system catalog
changes involved in creating/dropping those tables (since even a temp
table must have entries in pg_class, pg_attribute, etc).  That's only
a theory though, given the lack of any hard evidence in this thread.

            regards, tom lane

That sounds right.
Every session (72 for now) creates and drops several (temp-) tables and
indexes. Every session runs for about 30min, then does a final insert
and exits afterwards.
Depending on parallelism chosen, we run 8, 16 or 24 sessions in parallel.
The main, (global-) table which persists has been declared as either
logged or unlogged, there is much WAL traffic in any case.




On 3/16/18 08:25, Tom Lane wrote:
> Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
>> "Enno" == Enno Wein <ennowein@yahoo.com> writes:
>>  Enno> However, it doesn't seem to work like that. Even with ONLY either
>>  Enno> unlogged (the global ones) and temprorary tables (the local ones), the
>>  Enno> system still produces a huge amount of WAL/checkpoint writes.
> 
>> So what you just said above is that you get a lot of WAL/checkpoint
>> writes when using unlogged non-temporary tables. What this implies is
>> that you have something _else_ going on, involving persistent tables
>> (i.e. non-temporary non-unlogged tables - these are the only kind that
>> get their data changes WAL-logged).
> 
> One theory is that the OP's workload involves a huge number of short-lived
> temp tables, and then the WAL traffic is coming from the system catalog
> changes involved in creating/dropping those tables (since even a temp
> table must have entries in pg_class, pg_attribute, etc).  That's only
> a theory though, given the lack of any hard evidence in this thread.
> 
>             regards, tom lane
> 

Enno Wein <ennowein@yahoo.com> writes:
> Every session (72 for now) creates and drops several (temp-) tables and
> indexes. Every session runs for about 30min, then does a final insert
> and exits afterwards.
> Depending on parallelism chosen, we run 8, 16 or 24 sessions in parallel.
> The main, (global-) table which persists has been declared as either
> logged or unlogged, there is much WAL traffic in any case.

That still sounds like it'd just add up to a few temp table creations/
deletions per minute, which would not account for any huge volume of
WAL traffic.  I'm suspicious that somewhere you've got activity going
into a table that you forgot to make temp or unlogged.

You might grab pg_xlogdump or a logical decoding tool and look into
the WAL stream to see exactly what's going by.

            regards, tom lane

On Fri, Mar 16, 2018 at 02:31:40PM +0000, Andrew Gierth wrote:
> ALL TEMPORARY TABLES ARE ALREADY UNLOGGED.
> ==========================================
>
> I don't know how to make this any clearer.

Please note that the definitions of those relations are WAL-logged
though, but not their data.
--
Michael