8

My MaximumUsedTransactionIDs seems to keep growing indefinitely, but I have these evidences hinting me it maybe should not be happening that way:

$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
my_main_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.5.8, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 
256, compression: off)
Type "help" for help.

my_main_data_base=> SELECT datname FROM pg_database;
     datname
-------------------
 template0
 rdsadmin
 template1
 postgres
 my_main_data_base
 another_data_base
(6 rows)

my_main_data_base=> VACUUM FREEZE; SELECT relname, age(relfrozenxid) AS xid_ag
e FROM pg_class JOIN pg_stat_all_tables USING (relname) ORDER BY xid_age DESC 
LIMIT 5;
VACUUM
       relname        | xid_age
----------------------+---------
 pg_toast_1150794     |   63707
 a_user_table         |   63707
 another_user_table   |   63707
 pg_toast_1223948     |   63707
 pg_toast_1223998     |   63707
(5 rows)

my_main_data_base=> \q
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
another_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.5.8, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 
256, compression: off)
Type "help" for help.

another_data_base=> VACUUM FREEZE; SELECT relname, age(relfrozenxid) AS xid_ag
e FROM pg_class JOIN pg_stat_all_tables USING (relname) ORDER BY xid_age DESC 
LIMIT 5;
VACUUM
      relname       | xid_age
--------------------+---------
 pg_toast_2964      |   12350
 pg_toast_3592      |   12350
 pg_toast_2396      |   12350
 pg_db_role_setting |   12350
 pg_authid          |   12350
(5 rows)

another_data_base=> \q
$ _

$MY_USER_LAND_RDS_SUPER_USER is what AWS RDS provides me as a superuser. It is a super user from the RDS point of view, but it is not a superuser from the PostgreSQL point of view.


I would expect now to have MaximumUsedTransactionIDs decreased, but it does not. It is still at about ~80.000.000.

I have planned that after it reaches 100.000.000 I'll fork a development version of the RDS instance where I'll make it so AWS boots the autovacuum process at it.

Questions

  1. What is happening with MaximumUsedTransactionIDs? Why it does not shrink?

  2. Will autovacuum take care of MaximumUsedTransactionIDs once booted?

  3. Is autovacuum irrelevant in my scenario because I run manually unqualified VACUUM FREEZEs from time to time on all my user land databases?

  4. Should I just ignore MaximumUsedTransactionIDs?

  5. Can I somehow connect to database rdsadmin to SELECT age(relfrozenxid) there?

EDIT, fulfilling your first request, adding...

$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
my_main_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.6.6, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 
256, compression: off)
Type "help" for help.

postgresql=> SELECT datname, datfrozenxid FROM pg_database;
     datname       | datfrozenxid
-------------------+--------------
 template0         |         1822
 rdsadmin          |         1822
 template1         |         1822
 postgres          |         1822
 my_main_data_base |     76628948
 another_data_base |     76681257
(6 rows)

postgresql=> \q
$ _
7
  • Do you see which database of yours is aging? Run SELECT datname, datfrozenxid FROM pg_database; so check. Apr 18, 2018 at 12:01
  • so = to above. And the smaller the datfrozenxid, the older it is. Apr 18, 2018 at 12:09
  • Cool... I suppose autovacuum will fix the situation by freezing the templates and postgres and rdsadmin..?
    – 48347
    Apr 18, 2018 at 15:05
  • I am not so sure about template0, as one cannot just connect to it. But autovacuum should be turned on in any case, and in normal cases it should take care of this. Apr 18, 2018 at 15:06
  • I expect that to reach 100.000.000 transactions in two to ten months and then I'll follow this up eventually.
    – 48347
    Apr 18, 2018 at 15:22

1 Answer 1

2

Activate autovacuum in the parameter group and when it hits the configured level (min 100.000.000) it'll start and MaximumUsedTransactionIDs will go down drastically.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.