From: Chao Li <li.evan.chao@gmail.com>
Message-Id: <25A25667-AC82-46FD-A5FC-0B2AA0CC7DB9@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_4B380B2A-09F4-47E9-87BF-27A9F640DD9F"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when
 jumping short distance forward
Date: Tue, 14 Oct 2025 08:09:56 +0800
In-Reply-To: <717335.1760389672@sss.pgh.pa.us>
Cc: Dimitrios Apostolou <jimis@gmx.net>,
 Nathan Bossart <nathandbossart@gmail.com>,
 Thomas Munro <thomas.munro@gmail.com>,
 pgsql-hackers@lists.postgresql.org
To: Tom Lane <tgl@sss.pgh.pa.us>
References: <2edb7a57-b225-3b23-a680-62ba90658fec@gmx.net>
 <a7344d8f-927c-1f97-6cc0-ac630deb98c4@gmx.net>
 <c0e531f9-accc-3a93-e9e6-2cec1471f9e9@gmx.net>
 <1282067.1744651649@sss.pgh.pa.us>
 <c6bb93c1-4042-743c-096c-27c5e7c73347@gmx.net>
 <f7a23708-c336-c189-f155-4b3197bf6ea0@gmx.net> <aEioDuePEBRnfJYk@nathan>
 <5ed51587-5a24-4d79-e36e-927620d0e3a2@gmx.net> <aEygf8AchBk04gOA@nathan>
 <f99e9774-da49-60f0-20e0-b97d9f12a04e@gmx.net>
 <72d2c81b-a0fe-3d99-9dd4-0d771e6e673e@gmx.net>
 <1po8os49-r63o-2923-p37n-12698o1qn7p0@tzk.arg>
 <3515357.1760128017@sss.pgh.pa.us> <3698234.1760149336@sss.pgh.pa.us>
 <3686.1760232320@sss.pgh.pa.us>
 <CBF70398-279C-4BA7-9FA1-2879A49F4EC9@gmail.com>
 <717335.1760389672@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/25A25667-AC82-46FD-A5FC-0B2AA0CC7DB9%40gmail.com>
Precedence: bulk


--Apple-Mail=_4B380B2A-09F4-47E9-87BF-27A9F640DD9F
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8


> On Oct 14, 2025, at 05:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
>=20
> I've pushed the parts of that patch set that I thought were
> uncontroversial.  What's left is the business about increasing
> DEFAULT_IO_BUFFER_SIZE and then adjusting the tests appropriately.
>=20
> So, v4-0001 attached is the previous v3-0002 to increase
> DEFAULT_IO_BUFFER_SIZE, plus additions in compress_none.c to make
> --compress=3Dnone also produce predictably large data blocks.
> I decided that if we're going to rely on that behavior as part
> of the solution for this thread's original problem, we'd better
> make it happen for all compression options.
>=20
> 0002 adds a test case in 002_pg_dump.pl to exercise --compress=3Dnone,
> because without that we don't have any coverage of the new code
> 0001 added in compress_none.c.  That makes for a small increase
> in the runtime of 002_pg_dump.pl, but I'm inclined to think it's
> worth doing.
>=20
> 0003 modifies the existing test cases that manually compress
> blobs.toc files so that they also compress toc.dat.  I feel
> like it's mostly an oversight that that wasn't done to begin
> with; if it had been done, we'd have caught the Gzip_read bug
> right away.  Also, AFAICT, this doesn't cost anything measurable
> in test runtime.
>=20
> 0004 increases the row width in the existing test case that says
> it's trying to push more than DEFAULT_IO_BUFFER_SIZE through
> the compressors.  While I agree with the premise, this solution
> is hugely expensive: it adds about 12% to the already-long runtime
> of 002_pg_dump.pl.  I'd like to find a better way, but ran out of
> energy for today.  (I think the reason this costs so much is that
> it's effectively iterated hundreds of times because of
> 002_pg_dump.pl's more or less cross-product approach to testing
> everything.  Maybe we should pull it out of that structure?)
>=20

In v4 patch, the code changes are straightforward. 0001 changes =
compress_none.c to write data to a 128K buffer first, then only flush =
the buffer when it=E2=80=99s filled up. 0002, 0003 and 0004 add more =
test cases. I have no comment to the code diff.

