MIME-Version: 1.0
References: 
 <CAHgTRfdDkiX5bLRCYhrP18kFJjcvs1eHvL+jUtX1Ep-Y3=cFVg@mail.gmail.com>
 <66879d8bd44148f2ef1dcde1eff056e6c671306e.camel@cybertec.at>
 <F9A0F6D3-9365-4A32-BE14-C996E08C3A0D@gmail.com>
 <CAHgTRfff0+i4PV_9G-fAREdDcMi4J1StcCDG6NcJ=hGhT2-SvQ@mail.gmail.com>
 <FEA674C6-F43E-416F-A065-51C7F4A549B8@gmail.com>
 <CAHgTRfeguFjCFGWVwEDFMF5MGJMJhvwZhnPjbvqp1KCQ34SPdg@mail.gmail.com>
In-Reply-To: 
 <CAHgTRfeguFjCFGWVwEDFMF5MGJMJhvwZhnPjbvqp1KCQ34SPdg@mail.gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Thu, 26 Jun 2025 08:19:33 -0400
Message-ID: 
 <CA+TgmoZ95WZz5A0c8=T6tufGuB5Ku4AE7pOYDLXNA4HxLgFOKA@mail.gmail.com>
Subject: Re: many sessions waiting DataFileRead and extend
To: James Pang <jamespang886@gmail.com>
Cc: Frits Hoogland <frits.hoogland@gmail.com>,
 Laurenz Albe <laurenz.albe@cybertec.at>,
	pgsql-performance@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoZ95WZz5A0c8%3DT6tufGuB5Ku4AE7pOYDLXNA4HxLgFOKA%40mail.gmail.com>
Precedence: bulk

On Thu, Jun 26, 2025 at 2:48=E2=80=AFAM James Pang <jamespang886@gmail.com>=
 wrote:
>    we faced this issue 3 times this week, each time last only 2 seconds, =
so not easy to run perf in peak business time to capture that, anyway, I wi=
ll try. before that, I want to understand if "os page cache" or "pg buffer =
cache" can contribute to the wait_event time "extend" and "DataFileRead", o=
r bgwriter ,checkpoint flushing data to disk can impact that too ?  we enab=
le bgwriter , and we see checkpointer get scheduled by "wal" during the tim=
e, so I just increased max_wal_size to make checkpoint scheduled in longer =
time.

To be honest, given the additional information that this only lasted 2
seconds, this doesn't sound terribly abnormal to me. My guess would be
that a bunch of processes all tried to insert into the same relation
at the time, so PostgreSQL had to make it bigger, and the OS couldn't
extend the file quite quickly enough.

I don't want to make it sound like there's absolutely no problem here:
PostgreSQL's relation extension logic isn't as performant as we'd like
it to be, and there have been several attempts to fix it over the
years. I don't know if the last attempt was later than PG14, so you
might have more success on a newer version, but I'm also not really
sure if the limitations of the implementation are your main problem
here.

If I were troubleshooting this, I'd try to capture a snapshot of what
queries were running at the moment the problem happens. If, for
example, all the sessions suddenly decide to insert into the same
table at the same time, it might be good to think about whether
there's a way to avoid that.

--=20
Robert Haas
EDB: http://www.enterprisedb.com