Skip site navigation (1) Skip section navigation (2)

Re: disable heavily updated (but small) table auto-vecuuming

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Ludwik Dylag <ldylag(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: disable heavily updated (but small) table auto-vecuuming
Date: 2009-09-15 15:44:59
Message-ID: 603c8f070909150844y2a8136bpe711c85b61d7b047@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
2009/9/15 Ludwik Dylag <ldylag(at)gmail(dot)com>:
> Hello
> I have a database where I daily create a table.
> Every day it is being inserted with ~3mln rows and each of them is being
> updated two times.The process lasts ~24 hours so the db load is the same at
> all the time. total size of the table is ~3GB.
> My current vacuum settings are:
> autovacuum = on
> autovacuum_max_workers = 3
> autovacuum_freeze_max_age = 2000000000 (changed from 200000000)
> vacuum_freeze_min_age = 100000000
> I have over 250 mln of frozen ids.
> # SELECT datname, age(datfrozenxid) FROM pg_database;
>   datname   |    age
> ------------+-----------
> my_database | 256938425
> and every day (since max age exceeded 200mln.) the current table is being
> vacuumed two hours after it was created.
> My goal is to set the vacuum properties so the current table is not vacuumed
> when it is used. And to vacuum it manually one day after it was used.
> Is it enough to set
> autovacuum=off
> autovacuum_freeze_max_age=2000000000
> vacuum_freeze_min_age = 100000000
> and shedule in cron daily vacuum on selected table?

How about just disabling autovacuum for that table?

http://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS

...Robert

In response to

pgsql-performance by date

Next:From: Ivan VorasDate: 2009-09-15 16:12:38
Subject: Re: View vs Stored Proc Performance
Previous:From: Tom LaneDate: 2009-09-15 15:26:13
Subject: Re: View vs Stored Proc Performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group