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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

  From Date Subject
Next Message Ivan Voras 2009-09-15 16:12:38 Re: View vs Stored Proc Performance
Previous Message Tom Lane 2009-09-15 15:26:13 Re: View vs Stored Proc Performance