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 1rdChM-004OQA-Vt for pgsql-general@arkaria.postgresql.org; Thu, 22 Feb 2024 17:17:30 +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 1rdChL-00CpzW-Fp for pgsql-general@arkaria.postgresql.org; Thu, 22 Feb 2024 17:17:27 +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 1rdChJ-00CpyH-Qb for pgsql-general@lists.postgresql.org; Thu, 22 Feb 2024 17:17:27 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rdChF-000SvO-KS for pgsql-general@lists.postgresql.org; Thu, 22 Feb 2024 17:17:25 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.48]) by mailout.nyi.internal (Postfix) with ESMTP id 706D35C0056; Thu, 22 Feb 2024 12:17:20 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute7.internal (MEProxy); Thu, 22 Feb 2024 12:17:20 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1708622240; x=1708708640; bh=MFv9MF/qC6bnEJaTxEywzGZO58k5Gpg5Jps2z3lNN+Q=; b= NpHiN4j4HPqFCFza+nWCpMvrTQTZF/5YcD4o20NIEAPwoL9wBkRpqb2sWdiYLEWI 0/gPB3QDadA4cN64m6hOo+wLyFICby6nNFkVCG4fiOOlPfqybEjR05ZjNh3nqGlx hYdK8gZPKwe0j0y1rW/qfB+/dcYZUj+kve40ecrIvnrHisJlWN3REsCSbsNwa2z/ qiFNRny00zaDaFECDK2xFGym+hdzX71CRcVbmaSN/N6d+XYi/htsJN5/OIGRSsk8 XezQufOmdGK3b0nQObCE0+xBQV7TAhoL3nJSpRy22kXpCbHpc006h3IVRW8vvXv9 YGOxk9XqRYROcc97oBxc/A== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1708622240; x= 1708708640; bh=MFv9MF/qC6bnEJaTxEywzGZO58k5Gpg5Jps2z3lNN+Q=; b=f keyGkdY1I7F0qEm9rKT34aaRidJJhsP6VYpQjee2W2SV/QqucLzwzxZOQZYj4Elb Qo5cPwqmB+ZatVZAJtBhS/yLOKcTILk/ogVOnyXlBqllUejrPo+kKNHPzXXmezMf pQZGGvFpb516b0wfNlDLou/AB6aLy3WofmQA57LRnWofFvHTgBRHHrM/mXTeTt+L pOYloHRWEGbUmt++2JZCNKFFIGB0HkcvPRUaeV9gjvx09zr0ZHqRWnpurFdH765H LZWziF/HfX2FhJdCt18s+9/+96ImO0SNuUn/SUI5dLEdYNPcaTMAklnkG4B94LwI 04MEsOzMLbrS2bMBwiK+A== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrfeeggdeliecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfevfhfhjggtgfesthekredttddvjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpefgudefkefhveefleevieeuveehvdduudekuddvvdel hfeuueeijedtuedvvedvueenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 22 Feb 2024 12:17:19 -0500 (EST) Message-ID: Date: Thu, 22 Feb 2024 09:17:18 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: pg_dump performance issues To: Dominique Devienne , Francisco Olarte Cc: pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2/22/24 06:40, Dominique Devienne wrote: > On Thu, Feb 22, 2024 at 12:20 PM Dominique Devienne > wrote: > > On Thu, Feb 22, 2024 at 11:41 AM Francisco Olarte > > wrote: > > Dominique: > > .... in the 10-12MB/s throughput range..... > > This has the faint smell of a saturated 100Mbps link in the > middle (12*8=96Mbps) > > > So indeed, I'm not on 1Gb/s as I expected... I'll report back when > this is fixed. > > > So IT fixing the networking on my Windows machine is slower than I > expected... > > Thus I switched to a Linux client, which I verified using iperf3 really > has 1Gbps. > And there I can read at 100MB/s, which is almost saturating the network > link. > The Linux dumps are 45MB this time, instead of 100MB on Windows, as if > the former is compressed, and the latter isn't. Did you use the same pg_dump command on the Windows client? Where was the Windows client Postgres software installed from? Where was the Linux client Postgres software installed from? > Perhaps the (implicit?) compression of the dump is why it's 8x slower > than mine? (I do fewer catalog queries I suspect) From https://www.postgresql.org/docs/current/app-pgdump.html d directory <...> This format is compressed by default using gzip and also supports parallel dumps. > And I see no speedup from parallelizing, unfortunately, which I find > strange, as mentioned earlier. Any idea why? From same link as above -j njobs --jobs=njobs Run the dump in parallel by dumping njobs tables simultaneously. This option may reduce the time needed to perform the dump but it also increases the load on the database server. You can only use this option with the directory output format because this is the only output format where multiple processes can write their data at the same time. <...> I'm guessing on this small a database the overhead of -j is greater then the benefit derived. > Note that this is against the v12 server, and the client is still 16.1. > > Applying a two-threaded producer-consumer scheme to my dump, to > parallelize reads and writes, I might approach 100MB/s overall. > Before getting into parallelizing reads, which complicate matters, and > my dump container's API is serial anyway (it's SQLite...) > > So looks like I'm networking bound on 1Gbps, while Marc perhaps reached > PostgreSQL's (or the disks'?) limits before saturating his 10Gbps > networking. > > Thanks, --DD > > [ddevienne]$ my_dump ... > COPY:         25x (   88,073 rows,     56,768,104 bytes) in   0.537s > (100.7 MB/s) > DUMP'd 88,023 rows (56,761,583 bytes) from 25 tables in 1.062s (51.0 MB/s) > > [ddevienne]$ ll ....db > -rw-r--r-- 1 ddevienne users 58,351,616 Feb 22 15:10 ....db > > [ddevienne]$ time $PDGM_POSTGRESQL_DIR/bin/pg_dump --role="..." -Fc -n > '"..."' -f ...-Fc.dump "host=acme dbname=copyperf" > 7.561u 0.216s 0:08.46 91.8%     0+0k 0+91056io 0pf+0w > [ddevienne]$ time $PDGM_POSTGRESQL_DIR/bin/pg_dump --role="..." -Fd -n > '"..."' -f ...-Fd.dump "host=acme dbname=copyperf" > 7.351u 0.219s 0:08.33 90.7%     0+0k 0+90808io 0pf+0w > [ddevienne]$ time $PDGM_POSTGRESQL_DIR/bin/pg_dump --role="..." -Fd -j2 > -n '"..."' -f ...-Fdj2.dump "host=acme dbname=copyperf" > 7.667u 0.228s 0:08.56 92.0%     0+0k 0+90808io 0pf+0w > [ddevienne]$ time $PDGM_POSTGRESQL_DIR/bin/pg_dump --role="..." -Fd -j3 > -n '"..."' -f ...-Fdj3.dump "host=acme dbname=copyperf" > 7.964u 0.247s 0:08.71 94.1%     0+0k 0+90808io 0pf+0w > [ddevienne]$ time $PDGM_POSTGRESQL_DIR/bin/pg_dump --role="..." -Fd -j4 > -n '"..."' -f ...-Fdj4.dump "host=acme dbname=copyperf" > 7.491u 0.243s 0:08.21 94.1%     0+0k 0+90808io 0pf+0w > [ddevienne]$ du -sm ...-F* > 45      ...-Fc.dump > 45      ...-Fd.dump > 45      ...-Fdj2.dump > 45      ...-Fdj3.dump > 45      ...-Fdj4.dump -- Adrian Klaver adrian.klaver@aklaver.com