Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUjb1-005I2d-S6 for pgsql-admin@arkaria.postgresql.org; Fri, 19 Jul 2024 09:08:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sUjaz-00DLuz-Vi for pgsql-admin@arkaria.postgresql.org; Fri, 19 Jul 2024 09:08:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUaGI-005diW-5t; Thu, 18 Jul 2024 23:10:10 +0000 Received: from mail.network-systems-solutions.net ([162.250.175.178]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUaGC-000HYa-Rp; Thu, 18 Jul 2024 23:10:07 +0000 Received: from messaging.network-systems-solutions.net (unknown [192.168.242.37]) by mail.network-systems-solutions.net (Postfix) with ESMTP id D5C7D81510; Thu, 18 Jul 2024 23:47:48 +0000 (UTC) Received: from aox (localhost [127.0.0.1]) by messaging.network-systems-solutions.net (Postfix) with ESMTP id 21A218E0736; Thu, 18 Jul 2024 19:09:55 -0400 (EDT) Received: from jim@talentstack.to by aox (Archiveopteryx 3.2.0) with esmtpsa id 1721344194-3614-3610/6/46; Thu, 18 Jul 2024 23:09:54 +0000 Content-Type: multipart/alternative; boundary=------------EEhO090TrWuJCHu3C4tAHJ05 Message-Id: <95f5bff5-b392-4c2f-9708-8fd5547c620c@talentstack.to> Date: Thu, 18 Jul 2024 19:08:06 -0400 Mime-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues) To: pgsql-admin@lists.postgresql.org, pgsql-hackers@lists.postgresql.org References: <7dee2c00-6184-49c5-b303-58113c1d04c6@talentstack.to> Content-Language: en-CA From: Thomas Simpson In-Reply-To: X-NSSLTD-Archiving: Added to store 1 as 91337 X-Scanned-By: MIMEDefang 2.84 on 127.0.1.1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --------------EEhO090TrWuJCHu3C4tAHJ05 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable [Added cross post to pgsql-hackers@lists.postgresql.org - background is=20 multi-TB database needs recovered via pgdumpall & reload, thoughts on=20 ways to make pg_dump scale to multi-thread to expedite loading to a new=20 cluster.=C2=A0 Straight dump to a file is impractical as the dump will = be=20 >200TB; hackers may be a better home for the discussion than current=20 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=20 > 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=20 proposal is a new special mode which specifically makes multiple output=20 streams across *network sockets* to a listener which is listening on the=20 other side.=C2=A0 The goal is avoiding any files at all and only using=20 multiple network streams to gain multi-threaded processing with some=20 co-ordination to keep things organized and consistent. This would really be specifically for the use-case of dump/reload=20 upgrade or recreate rather than everyday use.=C2=A0 And particularly for = very=20 large databases. Looking at pg_dump.c it's doing the baseline organization but the=20 extension would be adding the required coordination with the=20 destination.=C2=A0 So, for a huge table (I have many) these would go in=20 different streams but if there is a dependency (FK relations etc) the=20 checkpoint needs to ensure those are met before proceeding. Worst case=20 scenario it would end up using only 1 thread but it would be very=20 unusual to have a database where every table depends on another table=20 all the way down. In theory at least, some gains should be achieved for typical databases=20 where a degree of parallelism is possible. Thanks Tom > On Thu, Jul 18, 2024 at 4:55=E2=80=AFPM Thomas Simpson wrote: > > > On 18-Jul-2024 16:32, Ron Johnson wrote: >> On Thu, Jul 18, 2024 at 3:01=E2=80=AFPM Thomas Simpson >> 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.=C2=A0 >> However, I doubt I am the only person in the world that needs >> to dump and reload a large database.=C2=A0 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. >> >> =C2=A0No database does fast single-threaded backups. > > Agreed.=C2=A0 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)=C2=A0 ('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.=C2=A0 It may even be doing a lot of this = for > 'directory' mode?=C2=A0 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 > > --------------EEhO090TrWuJCHu3C4tAHJ05 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

[Added cross post to pgsql-hackers@lists.po= stgresql.org - 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.=C2=A0 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.=C2=A0 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.=C2=A0 And particularl= y 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.=C2=A0 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.=C2=A0 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=E2=80=AFPM Thomas Simpson <ts@talentstack.to> wrote:


On 18-Jul-2024 16:32, Ron Johnson wrote:

Agreed.=C2=A0 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)=C2=A0 ('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.=C2=A0 It may even be doing a lot of this for 'directory' mode?=C2=A0 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


--------------EEhO090TrWuJCHu3C4tAHJ05--