From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: REINDEX DATABASE
Date: Wed, 27 Jul 2005 09:08:30 +0300 (EEST)
Message-ID: <Pine.LNX.4.44.0507270856070.32187-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <60pst51ocd.fsf@dba2.int.libertyrms.com>
O Chris Browne Ýãñáøå óôéò Jul 26, 2005 :
> achill@matrix.gatewaynet.com (Achilleus Mantzios) writes:
> > O Christopher Browne Ýãñáøå óôéò Jul 26, 2005 :
> >
> >> > Hello
> >> >
> >> > Would you like to advice to use REINDEX DATABASE on regular basis ?
> >> >
> >> > if (yes)
> >> > how it should be connected with VACUUM FULL ANALYZE which is run
> >> > regularly ? (reindex before vacuum or vacuum before reindex?)
> >> >
> >> > else
> >> > haw to determine _when_ to run REINDEX ?
> >>
> >> If you are doing ordinary VACUUM ANALYZE frequently enough, it
> >> shouldn't be necessary to either VACUUM FULL or REINDEX.
> >>
> >> Back in the 7.2 days, there were sorts of update patterns that would
> >> mandate reindexing every so often, as you could get cases where index
> >> pages would be very sparsely populated. That was alleviated in
> >> version 7.3, I believe, and was clearly evident in 7.4.
> >>
> >> You know you need to REINDEX if analysis of an index shows that it is
> >> sparsely populated. This generally shows up if you do an analyze on
> >> the table and find an index has more pages than tuples.
> >>
> >> But if you run VACUUM reasonably frequently, this shouldn't be
> >> necessary...
> >
> > I vacuum analyze every hour, however after reindexdb
> > my (currently) 2.47 GB db is reduced to 2.37 GB,
> > thus helping both db-wise and freebsd_cache-wise boost performance.
> >
> > All the above in 7.4.6.
> >
> > I am not arguing that i have done detailed analysis of the
> > situation, i'm just saying that i have witnessed a performance gain after
> > running contrib/reindexdb every month (or so).
>
> That doesn't strike me as being a material improvement, and it comes
> at a pretty high cost.
The numbers above are just the numbers reported after doing
a live reindex on the spot after deciding to answer to this email.
In the past (when the db was smaller) and for the same version,
i have come across much "striking" numbers,
and much more striking boost gain.
(e.g. a 2+ GB db directory turning to a ~1.5 GB dir).
BTW whats the limit of kern.ipc.shmmax for 32-bit intel?
~ 2^31 = ~ 2 GB.
In those cases i noticed severe boost gain.
(And of course the 1 hour vacuum analyze schedule was always there)
>
> You can get a savings of about 4% of the space, but at the cost of
> taking an appreciable outage during which the database is not usable.
1st not all database is unusable, during the whole reindexdb run,
2nd outage outside office hours is acceptable for those who apply.
>
> I wouldn't expect the 4% savings in space to lead to a particularly
> measurable improvement in performance, certainly not one worth the
> outage.
>
I am just saying that the common saying "reindex is not needed for 7.4+"
maybe is not true in all circumstances.
--
-Achilleus
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-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, cbbrowne@acm.org
Subject: Re: REINDEX DATABASE
In-Reply-To: <Pine.LNX.4.44.0507270856070.32187-100000@matrix.gatewaynet.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