Re: Odd sudden performance degradation related to temp object churn

[Jeremy Finzel <finzelj@xxxxxxxxx>]

On Tue, Aug 15, 2017 at 12:07 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> 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:

SAN IO:

CPU:

7-9pm on 8/10 (controlled attempts starting a little after 7):

CPU:

Write IO on SAN:

Read IO on Local disk:

Write IO on Local disk: