Re: Occasionally slow queries - should I be concerned?

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.

Cheers,
Steve