Date: Wed, 19 Mar 2025 19:46:58 +0100 (CET)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-performance@lists.postgresql.org
Subject: parallel pg_restore blocks on heavy random read I/O on all children
 processes
Message-ID: <6bd16bdb-aa5e-0512-739d-b84100596035@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=US-ASCII
UI-OutboundReport: notjunk:1;M01:P0:wl9ZHwcqvjI=;OKWl93lNIexc2FCZoOV0sXyM9gg
 g1daNWfb78coIBvVxK3AuIDvK+yspN3VnYNrI9jeAbUXipkmYrBn7OD1S2UmeXCpYAH/izTdA
 Rbss1pDxndNE+4y7ouMuShqZp1BFbaeOAaO+br6S04h9Q5fSbV9L45Xhhy+oKi/DC/1K+TfYP
 u5Um6IMj4kQjDGOsKnOMkoNWPES3smV3Py4rQOzx+hZhFKIBSYhZWDBhMA4KMgMW/P+ya6nv4
 u31tHyuH+Utpfcz57rrbe1g3cnF5Ln6ArqRR430hdMs0TzGrzV03R2ITmydvyDoOlzfVU51cs
 tjfI8HWUBbKP4QAN1DqL5O1uE/5cbTLiap4N4IKWKByzyZmZII0/SbhHUi1Gzjs0+PEelCXYH
 5DEB80XXF+orZB9y/vN6103vlT4s6jOIdgB4oKcSuiUpXQxlcMAV7LPlLW6g847SS0ypXPxwf
 e7jbTb5hzOHahTK/1me6suKEOe9v4D4Bh9u0Phuw3E5qIfHsu/fQt09AXM+f1bELjQYd/M3ye
 JyWQchJVCCNbI2+JrorP14/xfK+N9omJHS35YSyJ93M+Y2plrVqNKstiEiF4Mq2EqCICSrghR
 2IQjeU+RChVp5ftbSzpJYH8kV/wE9Y+XbeVnGBivkeN0c9yY3RGI0KBmraCNrg1rlJXuMQMfp
 l77jUrkDfRCUxGqWrvShRESNwvo6gALOF8s16tIKntg/+Cdw/PQD0ThsnABN5qBQ3iVwCQiAL
 DUhQExsCXAa2b7iA20VxR1huB8ZWhh09zCvTvFMaEJ0d24xFxUAE6mtf/TF6EYkEAaUp/iBfT
 gb4jmdvGAWE67hnygckgAFYJMkwqwk1J0gFSeHliMUlPyl94L7D24GmNNNPCaa37+nONaKvzu
 TigiT8iMS7p+8zpelHAmngE49NGsHTeFYPB9qHnB5SJ+gZKhILjqlXAH87OjSdidw2vjJ22yU
 9hmmn7TnBhouxi4cZzUcGYUz3uLHCgQ59iV8thLu243NZyfbuiLR4ptq7na6T6GzZfEbw11aQ
 Xlp6BO5CE9o7zk8ttCnnE+G0EvJwrHB+6yiYrPTP949ZIXzL44ShNujXx/lvvsX6LC+kN+3bW
 AyyZaWNaeUZnnErrSZrRs2dUuxxzX6pDWaCLQYgV7Sx/HltG45sYLE2xcc936U4/rOJvgHA9s
 QKCOMdEP1WWpypyJJXPAsc6wYbc2uSt8spSKL4VGWAWdLqumOXeMEPRBN7at5Gpat9vir+iMV
 CGn+trVG/2BLHnzar9mToA4JUuoO9PF/wfYdVkwhnLzOCxH33Ks+YCg2ATGkNnD5NCjJoN8sG
 3HFndQVIskxehEDYdYX1ccxn43nW2toDWfjLz0uAL9b56PUsBeki7FPW5mOnV0hGYmrGCNtG3
 MDCXNkir+iZQJ1aNkA0fZU6sFUVea5QuUunPXwZ/vHKD1+lcwydPHICi2meCSKybtgjkBRDG/
 RRXXp1g==
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/6bd16bdb-aa5e-0512-739d-b84100596035%40gmx.net>
Precedence: bulk

Hello list,

I noticed the weird behaviour that doing a pg_restore of a huge database
dump, leads to constant read I/O (at about 15K IOPS from the NVMe drive
that has the dump file) for about one hour. I believe it happens with
any -j value>=3D2.

In particular, I get output like the following in the pg_restore log, only
a few seconds after running it:

pg_restore: launching item 12110 TABLE DATA yyy
pg_restore: processing data for table "public.yyy"
[ long pause ...]
pg_restore: finished item 12110 TABLE DATA yyy

The output varies a bit, depending on when each process comes to the
bottleneck point, and it is hard to isolate each process' log since the
output is not prefixed with the PID, so take it with a grain of salt.

If I strace the pg_restore children, I see them read()ing 4K and seek()ing
a couple blocks forward, all the time:

read(4, "..."..., 4096) =3D 4096
lseek(4, 55544369152, SEEK_SET)         =3D 55544369152
read(4, "..."..., 4096) =3D 4096
lseek(4, 55544381440, SEEK_SET)         =3D 55544381440
read(4, "..."..., 4096) =3D 4096
lseek(4, 55544397824, SEEK_SET)         =3D 55544397824
read(4, "..."..., 4096) =3D 4096
lseek(4, 55544414208, SEEK_SET)         =3D 55544414208
read(4, "..."..., 4096) =3D 4096
lseek(4, 55544426496, SEEK_SET)         =3D 55544426496

No other syscalls are occuring (no sends or writes whatsoever).

After the ~1h pause, I don't notice the behaviour again during the many
hours restore, even though there are plenty of larger tables in the dump.
The normal restore activity resumes, during which the pg_restore processes
are barely noticeable in the system, and the COPY postgres backend
processes do most of the work.

Here is the exact pg_restore command that reproduced the issue for me last
on PostgreSQL 17:

pg_restore  -vvv -U $DBUSER -j8 -d $DBNAME --data-only --no-tablespaces --=
no-table-access-method --strict-names  $DUMP_FILENAME

And here is the pg_dump command which has created the dump file, executed
on PostgreSQL 16.

pg_dump -v --format=3Dcustom --compress=3Dzstd --no-toast-compression  $DB=
NAME   |  $send_to_remote_storage

The database is multiple TBs large with a couple thousands tables (notable
is one huge table with 1000 partitions). The zstd-compressed custom format
dump file is 816 GB in size.

What do you think causes this? Is it something that can be improved?

Thanks in advance,
Dimitris