From: Matthew T. O'Connor <matthew@zeut.net>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Is Vacuum/analyze destroying my performance?
Date: Tue, 05 Dec 2006 00:11:58 -0500
Message-ID: <4574FF9E.3000600@zeut.net> (raw)
In-Reply-To: <el1kmi$t75$1@news.hub.org>
References: <el0av7$1a6l$1@news.hub.org>
<el0ctq$1g1i$1@news.hub.org>
<45743240.7050302@zeut.net>
<el1kmi$t75$1@news.hub.org>
Carlo Stonebanks wrote:
>> Just a wild guess, but the performance problem sounds like maybe as your
>> data changes, eventually the planner moves some query from an index scan
>> to a sequential scan, do you have any details on what queries are taking
>> so long when things are running slow? You can turn on the GUC var
>> "log_min_duration_statement" and see what queries are slow and then
>> manually check them with an explain analyze, that might help.
>>
> This is pretty well what I think is happening - I expect all queries to
> eventually move from seq scans to index scans. I actually have a SQL logging
> opion built into the import app.
>
> I just can't figure out how the planner can be so wrong. We are running a 4
> CPU server (two dual core 3.2 GHz Xeons) with 4GB RAM and Windows Server
> 2003 x64 and a PERC RAID subsystem (I don't know the RAID type). I know that
> the metrics for the planner can be changed - is the default config for
> postgesql not suitable for our setup? For this server, we would like to be
> optimised for high speed over a few connections, rather than the classic
> balanced speed over many connections.
If it is the planner choosing a very bad plan, then I don't think your
hardware has anything to do with it. And, we can't diagnose why the
planner is doing what it's doing without a lot more detail. I suggest
you do something to figure out what queries are taking so long, then
send us an explain analyze, that might shine some light on the subject.
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-performance@postgresql.org
Cc: matthew@zeut.net, stonec.register@sympatico.ca
Subject: Re: Is Vacuum/analyze destroying my performance?
In-Reply-To: <4574FF9E.3000600@zeut.net>
* 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