Date: Sat, 14 Jun 2025 18:17:41 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Nathan Bossart <nathandbossart@gmail.com>
cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@lists.postgresql.org
Subject: Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when
 jumping short distance forward
In-Reply-To: <f99e9774-da49-60f0-20e0-b97d9f12a04e@gmx.net>
Message-ID: <72d2c81b-a0fe-3d99-9dd4-0d771e6e673e@gmx.net>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:g7WGX71W548=;il0ozpvZWC+WrATza5N2jHqjFXa
 mnYy078PsXaekRlgqbe/PDr8o9kVf5LmqL/E7OASRGb+JPgRzF5UAuBeNE9kfDdjcoXU5Rt6R
 6XSEgvEd1jlJepbgMUXERExBpCikpSx8TabBvKfMwNF96Gh7Xo3jAWctgKnSV3t4U8APknDs7
 McLR1FAo/1sSTRUwI6+K1UaXV9qFp03+0HIn+aZLsPKaarWGktlM7QD6n2GTTrDQqthm0ycq1
 pfXMTyQYNiy7rKT7HdeIDrOWi1+JffJCzd1Zpb1ZO4G3Q5IHQIcT+xadKvMPE338XvaRS8P9v
 pAb9FRj0XibxPO2znVtqvtKJvr8n3wAP5VvWV8eu5/s6UZ9g6KY3J2n0dQmxjIe7gU/k2d9nK
 10R+DlrUCF3xxP90mQYNkg9l/ZyNiRR/HvsxF40ul/lYHW9GXyGjsuAxUeZJVwYiXOJeUlql3
 8Jm+v4moNFlPam6rObm0fTfH+Q9fTo7rLezvMUM7teGeRJ+HGzko70V3ivyurMy5PxB0AplVI
 0GhvoZhVPRpQYs/tI0Xwzdz+SVz2WVpLGVRFhvbW40x0lUoYL1AaB4dtHCq05MxKaLN4zgY1T
 N/P8T1uhKeKUHFRIxFqsQSW1uW+YX8K6s5CvowdDFk5N+IrXHBcX36rMBjIzFGt6qCZNQY9ZP
 qq7aK/wKKo/7qtBIAJ1IQS0QDnDQ5zpVZcGiVZ/PP6egwtu/vJNSVDppavVoMcQhO9YkmDyBw
 mQ5B3BypyHkPhsd9YSlyfxHkmHA+p50KJZGir252ipDbx8yjeyRUT93PPUKJ8xPltGqm9dx57
 M0on+lJDlExhL/x0YecDEF8fMktW12WUObzVwgows8s2ljEEgtj0EXUZtPbVODx9mFLzkWqN+
 swDQpof0Vn/ZJGEj2LjDE5zP4yAsjtGhQ085oIDviejueGEsbewVVYqzOcByfwgjQC9QjzHJ3
 liP2hIJpFQZ9f0w2/3e0Bz7VmXNAUopPud8B++iE4kDcZYfOyDiqcDldTwKj/ddYMwj0V+/TZ
 VBreSFD30wfDYbLKcZbyMfW/S9Mh+b19M+hHnlCU/mU+e/owUyBZO3xmOSL2XD2tAWH6vQf/G
 tcLkLFqACi51JK6e27HvOqmWCnq7mX9xRW9geH1DW4GQ/TmohzhE/KakHF2WFvNQiMFZOF8nA
 hyNyxftej4Pj5wDtF7KpywONqYJSFDb42v8yHDvdz9JXclXKs85R93z194i11L5B6CknuPduz
 xzC74z9nTECY3rluP680iNVgFnlNrYd1Qc3zoBc2DAo7Iijv5aLJu2MUwYb/M7Y+D2wXwlR2r
 rxKAdc6wTEoSGco4cTlfEiV2WS+b+S0fZe+uVuNPoiwvzN7eHyAFtd7w4rCIQki83ZlJTL9AA
 ZtJYFSG3lUAL7HhON1YF2yzDv2EWJyCu3IKKgXP81tBIFK+sQk1ZaCeJ4PB1sN89DvOh7mrcO
 Znl6CPHcKBw76AyjAywg2kktfQizReEgiVSEl9+GfKkr0AXBEVIbMb0mcLKcUK/AzC6aedkgS
 Vb/gidaqwW9laDEfJe/k8sH3jTUyfcoVxwIrOPNex9W2mit6/jhlz0lx/8z7UXyMC7fIyMyaq
 G3qbOfHdeEIsbJ31jvMw7Niwng2ZyQq33UG0eIedbz7DRSSqlZdgucGLkyHyiqqJbULGb5pbK
 OX742hj4R6bd/5HJuH66faMt0j//PZEZL1/A6wLssWSJzJtI+YRxo0kGdDjzbMARWn4oNNmCR
 gyY5mKNc7Fa0VTpkpiKRYtahJuM7TU9GlvdLJ+kvD/5dE1jevthkrAN6qpDT3F/MI4Dzmvs9G
 kZkiE57mBJLx10C8wDIt2MJImmpmXqk0WNfJLaviRG+EPpit76afxXyDo1y3DYLaAV0f0W+aV
 Wl+vweon/FGM4u/YMlioIOblgZ4guH3G/fRYREtoNK6zFsdmLqYjHprBzuasmovgRcycjLHcQ
 KeXX1ZF6zy+d0icgj2GWF68FU5ixZnin+7jWWucGOhAt/Wnim5RQ7Kk1roEOWuzYXH3WBlr94
 Fh+4PJT24Xr/t1X9GiICMyo7bkhID778OiPltubrTVCz/mbBmLkgxq2/AZC/EGvSiskwJNVHm
 59lmtF38qLKM4Y/zh0/5LDfgETsA5nWLuuOjHzdO/nwl6EInnuQCs6qoidsq0hcV70i8NiRap
 UqVFAgHlX/aPASoJiSGm+CqgIx+gvlV/bpG9uLhdO4Q43UyglhHWdb7o8iZOYcNYi15dsGD+U
 1yFNaD/7Oxw+5t7/MLIQ1Jch9vLdoOJe0nKQJry4axHuRVpqh95xen0Md9dIpztgMmmI207GS
 n5P+qUpoSyef7M8fyljoVLxkYfoB7+QWLfSXCkTNGIzPrH7U92t8PvjufoTr6IQbEarKVfdME
 Mn4Zyf/CiV3hxWpODH3q9s7K1qLfqGGNNbRNXYXhzaLJf3zsYuMj9qU1gCiTlmLQxdrIXYRdH
 J4ibQsbeNwf0uo/peAY3YwGfJBh7Z6q/mLeFgbX0Od/gD3TvartIeilzeuhCK9IJmPN/gtuxC
 1ne1VrApa93d9/WtvQJ3YtZ8z3EHAwWO3KJzUNKDInIQDEPb+L8ZEwV5HUgL6MlNo0gx2ds0g
 RZEuDqjXTTpfr+wvjuCEnj/Z6scflt7qVMzyUmM1WLKn7qLjW2NsHUfM2If+R3faSFEdlViQ6
 PW3afO6sF/ER+jQ8k8CpKP5KjJhCvtYpRHYbSmvLjYNE9E39Uazzq9Ljoq90JrsEq3vKwC5Gd
 Huf5BCitIargChOXJQ8xn7MJYB9SZgu8X3wNLXuMeYKns+v5QbtZR86NtYbPrWkkpUOpG4Jak
 jaYoFlZLixsTaYgbnoDiqadSeYroXPt54T60/QkF448ff1LJAjew59p8q+TNJfb7HgzTiOcpZ
 N9qRJYBz8opnyPUiuVu6R0CGqhEllc+9WdtjxLjYEGwrAMwzk9/Xr3ZUKyqq2XmpCtZUPD1vw
 zcG6badBTNcLsCmGypmAWhpU4Lrp8R7hDOcyl3gSee06GPKyCuMLGj0Knp982COIJpVSTW1zU
 6Wcg7fHwJWxXzX1hTB5L5MHJLVwWJgVBxAgMqlBZpM+hanCrvuI4go/Oqtn0/D0Ww218Gknnw
 LhX087Rs82apG4Lx6FrvqraUUYSl87Uv2TDnvs5QkaOfFHroMmSeymMP5C0ZjAgoMdIC048PC
 aEihZ8x5vmEUFyIzIydtTXHyevR1wNmjvC8i/QsEtqrMx6g==
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/72d2c81b-a0fe-3d99-9dd4-0d771e6e673e%40gmx.net>
Precedence: bulk

