pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.

view thread (8+ messages)  latest in thread

Message-ID: <1828c313-5a85-4e31-93b4-ff2a37fad862@gmail.com>
Permalink:  ../1828c313-5a85-4e31-93b4-ff2a37fad862@gmail.com/
Also on:    postgresql.org/message-id/1828c313-5a85-4e31-93b4-ff2a37fad862@gmail.com

 · 

reply

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