Try to understand VACUUM and its settings

Started by Michael Chauover 10 years ago2 messagesgeneral
Jump to latest
#1Michael Chau
michael.chau@gameyourgame.com

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

#2Jim Nasby
Jim.Nasby@BlueTreble.com
In reply to: Michael Chau (#1)
Re: Try to understand VACUUM and its settings

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

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