Re: beginners autovacuum question

From: Devrim GÜNDÜZ <devrim(at)gunduz(dot)org>
To: u235sentinel <u235sentinel(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: beginners autovacuum question
Date: 2011-01-06 07:20:25
Message-ID: 1294298425.2686.59.camel@lenovo01-laptop03.gunduz.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

On Wed, 2011-01-05 at 18:08 -0700, u235sentinel wrote:
> I'm tracking a problem with our tables being bloated and was curious
> if someone regularly kills autovacuum jobs, will autovacuum later
> reattempt to vacuum the table it was killed under?

In 8.3+, autovacuum kills itself if when it conflicts with other
queries. If it is killed by a human or so, it will try to run again in
the next cycle (which depends on max_workers and timeout values).

> I've made autovacuum more aggressive and given more worker threads.

Giving more worker threads will probably not make autovacuum aggressive.
You will also want to decrease threshold values either in
postgresql.conf, or set custom parameters in pg_class per relation.

> Yet for some reason we're not keeping up.

Probably because of the threshold values.

> Vacuum does complete when I run it manually on a table. But I'm
> suspecting a coworker working late at night be my killing autovacuum.
> Reading through the logs right now to see if this is the case.

Set log_autovacuum_min_duration to 0, and log all autovac activities. It
will let you know when it cancels itself, like "ERROR: cancelling
autovacuum task" or so (this may not be the exact message, I did not
check).

Regards,
--
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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2011-01-06 08:02:43 Re: UUID column as pimrary key?
Previous Message Вячеслав Блинников 2011-01-06 06:13:40 Re: Asynchronous queries with bound data.