Message-ID: <fa9bda2e74987d819fc99bfc8533cc349e50dc03.camel@cybertec.at>
Subject: Re: many sessions waiting DataFileRead and extend
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: James Pang <jamespang886@gmail.com>, Frits Hoogland
	 <frits.hoogland@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Date: Thu, 26 Jun 2025 05:03:28 +0200
In-Reply-To: 
 <CAHgTRfff0+i4PV_9G-fAREdDcMi4J1StcCDG6NcJ=hGhT2-SvQ@mail.gmail.com>
References: 
	<CAHgTRfdDkiX5bLRCYhrP18kFJjcvs1eHvL+jUtX1Ep-Y3=cFVg@mail.gmail.com>
	 <66879d8bd44148f2ef1dcde1eff056e6c671306e.camel@cybertec.at>
	 <F9A0F6D3-9365-4A32-BE14-C996E08C3A0D@gmail.com>
	 <CAHgTRfff0+i4PV_9G-fAREdDcMi4J1StcCDG6NcJ=hGhT2-SvQ@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/fa9bda2e74987d819fc99bfc8533cc349e50dc03.camel%40cybertec.at>
Precedence: bulk

On Thu, 2025-06-26 at 10:32 +0800, James Pang wrote:
> thans for you suggestions, we have iowait from sar command too, copy here=
, checking with infra team not found abnormal IO activities either.=C2=A0=
=C2=A0
> 02:00:01 PM =C2=A0CPU =C2=A0 =C2=A0%usr =C2=A0 %nice =C2=A0 =C2=A0%sys %i=
owait =C2=A0 =C2=A0%irq =C2=A0 %soft =C2=A0%steal =C2=A0%guest =C2=A0%gnice=
 =C2=A0 %idle
> 02:00:03 PM =C2=A0all=C2=A0 =C2=A015.92 =C2=A0 =C2=A00.00 =C2=A0 43.02 =
=C2=A0 =C2=A00.65 =C2=A0 =C2=A00.76 =C2=A0 =C2=A02.56 =C2=A0 =C2=A00.00 =C2=
=A0 =C2=A00.00 =C2=A0 =C2=A00.00 =C2=A0 37.09

Crazy high system time.

If it is not transparent hugepages, the other suspect is fork().
Turn on "log_connections" for a minute or two and see if there are lots
of connections established.  That can easily use your CPU.

Other than that, I am out of guesses.

> Frits Hoogland <frits.hoogland@gmail.com> =E6=96=BC 2025=E5=B9=B46=E6=9C=
=8825=E6=97=A5=E9=80=B1=E4=B8=89 =E4=B8=8B=E5=8D=8810:27=E5=AF=AB=E9=81=93=
=EF=BC=9A
> > > On 25 Jun 2025, at 07:59, Laurenz Albe <laurenz.albe@cybertec.at> wro=
te:
> > >=20
> > > Run "sar -P all 1" and see if "%iowait" is high.
> >
> > I would (strongly) advise against the use of iowait as an indicator.

I have heard that before, and I am sure you are right.

I grant that if it is low, it may just mean that the CPU is under load.
But if %iowait is high, my experience is that that indicates an I/O problem=
.

Yours,
Laurenz Albe