pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (6+ messages)  latest in thread

Message-ID: <200310131937.14785.shridhar_daithankar@persistent.co.in>
Permalink:  ../200310131937.14785.shridhar_daithankar@persistent.co.in/
Also on:    postgresql.org/message-id/200310131937.14785.shridhar_daithankar@persistent.co.in

 · 

reply

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