Обсуждение: backend_xmin in pg_stat_replication

Поиск
Список
Период
Сортировка

backend_xmin in pg_stat_replication

От
Torsten Förtsch
Дата:
Hi,

if I understand it correctly, backend_xmin in pg_stat_replication is the xmin that's reported back by hot_standby_feedback. Given there are no long-running transactions on the replica, I presume that value should be pretty close to the xmin field of any recent snapshots on the master. This is true for all my databases but one:

select application_name,
       txid_snapshot_xmin(txid_current_snapshot()),
       backend_xmin::TEXT::BIGINT,
       txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT
  from pg_stat_replication;

 application_name | txid_snapshot_xmin | backend_xmin |  ?column?   
------------------+--------------------+--------------+------------
 xxxxxxxxxx       |         6957042833 |   2662075435 | 4294967398

Over time, that backend_xmin is slowly advancing.

If I call txid_current_snapshot() in a new session on the replica, I get reasonable numbers but the backend_xmin in pg_stat_activity is equally off.

select backend_xmin, txid_current_snapshot()
  from pg_stat_activity
 where backend_xmin is not null;

 backend_xmin | txid_current_snapshot   
--------------+------------------------
   2662207433 | 6957174729:6957174729:

Is that expected behavior? Or is there anything wrong?

All other backends are idle and all but one pretty fresh. One has been running for about 2 months with short-lasting transactions every now and again.

Thanks,
Torsten

Re: backend_xmin in pg_stat_replication

От
Andres Freund
Дата:
Hi,

On 2018-10-01 12:20:26 +0200, Torsten Förtsch wrote:
> if I understand it correctly, backend_xmin in pg_stat_replication is the
> xmin that's reported back by hot_standby_feedback. Given there are no
> long-running transactions on the replica, I presume that value should be
> pretty close to the xmin field of any recent snapshots on the master. This
> is true for all my databases but one:
> 
> select application_name,
>        txid_snapshot_xmin(txid_current_snapshot()),
>        backend_xmin::TEXT::BIGINT,
> 
>  txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT
>   from pg_stat_replication;
> 
>  application_name | txid_snapshot_xmin | backend_xmin |  ?column?
> ------------------+--------------------+--------------+------------
>  xxxxxxxxxx       |         6 957 042 833 |   2 662 075 435 | 4 294 967 398

I don't think the calculation you're doing here is correct.
backend_xmin is an xid (max 2^32-1), whereas txid_snapshot_xmin returns
an xid *with* epoch (max 2^64-1). What you're measuring here is simply
the fact that the xid counter has wrapped around.

Greetings,

Andres Freund

Re: backend_xmin in pg_stat_replication

От
Torsten Förtsch
Дата:
Thanks a lot! So, the correct calculation is like this:

select application_name,
       txid_snapshot_xmin(txid_current_snapshot()),
       backend_xmin::TEXT::BIGINT,
       (txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT)%(2^32)::BIGINT
  from pg_stat_replication;

 application_name | txid_snapshot_xmin | backend_xmin | ?column?  
------------------+--------------------+--------------+----------
 xxxxxxxxxx       |         6960964080 |   2665996642 |      142


That makes more sense.

On Mon, Oct 1, 2018 at 5:32 PM Andres Freund <andres@anarazel.de> wrote:
Hi,

On 2018-10-01 12:20:26 +0200, Torsten Förtsch wrote:
> if I understand it correctly, backend_xmin in pg_stat_replication is the
> xmin that's reported back by hot_standby_feedback. Given there are no
> long-running transactions on the replica, I presume that value should be
> pretty close to the xmin field of any recent snapshots on the master. This
> is true for all my databases but one:
>
> select application_name,
>        txid_snapshot_xmin(txid_current_snapshot()),
>        backend_xmin::TEXT::BIGINT,
>
>  txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT
>   from pg_stat_replication;
>
>  application_name | txid_snapshot_xmin | backend_xmin |  ?column?
> ------------------+--------------------+--------------+------------
>  xxxxxxxxxx       |         6 957 042 833 |   2 662 075 435 | 4 294 967 398

I don't think the calculation you're doing here is correct.
backend_xmin is an xid (max 2^32-1), whereas txid_snapshot_xmin returns
an xid *with* epoch (max 2^64-1). What you're measuring here is simply
the fact that the xid counter has wrapped around.

Greetings,

Andres Freund