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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2009/9/15 Ludwik Dylag <ldylag@xxxxxxxxx>:
> 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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux