Message-ID: <66879d8bd44148f2ef1dcde1eff056e6c671306e.camel@cybertec.at>
Subject: Re: many sessions waiting DataFileRead and extend
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: James Pang <jamespang886@gmail.com>,
	pgsql-performance@lists.postgresql.org
Date: Wed, 25 Jun 2025 07:59:41 +0200
In-Reply-To: 
 <CAHgTRfdDkiX5bLRCYhrP18kFJjcvs1eHvL+jUtX1Ep-Y3=cFVg@mail.gmail.com>
References: 
	<CAHgTRfdDkiX5bLRCYhrP18kFJjcvs1eHvL+jUtX1Ep-Y3=cFVg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/66879d8bd44148f2ef1dcde1eff056e6c671306e.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-06-25 at 11:15 +0800, James Pang wrote:
> pgv14, RHEL8, xfs , we suddenly see tens of sessions waiting on "DataFile=
Read" and
> "extend", it last about 2 seconds(based on pg_stat_activity query) , duri=
ng the
> waiting time, "%sys" cpu increased to 80% , but from "iostat" , no high i=
ops 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 in=
sert/delete
> other tables in the tigger.=C2=A0

One thing that almost certainly would improve your situation is to run fewe=
r
concurrent statements, for example by using a reasonably sized connection p=
ool.

Yours,
Laurenz Albe