Re: [GENERAL] what should be the best autovacuum configuration for daily partition table
On Wed, May 14, 2014 at 3:45 PM, Jeff Janes wrote:
> On Wed, May 14, 2014 at 12:06 AM, AI Rumman 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) > 1 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
Re: [GENERAL] what should be the best autovacuum configuration for daily partition table
On Wed, May 14, 2014 at 12:06 AM, AI Rumman 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
Re: [GENERAL] what should be the best autovacuum configuration for daily partition table
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 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. > >
[GENERAL] 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.
