From: Nathan Bossart <nathandbossart@gmail.com>
To: Dimitrios Apostolou <jimis@gmx.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward
Date: Tue, 10 Jun 2025 16:47:59 -0500
Message-ID: <aEioDuePEBRnfJYk@nathan> (raw)
In-Reply-To: <f7a23708-c336-c189-f155-4b3197bf6ea0@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>
On Mon, Jun 09, 2025 at 10:09:57PM +0200, Dimitrios Apostolou wrote:
> Fix by avoiding forward seeks for jumps of less than 1MB forward.
> Do instead sequential reads.
>
> Performance gain can be significant, depending on the size of the dump
> and the I/O subsystem. On my local NVMe drive, read speeds for that
> phase of pg_restore increased from 150MB/s to 3GB/s.
I was curious about what exactly was leading to the performance gains you
are seeing. This page has an explanation:
https://www.mjr19.org.uk/IT/fseek.html
I also wrote a couple of test programs to show the difference between
fseeko-ing and fread-ing through a file with various sizes. On a Linux
machine, I see this:
log2(n) | fseeko | fread
---------+---------+-------
1 | 109.288 | 5.528
2 | 54.881 | 2.848
3 | 27.65 | 1.504
4 | 13.953 | 0.834
5 | 7.1 | 0.49
6 | 3.665 | 0.322
7 | 1.944 | 0.244
8 | 1.085 | 0.201
9 | 0.658 | 0.185
10 | 0.443 | 0.175
11 | 0.253 | 0.171
12 | 0.102 | 0.162
13 | 0.075 | 0.13
14 | 0.061 | 0.114
15 | 0.054 | 0.1
So, fseeko() starts winning around 4096 bytes. On macOS, the differences
aren't quite as dramatic, but 4096 bytes is the break-even point there,
too. I imagine there's a buffer around that size somewhere...
This doesn't fully explain the results you are seeing, but it does seem to
validate the idea. I'm curious if you see further improvement with even
lower thresholds (e.g., 8KB, 16KB, 32KB).
--
nathan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, jimis@gmx.net, 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: <aEioDuePEBRnfJYk@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox