pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedmeasure database contention
10+ messages / 5 participants
[nested] [flat]
* measure database contention
@ 2008-12-16 19:32 Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 07:34 ` Re: measure database contention Albe Laurenz <laurenz.albe@wien.gv.at>
2008-12-17 14:18 ` Re: measure database contention Robert Haas <robertmhaas@gmail.com>
2008-12-17 14:49 ` Re: measure database contention Merlin Moncure <mmoncure@gmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: Jaime Casanova @ 2008-12-16 19:32 UTC (permalink / raw)
To: pgsql-performance
Hi,
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?
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: measure database contention
2008-12-16 19:32 measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2008-12-17 07:34 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2008-12-17 15:22 ` Re: measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2 siblings, 1 reply; 10+ messages in thread
From: Albe Laurenz @ 2008-12-17 07:34 UTC (permalink / raw)
To: Jaime Casanova *EXTERN* <jcasanov@systemguards.com.ec>; pgsql-performance
Jaime Casanova 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?
A good first step is to identify the bottleneck.
Frequently, but not always, this is I/O.
Do you see a lot of I/O wait? Are the disks busy?
I don't know anything about your system, but I once experienced a
similar problem with a 2.6 Linux system where things improved considerably
after changing the I/O-scheduler to "elevator=deadline".
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: measure database contention
2008-12-16 19:32 measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 07:34 ` Re: measure database contention Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2008-12-17 15:22 ` Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 0 replies; 10+ messages in thread
From: Jaime Casanova @ 2008-12-17 15:22 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-performance
On Wed, Dec 17, 2008 at 2:34 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Jaime Casanova 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?
>
> A good first step is to identify the bottleneck.
>
> Frequently, but not always, this is I/O.
> Do you see a lot of I/O wait? Are the disks busy?
>
the disks are an RAID 10 with 4 sata disks of 15000rpm
and nop iostat reports avg of 0.12 iowait
> I don't know anything about your system, but I once experienced a
> similar problem with a 2.6 Linux system where things improved considerably
> after changing the I/O-scheduler to "elevator=deadline".
>
i don't understand I/O-schedulers at all... anyone knows what is the
recommended for postgres?
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: measure database contention
2008-12-16 19:32 measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2008-12-17 14:18 ` Robert Haas <robertmhaas@gmail.com>
2008-12-17 15:38 ` Re: measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2 siblings, 1 reply; 10+ messages in thread
From: Robert Haas @ 2008-12-17 14:18 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: pgsql-performance
On Tue, Dec 16, 2008 at 2:32 PM, Jaime Casanova
<jcasanov@systemguards.com.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? 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.
You might also want to look at pg_locks.
...Robert
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: measure database contention
2008-12-16 19:32 measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 14:18 ` Re: measure database contention Robert Haas <robertmhaas@gmail.com>
@ 2008-12-17 15:38 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 16:56 ` Re: measure database contention Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jaime Casanova @ 2008-12-17 15:38 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-performance
On Wed, Dec 17, 2008 at 9:18 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Dec 16, 2008 at 2:32 PM, Jaime Casanova
> <jcasanov@systemguards.com.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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: measure database contention
2008-12-16 19:32 measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 14:18 ` Re: measure database contention Robert Haas <robertmhaas@gmail.com>
2008-12-17 15:38 ` Re: measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2008-12-17 16:56 ` Robert Haas <robertmhaas@gmail.com>
2008-12-17 18:19 ` Re: measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 1 reply; 10+ messages in thread
From: Robert Haas @ 2008-12-17 16:56 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: pgsql-performance
> 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
Well, it might help if you could provide the query, and the EXPLAIN output.
Unless the query is updating data (rather than just retrieving it),
checkpoints shouldn't be involved (I think).
...Robert
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: measure database contention
2008-12-16 19:32 measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 14:18 ` Re: measure database contention Robert Haas <robertmhaas@gmail.com>
2008-12-17 15:38 ` Re: measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 16:56 ` Re: measure database contention Robert Haas <robertmhaas@gmail.com>
@ 2008-12-17 18:19 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 18:57 ` Re: measure database contention Scott Marlowe <scott.marlowe@gmail.com>
2008-12-17 21:14 ` Re: measure database contention Robert Haas <robertmhaas@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Jaime Casanova @ 2008-12-17 18:19 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-performance
On Wed, Dec 17, 2008 at 11:56 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> 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
>
> Well, it might help if you could provide the query, and the EXPLAIN output.
>
ok... remember i say it's a bad query ;)
actually, seems there's a suitable index for that query (i guess it is
using it because of the order by)
mic=# explain analyze
mic-# SELECT * FROM tgen_persona ORDER BY empresa_id, persona_id ASC;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using pk_tgen_persona on tgen_persona (cost=0.00..8534.09
rows=86547 width=884) (actual time=0.096..129.980 rows=86596 loops=1)
Total runtime: 175.952 ms
(2 rows)
as you see, explain analyze says it will execute in 175.952ms and
because of network transfer of data executing this from pgadmin in
another machine it runs for 17s... but from time to time pgFouine is
shown upto 345.11 sec
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: measure database contention
2008-12-16 19:32 measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 14:18 ` Re: measure database contention Robert Haas <robertmhaas@gmail.com>
2008-12-17 15:38 ` Re: measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 16:56 ` Re: measure database contention Robert Haas <robertmhaas@gmail.com>
2008-12-17 18:19 ` Re: measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2008-12-17 18:57 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Scott Marlowe @ 2008-12-17 18:57 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Wed, Dec 17, 2008 at 11:19 AM, Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:
> On Wed, Dec 17, 2008 at 11:56 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> 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
>>
>> Well, it might help if you could provide the query, and the EXPLAIN output.
>>
>
> ok... remember i say it's a bad query ;)
> actually, seems there's a suitable index for that query (i guess it is
> using it because of the order by)
>
> mic=# explain analyze
> mic-# SELECT * FROM tgen_persona ORDER BY empresa_id, persona_id ASC;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using pk_tgen_persona on tgen_persona (cost=0.00..8534.09
> rows=86547 width=884) (actual time=0.096..129.980 rows=86596 loops=1)
> Total runtime: 175.952 ms
> (2 rows)
>
> as you see, explain analyze says it will execute in 175.952ms and
> because of network transfer of data executing this from pgadmin in
> another machine it runs for 17s... but from time to time pgFouine is
> shown upto 345.11 sec
I know it's a bad query but did you try clustering on that index?
Then a seq scan followed by a sort would likely be cheaper and faster.
85k rows aren't that many really.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: measure database contention
2008-12-16 19:32 measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 14:18 ` Re: measure database contention Robert Haas <robertmhaas@gmail.com>
2008-12-17 15:38 ` Re: measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 16:56 ` Re: measure database contention Robert Haas <robertmhaas@gmail.com>
2008-12-17 18:19 ` Re: measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2008-12-17 21:14 ` Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Robert Haas @ 2008-12-17 21:14 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: pgsql-performance
> as you see, explain analyze says it will execute in 175.952ms and
> because of network transfer of data executing this from pgadmin in
> another machine it runs for 17s... but from time to time pgFouine is
> shown upto 345.11 sec
Well, 86000 rows is not enough to give PostgreSQL a headache, even on
mediocre hardware. So I think that most likely culprit is the
application that is reading the data - pgadmin or pgFouine.
...Robert
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: measure database contention
2008-12-16 19:32 measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2008-12-17 14:49 ` Merlin Moncure <mmoncure@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Merlin Moncure @ 2008-12-17 14:49 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: pgsql-performance
On Tue, Dec 16, 2008 at 2:32 PM, Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:
> Hi,
>
> 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?
Sometimes queries can have fluctuating plans. For example this can
happen if you have sorts or hashes that are very near the allowed
limit set in work_mem. so you want to catch it both ways via explain
analyze.
merlin
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2008-12-17 21:14 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-16 19:32 measure database contention Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 07:34 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2008-12-17 15:22 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 14:18 ` Robert Haas <robertmhaas@gmail.com>
2008-12-17 15:38 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 16:56 ` Robert Haas <robertmhaas@gmail.com>
2008-12-17 18:19 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2008-12-17 18:57 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-17 21:14 ` Robert Haas <robertmhaas@gmail.com>
2008-12-17 14:49 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox