public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jeremy Finzel <[email protected]>
To: Scott Marlowe <[email protected]>
Cc: Jerry Sievers <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Cc: postgres performance list <[email protected]>
Subject: Re: Odd sudden performance degradation related to temp object churn
Date: Fri, 18 Aug 2017 09:21:36 -0500
Message-ID: <CAMa1XUg1MErrJifeeTNse+TQdbEZjs3t+tzOvnu7KCW9tUqcsA@mail.gmail.com> (raw)
In-Reply-To: <CAOR=d=3+N1467oddeN3E7r2PKELQt+85s+c+JjP-tgRHoYukpA@mail.gmail.com>
References: <CAMa1XUi6pbugdB7zUA6weoPjmUHTzNLZcSEAh06-7FZRzkuHQw@mail.gmail.com>
<CAH2-WznKCt_UBaPEY=WwBof+TkTaDYHbZmDsFE=Vigy3Rqi35A@mail.gmail.com>
<[email protected]>
<CAOR=d=3+N1467oddeN3E7r2PKELQt+85s+c+JjP-tgRHoYukpA@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
On Tue, Aug 15, 2017 at 12:07 PM, Scott Marlowe <[email protected]>
wrote:
> So do iostat or iotop show you if / where your disks are working
> hardest? Or is this CPU overhead that's killing performance?
>
Sorry for the delayed reply. I took a look in more detail at the query
plans from our problem query during this incident. There are actually 6
plans, because there were 6 unique queries. I traced one query through our
logs, and found something really interesting. That is that all of the
first 5 queries are creating temp tables, and all of them took upwards of
500ms each to run. The final query, however, is a simple select from the
last temp table, and that query took 0.035ms! This really confirms that
somehow, the issue had to do with *writing *to the SAN, I think. Of course
this doesn't answer a whole lot, because we had no other apparent issues
with write performance at all.
I also provide some graphs below.
7pm-3am on 8/10 (first incidents were around 10:30pm, other incidents ~1am,
2am):
Local Disk IO:
[image: Screen Shot 2017-08-18 at 8.20.06 AM.png]
SAN IO:
[image: Screen Shot 2017-08-18 at 8.16.59 AM.png]
CPU:
[image: Screen Shot 2017-08-18 at 8.20.58 AM.png]
7-9pm on 8/10 (controlled attempts starting a little after 7):
CPU:
[image: Screen Shot 2017-08-18 at 8.43.35 AM.png]
Write IO on SAN:
[image: Screen Shot 2017-08-18 at 8.44.32 AM.png]
Read IO on Local disk:
[image: Screen Shot 2017-08-18 at 8.46.27 AM.png]
Write IO on Local disk:
[image: Screen Shot 2017-08-18 at 8.46.58 AM.png]
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Odd sudden performance degradation related to temp object churn
In-Reply-To: <CAMa1XUg1MErrJifeeTNse+TQdbEZjs3t+tzOvnu7KCW9tUqcsA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox