Date: Mon, 24 Mar 2025 17:43:38 +0100 (CET)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: parallel pg_restore blocks on heavy random read I/O on all
 children processes
In-Reply-To: <556498.1742744802@sss.pgh.pa.us>
Message-ID: <3764bde7-ca09-a8a3-489c-0166f772245d@gmx.net>
References: <6bd16bdb-aa5e-0512-739d-b84100596035@gmx.net>
 <1095774.1742498237@sss.pgh.pa.us>
 <e11dd37a-3409-175e-040d-c773838b7934@gmx.net>
 <556498.1742744802@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:mLScCSOQiw8=;YEzh5tTpVLq7SHseNKPSbQ09MRi
 tQ6ZxbNkfNn2e37XD2NPSf1YHXdOm0N7j9JZwE6DljdJniKPH4qZ0pFAGvuUEyCM40N20yqEj
 AVqPfw787cVUSu08WDs0Lrkoi2Ybmd9oevvuCaQ4GNWKPINzgqE/zA3ysWLn0uEpoJFDEtUgA
 ntVfJNsRNHl76WqCMqs+8gAZ3nz+gMp1QhhjaihSvhuIrIrTs18oCDJnKf/FyktGRKcighGbc
 8olH5g1nfRBiuL12GnDHCwhD4cC2KsiJkQtxwGAkylZSlwh4dGmHyoRCwjFzzN8kpsNNd3iwC
 htf5Jv32GK1X7zKAr35h+ww8NThw4foNINS49I1DGfUENpeLDEORNkEbXyJ6L/fp1LoSQcupE
 VYZa627EZweVZ0ulWCAB4ZqQCxtykoDfR2Z+62xseOEoG8sqT9x+tVNysq49HCqQh+KX57GcX
 GU7KHGUN4DecznFJxVqodDEJSUo11XVP0jM9IDTswSNRmGF1e7hPdUWVZS3IMkNP8IA18ilaI
 R2hRi3mzuGhyiM8M9bqDqv4bvVM97iHLL8pwUyfQ1j4fr/Pz3MTAb6JKLPPkI+E46gzslJnTE
 s/bbiaTgd4geJPqBla+Zp6sIpZ3v9h9NTIsiRvfWYyeWPjIZ2IKTByEeYquQwJKOrt3ETvwtl
 tAWwFCr7xqJzrybFTNOPF4f1+CXPC3cZtPyhYx+X/6gkOyRi+k3A6rbBrjyQg4dFUXl9ItQ0D
 NG2tgQweFGx4Nmzdjd7W/BtEKD6CrNZ+W/qs50Fgm/knl/rLJDTSnYPa7D8UcwQQf4QFH/sGH
 y37BiprxdcI0JBb/k22139e8xGDSjQEFwZcNZnKxCTc7dXvyAeK/3xBKpdB3mEqRJJFKZR86+
 hhcPqGZRXIG+0pfqRBaJ4zXl4YdYXiypQiXzDvnSZhEoa3FfkQFtzuBDJLiEMkMIJJReIpTMn
 1QMOsxIAzdq2HvyPNqEmNjh7jpdqqnI1X7mUjap+Aib+ujeAMHC6QO+hn0sjRSObfHiLucGpL
 GPnxGeumaMCF0E92uGbpGSmOyXmKGdU3qsU6sfcr1bLxbj0TfhgJ2/CAs6e7w3OeUAulsW2JH
 7WFpUqhU8Fe33tI0dmD7B9Bip54MjqE3RCaq7gDhkh/KzIHQf4B2STOuOYTGLkK2sKd8GsDEC
 PRHPyYuYpc5zMEkekbtNp3Dl1t5VNCPZqvkceYtRi0yCaoRA9YLE42Aq8dCN7tCaG946tCT2G
 XoWAHTcXVwAw+Uab4RBd1sYm6156Bxd0zP+Qii0am66zN2VdencopuTp15APOap2ZdJRJ8EbN
 f7Qutzl6w1ud1Q5QBxXrkKoBVJ9ew40+7tDC/wVr1MoNAJ2sp2EQoinoKrFGn06qI3tjCgW7L
 h9WCdBh0ZjRmQzTem9WHZLL7+pvfrLlMmJRW08rjIeaCF4ULAfTSAhQvXt530f3I5tGDMpxIo
 fECHAzr9Zs8d8Q1oclLljzWKxEzo=
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/3764bde7-ca09-a8a3-489c-0166f772245d%40gmx.net>
Precedence: bulk

On Sun, 23 Mar 2025, Tom Lane wrote:

> Dimitrios Apostolou <jimis@gmx.net> writes:
>> On Thu, 20 Mar 2025, Tom Lane wrote:
>>> I am betting that the problem is that the dump's TOC (table of
>>> contents) lacks offsets to the actual data of the database objects,
>>> and thus the readers have to reconstruct that information by scanning
>>> the dump file.  Normally, pg_dump will back-fill offset data in the
>>> TOC at completion of the dump, but if it's told to write to an
>>> un-seekable output file then it cannot do that.
>
>> Further questions:
>
>> * The seeking pattern in pg_restore seems non-sensical to me: reading 4=
K,
>>    jumping 8-12K, repeat for the whole file? Consuming 15K IOPS for an
>>    hour. /Maybe/ something to improve there... Where can I read more ab=
out
>>    the format?
>
> It's reading data blocks (or at least the headers thereof), which have
> a limited size.  I don't think that size has changed since circa 1999,
> so maybe we could consider increasing it; but I doubt we could move
> the needle very far that way.

If it could be substantially increased then it would cut down the number
of seeks by a big factor and definitely make a difference. But it would
have a negative impact on the size of small backups?

However, I would have expected the kernel to handle this reading-seeking
pattern better: The 4K read() should cause a big pre-fetch (read-ahead) to
cover the next seek and read. Not sure why I don't see it happening, it's
a rather typical setup (NVMe SSD with ext4 fs on Linux 6.11).

As a workaround, pg_restore could sequentially read() the whole archive to
construct the TOC, instead of jumping around. I'm a bit split on this one,
as it would be much faster on the compressed archive (~800GB) but not that
much on the uncompressed one (>5TB).

I'm also under the impression that all the pg_restore child processes are
doing the same thing: seeking+reading the whole file, each for their own
sake. Can you verify or disprove?


Thank you for the detailed response.

Dimitris