pg_stat_database.xact_commit + pg_stat_database.xact_rollback as areliable metric for xid consumption ?
От | Achilleas Mantzios |
---|---|
Тема | pg_stat_database.xact_commit + pg_stat_database.xact_rollback as areliable metric for xid consumption ? |
Дата | |
Msg-id | 51fa222b-374e-39da-eb8c-66d667426bae@matrix.gatewaynet.com обсуждение исходный текст |
Ответы |
Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback asa reliable metric for xid consumption ?
|
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления: