Thread: VACUUM FULL for performance
MayVACUUM FULL on a table improve perfromance of the system?
On 7 October 2010 12:38, AI Rumman <rummandba@gmail.com> wrote: > MayVACUUM FULL on a table improve perfromance of the system? > Please treat VACUUM FULL as a last resort. Read this: http://wiki.postgresql.org/wiki/VACUUM_FULL A VACUUM ANALYZE will mark dead tuples as free and update the stats. Also check your query plans to see where the real bottlenecks are. Is your system properly configured too? Check out http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On Thu, 2010-10-07 at 17:38 +0600, AI Rumman wrote: > MayVACUUM FULL on a table improve perfromance of the system? No, it will make things worse. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
> > MayVACUUM FULL on a table improve perfromance of the system? > > No, it will make things worse. ??? Why? "The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans" This is for 9.0. For 8.4 I think the indexes on the table could grow; but seq scan on the table will be faster than with a plain vacuum...
2010/10/7 Devrim GÜNDÜZ <devrim@gunduz.org>
On Thu, 2010-10-07 at 17:38 +0600, AI Rumman wrote:No, it will make things worse.
> MayVACUUM FULL on a table improve perfromance of the system?
That's not true, I'd rather say that it depends. The whole performance is not just about the vacuum full.
regards
Szymon
In response to Szymon Guz <mabewlun@gmail.com>: > 2010/10/7 Devrim GÜNDÜZ <devrim@gunduz.org> > > > On Thu, 2010-10-07 at 17:38 +0600, AI Rumman wrote: > > > MayVACUUM FULL on a table improve perfromance of the system? > > > > No, it will make things worse. > > That's not true, I'd rather say that it depends. The whole performance is > not just about the vacuum full. My experience: VACUUM FULL is indicated under the following conditions: * When routine vacuum has failed for a while due to some problem, or there are indicators that vacuuming was not being done often enough and that table bloat has gotten out of hand. * A major, unusual event has occurred that has cause the # of dead rows in a table to bloat far out of what happens with normal usage. I also recommend reindexing any table that has been VACUUM FULLed. As everyone else has mentioned, VACUUM FULL is not a silver bullet, and is not _guaranteed_ to improve performance. There are also very few cases where it's a good idea to do it as routine maintenance. However, it is a tool that is useful at times, and it's worthwhile to understand how it works. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
> I also recommend reindexing any table that has been VACUUM FULLed. Mmmh, from the docs I get that in 9.0 a "vacuum full" rewrites the whole table, so I expect the indexes to be re-created anyway... so a reindexing would be totally useless. Am I wrong?
Leonardo Francalanci <m_lists@yahoo.it> writes: >> I also recommend reindexing any table that has been VACUUM FULLed. > Mmmh, from the docs I get that in 9.0 a "vacuum full" rewrites the whole table, > so I expect the indexes to be re-created anyway... so a reindexing would > be totally useless. Any discussion of VACUUM FULL has to recognize that 9.0's implementation of it is completely different from all prior versions. It still has disadvantages, but not the ones that were there before. regards, tom lane