Re: REINDEX DATABASE - Mailing list pgsql-sql

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