pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery time
8+ messages / 5 participants
[nested] [flat]
* Query time
@ 2006-03-10 10:05 Ruben Rubio Rey <ruben@rentalia.com>
2006-03-10 10:48 ` Re: Query time Michael Fuhr <mike@fuhr.org>
2006-03-10 13:41 ` Re: Query time Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 8+ messages in thread
From: Ruben Rubio Rey @ 2006-03-10 10:05 UTC (permalink / raw)
To: pgsql-performance
Hi,
I think im specting problems with a 7.4.8 postgres database.
Sometimes some big query takes between 5 to 15 seconds. It happens
sometimes all the day it does not depend if database is busy.
I have measured that sentence in 15 - 70 ms in normal circunstances.
Why sometimes its takes too much time?
How can I fix it?
Is a postgres version problem, database problem or query problem?
Any ideas will be apreciatted.
Ruben Rubio
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query time
2006-03-10 10:05 Query time Ruben Rubio Rey <ruben@rentalia.com>
@ 2006-03-10 10:48 ` Michael Fuhr <mike@fuhr.org>
2006-03-10 11:52 ` Re: Query time Ruben Rubio Rey <ruben@rentalia.com>
1 sibling, 1 reply; 8+ messages in thread
From: Michael Fuhr @ 2006-03-10 10:48 UTC (permalink / raw)
To: Ruben Rubio Rey <ruben@rentalia.com>; +Cc: pgsql-performance
On Fri, Mar 10, 2006 at 11:05:57AM +0100, Ruben Rubio Rey wrote:
> Sometimes some big query takes between 5 to 15 seconds. It happens
> sometimes all the day it does not depend if database is busy.
>
> I have measured that sentence in 15 - 70 ms in normal circunstances.
Is it the *exact* same query, including the values you're querying
for? The same query with different values can run with different
plans depending on row count estimates. It might be useful to see
the query string and the EXPLAIN ANALYZE output for a fast query
and a slow one.
How many tables are you querying? Might you be hitting geqo_threshold
(default 12)? If so then the following thread might be helpful:
http://archives.postgresql.org/pgsql-performance/2006-01/msg00132.php
--
Michael Fuhr
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query time
2006-03-10 10:05 Query time Ruben Rubio Rey <ruben@rentalia.com>
2006-03-10 10:48 ` Re: Query time Michael Fuhr <mike@fuhr.org>
@ 2006-03-10 11:52 ` Ruben Rubio Rey <ruben@rentalia.com>
0 siblings, 0 replies; 8+ messages in thread
From: Ruben Rubio Rey @ 2006-03-10 11:52 UTC (permalink / raw)
To: pgsql-performance
Michael Fuhr wrote:
>On Fri, Mar 10, 2006 at 11:05:57AM +0100, Ruben Rubio Rey wrote:
>
>
>>Sometimes some big query takes between 5 to 15 seconds. It happens
>>sometimes all the day it does not depend if database is busy.
>>
>>I have measured that sentence in 15 - 70 ms in normal circunstances.
>>
>>
>
>Is it the *exact* same query, including the values you're querying
>for? The same query with different values can run with different
>plans depending on row count estimates. It might be useful to see
>the query string and the EXPLAIN ANALYZE output for a fast query
>and a slow one.
>
>How many tables are you querying? Might you be hitting geqo_threshold
>(default 12)? If so then the following thread might be helpful:
>
>http://archives.postgresql.org/pgsql-performance/2006-01/msg00132.php
>
>
>
The querys that are failing are very similar. But, when I see the
warning in logs files, I take that query and try it, and takes a few
miliseconds (as spected). That query one table on FROM and 2 or 3 on
WHERE clause.
Rigth now Im using the default server configuration for geqo_threshold,
but i'll ckeck the link that u provide me.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query time
2006-03-10 10:05 Query time Ruben Rubio Rey <ruben@rentalia.com>
@ 2006-03-10 13:41 ` Richard Huxton <dev@archonet.com>
2006-03-13 08:02 ` Re: Query time Ruben Rubio Rey <ruben@rentalia.com>
1 sibling, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2006-03-10 13:41 UTC (permalink / raw)
To: Ruben Rubio Rey <ruben@rentalia.com>; +Cc: pgsql-performance
Ruben Rubio Rey wrote:
> Hi,
>
> I think im specting problems with a 7.4.8 postgres database.
>
> Sometimes some big query takes between 5 to 15 seconds. It happens
> sometimes all the day it does not depend if database is busy.
>
> I have measured that sentence in 15 - 70 ms in normal circunstances.
>
> Why sometimes its takes too much time?
> How can I fix it?
> Is a postgres version problem, database problem or query problem?
Information, Ruben - we can't do anything without information.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query time
2006-03-10 10:05 Query time Ruben Rubio Rey <ruben@rentalia.com>
2006-03-10 13:41 ` Re: Query time Richard Huxton <dev@archonet.com>
@ 2006-03-13 08:02 ` Ruben Rubio Rey <ruben@rentalia.com>
2006-03-13 08:16 ` Re: Query time PFC <lists@peufeu.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ruben Rubio Rey @ 2006-03-13 08:02 UTC (permalink / raw)
To: pgsql-performance
Richard Huxton wrote:
> Ruben Rubio Rey wrote:
>
>> Hi,
>>
>> I think im specting problems with a 7.4.8 postgres database.
>>
>> Sometimes some big query takes between 5 to 15 seconds. It happens
>> sometimes all the day it does not depend if database is busy.
>>
>> I have measured that sentence in 15 - 70 ms in normal circunstances.
>>
>> Why sometimes its takes too much time?
>> How can I fix it?
>> Is a postgres version problem, database problem or query problem?
>
>
> Information, Ruben - we can't do anything without information.
>
What usefull information could I provide?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query time
2006-03-10 10:05 Query time Ruben Rubio Rey <ruben@rentalia.com>
2006-03-10 13:41 ` Re: Query time Richard Huxton <dev@archonet.com>
2006-03-13 08:02 ` Re: Query time Ruben Rubio Rey <ruben@rentalia.com>
@ 2006-03-13 08:16 ` PFC <lists@peufeu.com>
0 siblings, 0 replies; 8+ messages in thread
From: PFC @ 2006-03-13 08:16 UTC (permalink / raw)
To: pgsql-performance
>> Information, Ruben - we can't do anything without information.
>>
> What usefull information could I provide?
Offending queries, EXPLAIN ANALYZE, tables description, condiguration
parameters, hardware, intended use...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query time
@ 2006-03-10 10:29 Ruben Rubio Rey <ruben@rentalia.com>
2006-03-10 15:26 ` Re: Query time Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ruben Rubio Rey @ 2006-03-10 10:29 UTC (permalink / raw)
To: pgsql-performance
There is not possibility to use another database. It's the best option I
have seen. We have been working in postgres in last 3 years, and this is
the first problem I have seen. (The database is working in a large
website, 6.000 visits per day in a dedicated server)
Any other idea?
Chethana, Rao (IE10) wrote:
>USUALLY POSTGRES DATABASE TAKES MORE TIME, COMPARED TO OTHER DATABASES.
>HOWEVER U CAN FINETUNE THE PERFORMANCE OF POSTGRESQL.
>IF U HAVE AN OPTION GO FOR SQLITE, MYSQL OR FIREBIRD.
>
>-----Original Message-----
>From: pgsql-performance-owner@postgresql.org
>[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Ruben Rubio
>Rey
>Sent: Friday, March 10, 2006 2:06 AM
>To: pgsql-performance@postgresql.org
>Subject: [PERFORM] Query time
>
>Hi,
>
>I think im specting problems with a 7.4.8 postgres database.
>
>Sometimes some big query takes between 5 to 15 seconds. It happens
>sometimes all the day it does not depend if database is busy.
>
>I have measured that sentence in 15 - 70 ms in normal circunstances.
>
>Why sometimes its takes too much time?
>How can I fix it?
>Is a postgres version problem, database problem or query problem?
>
>Any ideas will be apreciatted.
>
>Ruben Rubio
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query time
2006-03-10 10:29 Re: Query time Ruben Rubio Rey <ruben@rentalia.com>
@ 2006-03-10 15:26 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-03-10 15:26 UTC (permalink / raw)
To: Ruben Rubio Rey <ruben@rentalia.com>; +Cc: pgsql-performance
On Fri, Mar 10, 2006 at 11:29:53AM +0100, Ruben Rubio Rey wrote:
> There is not possibility to use another database. It's the best option I
> have seen. We have been working in postgres in last 3 years, and this is
> the first problem I have seen. (The database is working in a large
> website, 6.000 visits per day in a dedicated server)
>
> Any other idea?
>
>
> Chethana, Rao (IE10) wrote:
>
> >USUALLY POSTGRES DATABASE TAKES MORE TIME, COMPARED TO OTHER DATABASES.
> >HOWEVER U CAN FINETUNE THE PERFORMANCE OF POSTGRESQL.
> >IF U HAVE AN OPTION GO FOR SQLITE, MYSQL OR FIREBIRD.
If I were you I wouldn't believe any performance recommendations from
someone who can't find their caps-lock key or spell "you".
The fact is, on any meaningful benchmark current versions of PostgreSQL
are on par with other databases. Any benchmark that shows PostgreSQL to
be 'slow' is almost certain to be very old and/or does a very poor job
of reflecting how client-server databases are normally used. The one
caveat is that PostgreSQL is often overkill for single user embedded
database type apps.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-03-13 08:16 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-10 10:05 Query time Ruben Rubio Rey <ruben@rentalia.com>
2006-03-10 10:48 ` Michael Fuhr <mike@fuhr.org>
2006-03-10 11:52 ` Ruben Rubio Rey <ruben@rentalia.com>
2006-03-10 13:41 ` Richard Huxton <dev@archonet.com>
2006-03-13 08:02 ` Ruben Rubio Rey <ruben@rentalia.com>
2006-03-13 08:16 ` PFC <lists@peufeu.com>
2006-03-10 10:29 Re: Query time Ruben Rubio Rey <ruben@rentalia.com>
2006-03-10 15:26 ` Jim C. Nasby <jnasby@pervasive.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