Skip site navigation (1) Skip section navigation (2)

Re: Occasionally slow queries - should I be concerned?

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
Cc: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Occasionally slow queries - should I be concerned?
Date: 2012-07-26 19:47:20
Message-ID: (view raw, whole thread or download thread mbox)
Lists: sfpug
On 07/26/2012 12:19 PM, Brian Ghidinelli wrote:
> I have a few queries that show up repeatedly in my pgsi/pg_fouine 
> reports.  One of them is a 4-way join SELECT that is called hundreds 
> of times per day.  When I run it myself, it executes in 18-40ms 
> depending on the query parameters but the logs show a small percentage 
> of those runs, say 10 times a day, the query runs two orders of 
> magnitude slower in the 1100-2500ms range.
> Is this normal?  The box has 16gb of RAM and the database base folder 
> is 12GB in size so I believe the database fits in memory. The 
> postgresql.conf settings are:
> shared_buffers = 4GB
> work_mem = 8MB
> maintenance_work_mem = 256MB
> effective_cache_size = 14GB
> Normal behavior I should ignore or worth investigating?
Hard to say without context. Queries can be intermittently slow for many 
reasons including cache is being flushed to disk, cache that was flushed 
from RAM and must be reread from disk, queries waiting for locks, 
contention from other PostgreSQL or non-PostgreSQL work on the machine, etc.

Keep an eye on it if you are concerned or if it is causing you problems. 
If it is, then post the relevant useful info such as Pg version, OS 
version, something about disk layout (local, NFS, spindles, RAID 
config), queries or other operations like backups that may be causing 
resource contention, and other observations that you feel may provide clues.


In response to

sfpug by date

Next:From: Josh BerkusDate: 2012-07-28 01:35:43
Subject: Next SFPUG: PostgresXC
Previous:From: Brian GhidinelliDate: 2012-07-26 19:19:25
Subject: Occasionally slow queries - should I be concerned?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group