pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
Date: 2020-06-01 10:02:19
Message-ID: 51fa222b-374e-39da-eb8c-66d667426bae@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello
I attended two interesting online courses by 2ndQuadrant recently, about MVCC and Freezing , and I started to seriously dig into this for the first time in our almost 20-yrs postgersql installation
(running 10.x currently).
I thought of using the info from the statistics collector : pg_stat_database xact_commit and pg_stat_database.xact_rollback but to my surprise this gave very high numbers, almost 20 times up , so I
wrote a cron to insert every morning into an archive/historic table : pgstat_database_arc which is basically the schema of pgstat_database plus a timestamp col. And I got two instances of it one
yesterday and one this morning :

dynacom=# select xmin,datname,xact_commit+xact_rollback as num_of_xacts,ts from pgstat_database_arc WHERE datname='dynacom';
   xmin    | datname | num_of_xacts | ts
-----------+---------+--------------+-------------------------------
 813560437 | dynacom |   7734784237 | 2020-05-31 18:37:08.950399+03
 813889272 | dynacom |   7740424341 | 2020-06-01 08:00:01.912157+03
(2 rows)

But the reported increase in the total number of xacts is 7740424341 - 7734784237 = 5640104 , while the actual xid consumption (using xmin, this table only does inserts) is : 813889272 - 813560437 =
328835. The xid difference (via xmin) is compatible with another system that I maintain (for our custom replication solution), so I am wondering why this almost 20-fold difference between actual xid
consumption and pg_stat_database metrics.

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Julien Rouhaud 2020-06-01 10:36:25 Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
Previous Message Rene Romero Benavides 2020-06-01 00:06:10 Re: Suggestion to Monitoring Tool