MIME-Version: 1.0
From: Jeremy Finzel <finzelj@gmail.com>
Date: Mon, 14 Aug 2017 14:53:48 -0500
Message-ID: 
 <CAMa1XUi6pbugdB7zUA6weoPjmUHTzNLZcSEAh06-7FZRzkuHQw@mail.gmail.com>
Subject: Odd sudden performance degradation related to temp object churn
To: postgres performance list <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="001a1144226e7b2a1e0556bc05c9"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a1144226e7b2a1e0556bc05c9
Content-Type: text/plain; charset="UTF-8"

This particular db is on 9.3.15.  Recently we had a serious performance
degradation related to a batch job that creates 4-5 temp tables and 5
indexes.  It is a really badly written job but what really confuses us is
that this job has been running for years with no issue remotely approaching
this one.  We are also using pgpool.

The job would kick off with 20-30 of similar queries running at once.  The
thing normally takes only 30ms or so to run - it only operates on 1
customer at a time (yes, it's horribly written).  All of a sudden the
cluster started thrashing and performance seriously degraded.  We tried a
number of things with no success:

   - Analyzed the whole database
   - Turned off full logging
   - Turned off synchronous commit
   - Vacuumed several of the catalog tables
   - Checked if we had an abnormal high amount of traffic this time - we
   didn't
   - No abnormal disk/network issues (we would have seen much larger issues
   if that had been the case)
   - Tried turning down the number of app nodes running

What ended up completely resolving the issue was converting the query to
use ctes instead of temp tables.  That means we avoided the disk writing
and the catalog churn, and useless indexes.  However, we are baffled as to
why this could make such a big difference when we had no issue like this
before, and we have seen no systematic performance degradation in our
system.

Any insights would be greatly appreciated, as we are concerned not knowing
the root cause.

Thanks,
Jeremy

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

<div dir=3D"ltr">This particular db is on 9.3.15.=C2=A0 Recently we had a s=
erious performance degradation related to a batch job that creates 4-5 temp=
 tables and 5 indexes.=C2=A0 It is a really badly written job but what real=
ly confuses us is that this job has been running for years with no issue re=
motely approaching this one.=C2=A0 We are also using pgpool.<div><br></div>=
<div>The job would kick off with 20-30 of similar queries running at once.=
=C2=A0 The thing normally takes only 30ms or so to run - it only operates o=
n 1 customer at a time (yes, it&#39;s horribly written).=C2=A0 All of a sud=
den the cluster started thrashing and performance seriously degraded.=C2=A0=
 We tried a number of things with no success:</div><div><ul><li>Analyzed th=
e whole database</li><li>Turned off full logging</li><li>Turned off synchro=
nous commit</li><li>Vacuumed several of the catalog tables</li><li>Checked =
if we had an abnormal high amount of traffic this time - we didn&#39;t</li>=
<li>No abnormal disk/network issues (we would have seen much larger issues =
if that had been the case)</li><li>Tried turning down the number of app nod=
es running</li></ul><div>What ended up completely resolving the issue was c=
onverting the query to use ctes instead of temp tables.=C2=A0 That means we=
 avoided the disk writing and the catalog churn, and useless indexes.=C2=A0=
 However, we are baffled as to why this could make such a big difference wh=
en we had no issue like this before, and we have seen no systematic perform=
ance degradation in our system.</div></div><div><br></div><div>Any insights=
 would be greatly appreciated, as we are concerned not knowing the root cau=
se.</div><div><br></div><div>Thanks,</div><div>Jeremy</div></div>

--001a1144226e7b2a1e0556bc05c9--