Message-ID: <5e3a16ad-c8e2-4695-93cb-74121c3add56@dunslane.net>
Date: Mon, 22 Jul 2024 11:50:10 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem
 full during vacuum - space recovery issues)
To: Thomas Simpson <ts@talentstack.to>,
 Scott Ribe <scott_ribe@elevated-dev.com>, pgsql-hackers@lists.postgresql.org
References: <d141b2e2-797c-487a-b492-e4fbeeafad3e@talentstack.to>
 <ef82293d-b970-4678-ba18-ed1560a6922b@pscs.co.uk>
 <d4cdd945-a48b-41e9-8c25-1b198d86b91e@talentstack.to>
 <CANzqJaCpSNZU8AaHPLS2Ms7WzsqBX0L1ZufD8cmUaLi78uA1=A@mail.gmail.com>
 <7dee2c00-6184-49c5-b303-58113c1d04c6@talentstack.to>
 <CANzqJaD_bEyG9Fve0OPGUfAdjJL36_ZKm4aLQuMytCBp0yYOOA@mail.gmail.com>
 <95f5bff5-b392-4c2f-9708-8fd5547c620c@talentstack.to>
 <6F46CFEB-3C3A-4CEC-89DF-57D8225A5863@elevated-dev.com>
 <6107c011-f907-48ba-94e4-884f59c3da1a@talentstack.to>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAmwQTAQgARQIbAwIX
 gAIZAQULCQgHAgMiAgEGFQoJCAsCBBYCAwECHgcWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUC
 ZFlxxwUJGVGAbgAKCRCZ+n/LWfw7gXikB/9ZdcUy6CTBFIIuL/bVsc1eLEW/gJBjJBF6HxNY
 xgEkAgXAp4Lg4A5U+QB9GouFr7+GYxF0BU4hzoGhNPUWltxnHdMWP8nC/38LAqgMi8L/bbsm
 HW5YPBdWYaAZAPJQVfOAgjTbRUb26KSprpyrrJKW0ZmrZfjhNPcQ72jpWzoPLQqx2X6B0fru
 1jq+cBh8lb6r1mJTim1T3JIn+F/v5VpdQS+EL8xqsHkfzKjIPsW3CIXpkypSk6saA55Rkkbl
 26AW8ftPVB0Q6Lnn6FLt9CP0MGNixBQ55yq8r1K+nCBvCCjvQjM8RDm0UUum0WNl+ifQgTLO
 E8TWEnwVtkBf+3QWzsBNBE7KWFkBCADRnOM0FCzsYW6jtncg+dWIagjUZpvaClmqn/sJluLa
 Q3v1VXMQJzYs3eC1gh386W+XBwLRpDj3jzH81lX+p73Re3d3oJW7X+ffsxuzu5ZVdMUkqBYo
 nkAbKxr6gyJ12F/+JkUVzLcoTN+d/7YsQvUVi7NaKH8mJgjz112O4fUe3p9wfAaFa0RXHc5S
 GPzRTYRRlv/XZBIho4J2tkZOnteZJZ+GbxQVlINt6fd8P6al3MWOvpP/ExJPguEfjOsO6Njy
 xjo3WfpD4lHMOR/Oc3/8mScEF84rF2jXbsFgelWnbPWAvXY+pD0dXOFRkagGmC/viwBDqq5b
 5tk76kKmUbZxABEBAAHCwHwEGAEIACYCGwwWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUCZFlx
 5wUJGVGAjgAKCRCZ+n/LWfw7gf+iB/4g8CPY5jihf5r/8EsoIGe2H+dpVmpPF8YGBzTIvCz/
 fQoOq8AX/pE76QEuFnFZWfjw+wgBXgCVmkox2Eflkk6z4ND3pcwGZ6CfCxTQCDk/dij+2DQ4
 6bmDCy/sBgcbz9mTpoLC11HLoPae6YN9nBNQRZDcEFEu54OaVOqlIdbA6m+POIBCXZdHOFc0
 WoDTgxHRzC1jgQNidyd6tKqcsVJs0dzF0oKTmFFmUAqTdJO12LBuNA1rlqrR3EtpYk8B/wtS
 5dIMD7Q8hwQpL+4C6GNpb6ZKnPkLi47pDOLhz2qBrqN+rqUEsT3YnExYpzj5yOBi+FlmV1Hw
 49QYe1sn2ZPs
In-Reply-To: <6107c011-f907-48ba-94e4-884f59c3da1a@talentstack.to>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/5e3a16ad-c8e2-4695-93cb-74121c3add56%40dunslane.net>
Precedence: bulk


On 2024-07-19 Fr 9:46 AM, Thomas Simpson wrote:
>
> 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).
>
> On 19-Jul-2024 09:26, Scott Ribe wrote:
>> 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.
>> 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.
>
> 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.
>
>

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