Thread: autovacuum, exclude table
I have a couple of tables with about 400millions of records increasing about 5 millions per day.
I think that disabling autovac over those tables, and enabling daily manual vacuum (in some idle hour) will be better.
I am right?
Is possible to exclude autovacuum over some tables?
Thanks!
Anibal
Top-posting because this is context free:
You need to provide more info for anybody to help you. Are the tables append-only or are deletes/updates also performed? Also this:
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
I have a couple of tables with about 400millions of records increasing about 5 millions per day.
I think that disabling autovac over those tables, and enabling daily manual vacuum (in some idle hour) will be better.
I am right?
Is possible to exclude autovacuum over some tables?
Thanks!
Anibal
The postgres version is 9.0.4 on a Windows Server 2008 (planning to upgrade to 9.1)
Tables has insert (in bulk every 3 minutes) and delete one per day (delete records older than XX days)
There are not much additional relevant information.
Thanks!
De: Craig Ringer [mailto:ringerc@ringerc.id.au]
Enviado el: lunes, 12 de diciembre de 2011 11:45 a.m.
Para: Anibal David Acosta
CC: pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] autovacuum, exclude table
Top-posting because this is context free:
You need to provide more info for anybody to help you. Are the tables append-only or are deletes/updates also performed? Also this:
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
On Dec 12, 2011 10:26 PM, "Anibal David Acosta" <aa@devshock.com> wrote:
I have a couple of tables with about 400millions of records increasing about 5 millions per day.
I think that disabling autovac over those tables, and enabling daily manual vacuum (in some idle hour) will be better.
I am right?
Is possible to exclude autovacuum over some tables?
Thanks!
Anibal
On 12.12.2011 16:25, Anibal David Acosta wrote: > I have a couple of tables with about 400millions of records increasing about > 5 millions per day. > > I think that disabling autovac over those tables, and enabling daily manual > vacuum (in some idle hour) will be better. > > I am right? Possibly. If the system is otherwise idle, it sounds sensible to do routine maintenance at that time. > Is possible to exclude autovacuum over some tables? Sure, see http://www.postgresql.org/docs/9.1/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS ALTER TABLE foo SET (autovacuum_enabled=false, toast.autovacuum_enabled = false); It might be better, though, to let autovacuum enabled, and just do the additional manual VACUUM in the idle period. If the daily manual VACUUM is enough to keep the bloat within the autovacuum thresholds, autovacuum will never kick in. If it's not enough, then you probably want autovacuum to run. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
"Anibal David Acosta" <aa@devshock.com> writes:
> Tables has insert (in bulk every 3 minutes) and delete one per day (delete records older than XX days)
No updates at all, just inserts and a daily delete?
If so, you're wasting your time even thinking about suppressing
autovacuum, because it won't fire on this table except after the daily
delete, which is exactly when you need it to.
Also, if you suppress autovacuum you also suppress autoanalyze,
which is something that *will* fire after large inserts, and probably
should. At least, this usage pattern doesn't suggest to me that it's
clearly safe to run without up-to-date stats.
Right offhand, I'm not convinced either that you have a problem, or that
turning off autovacuum would fix it.
regards, tom lane