Thread: 8.2rc1: vacuum full fills up disk space
this somehow sounds buggy: there's this table forum.posts which had 224mb table size, 145mb toast tabl= e size and 176mb indexes size (aproximately 60'000 rows). as i was doing so= me updates of all the records, i've issued a VACUUM FULL <tablename>... this was merely 60min ago, and it hasn't yet finished... instead in pg_xlog= there are now 380+ files, totalling in over 6gb. available disk space is a= lmost used up... the table was freshly loaded yestertday when reloading for rc1. as the process of vacuuming is still ongoing, is there something i can do t= o... a) .. prevent total diskspace fillup. what happens if i send a cancel signa= l to the process? b) .. gather some "evidence" if needed? c) .. check if the vacuum process is still alive? d) .. give the process in question a higher priority so it would finish soo= ner? thanks, thomas
"Thomas H." <me@alternize.com> writes:
> this somehow sounds buggy:
vacuum full absolutely *will* bloat your index, if run on a
heavily-modified table. I do not think it will bloat pg_xlog by itself
however; are you sure you don't have some other open transactions?
> a) .. prevent total diskspace fillup. what happens if i send a cancel =
> signal to the process?
Killing it should not corrupt anything; if it does that IS a serious bug
and will be met with a full alarm response...
regards, tom lane
>> this somehow sounds buggy: > > vacuum full absolutely *will* bloat your index, if run on a > heavily-modified table. I do not think it will bloat pg_xlog by itself > however; are you sure you don't have some other open transactions? well yes, as the system is "live", users are browsing the website. but all queries that try to access the table in question are stalled at the moment. when querying server status i'm seeing lots of queries that are waiting for access to the table. would vacuum freeze be faster? >> a) .. prevent total diskspace fillup. what happens if i send a cancel = >> signal to the process? > > Killing it should not corrupt anything; if it does that IS a serious bug > and will be met with a full alarm response... after "a while" it eventually finished just in time before the hd was filled up :-) 2006-11-27 07:18:04 [2096] LOG: duration: 6093332.000 ms statement: VACUUM FULL VERBOSE ANALYZE forum.posts thanks, - thomas
Thomas H. wrote: >>> this somehow sounds buggy: >> >> vacuum full absolutely *will* bloat your index, if run on a >> heavily-modified table. I do not think it will bloat pg_xlog by itself >> however; are you sure you don't have some other open transactions? > > well yes, as the system is "live", users are browsing the website. but > all queries that try to access the table in question are stalled at the > moment. when querying server status i'm seeing lots of queries that are > waiting for access to the table. > > would vacuum freeze be faster? Vacuum freeze won't move tuples so it won't reclaim any more space than a normal vacuum. Cluster, however, rewrites the whole table and compacts the space, and runs faster than vacuum full on a badly bloated table. It will also recreate all indexes. In the future, instead of updating a whole table with UPDATE, you should consider doing a SELECT INTO to create a new table, dropping the old table and renaming the new one in place of the old one. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
>> well yes, as the system is "live", users are browsing the website. but >> all queries that try to access the table in question are stalled at the >> moment. when querying server status i'm seeing lots of queries that are >> waiting for access to the table. >> >> would vacuum freeze be faster? > > Vacuum freeze won't move tuples so it won't reclaim any more space than a > normal vacuum. Cluster, however, rewrites the whole table and compacts the > space, and runs faster than vacuum full on a badly bloated table. It will > also recreate all indexes. will give it a try later on, thanks! > > In the future, instead of updating a whole table with UPDATE, you should > consider doing a SELECT INTO to create a new table, dropping the old table > and renaming the new one in place of the old one. the problem is: the table was far from being bloated, IMO. it was 2 days old, every record at most 2-3 times updated. the space needed for the table dropped from 400mb to roughly 200mb after the 1.5hr vacuum full... i've never had such a long vacuuming time before, even on tables that are much larger and contains more dead rows. the table uses tsearch2 and a gin-index, could that be the problem? the gin faq says a drop/create index would be much faster than a reindex. maybe this is also true when vacuuming a table with a gin-index? - thomas