Thread: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as areliable metric for xid consumption ?

Hello
I attended two interesting online courses by 2ndQuadrant recently, about MVCC and Freezing , and I started to seriously
diginto 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_rollbackbut 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
ofpgstat_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

On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
<achill@matrix.gatewaynet.com> wrote:
>
> Hello
> I attended two interesting online courses by 2ndQuadrant recently, about MVCC and Freezing , and I started to
seriouslydig 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_rollbackbut 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
schemaof 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.

Because read only transaction usually don't consume an xid.  So yes
pg_stat_database gives a more reasonable approximation of the real
number of transactions happening on the server.

On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
> <achill@matrix.gatewaynet.com> wrote:
> Because read only transaction usually don't consume an xid.  So yes
> pg_stat_database gives a more reasonable approximation of the real
> number of transactions happening on the server.
Thank you, so what would be the official way to monitor txid consumption (from a MVCC / Freezing admin POV)?
>
>


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

On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
<achill@matrix.gatewaynet.com> wrote:
>
> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
> > On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
> > <achill@matrix.gatewaynet.com> wrote:
> > Because read only transaction usually don't consume an xid.  So yes
> > pg_stat_database gives a more reasonable approximation of the real
> > number of transactions happening on the server.
> Thank you, so what would be the official way to monitor txid consumption (from a MVCC / Freezing admin POV)?

I'm not sure what you're really looking for.  If it's the txid
consumption rate, you can use txid_current() at regular interval to
compute it (note that this function will consume a txid).  For object
freeeze, you can use age(datfrozenxid) from pg_database, or
age(relfrozenxid) from pg_class.

Hello Julien
On 1/6/20 2:03 μ.μ., Julien Rouhaud wrote:
> On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
> <achill@matrix.gatewaynet.com> wrote:
>> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
>>> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
>>> <achill@matrix.gatewaynet.com> wrote:
>>> Because read only transaction usually don't consume an xid.  So yes
>>> pg_stat_database gives a more reasonable approximation of the real
>>> number of transactions happening on the server.
>> Thank you, so what would be the official way to monitor txid consumption (from a MVCC / Freezing admin POV)?
> I'm not sure what you're really looking for.
I've been as explicit as it gets. When ppl talk about vacuum freeze what they have in mind is xmin's and xmax's and the
ratethat xids grow and consume the 2^31 address space, so I find it strange 
 
that such a basic metric is not included in the core statistics collector.
That's all.
>


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

> On Jun 1, 2020, at 7:03 AM, Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
> <achill@matrix.gatewaynet.com> wrote:
>>
>> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
>>> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
>>> <achill@matrix.gatewaynet.com> wrote:
>>> Because read only transaction usually don't consume an xid.  So yes
>>> pg_stat_database gives a more reasonable approximation of the real
>>> number of transactions happening on the server.
>> Thank you, so what would be the official way to monitor txid consumption (from a MVCC / Freezing admin POV)?
>
> I'm not sure what you're really looking for.  If it's the txid
> consumption rate, you can use txid_current() at regular interval to
> compute it (note that this function will consume a txid).  For object
> freeeze, you can use age(datfrozenxid) from pg_database, or
> age(relfrozenxid) from pg_class.
>
>


I also record the stats tables every minute by either inserting into a table hist_ table or exporting the data out to
beinserted into another table.  When exporting the pg_stat_database table I added the following to record the
informationwithout the need to call txid_current(): 

coalesce(txid_current_if_assigned(), txid_snapshot_xmax(txid_current_snapshot())) as xact_txid, …

On Mon, Jun 1, 2020 at 8:41 AM Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

Hello Julien
On 1/6/20 2:03 μ.μ., Julien Rouhaud wrote:
> On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
> <achill@matrix.gatewaynet.com> wrote:
>> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
>>> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
>>> <achill@matrix.gatewaynet.com> wrote:
>>> Because read only transaction usually don't consume an xid.  So yes
>>> pg_stat_database gives a more reasonable approximation of the real
>>> number of transactions happening on the server.
>> Thank you, so what would be the official way to monitor txid consumption (from a MVCC / Freezing admin POV)?
> I'm not sure what you're really looking for.
I've been as explicit as it gets. When ppl talk about vacuum freeze what they have in mind is xmin's and xmax's and the rate that xids grow and consume the 2^31 address space, so I find it strange
that such a basic metric is not included in the core statistics collector.
That's all.
>


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

I've done a writeup on effectively monitoring for xid exhaustion/wraparound. 

https://info.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql

It is relatively simple to get the current xid state applying the age() function to the pg_database catalog:

SELECT datname, age(datfrozenxid), current_setting('autovacuum_freeze_max_age') FROM pg_database ORDER BY 2 DESC;

But the query in the linked blog post provides things in a more user-friendly manner for monitoring purposes as simple percentages

--

Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

On 1/6/20 6:58 μ.μ., Rui DeSousa wrote:
>
>> On Jun 1, 2020, at 7:03 AM, Julien Rouhaud <rjuju123@gmail.com> wrote:
>>
>> On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
>> <achill@matrix.gatewaynet.com> wrote:
>>> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
>>>> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
>>>> <achill@matrix.gatewaynet.com> wrote:
>>>> Because read only transaction usually don't consume an xid.  So yes
>>>> pg_stat_database gives a more reasonable approximation of the real
>>>> number of transactions happening on the server.
>>> Thank you, so what would be the official way to monitor txid consumption (from a MVCC / Freezing admin POV)?
>> I'm not sure what you're really looking for.  If it's the txid
>> consumption rate, you can use txid_current() at regular interval to
>> compute it (note that this function will consume a txid).  For object
>> freeeze, you can use age(datfrozenxid) from pg_database, or
>> age(relfrozenxid) from pg_class.
>>
>>
>
> I also record the stats tables every minute by either inserting into a table hist_ table or exporting the data out to
beinserted into another table.  When exporting the pg_stat_database table I added the following to record the
informationwithout the need to call txid_current():
 
>
> coalesce(txid_current_if_assigned(), txid_snapshot_xmax(txid_current_snapshot())) as xact_txid, …
if this is in the same system, and this _hist table only gets inserts, 
selects/delets, why not just use xmin ? Also since you are inserting how 
can you have null  txid_current_if_assigned()?
>
>
>

> On Jun 1, 2020, at 2:16 PM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
>
>>
>> coalesce(txid_current_if_assigned(), txid_snapshot_xmax(txid_current_snapshot())) as xact_txid, …
> if this is in the same system, and this _hist table only gets inserts, selects/delets, why not just use xmin ? Also
sinceyou are inserting how can you have null  txid_current_if_assigned()? 
>>
>>
>>

Because when exporting the data it’s a readonly transaction and current_txid will be null.  I normally use export on
theprimary and replicas which can’t do inserts.  I create the hist_ tables and insert locally for a standalone
instance.