MIME-Version: 1.0
References: 
 <CAHgTRff_1mTsU-QSaDBY5k9+D-i9sV0aQeRY9ShffV=6b7jOWQ@mail.gmail.com>
In-Reply-To: 
 <CAHgTRff_1mTsU-QSaDBY5k9+D-i9sV0aQeRY9ShffV=6b7jOWQ@mail.gmail.com>
From: David Mullineux <dmullx@gmail.com>
Date: Sat, 21 Dec 2024 16:40:54 +0000
Message-ID: 
 <CAGsyd8U0cgD=2wTG5HAsbs=e=9ynz3r55tH56xzQeUQYju94Yw@mail.gmail.com>
Subject: Re: huge shared_blocks_hit one select but manually run very fast
To: James Pang <jamespang886@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000fe45b30629ca6d00"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGsyd8U0cgD%3D2wTG5HAsbs%3De%3D9ynz3r55tH56xzQeUQYju94Yw%40mail.gmail.com>
Precedence: bulk

--000000000000fe45b30629ca6d00
Content-Type: text/plain; charset="UTF-8"

Depends on a lot of thongs...Visibility map sounds like it's impacted here.
Are your inserts towards the index (like a monotonically increasing serial
id)  or scattered around the index values ?   How big is the table  index
and shared buffers ?   An example would really help

On Sat, 21 Dec 2024, 11:51 James Pang, <jamespang886@gmail.com> wrote:

> Hi,
>    we have a simple select .... from table where ... (that mache the
> index) , table has 80million rows.  when many application sessions run the
> query and at the same time some other sessions doing insert into ... this
> table. from pg_stat_statements, shared_blks_hit show 31652 / per call.   we
> see very high cpu almost 100% cpu during application workload test, and
> high LWLock BufferMapping waiting for these querys.  But manually run the
> sql show only 2148 shared_blks_hit/ per call.  this is a simple sql, from
> pg_profile we did see it use same index scan as manually running.  What
> could be possible reason leading so big difference with shared_blks_hit ?
>  PGv14.8
>
> Thanks,
>
> James
>

--000000000000fe45b30629ca6d00
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<p dir=3D"ltr">Depends on a lot of thongs...Visibility map sounds like it&#=
39;s impacted here. Are your inserts towards the index (like a monotonicall=
y increasing serial id)=C2=A0 or scattered around the index values ?=C2=A0=
=C2=A0 How big is the table=C2=A0 index and shared buffers ?=C2=A0=C2=A0 An=
 example would really help</p>
<br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=
=3D"gmail_attr">On Sat, 21 Dec 2024, 11:51 James Pang, &lt;<a href=3D"mailt=
o:jamespang886@gmail.com">jamespang886@gmail.com</a>&gt; wrote:<br></div><b=
lockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px =
#ccc solid;padding-left:1ex"><div dir=3D"ltr">Hi,=C2=A0<div>=C2=A0 =C2=A0we=
 have a simple select .... from table where ... (that mache the index) , ta=
ble has 80million rows.=C2=A0 when many application sessions run the query =
and at the same time some other sessions doing insert into ... this table. =
from pg_stat_statements, shared_blks_hit show 31652 / per call.=C2=A0 =C2=
=A0we see very high cpu almost 100% cpu during application workload test, a=
nd high LWLock=C2=A0BufferMapping waiting for these querys.=C2=A0 But manua=
lly=C2=A0run the sql show only 2148 shared_blks_hit/ per call.=C2=A0 this i=
s a simple sql, from pg_profile we did see it use same index scan as manual=
ly=C2=A0running.=C2=A0 What could be possible reason leading so big differe=
nce with shared_blks_hit ?=C2=A0=C2=A0</div><div>=C2=A0PGv14.8=C2=A0</div><=
div><br></div><div>Thanks,</div><div><br></div><div>James</div></div>
</blockquote></div>

--000000000000fe45b30629ca6d00--