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:32:30 -0500
Message-ID: <28587.1452097950@sss.pgh.pa.us> (raw)
In-Reply-To: <27999.1452096879@sss.pgh.pa.us>
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>
<27999.1452096879@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
I 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?
BTW, a glaring flaw in that theory is that if this query is touching only
about four pages worth of data, and you are running it ten times a second,
how in heck would that data ever fall out of shared buffer cache at all?
Your working set across your whole DB would have to be enormously more
than your 8GB shared_buffers setting for that to possibly happen.
So what seems more likely after more thought is that the pages are staying
in our shared buffer arena just fine, but the kernel is randomly choosing
to swap out parts of the arena, and the delays correspond to swap-in
waits. (There would still have to be a mighty crummy disk subsystem
underlying things for swap-in to take so long, but this is a more
plausible theory for exactly what's invoking the disk read.)
Postgres can't directly see when this is happening, but you could try
watching "iostat 1" and noticing whether swap-in events seem to be
correlated with the slow queries.
If this is the problem, then the answer is to reduce the pressure on
system memory so that swap-outs are less likely. You might find that
a smaller shared_buffer arena is a good thing (so that all of it stays
"hot" and unswappable from the kernel's perspective). Or reduce the
number of active backend processes.
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: <28587.1452097950@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