Re: Consecutive Query Executions with Increasing Execution Time

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

Hi everyone!

Thanks a ton for this brilliant discussion here!
It turned out that Nicolas was correct! I found that the CPU was broken and
not spinning at all.
With consecutive parallel query execution, the CPU temperature hits 100C
almost immediately after 1 or 2 iterations.
So the processor starts throttling way below baseline clk frequency to
something like 1.2G or even 1G.

I waited until the new Fan came to report back, and now this weird behavior
went away.

Thanks,
Shijia

On Wed, Dec 18, 2019 at 7:44 AM Tom Lane <[email protected]> wrote:

> Laurenz Albe <[email protected]> writes:
> > On Tue, 2019-12-17 at 11:11 -0500, Jeff Janes wrote:
> >> If it is doing a seq scan (I don't know if it is) they intentionally
> use a
> >> small ring buffer to, so they evict their own recently used blocks,
> rather
> >> than evicting other people's blocks.  So these blocks won't build up in
> >> shared_buffers very rapidly just on the basis of repeated seq scans.
>
> > Sure, but according to the execution plans it is doing a Parallel Index
> Only Scan.
>
> Nonetheless, the presented test case consists of repeatedly doing
> the same query, in a fresh session each time.  If there's not other
> activity then this should reach some sort of steady state.  The
> table is apparently fairly large, so I don't find it surprising
> that the steady state fails to be 100% cached.
>
>                         regards, tom lane
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif">Hi everyone!</div><div class=3D"gmail_=
default" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div cl=
ass=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">Than=
ks a ton for this brilliant=C2=A0discussion here!</div><div class=3D"gmail_=
default" style=3D"font-family:arial,helvetica,sans-serif">It turned out tha=
t Nicolas was correct! I found that the CPU was broken and not spinning at =
all.</div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica=
,sans-serif">With consecutive parallel query execution, the CPU=C2=A0temper=
ature=C2=A0hits 100C almost immediately after 1 or 2 iterations.<br>So the =
processor starts throttling way below baseline <span class=3D"" id=3D":11g.=
1" tabindex=3D"-1" style=3D"">clk</span> frequency to something like 1.2G o=
r even 1G.</div><div class=3D"gmail_default" style=3D"font-family:arial,hel=
vetica,sans-serif"><br></div><div class=3D"gmail_default" style=3D"font-fam=
ily:arial,helvetica,sans-serif">I waited until the new Fan came to report b=
ack, and now this weird behavior went away.</div><div class=3D"gmail_defaul=
t" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div class=3D=
"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">Thanks,</d=
iv><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-s=
erif"><span class=3D"" id=3D":11g.2" tabindex=3D"-1" style=3D"">Shijia</spa=
n></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmai=
l_attr">On Wed, Dec 18, 2019 at 7:44 AM Tom Lane &lt;<span class=3D"" id=3D=
":11g.3" tabindex=3D"-1" style=3D"">tgl</span>@<span class=3D"" id=3D":11g.=
4" tabindex=3D"-1" style=3D"">sss</span>.<span class=3D"" id=3D":11g.5" tab=
index=3D"-1" style=3D"">pgh</span>.<a href=3D"http://pa.us">pa.us</a>&gt; w=
rote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p=
x 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Laurenz Al=
be &lt;<a href=3D"mailto:[email protected]" target=3D"_blank">lauren=
[email protected]</a>&gt; writes:<br>
&gt; On Tue, 2019-12-17 at 11:11 -0500, Jeff Janes wrote:<br>
&gt;&gt; If it is doing a seq scan (I don&#39;t know if it is) they intenti=
onally use a<br>
&gt;&gt; small ring buffer to, so they evict their own recently used blocks=
, rather<br>
&gt;&gt; than evicting other people&#39;s blocks.=C2=A0 So these blocks won=
&#39;t build up in<br>
&gt;&gt; shared_buffers very rapidly just on the basis of repeated seq scan=
s.<br>
<br>
&gt; Sure, but according to the execution plans it is doing a Parallel Inde=
x Only Scan.<br>
<br>
Nonetheless, the presented test case consists of repeatedly doing<br>
the same query, in a fresh session each time.=C2=A0 If there&#39;s not othe=
r<br>
activity then this should reach some sort of steady state.=C2=A0 The<br>
table is apparently fairly large, so I don&#39;t find it surprising<br>
that the steady state fails to be 100% cached.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div><div><br></div><div dir=3D"ltr" class=3D"gmail_signature=
"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div di=
r=3D"ltr"><div><div dir=3D"ltr"></div></div></div></div></div></div></div><=
/div></div></div></div>

--000000000000a2a399059a1bda98--