public inbox for [email protected]
help / color / mirror / Atom feedFrom: Thomas Simpson <[email protected]>
To: [email protected]
To: [email protected]
Subject: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
Date: Thu, 18 Jul 2024 19:08:06 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANzqJaD_bEyG9Fve0OPGUfAdjJL36_ZKm4aLQuMytCBp0yYOOA@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<CANzqJaCpSNZU8AaHPLS2Ms7WzsqBX0L1ZufD8cmUaLi78uA1=A@mail.gmail.com>
<[email protected]>
<CANzqJaD_bEyG9Fve0OPGUfAdjJL36_ZKm4aLQuMytCBp0yYOOA@mail.gmail.com>
[Added cross post to [email protected] - background is
multi-TB database needs recovered via pgdumpall & reload, thoughts on
ways to make pg_dump scale to multi-thread to expedite loading to a new
cluster. Straight dump to a file is impractical as the dump will be
>200TB; hackers may be a better home for the discussion than current
admin list]
Hi Ron
On 18-Jul-2024 18:41, Ron Johnson wrote:
> Multi-threaded writing to the same giant text file won't work too
> well, when all the data for one table needs to be together.
>
> Just temporarily add another disk for backups.
>
For clarity, I'm not proposing multi threaded writing to one file; the
proposal is a new special mode which specifically makes multiple output
streams across *network sockets* to a listener which is listening on the
other side. The goal is avoiding any files at all and only using
multiple network streams to gain multi-threaded processing with some
co-ordination to keep things organized and consistent.
This would really be specifically for the use-case of dump/reload
upgrade or recreate rather than everyday use. And particularly for very
large databases.
Looking at pg_dump.c it's doing the baseline organization but the
extension would be adding the required coordination with the
destination. So, for a huge table (I have many) these would go in
different streams but if there is a dependency (FK relations etc) the
checkpoint needs to ensure those are met before proceeding. Worst case
scenario it would end up using only 1 thread but it would be very
unusual to have a database where every table depends on another table
all the way down.
In theory at least, some gains should be achieved for typical databases
where a degree of parallelism is possible.
Thanks
Tom
> On Thu, Jul 18, 2024 at 4:55 PM Thomas Simpson <[email protected]> wrote:
>
>
> On 18-Jul-2024 16:32, Ron Johnson wrote:
>> On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson
>> <[email protected]> wrote:
>> [snip]
>>
>> [BTW, v9.6 which I know is old but this server is stuck there]
>>
>> [snip]
>>
>> I know I'm stuck with the slow rebuild at this point.
>> However, I doubt I am the only person in the world that needs
>> to dump and reload a large database. My thought is this is a
>> weak point for PostgreSQL so it makes sense to consider ways
>> to improve the dump reload process, especially as it's the
>> last-resort upgrade path recommended in the upgrade guide and
>> the general fail-safe route to get out of trouble.
>>
>> No database does fast single-threaded backups.
>
> Agreed. My thought is that is should be possible for a 'new
> dumpall' to be multi-threaded.
>
> Something like :
>
> * Set number of threads on 'source' (perhaps by querying a
> listening destination for how many threads it is prepared to
> accept via a control port)
>
> * Select each database in turn
>
> * Organize the tables which do not have references themselves
>
> * Send each table separately in each thread (or queue them until a
> thread is available) ('Stage 1')
>
> * Rendezvous stage 1 completion (pause sending, wait until
> feedback from destination confirming all completed) so we have a
> known consistent state that is safe to proceed to subsequent tables
>
> * Work through tables that do refer to the previously sent in the
> same way (since the tables they reference exist and have their
> data) ('Stage 2')
>
> * Repeat progressively until all tables are done ('Stage 3', 4
> etc. as necessary)
>
> The current dumpall is essentially doing this table organization
> currently [minus stage checkpoints/multi-thread] otherwise the
> dump/load would not work. It may even be doing a lot of this for
> 'directory' mode? The change here is organizing n threads to
> process them concurrently where possible and coordinating the
> pipes so they only send data which can be accepted.
>
> The destination would need to have a multi-thread listen and
> co-ordinate with the sender on some control channel so feed back
> completion of each stage.
>
> Something like a destination host and control channel port to
> establish the pipes and create additional netcat pipes on
> incremental ports above the control port for each thread used.
>
> Dumpall seems like it could be a reasonable start point since it
> is already doing the complicated bits of serializing the dump data
> so it can be consistently loaded.
>
> Probably not really an admin question at this point, more a
> feature enhancement.
>
> Is there anything fundamentally wrong that someone with more
> intimate knowledge of dumpall could point out?
>
> Thanks
>
> Tom
>
>
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox