Re: Maintenance question / DB size anomaly...

Kurt Overberg wrote:
>
> In my investigation of this anomaly, I noticed that the data/ dir on db1
> (the master) is around 60 Gigs. The data directory on the slaves is
> around 25Gb. After about 3 months of head scratching, someone on the
> irc channel suggested that it may be due to index bloat. Although,
> doing some research, it would seem that those problems were resolved in
> 7.4(ish), and it wouldn't account for one database being 2.5x bigger.
> Another unknown is Slony overhead (both in size and vacuum times).

Check the oid2name/dbsize utilities in the contrib RPM for 8.0.x
http://www.postgresql.org/docs/8.0/static/diskusage.html
Shouldn't be too hard to find out where the disk space is going.

Oh and 8.0.13 is the latest release of 8.0 series, so you'll want to use
your maintenance window to upgrade too. Lots of good bugfixes there.

> The ONLY thing I can think of is that I DROPped a large number of tables
> from db1 a few months ago (they weren't getting replicated). This is on
> the order of 1700+ fairly largeish (50,000+ row) tables. I do not
> remember doing a vacuum full after dropping them, so perhaps that's my
> problem. I'm planning on doing some maintenance this weekend, during
> which I will take the whole system down, then on db1, run a VACUUM FULL
> ANALYZE on the whole database, then a REINDEX on my very large tables.
> I may drop and recreate the indexes on my big tables, as I hear that may
> be faster than a REINDEX. I will probably run a VACUUM FULL ANALYZE on
> the slaves as well.

You'll probably find CLUSTER to be quicker than VACUUM FULL, although
you need enough disk-space free for temporary copies of the
table/indexes concerned.

Dropping and recreating indexes should prove much faster than VACUUMING
with them. Shouldn't matter for CLUSTER afaict.

> Thoughts? Suggestions? Anyone think this will actually help my problem
> of size and vacuum times? Do I need to take Slony down while I do
> this? Will the VACUUM FULL table locking interfere with Slony?

Well, I'd take the opportunity to uninstall/reinstall slony just to
check my scripts/procedures are working.

--
Richard Huxton
Archonet Ltd