Re: many sessions waiting DataFileRead and extend

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