Thread: oldest xmin is far in the past :: BUT xmin is not available in system
It seems vacuum is behaving somewhat weird on postgres database , observing below HINTS on the vacuum logs
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
Below is the auto-vacuum status on the bloated tables:
=> SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup
/ (n_live_tup
* current_setting('autovacuum_vacuum_scale_factor')::float8
+ current_setting('autovacuum_vacuum_threshold')::float8)
DESC
LIMIT 10;
schemaname | relname | n_live_tup | n_dead_tup | last_autovacuum
------------+---------------------+------------+------------+-------------------------------
pg_catalog | pg_statistic | 136 | 37563 | 2022-04-18 04:00:21.045089+00
public | test1 | 209405206 | 126752908 | 2022-04-18 03:59:43.013758+00
public | test2 | 513770985 | 49258312 | 2022-04-18 04:00:23.24043+00
public | test3 | 90853150 | 4090146 | 2022-04-18 04:00:25.868147+00
pg_catalog | pg_shdepend | 153 | 29 | 2022-04-08 12:16:02.816631+00
pg_catalog | pg_index | 73 | 18 |
pg_toast | pg_toast_2619 | 16 | 12 | 2022-03-13 23:01:54.334003+00
pg_catalog | pg_class | 425 | 19 | 2022-03-01 13:15:57.534378+00
pg_catalog | pg_proc | 2457 | 48 |
pg_toast | pg_toast_2618 | 252 | 10 |
i tried to vacuum the the first table pg_statistic , Below is the log
postgres=> VACUUM (VERBOSE) pg_statistic;
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
INFO: aggressively vacuuming "pg_catalog.pg_statistic"
INFO: "pg_statistic": found 0 removable, 37699 nonremovable row versions in 6331 out of 6351 pages
DETAIL: 37563 dead row versions cannot be removed yet, oldest xmin: 648320155
There were 3340 unused item identifiers.
Skipped 0 pages due to buffer pins, 20 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
INFO: aggressively vacuuming "pg_toast.pg_toast_2619"
INFO: "pg_toast_2619": found 0 removable, 16 nonremovable row versions in 3 out of 11 pages
DETAIL: 12 dead row versions cannot be removed yet, oldest xmin: 648320155
There were 11 unused item identifiers.
Skipped 0 pages due to buffer pins, 8 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
Table is getting vacuumed but not able to remove the dead tuples because of oldest xmin: 648320155 , but the mentioned xim is not associated with long running quries or stale replication slots or prepared transactions.
Long running:
postgres=> SELECT now()-query_start,pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
?column? | pid | datname | usename | state | backend_xmin
-----------------+-------+-------------------+---------+--------+--------------
00:00:29.910155 | 539 | postgres | | active | 832858371
00:00:23.766305 | 1211 | postgres | | active | 832858509
00:00:00.756961 | 2151 | postgres | | active | 832859484
00:00:00.060784 | 30833 | postgres | root | active | 832859508
00:00:00.004473 | 29270 | postgres | root | active | 832859508
00:00:00.009809 | 29271 | postgres | root | active | 832859508
00:00:00.015169 | 27145 | postgres | root | active | 832859508
00:00:00 | 1450 | postgres | postgres | active | 832859508
00:00:00.010672 | 544 | postgres | root | active | 832859508
00:00:00.034516 | 19940 | postgres | root | active | 832859508
(10 rows)
stale replication slots:
postgres=> SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;
slot_name | slot_type | database | xmin
-----------+-----------+----------+------
(0 rows)
Prepared transaction's :
postgres=> SELECT gid, prepared, owner, database, transaction AS xmin
postgres-> FROM pg_prepared_xacts
postgres-> ORDER BY age(transaction) DESC;
gid | prepared | owner | database | xmin
-----+----------+-------+----------+------
(0 rows)
Checked for long running queries on replica side , but haven't found any
postgres=> show hot_standby_feedback ;
hot_standby_feedback
----------------------
on
(1 row)
postgres=> SELECT pid, age(current_timestamp, xact_start),usename ,state,left(query,100)
FROM pg_stat_activity
WHERE state <> 'idle' and pid<>pg_backend_pid();
;
pid | age | usename | state | left
-----+-----+---------+-------+------
(0 rows)
postgres=> select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
Regards,
BK
Re: oldest xmin is far in the past :: BUT xmin is not available in system
On Monday, April 18, 2022, bhargav kamineni <kbn98406@gmail.com> wrote:
Hi Team,It seems vacuum is behaving somewhat weird on postgres database , observing below HINTS on the vacuum logs
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
Re: oldest xmin is far in the past :: BUT xmin is not available in system
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 13.5 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 7.4.0, 64-bit
(1 row)
postgres=> select aurora_version();
aurora_version
----------------
13.5.1
(1 row)
postgres=> \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-------------------+----------+----------+-------------+-------------+-----------------------+-----------+------------+--------------------------------------------
Postgres | root | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/root +| 361 GB | pg_default |
| | | | | root=CTc/root +| | |
| | | | | pmm=CTc/root | | |
Test | root | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8391 kB | pg_default | default administrative connection database
rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin | No Access | pg_default |
template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin +| 16 MB | pg_default | unmodifiable empty database
| | | | | rdsadmin=CTc/rdsadmin | | |
template1 | root | UTF8 | en_US.UTF-8 | en_US.UTF-8 | root=CTc/root +| 8215 kB | pg_default | default template for new databases
| | | | | =c/root | | |
(5 rows)
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING: oldest xmin is far in the past
On Monday, April 18, 2022, bhargav kamineni <kbn98406@gmail.com> wrote:Hi Team,It seems vacuum is behaving somewhat weird on postgres database , observing below HINTS on the vacuum logs
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
What version?What other databases are present?Others can give better (more detailed/nuanced) guidance but if you can just start vacuuming every table in every database manually, you probably should just do that. Vacuum freeze specifically.David J.
On Monday, April 18, 2022, bhargav kamineni <kbn98406@gmail.com> wrote:Hi Team,It seems vacuum is behaving somewhat weird on postgres database , observing below HINTS on the vacuum logs
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
What version?What other databases are present?Others can give better (more detailed/nuanced) guidance but if you can just start vacuuming every table in every database manually, you probably should just do that. Vacuum freeze specifically.David J.
This site has lots of useful queries for this sort of issue:
https://www.shanelynn.ie/postgresql-find-slow-long-running-and-blocked-queries. I think you're looking for a very old transaction that is probably not going to finish, must be terminated.
Rob Sargent <robjsargent@gmail.com> writes: > On 4/19/22 00:06, David G. Johnston wrote: >> On Monday, April 18, 2022, bhargav kamineni <kbn98406@gmail.com> wrote: >> It seems vacuum is behaving somewhat weird on postgres database , >> observing below HINTS on the vacuum logs >> WARNING: oldest xmin is far in the past > This site has lots of useful queries for this sort of issue: > https://www.shanelynn.ie/postgresql-find-slow-long-running-and-blocked-queries. > I think you're looking for a very old transaction that is probably not > going to finish, must be terminated. Yeah, that. Manual vacuuming isn't going to help until you get rid of the old open transaction. Look into pg_prepared_xacts and pg_stat_activity. regards, tom lane
Re: oldest xmin is far in the past :: BUT xmin is not available in system
On Mon, Apr 18, 2022 at 11:37 PM bhargav kamineni <kbn98406@gmail.com> wrote: > executing the vacuum on the entire cluster is also giving the same HINTS and WARNING's You're using Aurora, not PostgreSQL. Perhaps this is actually a bug, but there is no way for anybody here to know. -- Peter Geoghegan