Date: Fri, 28 Mar 2025 00:45:58 +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: <3764bde7-ca09-a8a3-489c-0166f772245d@gmx.net>
Message-ID: <1cbb9bd6-60cd-92cb-c3c2-4cf4fd8a7b64@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>
 <3764bde7-ca09-a8a3-489c-0166f772245d@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=US-ASCII
UI-OutboundReport: notjunk:1;M01:P0:MbCB05mtw7I=;+0qIAdgvETEIZWnTqt6Lq9citDh
 3leScbgDP0VMd53/H+EOUd0ON3AGHMeXLyMIuV9DfIXqX74a3Qi4h+HHIOV82a1jiiyu2JZnc
 keOWkijTUEJw4akDvHq1WL0198whBRDTp+5/zXFew+/iu4KOlFp0NlyZS4ZwVopOMbB0ZIEyI
 EPoVws+b2DDVPw9camBz8g4EFTDN5XxHmS54d9Et+tZEOpSIFEv5l4n3kFXdaxVTF3Vruyjp9
 sywLd6R4zXSqxXwKeRt69GhZLY5H0BV8qKrbJKnbilT4f5oBDZ+2WWkYNLj84SQrcPz7Lcxj5
 0aKTmjzVP8hf8Zr/tfaXcKnKgsG8Yze3QrtmhAKFkvE2Hcm+dvINFevIvnYN4Ft0i78hJFRZs
 lvOyzdj2VrSXip/Q6EOjArn8GhC837efsVEp+kmQDNHKmDaHIOF2CUCmz7uXZ/iDIIV9cZMnB
 nkHBnvZQ9cm3DynVVU3cGWhCa4Nqz5Iuc3JHokQB8KPy0nOcLUr/NysU6I9xUvUAuICigAu/I
 CG0Uj+Z+jTFjjkxv9vNK1U2RJShequyIPvEr8mekFiTKFa224s4/3oSWm3NKLBihSfgujtA14
 +kYTtweEOMl75l7J0KCxWMpTBNh4BrklyzbwgFcQ9u4LmibqD2AJav++O0c8jh0Vr6l+ABgBF
 dT3tuthu5AyiDeBSWRvHJim4sdgMcAHMLbRig2HUurzFYL5zc6BwoYbsSa9S0aiCgFNUZZAnb
 bLerL/5fLogYeKbz7QT/1wqu1R7OLgzZ6gYbiJpnhUqgtBAA+dnHZDm+W5bNJKYlZUhQEVgas
 PS7JbMQDP2EYnuj73KKa9dD25bIA5vtUUQS0QgUYs6GZ1FwJNiAU6sNJ7KokeHF3mUCGuXv6X
 8x6gux5+y15MdbUQ9jWgyPnLgOAarZZdbluyzfK+SnbJF9NIORO5Erly/4Z/40EAJyRJ5vS6U
 JucPjWjrdP+UprQufRDur+6R9Ybrx1G+/KYa+4b3YYK4zVIPb+nYtKgNLtxbzaQm6HeEw67mr
 xtFZst+pB5wXwix0yz9P4NmeS2fB2jIAbRd0u/iPbm6UPcyJmaizu4Ru+aLkJ1lBnR6UtXU6G
 /wNXWOMfckAcooyttNfiAATqwnnbwIYiRhgZYxvoEz+r5lMGIv1P9RBFF4mzF8sMOFHckFrKc
 r8197WxaZXO4kmmXA/7Rv2UCGPGn1xVXbg6bykoL0a0AuGYVNi8EQhhsvLSIN90O51z8O9bPN
 9rWpxUCEs1F8ctP1dB3FNrOFDHVFKALc6ATMK3N86F7bSYI5lGsY17snXwtgyCvfSNrT+ai/f
 9mkPTX2gj7d/KhnrSEK5W413Xf5cvfDLepEFFNC1tEF0GYAQDKmspCx9aB1GGqk07nrxwC0kJ
 VTMHneb8a2/PtF4/09Sexge1Hb65PwsltjbNgYnUHcy/snyhrdeTvVP6QtgaDX4NY3fsTNzlu
 bkxyFQwb2JTLrm6N5XZhHcfHb5i0=
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/1cbb9bd6-60cd-92cb-c3c2-4cf4fd8a7b64%40gmx.net>
Precedence: bulk

Hello again,

I traced the seeking-reading behaviour of parallel pg_restore inside
_skipData() when called from _PrintTocData(). Since most of today's I/O
devices (both rotating and solid state) can read 1MB faster sequentially
than it takes to seek and read 4KB, I tried the following change:

diff --git a/src/bin/pg_dump/pg_backup_custom.c
b/src/bin/pg_dump/pg_backup_custom.c
index 55107b20058..262ba509829 100644
=2D-- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -618,31 +618,31 @@ _skipLOs(ArchiveHandle *AH)
   * Skip data from current file position.
   * Data blocks are formatted as an integer length, followed by data.
   * A zero length indicates the end of the block.
  */
  static void
  _skipData(ArchiveHandle *AH)
  {
         lclContext *ctx =3D (lclContext *) AH->formatData;
         size_t          blkLen;
         char       *buf =3D NULL;
         int                     buflen =3D 0;

         blkLen =3D ReadInt(AH);
         while (blkLen !=3D 0)
         {
-               if (ctx->hasSeek)
+               if (ctx->hasSeek && blkLen > 1024 * 1024)
                 {
                         if (fseeko(AH->FH, blkLen, SEEK_CUR) !=3D 0)
                                 pg_fatal("error during file seek: %m");
                 }
                 else
                 {
                         if (blkLen > buflen)
                         {
                                 free(buf);
                                 buf =3D (char *) pg_malloc(blkLen);
                                 buflen =3D blkLen;
                         }
                         if (fread(buf, 1, blkLen, AH->FH) !=3D blkLen)
                         {
                                 if (feof(AH->FH))


This simple change improves immensely (10x maybe, depends on the number of
workers) the offset-table building phase of the parallel backup.

A problem still remaining is that this offset-table building phase is done
on every worker process, which means that all workers scan almost in
parallel the whole archive. A more intrusive improvement would be to move
this phase to the parent process, before spawning the children.

What do you think?

Regards,
Dimitris


P.S. I also have a simple change that changes -j1 switch to mean "parallel
but with one worker process", that I did for debugging purposes. Not sure
if it is of interest here.