From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Rankin <srankin@motus.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Queries intermittently slow
Date: Wed, 06 Jan 2016 11:14:39 -0500
Message-ID: <27999.1452096879@sss.pgh.pa.us> (raw)
In-Reply-To: <DC594B9F-BB6B-4AA8-A3F4-B3A47EE29112@motus.com>
References: <2A061A33-9E7C-44ED-A8A4-411FE128A1C9@motus.com>
<25400.1452093555@sss.pgh.pa.us>
<7EC59C76-D8FF-4880-8A50-9F1195D5D00E@motus.com>
<26050.1452094727@sss.pgh.pa.us>
<DC594B9F-BB6B-4AA8-A3F4-B3A47EE29112@motus.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Scott Rankin <srankin@motus.com> writes:
> On 1/6/16, 10:38 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>> A possible theory is that the slow cases represent times when the desired
>> page is not in cache, but you'd have to have a seriously overloaded disk
>> subsystem for a disk fetch to take hundreds of ms. Unless maybe this is
>> running on some cloud service with totally unspecified I/O bandwidth?
> This intrigues me. We are running on a, shall we say, less than name-brand cloud provider at the moment (transitioning to AWS later this month). Is there a reasonably straightforward way of confirming this hypothesis? We have had many performance issues with this vendor in the past, so I wouldn’t be surprised.
Hm, well, given that you are able to capture instances of the behavior
in EXPLAIN ANALYZE, I'd suggest trying EXPLAIN (ANALYZE,BUFFERS).
That will tell you the number of pages it found in shared buffers vs.
having to read them. Now, a "read" just means we had to ask the kernel,
not necessarily that the page came all the way from disk; if it's in
the kernel's disk cache that won't be very much slower than a shared-
buffers hit. Still, if the slowdowns are reliably seen only when a read
occurred, I'd say that's good evidence.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, srankin@motus.com
Subject: Re: Queries intermittently slow
In-Reply-To: <27999.1452096879@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox