Thread: delete/vacuum not freeing disk space

delete/vacuum not freeing disk space

From
Joshua Franklin
Date:
I've got a large database that's getting too big. I
checked with the users and deleted approximately 1/6th
of the records (we have archived backups) and then ran
a VACUUM, which took a really long time to complete
(several days). But, the disk space usage doesn't seem
to have changed. Do I need to VACUUM twice or
something? Should it be faster the second time?

Thanks.

__________________________________________________
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1

Re: delete/vacuum not freeing disk space

From
marc@oscar.eng.cv.net (Marc Spitzer)
Date:
In article <20011006165604.72133.qmail@web20005.mail.yahoo.com>,
Joshua Franklin wrote:
> I've got a large database that's getting too big. I
> checked with the users and deleted approximately 1/6th
> of the records (we have archived backups) and then ran
> a VACUUM, which took a really long time to complete
> (several days). But, the disk space usage doesn't seem
> to have changed. Do I need to VACUUM twice or
> something? Should it be faster the second time?
>
> Thanks.
>
> __________________________________________________
> Do You Yahoo!?
> NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
> http://geocities.yahoo.com/ps/info1
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

vacuum does not reclame space in indexes, if you use a command called
reindex to recreate the indexes it may reclame space.  It needs space
to write the new index's though.  Reindex should be run in sigle user
mode only, per the docs.

marc


Re: delete/vacuum not freeing disk space

From
Brian McCane
Date:
On Sun, 7 Oct 2001, Marc Spitzer wrote:

> In article <20011006165604.72133.qmail@web20005.mail.yahoo.com>,
> Joshua Franklin wrote:
> > I've got a large database that's getting too big. I
> > checked with the users and deleted approximately 1/6th
> > of the records (we have archived backups) and then ran
> > a VACUUM, which took a really long time to complete
> > (several days). But, the disk space usage doesn't seem
> > to have changed. Do I need to VACUUM twice or
> > something? Should it be faster the second time?
> >
> > Thanks.
> >
> > __________________________________________________
> > Do You Yahoo!?
> > NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
> > http://geocities.yahoo.com/ps/info1
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> vacuum does not reclame space in indexes, if you use a command called
> reindex to recreate the indexes it may reclame space.  It needs space
> to write the new index's though.  Reindex should be run in sigle user
> mode only, per the docs.
>
> marc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Being the perpetually lazy person that I am, I have written a PERL script
that takes care of indexes on a live system.  You tell the script the
database and table to reindex.  It then finds a list of indexes for the
table and creates exactly the same index, with a slightly modified name.
After the copy is made the old index is DROP'd, and the new one is
ALTER'd to the old name.  Unfortunately, this means that you must have
enough room for the new index plus the old at the same time, but I am
sure that most people have lots of spare room.  I seem to remember that
the last time I ran this, it didn't even stop my websites from using the
tables at the same time.  I assume this is because of the versioning
system used in PostgreSQL.

- brian

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"


Re: delete/vacuum not freeing disk space

From
Joshua Franklin
Date:
Sorry, I realize now that my original post was very
unclear. I read the FAQ and therefore know that just
running VACUUM will not show decreased disk usage.
However, I would expect that after deleting 1/6 of the
db and running vacuum, the db would quit growing on
the disk (except for maybe some logs). What I've been
seeing is that the db is growing on the disk at about
the same rate as before.

__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com

Re: delete/vacuum not freeing disk space

From
marc@oscar.eng.cv.net (Marc Spitzer)
Date:
In article <20011012165439.S71408-100000@fw.mccons.net>, Brian McCane wrote:
>
> Being the perpetually lazy person that I am, I have written a PERL script
> that takes care of indexes on a live system.  You tell the script the
> database and table to reindex.  It then finds a list of indexes for the
> table and creates exactly the same index, with a slightly modified name.
> After the copy is made the old index is DROP'd, and the new one is
> ALTER'd to the old name.  Unfortunately, this means that you must have
> enough room for the new index plus the old at the same time, but I am
> sure that most people have lots of spare room.  I seem to remember that
> the last time I ran this, it didn't even stop my websites from using the
> tables at the same time.  I assume this is because of the versioning
> system used in PostgreSQL.
>
> - brian
>
> Wm. Brian McCane                    | Life is full of doors that won't open
> Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
> Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
> Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

would you consider posting that script or emailing it to me?
It would realy come in handy at work.

Thanks

marc

Re: delete/vacuum not freeing disk space

From
Brian McCane
Date:
On Sun, 14 Oct 2001, Marc Spitzer wrote:

