pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFiguring out the correct age of datfrozenxid
2+ messages / 2 participants
[nested] [flat]
* Figuring out the correct age of datfrozenxid
@ 2019-07-26 05:12 Yuri Niyazov <yuri@academia.edu>
0 siblings, 1 reply; 2+ messages in thread
From: Yuri Niyazov @ 2019-07-26 05:12 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Figuring out the correct age of datfrozenxid
@ 2019-07-26 17:57 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Yuri Niyazov <yuri@academia.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Alvaro Herrera @ 2019-07-26 17:57 UTC (permalink / raw)
To: Yuri Niyazov <yuri@academia.edu>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 2019-Jul-25, Yuri Niyazov wrote:
> 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?
I think vacuuming any table will update the database age. For instance,
try creating an empty table and do "vacuum freeze" on it.
> Were we wrong to truncate and drop this unneeded table without letting
> a vacuum on it finish?
That seems an appropriate measure to have taken You just need to have
the system figure out that it's gone.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-07-26 17:57 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-26 05:12 Figuring out the correct age of datfrozenxid Yuri Niyazov <yuri@academia.edu>
2019-07-26 17:57 ` Alvaro Herrera <alvherre@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox