Subject: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)

From: Thomas Simpson <[email-masked]>
Date: Thu, 18 Jul 2024 19:08:06 -0400

[Added cross post to [email-masked] - 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-masked]> wrote:
>
>
>     On 18-Jul-2024 16:32, Ron Johnson wrote:
>>     On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson
>>     <[email-masked]> 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
>
>

---

From: Ron Johnson <[email-masked]>
Date: Fri, 19 Jul 2024 08:21:36 -0400

200TB... how do you currently back up your database?

On Fri, Jul 19, 2024 at 5:08 AM Thomas Simpson <[email-masked]> wrote:

> 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
> >200TB; hackers may be a better home for the discussion than current admin
> list]
>
> Hi Ron
>
> 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.
>
> 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.
>
> 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.
>
>> On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson <[email-masked]> wrote:
>> [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.
>>>
>> 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)
>>
>> * 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)
>>
>> 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
>>
>>
>>

---

From: Scott Ribe <[email-masked]>
Date: Fri, 19 Jul 2024 07:26:47 -0600

Do you actually have 100G networking between the nodes? Because if not, a single CPU should be able to saturate 10G. 

Likewise the receiving end would need disk capable of keeping up. Which brings up the question, why not write to disk, but directly to the destination rather than write locally then copy?

Do you require dump-reload because of suspected corruption? That's a tough one. But if not, if the goal is just to get up and running on a new server, why not pg_basebackup, streaming replica, promote? That depends on the level of data modification activity being low enough that pg_basebackup can keep up with WAL as it's generated and apply it faster than new WAL comes in, but given that your server is currently keeping up with writing that much WAL and flushing that many changes, seems likely it would keep up as long as the network connection is fast enough. Anyway, in that scenario, you don't need to care how long pg_basebackup takes.

If you do need a dump/reload because of suspected corruption, the only thing I can think of is something like doing it a table at a time--partitioning would help here, if practical.

---

From: Thomas Simpson <[email-masked]>
Date: Fri, 19 Jul 2024 09:46:14 -0400

Hi Scott,

I realize some of the background was snipped on what I sent to the 
hacker list, I'll try to fill in the details.

Short background is very large database ran out of space during vacuum 
full taking down the server.  There is a replica which was applying the 
WALs and so it too ran out of space.  On restart after clearing some 
space, the database came back up but left over the in-progress rebuild 
files.  I've cleared that replica and am using it as my rebuild target 
just now.

Trying to identify the 'orphan' files and move them away always led to 
the database spotting the supposedly unused files having gone and 
refusing to start, so I had no successful way to clean up and get space 
back.

Last resort after discussion is pg_dumpall & reload.  I'm doing this via 
a network pipe (netcat) as I do not have the vast amount of storage 
necessary for the dump file to be stored (in any format).

> Do you actually have 100G networking between the nodes? Because if not, a single CPU should be able to saturate 10G.
Servers connect via 10G WAN; sending is not the issue, it's application 
of the incoming stream on the destination which is bottlenecked.
> Likewise the receiving end would need disk capable of keeping up. Which brings up the question, why not write to disk, but directly to the destination rather than write locally then copy?
In this case, it's not a local write, it's piped via netcat.
> If you do need a dump/reload because of suspected corruption, the only thing I can think of is something like doing it a table at a time--partitioning would help here, if practical.

The basebackup is, to the best of my understanding, essentially just 
copying the database files.  Since the failed vacuum has left extra 
files, my expectation is these too would be copied, leaving me in the 
same position I started in.  If I'm wrong, please tell me as that would 
be vastly quicker - it is how I originally set up the replica and it 
took only a few hours on the 10G link.

The inability to get a clean start if I move any files out the way leads 
me to be concerned for some underlying corruption/issue and the 
recommendation earlier in the discussion was opt for dump/reload as the 
fail-safe.

Resigned to my fate, my thoughts were to see if there is a way to 
improve the dump-reload approach for the future.  Since dump-reload is 
the ultimate upgrade suggestion in the documentation, it seems 
worthwhile to see if there is a way to improve the performance of that 
especially as very large databases like mine are a thing with 
PostgreSQL.  From a quick review of pg_dump.c (I'm no expert on it 
obviously), it feels like it's already doing most of what needs done and 
the addition is some sort of multi-thread coordination with a restore 
client to ensure each thread can successfully complete each task it has 
before accepting more work.  I realize that's actually difficult to 
implement.