> In article <20011012165439.S71408-100000@fw.mccons.net>, Brian McCane wrote:
> >
> > Being the perpetually lazy person that I am, I have written a PERL script
> > that takes care of indexes on a live system.  You tell the script the
> > database and table to re-index.  It then finds a list of indexes for the
> > table and creates exactly the same index, with a slightly modified name.
> > After the copy is made the old index is DROP'd, and the new one is
> > ALTER'd to the old name.  Unfortunately, this means that you must have
> > enough room for the new index plus the old at the same time, but I am
> > sure that most people have lots of spare room.  I seem to remember that
> > the last time I ran this, it didn't even stop my web sites from using the
> > tables at the same time.  I assume this is because of the versioning
> > system used in PostgreSQL.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> would you consider posting that script or emailing it to me?
> It would realy come in handy at work.
>
> Thanks
>
> marc
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Short answer, sure no problem.

Long answer, not yet.

I am having a couple of inconsistencies with it, and I would prefer not to
release it to anyone else until I at least feel comfortable with my work.
No tool is often better than the wrong or a defective tool.  As soon as I
am sure it is not what caused a recent problem, I will make it available
either here or via FTP.

NOTE:  The problem was a duplicated key in a unique index on a BIG (at least
35Million+ records is big to me) table, which caused my script to fail
because it couldn't re-create the index.  I don't know how my script could
have caused it, but it was a table which I had previously run my script
on, so I am testing.  I did, however, write the script to "failsafe",
ie. failure to create the index just left the original index in place.

An interesting point was that the duplicated key was actually in the
table, but the existing unique index was happy as a clam.  I have often
encountered this with MySQL, but this is the first time with PostgreSQL.
I had to delete the record by OID, once I finally located it, then my
script worked fine.

- brian

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"


Re: delete/vacuum not freeing disk space

From
Tom Lane
Date:
Brian McCane <bmccane@mccons.net> writes:
> An interesting point was that the duplicated key was actually in the
> table, but the existing unique index was happy as a clam.

Hmm.  What datatype is the key, and what PG version are you running?

The only known cause of such problems at the moment is that if you
have LOCALE support compiled in, then the correct sort ordering of
textual datatypes depends on locale.  Change the locale, and presto
your index is out of order --- and therefore corrupt.  (The btree
algorithms do not cope at all well with out-of-order index data.
Failing to find entries that are there would be a very common result.)

Prior to 7.1 you could easily shoot yourself in the foot this way
by starting the postmaster with different locale environment variables
at different times.  As of 7.1, we save the locale seen at initdb time
and adopt that at every postmaster startup, so in theory this class of
problems is gone in 7.1.

If you've got an example that doesn't fit into this case then I'd
like to know about it, especially if you still have the broken index
available for examination ...

            regards, tom lane

Re: delete/vacuum not freeing disk space

From
Brian McCane
Date:
On Thu, 18 Oct 2001, Tom Lane wrote:

> Brian McCane <bmccane@mccons.net> writes:
> > An interesting point was that the duplicated key was actually in the
> > table, but the existing unique index was happy as a clam.
>
> Hmm.  What datatype is the key, and what PG version are you running?
>
> The only known cause of such problems at the moment is that if you
> have LOCALE support compiled in, then the correct sort ordering of
> textual datatypes depends on locale.  Change the locale, and presto
> your index is out of order --- and therefore corrupt.  (The btree
> algorithms do not cope at all well with out-of-order index data.
> Failing to find entries that are there would be a very common result.)
>
> Prior to 7.1 you could easily shoot yourself in the foot this way
> by starting the postmaster with different locale environment variables
> at different times.  As of 7.1, we save the locale seen at initdb time
> and adopt that at every postmaster startup, so in theory this class of
> problems is gone in 7.1.
>
> If you've got an example that doesn't fit into this case then I'd
> like to know about it, especially if you still have the broken index
> available for examination ...
>
>             regards, tom lane
>

Datatype was an int4, and I am running 7.1.3.  However, I have been slowly
banging this thing along since 7.0.?, so it might have gotten there at any
point.  Come to think of it though, I had to pg_dump to move up to 7.1, so
it must have happened since then.  I do a nightly 'vacuumdb -z -a', and
the error never showed up there.  I only speak locale "C" ;), and I don't
even build the database support for multi-byte.

Of course, since I use mirrored RAIDs (and a nightly offsite) for backups,
I no longer have an copy of the database that was in question.  Plus, it
was app 2.1GB without it's indexes, so I wouldn't want to mail it to you
anyway :).  My guess would be that it got screwed up by a system crash.  I
had a couple of those a few weeks ago when I had a runaway program take my
machine down (not really a crash, after about 30seconds your keypress
would appear).  I have since executed the programmer, so hopefully this
won't happen again.

- brian

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"