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 10:38:47 -0500
Message-ID: <26050.1452094727@sss.pgh.pa.us> (raw)
In-Reply-To: <7EC59C76-D8FF-4880-8A50-9F1195D5D00E@motus.com>
References: <2A061A33-9E7C-44ED-A8A4-411FE128A1C9@motus.com>
<25400.1452093555@sss.pgh.pa.us>
<7EC59C76-D8FF-4880-8A50-9F1195D5D00E@motus.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Scott Rankin <srankin@motus.com> writes:
> Thanks for the update. The query in question is a pretty simple one - it joins 3 tables, all of which are static - they don’t have any writes being done against them. They have very few rows, and the query plan for them indicates that they are all sequential scans. When doing an EXPLAIN ANALYZE, the delay is not consistently on one table, it can vary between the three tables involved in the query.
No writes at all? That's pretty odd then. All the likely explanations
involve autovacuum or other forms of deferred maintenance.
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?
> I will look at changing the deadlock_timeout, but that might have to wait for the weekend since this is a production system.
You needn't restart the server for that, just edit postgresql.conf and
SIGHUP the postmaster.
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: <26050.1452094727@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