Thanks

Tom

---

From: Scott Ribe <[email-masked]>
Date: Fri, 19 Jul 2024 13:34:56 -0600

> On Jul 19, 2024, at 7:46 AM, Thomas Simpson <[email-masked]> wrote:
> 
> I realize some of the background was snipped on what I sent to the hacker list, I'll try to fill in the details.

I was gone from my computer for a day and lost track of the thread.

Perhaps logical replication could help you out here?

---

From: Thomas Simpson <[email-masked]>
Date: Fri, 19 Jul 2024 16:23:29 -0400

Hi Scott

On 19-Jul-2024 15:34, Scott Ribe wrote:
>> On Jul 19, 2024, at 7:46 AM, Thomas Simpson<[email-masked]>  wrote:
>>

I'm not sure - perhaps, but at this point, I've got that dump/reload 
running and provided it completes ok (in about 20 days time at current 
rate), I'll be fine with this.

The database itself is essentially an archive of data so is no longer 
being added to at this point, so it's an annoyance for the rebuild time 
rather than a disaster.

[But incidentally, I am working on an even larger project which is 
likely to make this one seem small, so improvement around large 
databases is important to me.]

However, my thought is around how to avoid this issue in the future and 
to improve the experience for others faced with the dump-reload which is 
always the fall-back upgrade suggestion between versions.

Getting parallelism should be possible and the current pg_dump does that 
for directory mode from what I can see - making multiple threads etc.  
according to parallel.c in pg_dump, it even looks like most of where my 
thought process was going is actually already there.

The extension should be adding synchronization/checkpointing between the 
generating dump and the receiving reload to ensure objects are not 
processed until all their requirements are already present in the new 
database.  This is all based around routing via network streams instead 
of the filesystem as currently happens.

Perhaps this is already in place since the restore can be done in 
parallel, so must need to implement that ordering already?  If someone 
with a good understanding of dump is able to comment or even give 
suggestions, I'm not against making an attempt to implement something as 
a first attempt.

I see Tom Lane from git blame did a bunch of work around the parallel 
dump back in 2020 - perhaps he could make suggestions either via private 
direct email or the list ?

Thanks

Tom

---

From: Andrew Dunstan <[email-masked]>
Date: Mon, 22 Jul 2024 11:50:10 -0400

On 2024-07-19 Fr 9:46 AM, Thomas Simpson wrote:
> full taking down the server.  There is a replica which was applying 
> the WALs and so it too ran out of space.  On restart after clearing 
> some space, the database came back up but left over the in-progress 
> rebuild files.  I've cleared that replica and am using it as my 
> rebuild target just now.
>
> refusing to start, so I had no successful way to clean up and get 
> space back.
>
> Last resort after discussion is pg_dumpall & reload.  I'm doing this 
> via a network pipe (netcat) as I do not have the vast amount of 
> storage necessary for the dump file to be stored (in any format).
>
> Servers connect via 10G WAN; sending is not the issue, it's 
> application of the incoming stream on the destination which is 
> bottlenecked.
> same position I started in.  If I'm wrong, please tell me as that 
> would be vastly quicker - it is how I originally set up the replica 
> and it took only a few hours on the 10G link.
>
> The inability to get a clean start if I move any files out the way 
> leads me to be concerned for some underlying corruption/issue and the 
> recommendation earlier in the discussion was opt for dump/reload as 
> the fail-safe.
>
> obviously), it feels like it's already doing most of what needs done 
> and the addition is some sort of multi-thread coordination with a 
> restore client to ensure each thread can successfully complete each 
> task it has before accepting more work.  I realize that's actually 
> difficult to implement.
>
>

There is a plan for a non-text mode for pg_dumpall. I have started work 
on it, and hope to have a WIP patch in a month or so. It's not my 
intention to parallelize it for the first cut, but it could definitely 
be parallelizable in future. However, it will require writing to disk 
somewhere, albeit that the data will be compressed. It's well nigh 
impossible to parallelize text format dumps.

Restoration of custom and directory format dumps has long been 
parallelized. Parallel dumps require directory format, and so will 
non-text pg_dumpall.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com