Обсуждение: Disk space problem...

Hi All,

Has anyone else run into a situation where the vacuum procedures didn't
really work?

I have maybe 500,000 rows in a database.  When using pg_dumpall, I get a 92MB
sql file.  This database was managing to fill up a 13GB partition, even
though I had a 'vacuumdb -a -z' running every night.

So, I deleted the old data and restored into an empty database freshly
created with initdb, the data only took up 256MB.

By the next morning, disk usage had grown to 650MB.  Vacuuming only seemed to
exacerbate the problem, as after vacuuming it's now 850 MB.  92 of that is in
pg_xlog, which I know will fluctuate, and am assiming will eventually go
away.  However, I just lost another 100 MB!

Several of my tables are being emptied and totally replaced (statistics about
how my website is running, that sort of thing).  Is it better to drop the
table, and rebuild it in this case?

Any other suggestions other than to pg_dumpall, initdb, and restore on a
regular basis?

--
Ken Corey, CTO    Atomic Interactive, Ltd.   ken.corey@atomic-interactive.com

Ken Corey <ken.corey@atomic-interactive.com> writes:
> Several of my tables are being emptied and totally replaced (statistics about
> how my website is running, that sort of thing).  Is it better to drop the
> table, and rebuild it in this case?

Consider TRUNCATE.

I suspect if you look at the stats (see pg_class.relpages) you'll find
that the space VACUUM fails to reclaim is in indexes.  Possibly you've
gone overboard on adding indexes to your database; are there any that
you don't need?

            regards, tom lane

On Saturday 01 December 2001  5:20 pm, Tom Lane wrote:
> Consider TRUNCATE.
>
> I suspect if you look at the stats (see pg_class.relpages) you'll find
> that the space VACUUM fails to reclaim is in indexes.  Possibly you've
> gone overboard on adding indexes to your database; are there any that
> you don't need?

Amazing!  Once again, you've hit it on the head.

Thanks for pointing out pg_class...being able to see the sizes of things in
the database allowed me to directly see the changes in size as I played.

The indexes on the table which had all its rows removed and rebuilt was
indeed the root of the problems I was seeing.

I changed my statistics update script to drop all indexes, truncate the
table, rebuild the table, and then rebuild the indexes, and have seen
dramatically reduced size, and dramatically increased speed. (And my vacuum's
even run faster!)

I'm back down to 380MB to hold my data online, too.

Thanks, Tom!

--
Ken Corey, CTO    Atomic Interactive, Ltd.   ken.corey@atomic-interactive.com

On Sat, 1 Dec 2001, Tom Lane wrote:

> Ken Corey <ken.corey@atomic-interactive.com> writes:
> > Several of my tables are being emptied and totally replaced (statistics about
> > how my website is running, that sort of thing).  Is it better to drop the
> > table, and rebuild it in this case?
>
> Consider TRUNCATE.

Does truncate releases all the space from the indexes?

> I suspect if you look at the stats (see pg_class.relpages) you'll find
> that the space VACUUM fails to reclaim is in indexes.

Does it mean that if records are deleted an one does vacuum, that index
space may not be released?