From: Richard Broersma Jr <rabroersma@yahoo.com>
To: Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>
To: pgsql-sql@postgresql.org
Subject: Re: Query response time
Date: Tue, 8 Aug 2006 05:53:36 -0700 (PDT)
Message-ID: <20060808125336.44936.qmail@web31805.mail.mud.yahoo.com> (raw)
In-Reply-To: <746457F7E51F3B4BB53A8447D7204B78E456@MVGMAIL.mvg.local>
> I am using PostgresSQL 7.4 and having some serious performance issues.
> Trawling through the archives and previous posts the only visible advice
> I could see was either by running vacuum or setting the fsynch flag to
> false.
>
> I am using tables that only contain approx 2GB of data. However
> performing a number of simple conditional select statements takes a
> great deal of time. Putting limits on the data obviously reduces the
> time, but there is still a delay. (Note: on one particular query I set
> the limit to 538 and the query returns in under 2mins if the limit
> becomes 539 the query loops indefinitely!)
> From previous experience I know these delays are longer than both
> Informix and MySql. In some instances it takes so long I end up having
> to kill the query.
>
> The install was performed by yum onto a RAID server using Centos. I am
> sure there is something fundamentally wrong for I can't believe that
> postgres would have the reputation it has based on the statistics I'm
> getting. Does anyone have any advice?
>
> The data I am using was imported from an Informix system as part of a
> migration strategy.
> I know this is long shot but I hope someone can shed some light.
Are the Update/Insert queries slow or is it the select queries that are taking awhile?
For select queries, an explain analyze of the offending query would be helpful. Also, in addition
to vacuuming you may want to reindex you db in order to clean all of the dead tuples from your
indexs.
For heavy insert/update queries check your postgres logs to see if any messages suggest increasing
your check-point-segments.
If this is the case, try increasing you check_point_segments and try moving your pg_xlog to a
different spindle.
Regards,
Richard Broersma Jr.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com, jonathan.sinclair@molevalleyfarmers.com
Subject: Re: Query response time
In-Reply-To: <20060808125336.44936.qmail@web31805.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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