Subject: Re: Odd sudden performance degradation related to temp
 object churn
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Jeremy Finzel <finzelj@gmail.com>, Scott Marlowe <scott.marlowe@gmail.com>
Cc: Jerry Sievers <gsievers19@comcast.net>, Peter Geoghegan <pg@bowt.ie>,
 postgres performance list <pgsql-performance@postgresql.org>
References: 
 <CAMa1XUi6pbugdB7zUA6weoPjmUHTzNLZcSEAh06-7FZRzkuHQw@mail.gmail.com>
 <CAH2-WznKCt_UBaPEY=WwBof+TkTaDYHbZmDsFE=Vigy3Rqi35A@mail.gmail.com>
 <87mv71eoep.fsf@jsievers.enova.com>
 <CAOR=d=3+N1467oddeN3E7r2PKELQt+85s+c+JjP-tgRHoYukpA@mail.gmail.com>
 <CAMa1XUg1MErrJifeeTNse+TQdbEZjs3t+tzOvnu7KCW9tUqcsA@mail.gmail.com>
 <c905f589-0166-61fa-194e-26fdc162006e@catalyst.net.nz>
Message-ID: <83942a43-c11f-2325-7fbb-dbc5539ecf7d@catalyst.net.nz>
Date: Tue, 22 Aug 2017 13:04:13 +1200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.2.1
MIME-Version: 1.0
In-Reply-To: <c905f589-0166-61fa-194e-26fdc162006e@catalyst.net.nz>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 19/08/17 13:49, Mark Kirkwood wrote:

>
>
> On 19/08/17 02:21, Jeremy Finzel wrote:
>> On Tue, Aug 15, 2017 at 12:07 PM, Scott Marlowe 
>> <scott.marlowe@gmail.com <mailto:scott.marlowe@gmail.com>> 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.
>>
>>
> Hi, graphs for latency (or await etc) might be worth looking at too - 
> sometimes the troughs between the IO spikes are actually when the 
> disks have been overwhelmed with queued up pending IOs...
>
>

Sorry - I see you *did* actually have iowait in there under your CPU 
graph...which doesn't look to be showing up a lot of waiting. However 
still might be well worth getting graphs showing per device waits and 
utilizations.

regards

Mark


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance