From: Robert Treat <xzilla@users.sourceforge.net>
To: Clarence Gardner <clarence@silcom.com>
Cc: Oleg Lebedev <oleg.lebedev@waterford.org>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: slow query
Date: 24 Feb 2003 13:30:34 -0500
Message-ID: <1046111434.1014.165.camel@camel> (raw)
In-Reply-To: <Pine.LNX.4.44.0302240925360.25665-100000@liberty.sba2.netlojix.net>
References: <Pine.LNX.4.44.0302240925360.25665-100000@liberty.sba2.netlojix.net>
On Mon, 2003-02-24 at 12:27, Clarence Gardner wrote:
> On 24 Feb 2003, Robert Treat wrote:
>
> >
> > If your seeing wildly dramatic improvments from vacuum full, you might
> > want to look into running regular vacuums more often (especially for
> > high turnover tables), increase your max_fsm_relations to 1000, and
> > increasing your max_fsm_pages.
>
> I don't know about the settings you mention, but a frequent vacuum
> does not at all obviate a vacuum full. My database is vacuumed every
> night, but a while ago I found that a vacuum full changed a simple
> single-table query from well over 30 seconds to one or two. We now
> do a vacuum full every night.
>
Actually if you are vacuuming frequently enough, it can (and should*)
obviate a vacuum full. Be aware that frequently enough might mean really
frequent, for instance I have several tables in my database that update
every row within a 15 minute timeframe, so I run a "lazy" vacuum on
these tables every 10 minutes. This allows postgresql to reuse the space
for these tables almost continuously so I never have to vacuum full
them.
(* this assumes your max_fsm_pages/relations settings are set correctly)
Robert Treat
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: xzilla@users.sourceforge.net, clarence@silcom.com, oleg.lebedev@waterford.org, josh@agliodbs.com
Subject: Re: slow query
In-Reply-To: <1046111434.1014.165.camel@camel>
* 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