From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Scott Rankin <srankin(at)motus(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Queries intermittently slow |
Date: | 2016-01-06 15:19:15 |
Message-ID: | 25400.1452093555@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Scott Rankin <srankin(at)motus(dot)com> writes:
> Im 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 Im trying to trace the reason. Ive 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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Rankin | 2016-01-06 15:30:56 | Re: Queries intermittently slow |
Previous Message | Scott Rankin | 2016-01-06 14:58:05 | Queries intermittently slow |