public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dimitrios Apostolou <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: parallel pg_restore blocks on heavy random read I/O on all children processes
Date: Fri, 28 Mar 2025 00:45:58 +0100 (CET)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

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
--- 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 = (lclContext *) AH->formatData;
         size_t          blkLen;
         char       *buf = NULL;
         int                     buflen = 0;

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

view thread (8+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: parallel pg_restore blocks on heavy random read I/O on all children processes
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox