Re: what should be the best autovacuum configuration for daily partition table

On Wed, May 14, 2014 at 3:45 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Wed, May 14, 2014 at 12:06 AM, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>
>> Hi,
>>
>> I have a table with daily partition setup where old partitions are static
>> tables that is after each day we don't get any new data in old partitions.
>> The database size is 2 TB and I am running with autovacuum on for
>> Postgresql 8.4.
>> Now, I am facing a problem where old tables are not being vacuumed by
>> autovacuum deamon and every now and then we are seeing autovacuum to
>> prevent wrap around in the database and age(datfrozenzid) gets high for the
>> database.
>> Any idea what should be best configuration for this type of database
>> environment.
>>
>
> How high is age(datfrozenxid) getting? What is the problem you are
> experiencing?
>
> Cheers,
>
> Jeff
>

It'd be good to know what you have autovacuum_freeze_max_age set to. You
may have it set a bit too low and causing that automatic vacuuming to kick
in too soon.

Even with autovacuum_freeze_max_age set to a reasonable value, we still see
this issue often with data warehousing systems with a lot of static data.
As you are seeing, autovacuum will never kick in for these tables until you
hit autovacuum_freeze_max_age. The best solution we've found for this is to
run a cronjob to routinely vacuum a controlled batch of the tables with the
oldest vacuum freeze age. This controls how many tables are being vacuumed
instead of running into the situation where many of them all hit
autovacuum_freeze_max_age at the same time and cause extensively long
vacuuming sessions.

Below is a script we run for one of our clients twice a day. You can adjust
the limit on the first query to set how many you want to run per batch.
This has to be high enough (or run the script often enough) to keep the
count of old tables below hitting autovacuum_freeze_max_age and having
autovacuum kick in on them. Just pass the name of the database as a
parameter to the script.

manual_vacuum.sh:

# Manually vacuum tables with the oldest xid (25)

psql -d $1 -t -o /tmp/manual_vacuum_$1.sql -c "select 'vacuum analyze
verbose ' || oid::regclass || ';' from pg_class where relkind in ('r', 't')
and age(relfrozenxid) > 100000000 order by age(relfrozenxid) desc limit 25"

psql -d $1 -t -a -f /tmp/manual_vacuum_$1.sql > $HOME/manual_vacuum_$1.log
2>&1

Keith
http://www.keithf4.com