I tested DEFAULT_IO_BUFFER_SIZE with 4K, 32K, 64K, 128K and 256K. Looks =
like increasing the buffer size doesn=E2=80=99t improve the performance =
significantly. Actually, with the buffer size 64K, 128K and 256K, the =
test results are very close. I tested both with lz4 and none =
compression. I am not suggesting tuning the buffer size. These data are =
only for your reference.

To do the test, I created a test db and filled in several GB of data.

```
256K =3D=3D=3D=3D
% time pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest
pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest  3.37s user 0.82s =
system 57% cpu 7.249 total
% time pg_restore -f /dev/null dump_A.dir
pg_restore -f /dev/null dump_A.dir  0.24s user 0.19s system 43% cpu =
0.991 total

% time pg_dump -Fd --compress=3Dnone -f dump_A.dir evantest
pg_dump -Fd --compress=3Dnone -f dump_A.dir evantest  2.34s user 1.72s =
system 68% cpu 5.949 total
% time pg_restore -f /dev/null dump_A.dir
pg_restore -f /dev/null dump_A.dir  0.02s user 0.19s system 22% cpu =
0.921 total

128K =3D=3D=3D
% time pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest
pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest  3.38s user 0.85s =
system 64% cpu 6.525 total
% time pg_restore -f /dev/null dump_A.dir
pg_restore -f /dev/null dump_A.dir  0.28s user 0.21s system 47% cpu =
1.042 total

% time pg_dump -Fd --compress=3Dnone -f dump_A.dir evantest
pg_dump -Fd --compress=3Dnone -f dump_A.dir evantest  2.34s user 1.67s =
system 68% cpu 5.835 total
% time pg_restore -f /dev/null dump_A.dir
pg_restore -f /dev/null dump_A.dir  0.03s user 0.22s system 22% cpu =
1.118 total

64K =3D=3D=3D
% time pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest
pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest  3.39s user 0.92s =
system 63% cpu 6.761 total
% time pg_restore -f /dev/null dump_A.dir
pg_restore -f /dev/null dump_A.dir  0.33s user 0.24s system 40% cpu =
1.420 total

% time pg_dump -Fd --compress=3Dnone -f dump_A.dir evantest
pg_dump -Fd --compress=3Dnone -f dump_A.dir evantest  2.35s user 1.74s =
system 69% cpu 5.849 total
% time pg_restore -f /dev/null dump_A.dir
pg_restore -f /dev/null dump_A.dir  0.04s user 0.22s system 27% cpu =
0.939 total

32K =3D=3D=3D
% time pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest
pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest  3.43s user 0.94s =
system 58% cpu 7.416 total
% time pg_restore -f /dev/null dump_A.dir
pg_restore -f /dev/null dump_A.dir  0.34s user 0.22s system 56% cpu =
0.983 total

% time pg_dump -Fd --compress=3Dnone -f dump_A.dir evantest
pg_dump -Fd --compress=3Dnone -f dump_A.dir evantest  2.34s user 1.75s =
system 67% cpu 6.070 total
% time pg_restore -f /dev/null dump_A.dir
pg_restore -f /dev/null dump_A.dir  0.05s user 0.23s system 29% cpu =
0.926 total

4k=3D=3D=3D=3D
% time pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest
pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest  3.45s user 0.94s =
system 60% cpu 7.298 total
% time pg_restore -f /dev/null dump_A.dir
pg_restore -f /dev/null dump_A.dir  0.37s user 0.29s system 64% cpu =
1.016 total

% time pg_dump -Fd --compress=3Dnone -f dump_A.dir evantest
pg_dump -Fd --compress=3Dnone -f dump_A.dir evantest  2.33s user 1.78s =
system 69% cpu 5.947 total
% time pg_restore -f /dev/null dump_A.dir
pg_restore -f /dev/null dump_A.dir  0.12s user 0.29s system 40% cpu =
1.009 total
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/


--Apple-Mail=_4B380B2A-09F4-47E9-87BF-27A9F640DD9F
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;"><br =
id=3D"lineBreakAtBeginningOfMessage"><div><br><blockquote =
type=3D"cite"><div>On Oct 14, 2025, at 05:07, Tom Lane =
&lt;tgl@sss.pgh.pa.us&gt; wrote:</div><div><br><br>I've pushed the parts =
of that patch set that I thought were<br>uncontroversial. &nbsp;What's =
left is the business about increasing<br>DEFAULT_IO_BUFFER_SIZE and then =
adjusting the tests appropriately.<br><br>So, v4-0001 attached is the =
previous v3-0002 to increase<br>DEFAULT_IO_BUFFER_SIZE, plus additions =
in compress_none.c to make<br>--compress=3Dnone also produce predictably =
large data blocks.<br>I decided that if we're going to rely on that =
behavior as part<br>of the solution for this thread's original problem, =
we'd better<br>make it happen for all compression options.<br><br>0002 =
adds a test case in 002_pg_dump.pl to exercise =
--compress=3Dnone,<br>because without that we don't have any coverage of =
the new code<br>0001 added in compress_none.c. &nbsp;That makes for a =
small increase<br>in the runtime of 002_pg_dump.pl, but I'm inclined to =
think it's<br>worth doing.<br><br>0003 modifies the existing test cases =
that manually compress<br>blobs.toc files so that they also compress =
toc.dat. &nbsp;I feel<br>like it's mostly an oversight that that wasn't =
done to begin<br>with; if it had been done, we'd have caught the =
Gzip_read bug<br>right away. &nbsp;Also, AFAICT, this doesn't cost =
anything measurable<br>in test runtime.<br><br>0004 increases the row =
width in the existing test case that says<br>it's trying to push more =
than DEFAULT_IO_BUFFER_SIZE through<br>the compressors. &nbsp;While I =
agree with the premise, this solution<br>is hugely expensive: it adds =
about 12% to the already-long runtime<br>of 002_pg_dump.pl. &nbsp;I'd =
like to find a better way, but ran out of<br>energy for today. &nbsp;(I =
think the reason this costs so much is that<br>it's effectively iterated =
hundreds of times because of<br>002_pg_dump.pl's more or less =
cross-product approach to testing<br>everything. &nbsp;Maybe we should =
pull it out of that =
structure?)<br><br></div></blockquote><br></div><div>In v4 patch, the =
code changes are straightforward. 0001 changes compress_none.c to write =
data to a 128K buffer first, then only flush the buffer when it=E2=80=99s =
filled up. 0002, 0003 and 0004 add more test cases. I have no comment to =
the code diff.</div><div><br></div><div>I tested DEFAULT_IO_BUFFER_SIZE =
with 4K, 32K, 64K, 128K and 256K. Looks like increasing the buffer size =
doesn=E2=80=99t improve the performance significantly. Actually, with =
the buffer size 64K, 128K and 256K, the test results are very close. I =
tested both with lz4 and none compression. I am not suggesting tuning =
the buffer size. These data are only for your =
reference.</div><div><br></div><div>To do the test, I created a test db =
and filled in several GB of =
data.</div><div><br></div><div>```</div><div><div>256K =3D=3D=3D=3D</div><=
div>% time pg_dump -Fd --compress=3Dlz4 -f dump_A.dir =
evantest</div><div>pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest =
&nbsp;3.37s user 0.82s system 57% cpu 7.249 total</div><div>% time =
pg_restore -f /dev/null dump_A.dir</div><div>pg_restore -f /dev/null =
dump_A.dir &nbsp;0.24s user 0.19s system 43% cpu 0.991 =
total</div><div><br></div><div>% time pg_dump -Fd --compress=3Dnone -f =
dump_A.dir evantest</div><div>pg_dump -Fd --compress=3Dnone -f =
dump_A.dir evantest &nbsp;2.34s user 1.72s system 68% cpu 5.949 =
total</div><div>% time pg_restore -f /dev/null =
dump_A.dir</div><div>pg_restore -f /dev/null dump_A.dir &nbsp;0.02s user =
0.19s system 22% cpu 0.921 total</div><div><br></div><div>128K =
=3D=3D=3D</div><div>% time pg_dump -Fd --compress=3Dlz4 -f dump_A.dir =
evantest</div><div>pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest =
&nbsp;3.38s user 0.85s system 64% cpu 6.525 total</div><div>% time =
pg_restore -f /dev/null dump_A.dir</div><div>pg_restore -f /dev/null =
dump_A.dir &nbsp;0.28s user 0.21s system 47% cpu 1.042 =
total</div><div><br></div><div>% time pg_dump -Fd --compress=3Dnone -f =
dump_A.dir evantest</div><div>pg_dump -Fd --compress=3Dnone -f =
dump_A.dir evantest &nbsp;2.34s user 1.67s system 68% cpu 5.835 =
total</div><div>% time pg_restore -f /dev/null =
dump_A.dir</div><div>pg_restore -f /dev/null dump_A.dir &nbsp;0.03s user =
0.22s system 22% cpu 1.118 total</div><div><br></div><div>64K =
=3D=3D=3D</div><div>% time pg_dump -Fd --compress=3Dlz4 -f dump_A.dir =
evantest</div><div>pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest =
&nbsp;3.39s user 0.92s system 63% cpu 6.761 total</div><div>% time =
pg_restore -f /dev/null dump_A.dir</div><div>pg_restore -f /dev/null =
dump_A.dir &nbsp;0.33s user 0.24s system 40% cpu 1.420 =
total</div><div><br></div><div>% time pg_dump -Fd --compress=3Dnone -f =
dump_A.dir evantest</div><div>pg_dump -Fd --compress=3Dnone -f =
dump_A.dir evantest &nbsp;2.35s user 1.74s system 69% cpu 5.849 =
total</div><div>% time pg_restore -f /dev/null =
dump_A.dir</div><div>pg_restore -f /dev/null dump_A.dir &nbsp;0.04s user =
0.22s system 27% cpu 0.939 total</div><div><br></div><div>32K =
=3D=3D=3D</div><div>% time pg_dump -Fd --compress=3Dlz4 -f dump_A.dir =
evantest</div><div>pg_dump -Fd --compress=3Dlz4 -f dump_A.dir evantest =
&nbsp;3.43s user 0.94s system 58% cpu 7.416 total</div><div>% time =
pg_restore -f /dev/null dump_A.dir</div><div>pg_restore -f /dev/null =
dump_A.dir &nbsp;0.34s user 0.22s system 56% cpu 0.983 =
total</div><div><br></div><div>% time pg_dump -Fd --compress=3Dnone -f =
dump_A.dir evantest</div><div>pg_dump -Fd --compress=3Dnone -f =
dump_A.dir evantest &nbsp;2.34s user 1.75s system 67% cpu 6.070 =
total</div><div>% time pg_restore -f /dev/null =
dump_A.dir</div><div>pg_restore -f /dev/null dump_A.dir &nbsp;0.05s user =
0.23s system 29% cpu 0.926 =
total</div><div><br></div><div>4k=3D=3D=3D=3D</div><div>% time pg_dump =
-Fd --compress=3Dlz4 -f dump_A.dir evantest</div><div>pg_dump -Fd =
--compress=3Dlz4 -f dump_A.dir evantest &nbsp;3.45s user 0.94s system =
60% cpu 7.298 total</div><div>% time pg_restore -f /dev/null =
dump_A.dir</div><div>pg_restore -f /dev/null dump_A.dir &nbsp;0.37s user =
0.29s system 64% cpu 1.016 total</div><div><br></div><div>% time pg_dump =
-Fd --compress=3Dnone -f dump_A.dir evantest</div><div>pg_dump -Fd =
--compress=3Dnone -f dump_A.dir evantest &nbsp;2.33s user 1.78s system =
69% cpu 5.947 total</div><div>% time pg_restore -f /dev/null =
dump_A.dir</div><div>pg_restore -f /dev/null dump_A.dir &nbsp;0.12s user =
0.29s system 40% cpu 1.009 =
total</div><div>```</div></div><div><br></div>Best regards,<br><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, =
0); letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; overflow-wrap: =
break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;"><div>--</div><div>Chao Li (Evan)<br>HighGo Software =
Co., Ltd.<br>https://www.highgo.com/</div><div><br></div></div><br =
class=3D"Apple-interchange-newline"><br =
class=3D"Apple-interchange-newline">

</div>
<br></body></html>=

--Apple-Mail=_4B380B2A-09F4-47E9-87BF-27A9F640DD9F--