| Lists: | sfpug |
|---|
| From: | Brian Ghidinelli <brian(at)pukkasoft(dot)com> |
|---|---|
| To: | SF Postgres <sfpug(at)postgresql(dot)org> |
| Subject: | Occasionally slow queries - should I be concerned? |
| Date: | 2012-07-26 19:19:25 |
| Message-ID: | 5011983D.20503@pukkasoft.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | sfpug |
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?
Brian
PS - I do have logging enabled over 200ms and auto_explain enabled over
500ms; not sure if that would factor in here?
| 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: | 50119EC8.3080105@pinpointresearch.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| 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.
Cheers,
Steve