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/
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