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:19:15 -0500
Message-ID: <25400.1452093555@sss.pgh.pa.us> (raw)
In-Reply-To: <2A061A33-9E7C-44ED-A8A4-411FE128A1C9@motus.com>
References: <2A061A33-9E7C-44ED-A8A4-411FE128A1C9@motus.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Scott Rankin <srankin@motus.com> writes:
> I’m trying to track down why some queries on my database system are intermittently much slower than usual. I have some queries that run, on average, 2-3ms, and they run at a rate of about 10-20 queries/second. However, every 3-5 seconds, one of the queries will be 500-100ms. This is making the average query time turn out to be closer to 20ms, with a very large standard deviation.
> This happens to a number of otherwise very fast queries, and I’m trying to trace the reason. I’ve turned on lock logging and checkpoint logging, and this behavior happens whether or not a checkpoint is occurring. There are no lock waits happening in the system either.
I doubt you've proved that --- log_lock_waits will only report on waits
longer than deadlock_timeout, which you don't appear to have changed from
its default of 1 sec. If you're trying to capture events that last a few
hundred msec, you're going to need to reduce deadlock_timeout to maybe
100ms.
It would help to know more about what the queries are, too. The cause
might be something like GIN index pending-list cleanup but we can't tell
on the basis of this much info.
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: <25400.1452093555@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