Re: VACUUM FULL for performance

MayVACUUM FULL on a table improve perfromance of the system?

On 7 October 2010 12:38, AI Rumman <rummandba(at)gmail(dot)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

> > 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(at)gunduz(dot)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.

regards
Szymon

In response to Szymon Guz <mabewlun(at)gmail(dot)com>:

> 2010/10/7 Devrim GÜNDÜZ <devrim(at)gunduz(dot)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(at)yahoo(dot)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