Thread: 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
Attachment
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