pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
10+ messages / 4 participants
[nested] [flat]
* pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
@ 2020-06-01 10:02 Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Achilleas Mantzios @ 2020-06-01 10:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
@ 2020-06-01 10:36 Julien Rouhaud <rjuju123@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Julien Rouhaud @ 2020-06-01 10:36 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@lists.postgresql.org
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 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
@ 2020-06-01 10:48 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Achilleas Mantzios @ 2020-06-01 10:48 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
@ 2020-06-01 11:03 Julien Rouhaud <rjuju123@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 10+ messages in thread
From: Julien Rouhaud @ 2020-06-01 11:03 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
@ 2020-06-01 12:40 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Julien Rouhaud <rjuju123@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Achilleas Mantzios @ 2020-06-01 12:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
@ 2020-06-01 14:52 Keith Fiske <keith.fiske@crunchydata.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Keith Fiske @ 2020-06-01 14:52 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
@ 2020-06-01 15:58 Rui DeSousa <rui@crazybean.net>
parent: Julien Rouhaud <rjuju123@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Rui DeSousa @ 2020-06-01 15:58 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org
> 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 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, …
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
@ 2020-06-01 18:13 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 0 replies; 10+ messages in thread
From: Achilleas Mantzios @ 2020-06-01 18:13 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 1/6/20 5:52 μ.μ., Keith Fiske wrote:
>
>
> On Mon, Jun 1, 2020 at 8:41 AM Achilleas Mantzios
> <achill@matrix.gatewaynet.com <mailto: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
> <mailto: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
> <mailto: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
>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
@ 2020-06-01 18:16 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 10+ messages in thread
From: Achilleas Mantzios @ 2020-06-01 18:16 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 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()?
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ?
@ 2020-06-01 18:22 Rui DeSousa <rui@crazybean.net>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rui DeSousa @ 2020-06-01 18:22 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@lists.postgresql.org
> 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 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2020-06-01 18:22 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-01 10:02 pg_stat_database.xact_commit + pg_stat_database.xact_rollback as a reliable metric for xid consumption ? Achilleas Mantzios <achill@matrix.gatewaynet.com>
2020-06-01 10:36 ` Julien Rouhaud <rjuju123@gmail.com>
2020-06-01 10:48 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2020-06-01 11:03 ` Julien Rouhaud <rjuju123@gmail.com>
2020-06-01 12:40 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2020-06-01 14:52 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-06-01 18:13 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2020-06-01 15:58 ` Rui DeSousa <rui@crazybean.net>
2020-06-01 18:16 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2020-06-01 18:22 ` Rui DeSousa <rui@crazybean.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox