many sessions waiting DataFileRead and extend
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. many sessions were running same "DELETE FROM " in parallel waiting on "extend" and "DataFileRead", there are triggers in this table "After delete" to insert/delete other tables in the tigger. time wait_event count(sessions) 2025-06-24 18:00:11.687245+00 | DataFileFlush | 1 2025-06-24 18:00:11.687245+00 | DataFileWrite| 1 2025-06-24 18:00:11.687245+00 | PgSleep| 1 2025-06-24 18:00:11.687245+00 | DataFileExtend | 7 2025-06-24 18:00:11.687245+00 | ProcArrayGroupUpdate | 1 2025-06-24 18:00:11.687245+00 | WALWrite| 5 2025-06-24 18:00:11.687245+00 | extend |55 2025-06-24 18:00:11.687245+00 | DataFileRead |45 2025-06-24 18:00:11.687245+00 | DataFilePrefetch | 2 Thanks, James
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 " 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
