From: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: Seum-Lim Gan <slgan@lucent.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance, vacuum and reclaiming space, fsm
Date: Mon, 13 Oct 2003 19:37:14 +0530
Message-ID: <200310131937.14785.shridhar_daithankar@persistent.co.in> (raw)
In-Reply-To: <p05100303bbb05d6f27dd@[192.168.10.43]>
References: <p05100301bbb01a5b6576@[192.168.10.43]>
<3F8A6F82.6030300@persistent.co.in>
<p05100303bbb05d6f27dd@[192.168.10.43]>
On Monday 13 October 2003 19:22, Seum-Lim Gan wrote:
> I am not sure I can do the full vacuum.
> If my system is doing updates in realtime and needs to be
> ok 24 hours and 7 days a week non-stop, once I do
> vacuum full, even on that table, that table will
> get locked out and any quiery or updates that come in
> will timeout.
If you have 150MB type of data as you said last time, you could take a pg_dump
of database, drop the database and recreate it. By all chances it will take
less time than compacting a database from 2GB to 150MB.
It does involve downtime but can't help it. Thats closet you can get.
> Any suggestion on what to do besides shutting down to
> do full vacuum ?
Drop the indexes and recreate them. While creating the index, all the updates
will be blocked anyways.
> Peter Child also mentions there is indexing bugs.
> Is this fixed in 7.3.4 ? I did notice after the database
No. It is fixed in 7.4 and 7.4 is in beta still..
> grew in disk usage, its performance greatly decreases !
Obviously that is due to unnecessary IO it has to do.
Thing is your database has reached a state that is really bad for it's
operation. I strongly encourage you to recreate the database from backup,
from scratch, tune postgresql properly and run autovacuum daemon from 7.4
source dir. Besides that you would need to reindex nightly or per 5-6 hour
depending upon rate of insertion.
Check http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html for
performance tuning starter tips..
HTH
Shridhar
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: shridhar_daithankar@persistent.co.in, slgan@lucent.com
Subject: Re: Performance, vacuum and reclaiming space, fsm
In-Reply-To: <200310131937.14785.shridhar_daithankar@persistent.co.in>
References: <p05100301bbb01a5b6576@[192.168.10.43]> <3F8A6F82.6030300@persistent.co.in> <p05100303bbb05d6f27dd@[192.168.10.43]> <200310131937.14785.shridhar_daithankar@persistent.co.in>
* 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