Re: many sessions waiting DataFileRead and extend

On Thu, Jun 26, 2025 at 2:48 AM 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
timeto capture that, anyway, I will try. before that, I want to understand if "os page cache" or "pg buffer cache" can
contributeto the wait_event time "extend" and "DataFileRead", or bgwriter ,checkpoint flushing data to disk can impact
thattoo ?  we enable bgwriter , and we see checkpointer get scheduled by "wal" during the time, so I just increased
max_wal_sizeto 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.

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