From: | "Jaime Casanova" <jcasanov(at)systemguards(dot)com(dot)ec> |
---|---|
To: | "Robert Haas" <robertmhaas(at)gmail(dot)com> |
Cc: | "psql performance list" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: measure database contention |
Date: | 2008-12-17 15:38:29 |
Message-ID: | 3073cc9b0812170738v69e43d89r8d6dae87453b4993@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Dec 17, 2008 at 9:18 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Dec 16, 2008 at 2:32 PM, Jaime Casanova
> <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
>> we have a some bad queries (developers are working on that), some of
>> them run in 17 secs and that is the average but when analyzing logs i
>> found that from time to time some of them took upto 3 mins (the same
>> query that normally runs in 17secs).
>>
>> so my question is: how could i look for contention problems?
>
> Is it the exact same query?
is the exact query... i think it will be removed later today because
is a bad query anyway... but my fear is that something like happens
even with good ones...
maybe chekpoints could be the problem?
i have 8.3.5 and condigured checkpoint_timeout in 15 minutes,
chekpoint_segments 6 and checkpoint_completion_target to 0.5
i'm putting log_checkpoints to on, but should be good if there is way
to analyze them better than looking through the log
> Sometimes you might find that the query
> plan changes depending on the particular values you have in there; it
> is worth running "EXPLAIN ANALYZE" to look for such cases.
>
don't think that could happen in this query, because there is no way
it will choose something better than seqscan
> You might also want to look at pg_locks.
>
Only Shared ones...
PS: more info about my system (sorry for don't giving it in the first post)
2 PROCESSORS Xeon(R) CPU E5430 @ 2.66GHz with 4 cores each
18Gb in Ram (4gb in shared_buffers, 4mb in work_mem)
the db size is 2gb (reported by pg_database_size)
max. concurrent connections seen until now: 256
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2008-12-17 16:56:56 | Re: measure database contention |
Previous Message | Jaime Casanova | 2008-12-17 15:22:04 | Re: measure database contention |