Re: many sessions waiting DataFileRead and extend

transparent_hugepage=never in our prod servers, %iowait is low 0.x-1.x%
, read/write iops <2k, and read/write wait 0.x ms. we did not find other
abnormal logs from OS logs either. Yes, we are discussing with our
application team to reduce concurrency. more questions about DataFileRead
and extend wait_event. when starting read data from physical datafiles ,
it report DataFileRead wait_event, until the data pages successfully loaded
into buffer cache, then end wait_event report from
pg_stat_activity,right? if some contention in buffer cache, that will make
the session waiting there while in report DataFileRead, right? another
thing is Operating system page cache, may sessions starting read data in
parallel may bring contention on OS pagecache, bgwriter and checkpointer is
flushing data, or OS background worker may flush dirty pages from OS page
cache too, bgwriter or checkpointer maybe contention on OS pagecache or PG
buffer cache? . no idea how to get the reason from online production
server.

Thanks,

James

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> 於 2025年6月25日週三 下午1:59寫道：

> On Wed, 2025-06-25 at 11:15 +0800, James Pang wrote:
> > pgv14, RHEL8, xfs , we suddenly see tens of sessions waiting on
> "DataFileRead" and
> > "extend", it last about 2 seconds(based on pg_stat_activity query) ,
> during the
> > waiting time, "%sys" cpu increased to 80% , but from "iostat" , no high
> iops and
> > io read/write latency increased either.
>
> Run "sar -P all 1" and see if "%iowait" is high.
>
> Check if you have transparent hugepages enabled:
>
> cat /sys/kernel/mm/transparent_hugepage/enabled
>
> If they are enabled, disable them and see if it makes a difference.
>
> I am only guessing here.
>
> > many sessions were running same "DELETE FROM xxxx" in parallel waiting
> on "extend"
> > and "DataFileRead", there are triggers in this table "After delete" to
> insert/delete
> > other tables in the tigger.
>
> One thing that almost certainly would improve your situation is to run
> fewer
> concurrent statements, for example by using a reasonably sized connection
> pool.
>
> Yours,
> Laurenz Albe
>