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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Achilleas Mantzios
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



Reply | Threaded
Open this post in threaded view
|

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

Julien Rouhaud
On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
<[hidden email]> wrote:

>
> 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.

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.


Reply | Threaded
Open this post in threaded view
|

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

Achilleas Mantzios
On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
> <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

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

Julien Rouhaud
On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
<[hidden email]> wrote:
>
> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
> > On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
> > <[hidden email]> 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.


Reply | Threaded
Open this post in threaded view
|

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

Achilleas Mantzios
Hello Julien
On 1/6/20 2:03 μ.μ., Julien Rouhaud wrote:

> On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
> <[hidden email]> wrote:
>> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
>>> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
>>> <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

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

Keith Fiske-2


On Mon, Jun 1, 2020 at 8:41 AM Achilleas Mantzios <[hidden email]> wrote:
Hello Julien
On 1/6/20 2:03 μ.μ., Julien Rouhaud wrote:
> On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
> <[hidden email]> wrote:
>> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
>>> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
>>> <[hidden email]> 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. 


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
Reply | Threaded
Open this post in threaded view
|

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

Rui DeSousa
In reply to this post by Julien Rouhaud


> On Jun 1, 2020, at 7:03 AM, Julien Rouhaud <[hidden email]> wrote:
>
> On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
> <[hidden email]> wrote:
>>
>> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
>>> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
>>> <[hidden email]> 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 be inserted into another table.  When exporting the pg_stat_database table I added the following to record the information without the need to call txid_current():

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



Reply | Threaded
Open this post in threaded view
|

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

Achilleas Mantzios
In reply to this post by Keith Fiske-2


On 1/6/20 5:52 μ.μ., Keith Fiske wrote:


On Mon, Jun 1, 2020 at 8:41 AM Achilleas Mantzios <[hidden email]> wrote:
Hello Julien
On 1/6/20 2:03 μ.μ., Julien Rouhaud wrote:
> On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
> <[hidden email]> wrote:
>> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
>>> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
>>> <[hidden email]> 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. 


Thank you, seems like a nice write-up, I'll check this out.
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
Reply | Threaded
Open this post in threaded view
|

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

Achilleas Mantzios
In reply to this post by Rui DeSousa

On 1/6/20 6:58 μ.μ., Rui DeSousa wrote:

>
>> On Jun 1, 2020, at 7:03 AM, Julien Rouhaud <[hidden email]> wrote:
>>
>> On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
>> <[hidden email]> wrote:
>>> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
>>>> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
>>>> <[hidden email]> 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 be inserted into another table.  When exporting the pg_stat_database table I added the following to record the information without 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()?
>
>
>


Reply | Threaded
Open this post in threaded view
|

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

Rui DeSousa


> On Jun 1, 2020, at 2:16 PM, Achilleas Mantzios <[hidden email]> 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 since you 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 the primary and replicas which can’t do inserts.  I create the hist_ tables and insert locally for a standalone instance.