On Sat, 14 Jun 2025, Dimitrios Apostolou wrote:

> On Fri, 13 Jun 2025, Nathan Bossart wrote:
>
>>  On Fri, Jun 13, 2025 at 01:00:26AM +0200, Dimitrios Apostolou wrote:
>>>  By the way, I might have set the threshold to 1MB in my program, but
>>>  lowering it won't show a difference in my test case, since the lseek(=
)s I
>>>  was noticing before the patch were mostly 8-16KB forward. Not sure wh=
at
>>>  is
>>>  the defining factor for that. Maybe the compression algorithm, or how
>>>  wide
>>>  the table is?
>>
>>  I may have missed it, but could you share what the strace looks like w=
ith
>>  the patch applied?
>
> read(4, "..."..., 8192) =3D 8192
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 12288) =3D 12288
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 8192) =3D 8192
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 8192) =3D 8192
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 8192) =3D 8192
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 8192) =3D 8192
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 8192) =3D 8192
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 8192) =3D 8192
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 12288) =3D 12288
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 8192) =3D 8192
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 12288) =3D 12288
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 8192) =3D 8192
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 8192) =3D 8192
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 12288) =3D 12288
> read(4, "..."..., 4096) =3D 4096
> read(4, "..."..., 8192) =3D 8192
> read(4, "..."..., 4096) =3D 4096


