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 1qyMNk-007rTh-WF for pgsql-general@arkaria.postgresql.org; Thu, 02 Nov 2023 01:20:25 +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 1qyMNj-00A40O-F1 for pgsql-general@arkaria.postgresql.org; Thu, 02 Nov 2023 01:20:23 +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 1qyMNj-00A40G-3O for pgsql-general@lists.postgresql.org; Thu, 02 Nov 2023 01:20:23 +0000 Received: from mail-yb1-xb2b.google.com ([2607:f8b0:4864:20::b2b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qyMNg-004FQx-SY for pgsql-general@lists.postgresql.org; Thu, 02 Nov 2023 01:20:22 +0000 Received: by mail-yb1-xb2b.google.com with SMTP id 3f1490d57ef6-d9a3d737d66so392883276.2 for ; Wed, 01 Nov 2023 18:20:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1698888019; x=1699492819; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=mM4NEm5D4DGA/FEPeDZ2x0CxEmdGMEL3C1NSJ4qca1w=; b=LSk5pgtDfPVGPf5wv1GJE/PTOoYI4/TQtGLC5rbHeLpK44TH7AQNUBdKSdY4AaxQEI 8hKxp65DFKcahLud8YNn7GwMpyo+Eg+XkPn6qD7HoCrQMMU1wZSI0/ZeIhIcVgQPEWdu UzlIuUwLYGfNkJ5a3TK9hVLyKm1kW6wHkEbXNht0CSMSJX7i+/tMLgXaZJT0/e4PhADK Qiq9NbbB/eoTfOV/8ScrT9VL9v4k1HL9YbaHRflLWxCzH4U8z8IGX/ybiBBwyGlhwNf8 EBhMM1/FhOaK4euDQWRwWJnfAhqu7Nn/q8coZInLLQPI7PhJFjHUeHZJ/kWLdIWu3rOf pPog== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1698888019; x=1699492819; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=mM4NEm5D4DGA/FEPeDZ2x0CxEmdGMEL3C1NSJ4qca1w=; b=wOGwcZDoJSBblCJW89SLEaF//k8Ddkqgse9ehLADEqGgpETZ9EpRE+12rSjSij11pK jkadKhjksC/OrPO++jpR9Adc8dwks0t9/4FH8nzPp6R/yKWwPyrnZgE8gJAHFQFEyj/v t5ZcGiBpErAaHyWW9v/SUlKH/rl6xqHYjpVRiBTbN5ElrwHLhhYcQwicqUFEUf1TigUh gFboLEUK8haF8Bi8WfI5wVugemm+Wqg69Fhzx88rbesNjFftBeMB7LbMTQsINFm9RJEz sYpVY3oaO+/bMGWyhPCkaUTvh8qknfZ3A6Ulyc9XQ9lyVgcXnscDoXbe/UVnYvROFT6n ER4w== X-Gm-Message-State: AOJu0YzqNOOKsTaYsndhjd+vkDoqpBxCXrP8Jpp4mY2XaKvQbr4jEl0m LvPLAjIt6Yvi3/fZxx8c9q72gK52AH0= X-Google-Smtp-Source: AGHT+IG2SwBmkq9FkSZ76vNqFRvG2bFSx2bFWPDjE89FzNGXuGdYr/8j2/QDrACIs9QA/RHK3+hzfw== X-Received: by 2002:a25:dfc7:0:b0:d0f:846c:ef7b with SMTP id w190-20020a25dfc7000000b00d0f846cef7bmr14732864ybg.17.1698888018952; Wed, 01 Nov 2023 18:20:18 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810::44? ([2600:1700:bd2c:b810::44]) by smtp.googlemail.com with ESMTPSA id z13-20020a25ad8d000000b00d9a43500f1dsm565318ybi.28.2023.11.01.18.20.18 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 01 Nov 2023 18:20:18 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------x1dwz0GhbR0R1AgF8vkTcb9W" Message-ID: <1828c313-5a85-4e31-93b4-ff2a37fad862@gmail.com> Date: Wed, 1 Nov 2023 20:20:16 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: pg_dump/pg_restore --jobs practical limit? Content-Language: en-US To: "pgsql-general@lists.postgresql.org" References: <0c82a8d32181fca3b979347f34ffbb2f7fdf0e90.camel@cybertec.at> <93e2708b-2dae-4a04-8660-ba5e1f5b857a@gmail.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------x1dwz0GhbR0R1AgF8vkTcb9W Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/1/23 20:05, Brad White wrote: > > > ---------------------------------------------------------------------------- > *From:* Ron > *Sent:* Thursday, November 2, 2023 3:01:47 AM > *To:* 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. --------------x1dwz0GhbR0R1AgF8vkTcb9W Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------x1dwz0GhbR0R1AgF8vkTcb9W--