oldest xmin is far in the past :: BUT xmin is not available in system
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.
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
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 logsWARNING: 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.
Other details:
postgres=> select version();
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)
executing the vacuum on the entire cluster is also giving the same HINTS
and WARNING'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.
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
Regards,
BK
On Tue, Apr 19, 2022 at 11:36 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
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 logsWARNING: 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 4/19/22 00:06, David G. Johnston wrote:
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 logsWARNING: 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
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