From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: pg_dump/pg_restore --jobs practical limit?
Date: Wed, 1 Nov 2023 20:20:16 -0500
Message-ID: <1828c313-5a85-4e31-93b4-ff2a37fad862@gmail.com> (raw)
In-Reply-To: <CH0PR10MB74985C1A93DB676D20EF8DDDF7A6A@CH0PR10MB7498.namprd10.prod.outlook.com>
References: <cabaebac-ae7b-493a-a127-aee941f5f335@gmail.com>
<0c82a8d32181fca3b979347f34ffbb2f7fdf0e90.camel@cybertec.at>
<93e2708b-2dae-4a04-8660-ba5e1f5b857a@gmail.com>
<CH0PR10MB74985C1A93DB676D20EF8DDDF7A6A@CH0PR10MB7498.namprd10.prod.outlook.com>
On 11/1/23 20:05, Brad White wrote:
>
>
> ----------------------------------------------------------------------------
> *From:* Ron <ronljohnsonjr@gmail.com>
> *Sent:* Thursday, November 2, 2023 3:01:47 AM
> *To:* pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ronljohnsonjr@gmail.com
Subject: Re: pg_dump/pg_restore --jobs practical limit?
In-Reply-To: <1828c313-5a85-4e31-93b4-ff2a37fad862@gmail.com>
References: <cabaebac-ae7b-493a-a127-aee941f5f335@gmail.com> <0c82a8d32181fca3b979347f34ffbb2f7fdf0e90.camel@cybertec.at> <93e2708b-2dae-4a04-8660-ba5e1f5b857a@gmail.com> <CH0PR10MB74985C1A93DB676D20EF8DDDF7A6A@CH0PR10MB7498.namprd10.prod.outlook.com> <1828c313-5a85-4e31-93b4-ff2a37fad862@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox