Re: pg_dump/pg_restore --jobs practical limit?

On 11/1/23 20:05, Brad White wrote:
>
>
> ----------------------------------------------------------------------------
> *From:* Ron <ronljohnsonjr(at)gmail(dot)com>
> *Sent:* Thursday, November 2, 2023 3:01:47 AM
> *To:* pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
> *Subject:* Re: pg_dump/pg_restore --jobs practical limit?
>
> On 11/1/23 15:42, Laurenz Albe wrote:
>> On Wed, 2023-11-01 at 13:09 -0500, Ron wrote:
>>> I will "soon" migrate some 1+ TB database from Pg 9.6.24 on RHEL 6 VMs to Pg
>>> 14.latest on RHEL 8 VMs.  The VMs have 10Gbps "NICs", SAN-based LUNs managed
>>> by LVM, and are all on ESX blades.  nproc count on some is 16 and on others
>>> is 32.
>>>
>>> Does anyone have experience as to the point of diminishing returns?
>>>
>>> IOW, can I crank them processes up to --jobs=30, will I see no gain -- or
>>> even degradation -- after, for example, --jobs=24?
>>>
>>> This would be for both pg_dump and pg_restore (which would be run on the
>>> RHEL 8 VM).
>> Test, test, test. Theoretical considerations are pretty worthless,
>
> Which is why I asked if anyone has experience.
>
>> and it is easy to measure that.
>
> Not necessarily. Our test systems are way too small (only good enough to
> validate that the script works correctly), and there's always something
> (sometimes a lot, sometime just "some") going on in production, whether
> it's my customer's work, or the SAN (like snapshotting /every/ VM and then
> copying the snapshots to the virtual tape device) or something else.
>
> Sure,  but are the new systems busy already?
> Ideally you could run tests on them before they are put into production.

Testing pg_restore with different --jobs= values will be easier. pg_dump is
what's going to be reading from a constantly varying system.

--
Born in Arizona, moved to Babylonia.