Thread: what should be the best autovacuum configuration for daily partition table
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.
Thanks.
Hi Rumman,
Please publish the below information.
1. vacuum and auto-vacuum parametters current settings on cluster.
select name,setting from pg_settings where name ilike '%vacuum%';
2. show maintenance_work_mem ;
show autovacuum_max_workers ;
3. Physical Ram size on server.
--Chiru
On Wed, May 14, 2014 at 12:36 PM, AI Rumman <rummandba@gmail.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.Thanks.
Re: what should be the best autovacuum configuration for daily partition table
From
Jeff Janes
Date:
On Wed, May 14, 2014 at 12:06 AM, AI Rumman <rummandba@gmail.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
On Wed, May 14, 2014 at 3:45 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Wed, May 14, 2014 at 12:06 AM, AI Rumman <rummandba@gmail.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
# 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