Re: [BUGS] 8.2rc1: vacuum full fills up disk space
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] 8.2rc1: vacuum full fills up disk space
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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] 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 table size and 176mb indexes size (aproximately 60'000 rows). as i was doing some 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 almost 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 to... a) .. prevent total diskspace fillup. what happens if i send a cancel signal 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 sooner? thanks, thomas
Re: [BUGS] 8.2rc1: vacuum full fills up disk space
Thomas H. [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] 8.2rc1: vacuum full fills up disk space
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
