Thread: delete/vacuum not freeing disk space
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
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
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"
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
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
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"
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
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"