This was from pg_restoring a zstd-compressed custom format dump.

Out of curiosity I've tried the same with an uncompressed dump=20
(--compress=3Dnone). Surprisingly it seems the blocksize is even smaller.

With my patched pg_restore I only get 4K reads and nothing else on=20
the strace output.

read(4, "..."..., 4096) =3D 4096
read(4, "..."..., 4096) =3D 4096
read(4, "..."..., 4096) =3D 4096
read(4, "..."..., 4096) =3D 4096
read(4, "..."..., 4096) =3D 4096
read(4, "..."..., 4096) =3D 4096

The unpatched pg_restore gives me the weirdest output ever:

read(4, "..."..., 4096) =3D 4096
lseek(4, 98527916032, SEEK_SET)         =3D 98527916032
lseek(4, 98527916032, SEEK_SET)         =3D 98527916032
lseek(4, 98527916032, SEEK_SET)         =3D 98527916032
lseek(4, 98527916032, SEEK_SET)         =3D 98527916032
lseek(4, 98527916032, SEEK_SET)         =3D 98527916032
lseek(4, 98527916032, SEEK_SET)         =3D 98527916032
[ ... repeats about 80 times ...]
read(4, "..."..., 4096) =3D 4096
lseek(4, 98527920128, SEEK_SET)         =3D 98527920128
lseek(4, 98527920128, SEEK_SET)         =3D 98527920128
lseek(4, 98527920128, SEEK_SET)         =3D 98527920128
lseek(4, 98527920128, SEEK_SET)         =3D 98527920128
[ ... repeats ... ]


Seeing this, I think we should really consider raising the pg_dump block=
=20
size like Tom suggested on a previous thread.


Dimitris