Обсуждение: Try to understand VACUUM and its settings
Hi,
Last Friday, I ran :
postgres=# select max(age(datfrozenxid)) from pg_database;
max
42579490
and then I ran :
SELECT relname, age(relfrozenxid) as xid_age,
pg_size_pretty(pg_table_size(oid)) as table_size
FROM pg_class
WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 20;
"table_1";42574012;"10111 MB"
"table_2";42567398;"5961 MB"
"table_3";20320509;"12 GB"
Today, the max is
43068744
and
"table_1";43063214;"10134 MB"
"table_2";43056600;"5984 MB"
"table_3";20809711;"12 GB"
====
It looks like the age(relfrozenxid) of the tables go up in tandem with the max. autovacuum_freeze_max_age and vacuum_freeze_table_age are commented out in postgresql.conf
#autovacuum_freeze_max_age = 200000000
#vacuum_freeze_table_age = 150000000
So, do I need to run vacuum freeze on those tables? Also, if autovacuum_freeze_max_age is commented, does it still mean that the default is 200M?
Thanks,
Michael
On 10/5/15 5:00 PM, Michael Chau wrote: > So, do I need to run vacuum freeze on those tables? Also, if > autovacuum_freeze_max_age is commented, does it still mean that the > default is 200M? Yes, and unless you're running a very high transaction rate you probably don't need to mess with it. Likewise you normally don't need to explicitly freeze, though it's not a bad idea to do so after a large data load. Just make sure that there's no open transactions that were started before the data load transaction when you run the VACUUM. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com