pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedautovacuum, exclude table
5+ messages / 4 participants
[nested] [flat]
* autovacuum, exclude table
@ 2011-12-12 14:25 Anibal David Acosta <aa@devshock.com>
2011-12-12 14:45 ` Re: autovacuum, exclude table Craig Ringer <ringerc@ringerc.id.au>
2011-12-12 15:16 ` Re: autovacuum, exclude table Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
0 siblings, 2 replies; 5+ messages in thread
From: Anibal David Acosta @ 2011-12-12 14:25 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: autovacuum, exclude table
2011-12-12 14:25 autovacuum, exclude table Anibal David Acosta <aa@devshock.com>
@ 2011-12-12 14:45 ` Craig Ringer <ringerc@ringerc.id.au>
2011-12-12 14:55 ` Re: autovacuum, exclude table Anibal David Acosta <aa@devshock.com>
1 sibling, 1 reply; 5+ messages in thread
From: Craig Ringer @ 2011-12-12 14:45 UTC (permalink / raw)
To: Anibal David Acosta <aa@devshock.com>; +Cc: pgsql-performance
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****
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: autovacuum, exclude table
2011-12-12 14:25 autovacuum, exclude table Anibal David Acosta <aa@devshock.com>
2011-12-12 14:45 ` Re: autovacuum, exclude table Craig Ringer <ringerc@ringerc.id.au>
@ 2011-12-12 14:55 ` Anibal David Acosta <aa@devshock.com>
2011-12-12 15:26 ` Re: autovacuum, exclude table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Anibal David Acosta @ 2011-12-12 14:55 UTC (permalink / raw)
To: 'Craig Ringer' <ringerc@ringerc.id.au>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: autovacuum, exclude table
2011-12-12 14:25 autovacuum, exclude table Anibal David Acosta <aa@devshock.com>
2011-12-12 14:45 ` Re: autovacuum, exclude table Craig Ringer <ringerc@ringerc.id.au>
2011-12-12 14:55 ` Re: autovacuum, exclude table Anibal David Acosta <aa@devshock.com>
@ 2011-12-12 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2011-12-12 15:26 UTC (permalink / raw)
To: Anibal David Acosta <aa@devshock.com>; +Cc: 'Craig Ringer' <ringerc@ringerc.id.au>; pgsql-performance
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: autovacuum, exclude table
2011-12-12 14:25 autovacuum, exclude table Anibal David Acosta <aa@devshock.com>
@ 2011-12-12 15:16 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
1 sibling, 0 replies; 5+ messages in thread
From: Heikki Linnakangas @ 2011-12-12 15:16 UTC (permalink / raw)
To: Anibal David Acosta <aa@devshock.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-12-12 15:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-12 14:25 autovacuum, exclude table Anibal David Acosta <aa@devshock.com>
2011-12-12 14:45 ` Craig Ringer <ringerc@ringerc.id.au>
2011-12-12 14:55 ` Anibal David Acosta <aa@devshock.com>
2011-12-12 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-12-12 15:16 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox