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 1qyIZt-007SoW-G7 for pgsql-general@arkaria.postgresql.org; Wed, 01 Nov 2023 21:16:41 +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 1qyIZs-0090zE-3w for pgsql-general@arkaria.postgresql.org; Wed, 01 Nov 2023 21:16:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qyIZr-0090z6-P4 for pgsql-general@lists.postgresql.org; Wed, 01 Nov 2023 21:16:39 +0000 Received: from mail-yw1-x1136.google.com ([2607:f8b0:4864:20::1136]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qyIZp-003VEf-77 for pgsql-general@lists.postgresql.org; Wed, 01 Nov 2023 21:16:38 +0000 Received: by mail-yw1-x1136.google.com with SMTP id 00721157ae682-5a7b91faf40so3469467b3.1 for ; Wed, 01 Nov 2023 14:16:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1698873396; x=1699478196; 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=857QmTrRNwHDhsNecjHtDKzRqO7/0GZ1KVU9BZSgzn0=; b=JvZTbIpBRuxU6B9RFt0WCyGQX2S0ra87O66kYmWvbrCtSzi19DDmBAdTeOuvNqdeCI KqgnRpoT4BOT+1fgNEWUwmvyUqxkJ3CBOGkwOK3QkJQW2XY34USVCTLbxdrG7xUEfgtH 5gOvzMMYLVhMmclsQw31kX1qdFjvV4OwVBZ/2ff2Pq0/TbQnh8CdlH0CmkkiLw8OKtEA xoLNDrS1e/3wD6aaJ9DN1VHdj2x3yxftxN5mqCMnrBKUfOaD78rgsApk+nMZuI9OPG7w S/fzS7y6J7GYm8+ASDTlKUDU4g7W2YPiZ4+XbteVN9Xv0P/EZiNJJ1EJ0xlpkk1jwBBq 94tw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1698873396; x=1699478196; 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=857QmTrRNwHDhsNecjHtDKzRqO7/0GZ1KVU9BZSgzn0=; b=OBEOXwnZLmEFzggQU6j71uqbWQNsqiRSzQm6IIZdbWYW5v7Ilw5Am84vQh2TFIORtE efWg9jONGe2z0FdLyjf1E5OWHqHms/QqKajZdicDPz4nCkDqbFosO4GcRJG2XX9M8Ts2 rCaONEWJwkpJUFKxjYeqAuq+u97SNjtWIcefb1cNA+O57oGEgwTz6/QWkyt1dkBvbFai mNZMx2xQ22p5Olf0heuxtB4d6wWjTU2qc7rqOvwBG9GfgnAZ+9Ax4AW/5ZXPb82COPaF e9MiZTbgKXXHoDKUzaEwDCaCZaNrOOp6glxaQnWZDYWUJ6wnLh3DTpKwHE56HPV8IKcB 5q+A== X-Gm-Message-State: AOJu0Yw9XnBBDblaSEFp5zbXDgEY/7CU0AOYxGl5vTqRNo4AxlpX6kcW KwN9gpCs9U1EX20IRJGM0ygMux7qUfc= X-Google-Smtp-Source: AGHT+IGsdZ9OQrsmSiHT0rqrwBBZdnmreYOg5h/lRdbPu3jnfL1aVpoG6dFZqOQjCVAn6CFkDt4TZQ== X-Received: by 2002:a81:9ac9:0:b0:5a7:c973:c82 with SMTP id r192-20020a819ac9000000b005a7c9730c82mr14984817ywg.13.1698873396120; Wed, 01 Nov 2023 14:16:36 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810::44? ([2600:1700:bd2c:b810::44]) by smtp.googlemail.com with ESMTPSA id j4-20020a0df904000000b005a23fc389d8sm421771ywf.103.2023.11.01.14.16.35 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 01 Nov 2023 14:16:35 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------lZAUK809w9tst0YFrEygLhgj" Message-ID: <93e2708b-2dae-4a04-8660-ba5e1f5b857a@gmail.com> Date: Wed, 1 Nov 2023 16:16:35 -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> From: Ron In-Reply-To: <0c82a8d32181fca3b979347f34ffbb2f7fdf0e90.camel@cybertec.at> 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. --------------lZAUK809w9tst0YFrEygLhgj Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. -- Born in Arizona, moved to Babylonia. --------------lZAUK809w9tst0YFrEygLhgj Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--
Born in Arizona, moved to Babylonia.
--------------lZAUK809w9tst0YFrEygLhgj--