pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Vivek Khera <khera@kcilink.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance, vacuum and reclaiming space, fsm
Date: Mon, 13 Oct 2003 11:52:44 -0400
Message-ID: <x73cdxp22b.fsf@yertle.int.kciLink.com> (raw)
References: <p05100301bbb01a5b6576@[192.168.10.43]>
	<3F8A6F82.6030300@persistent.co.in>
	<p05100303bbb05d6f27dd@[192.168.10.43]>
	<200310131937.14785.shridhar_daithankar@persistent.co.in>

>>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:

SD> If you have 150MB type of data as you said last time, you could
SD> take a pg_dump of database, drop the database and recreate it. By
SD> all chances it will take less time than compacting a database from
SD> 2GB to 150MB.

That's it?  That's not so big of a disk footprint.

SD> Drop the indexes and recreate them. While creating the index, all
SD> the updates will be blocked anyways.

Be *very careful* doing this, especially with UNIQUE indexes on a live
system!  My recommendation is to get a list of all indexes on your
system with \di in psql, then running "reindex index XXXX" per index.
Be sure to bump sort_mem beforehand.  Here's a script I ran over the
weekend (during early morning low-usage time) on my system:

SET sort_mem = 131072;
SELECT NOW(); SELECT relname,relpages FROM pg_class WHERE relname LIKE 'user_list%' ORDER BY relname;
SELECT NOW(); REINDEX INDEX  user_list_pkey            ;
SELECT NOW(); REINDEX INDEX  user_list_XXX        ;
SELECT NOW(); REINDEX INDEX  user_list_YYY     ;
SELECT NOW(); SELECT relname,relpages FROM pg_class WHERE relname LIKE 'user_list%' ORDER BY relname;

The relpages used by the latter two indexes shrunk dramatically:

user_list_XXX    |   109655
user_list_YYY    |    69837

to

user_list_XXX    |    57032
user_list_YYY    |    30911

and disk usage went down quite a bit as well.  Unfortunately, the pkey
reindex failed due to a deadlock being detected, but the XXX index is
most popular...  This is my "hottest" table, so I reindex it about
once a month.  My other "hot" table takes 45 minutes per index to
redo, so I try to avoid that until I *really* have to do it (about 6
months).  I don't think you'll need a nightly reindex.

Of course, regular vacuums throughout the day on the busy talbes help
keep it from getting too fragmented.


-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/



view thread (6+ messages)

Message-ID: <x73cdxp22b.fsf@yertle.int.kciLink.com>
Permalink:  ../x73cdxp22b.fsf@yertle.int.kciLink.com/
Also on:    postgresql.org/message-id/x73cdxp22b.fsf@yertle.int.kciLink.com

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: khera@kcilink.com
  Subject: Re: Performance, vacuum and reclaiming space, fsm
  In-Reply-To: <x73cdxp22b.fsf@yertle.int.kciLink.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