From: | Yuri Niyazov <yuri(at)academia(dot)edu> |
---|---|
To: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Figuring out the correct age of datfrozenxid |
Date: | 2019-07-26 05:12:57 |
Message-ID: | CACuBw0hbStKjfKBEPE+weS=jp00iOxLkNCJ9XmXehwsanCg5Wg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Trying to figure out how to handle the following:
Our alerts that check whether a table is coming close to the 2 billion
point mark started firing on a database. We noticed that the table that had
the biggest age had a vacuum running on it that hasn't finished in 85 days.
It was a table that was no longer use, so instead of restarting the vacuum,
we truncated and deleted the table, expecting that would get rid of the
need to vacuum the table.
Afterwards, running the two queries from the documentation at
https://www.postgresql.org/docs/9.4/routine-vacuuming.html produces the
resutls below, the TL;DR of which is:
all the tables have a very reasonable age, but the database itself still
has an age approaching two billion. So, what do we do now? Were we wrong to
truncate and drop this unneeded table without letting a vacuum on it finish?
academia_notifications=# SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm') and
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) > 100000000;
table_name | age
------------------------------------+-----------
notification_messages | 227283989
information_schema.sql_features | 177276271
information_schema.sql_parts | 177276271
user_notification_message_activity | 159132783
bundles_tmp | 177276271
bundles_old_int_id | 244381510
bundles | 146576938
(7 rows)
academia_notifications=# select datname, age(datfrozenxid) FROM pg_database;
datname | age
------------------------+------------
template1 | 1901010993
template0 | 1901010993
academia_notifications | 1951010993
postgres | 1186462760
(4 rows)
Our alerts that check whether a table is coming close to the 2 billion
point mark started firing on a database. We noticed that the table that had
the biggest age had a vacuum running on it that hasn't finished in 85 days.
It was a table that was no longer use, so instead of restarting the vacuum,
we truncated and deleted the table, expecting that would get rid of the
need to
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2019-07-26 17:57:22 | Re: Figuring out the correct age of datfrozenxid |
Previous Message | Thomas Kellerer | 2019-07-24 18:59:35 | Re: Restrict permissions on schema to hide pl/pgsql code |