Thread: Proposal: Expose oldest xmin as SQL function for monitoring

Currently there's no good way that I'm aware of for monitoring
software to check what the xmin horizon is being blocked at. You can
check pg_stat_replication and pg_replication_slots and
txid_snapshot_xmin(txid_current_snapshot()) and so on, but that list
can grow, and it means monitoring setups need to update any time any
new feature might hold another snapshot and expose it in a different
way.

To my knowledge the current oldest xmin (GetOldestXmin() if I'm not
mistaken) isn't exposed directly in any view or function by Postgres.

Am I missing anything in the above description? And if not, would
there be any reason why we would want to avoid exposing that
information? And if not, then would exposing it as a function be
acceptable?

Thanks,
James

James Coleman <jtc331@gmail.com> writes:
> To my knowledge the current oldest xmin (GetOldestXmin() if I'm not
> mistaken) isn't exposed directly in any view or function by Postgres.

You could do something like

select max(age(backend_xmin)) from pg_stat_activity;

though I'm not sure whether that accounts for absolutely every process.

> Am I missing anything in the above description? And if not, would
> there be any reason why we would want to avoid exposing that
> information? And if not, then would exposing it as a function be
> acceptable?

The fact that I had to use max(age(...)) in that sample query
hints at one reason: it's really hard to do arithmetic correctly
on raw XIDs.  Dealing with wraparound is a problem, and knowing
what's past or future is even harder.  What use-case do you
foresee exactly?

            regards, tom lane

On 2020-Apr-01, Tom Lane wrote:

> James Coleman <jtc331@gmail.com> writes:
> > To my knowledge the current oldest xmin (GetOldestXmin() if I'm not
> > mistaken) isn't exposed directly in any view or function by Postgres.
> 
> You could do something like
> 
> select max(age(backend_xmin)) from pg_stat_activity;
> 
> though I'm not sure whether that accounts for absolutely every process.
>
> > Am I missing anything in the above description? And if not, would
> > there be any reason why we would want to avoid exposing that
> > information? And if not, then would exposing it as a function be
> > acceptable?
> 
> The fact that I had to use max(age(...)) in that sample query
> hints at one reason: it's really hard to do arithmetic correctly
> on raw XIDs.  Dealing with wraparound is a problem, and knowing
> what's past or future is even harder.  What use-case do you
> foresee exactly?

Maybe it would make sense to start exposing fullXids in these views and
functions, for this reason.  There's no good reason to continue to
expose bare Xids to userspace, we should use them only for storage.

But I think James' point is precisely that it's not easy to know where
to look for things that keep Xmin from advancing.  Currently it's
backends, replication slots, prepared transactions, and replicas with
hot_standby_feedback.  If you forget to monitor just one of these, your
vacuums might be useless and you won't notice until disaster strikes.


Maybe a useful value to publish in some monitoring view is
RecentGlobalXmin -- which has a valid value when reading a view, since
you had to acquire a snapshot to read the view in the first place.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, Apr 1, 2020 at 5:58 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> On 2020-Apr-01, Tom Lane wrote:
>
> > James Coleman <jtc331@gmail.com> writes:
> > > To my knowledge the current oldest xmin (GetOldestXmin() if I'm not
> > > mistaken) isn't exposed directly in any view or function by Postgres.
> >
> > You could do something like
> >
> > select max(age(backend_xmin)) from pg_stat_activity;
> >
> > though I'm not sure whether that accounts for absolutely every process.

That doesn't account for for replication slots that's aren't active, right?

> > > Am I missing anything in the above description? And if not, would
> > > there be any reason why we would want to avoid exposing that
> > > information? And if not, then would exposing it as a function be
> > > acceptable?
> >
> > The fact that I had to use max(age(...)) in that sample query
> > hints at one reason: it's really hard to do arithmetic correctly
> > on raw XIDs.  Dealing with wraparound is a problem, and knowing
> > what's past or future is even harder.  What use-case do you
> > foresee exactly?

So the use case we've encountered multiple times is some (at that
point unknown) process or object that's preventing the xmin from
advancing, and thus blocking vacuum. That kind of situation can pretty
quickly lead to query plans that can result in significant business
impact.

As such, it'd be helpful to be able to monitor something like "how old
is the current xmin on the cluster". Ideally it would also tell you
what process or object is holding that xmin, but starting with the
xmin itself would at least alert to the problem so you can
investigate.

On that note, for this particular use case it would be sufficient to
have something like pg_timestamp_of_oldest_xmin() (given we have
commit timestamps tracking enabled) or even a function returning the
number of xids consumed since the oldest xmin, but it seems more
broadly useful to provide a function that gives the oldest xmin and
allow users to build on top of that.

> Maybe it would make sense to start exposing fullXids in these views and
> functions, for this reason.  There's no good reason to continue to
> expose bare Xids to userspace, we should use them only for storage.

This would be useful too (and for more reasons than the above).

> But I think James' point is precisely that it's not easy to know where
> to look for things that keep Xmin from advancing.  Currently it's
> backends, replication slots, prepared transactions, and replicas with
> hot_standby_feedback.  If you forget to monitor just one of these, your
> vacuums might be useless and you won't notice until disaster strikes.
>
>
> Maybe a useful value to publish in some monitoring view is
> RecentGlobalXmin -- which has a valid value when reading a view, since
> you had to acquire a snapshot to read the view in the first place.

If we went down that path what view do you think would be best -- an
existing one or a new one?

I go back and forth on whether this is best exposed as a monitoring
oriented view or as part of the suite of txid functions we already
have that seem to have broader applicability.

James

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2020-Apr-01, Tom Lane wrote:
>> The fact that I had to use max(age(...)) in that sample query
>> hints at one reason: it's really hard to do arithmetic correctly
>> on raw XIDs.  Dealing with wraparound is a problem, and knowing
>> what's past or future is even harder.  What use-case do you
>> foresee exactly?

> Maybe it would make sense to start exposing fullXids in these views and
> functions, for this reason.  There's no good reason to continue to
> expose bare Xids to userspace, we should use them only for storage.

+1, that would help a lot.

> But I think James' point is precisely that it's not easy to know where
> to look for things that keep Xmin from advancing.  Currently it's
> backends, replication slots, prepared transactions, and replicas with
> hot_standby_feedback.  If you forget to monitor just one of these, your
> vacuums might be useless and you won't notice until disaster strikes.

Agreed, but just knowing what the oldest xmin is doesn't help you
find *where* it is.  Maybe what we need is a view showing all of
these potential sources of an old xmin.

            regards, tom lane

On Thu, Apr 2, 2020 at 12:13 AM Craig Ringer <craig@2ndquadrant.com> wrote:
>
>
>
>
> On Thu, 2 Apr 2020 at 07:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> > On 2020-Apr-01, Tom Lane wrote:
>> >> The fact that I had to use max(age(...)) in that sample query
>> >> hints at one reason: it's really hard to do arithmetic correctly
>> >> on raw XIDs.  Dealing with wraparound is a problem, and knowing
>> >> what's past or future is even harder.  What use-case do you
>> >> foresee exactly?
>>
>> > Maybe it would make sense to start exposing fullXids in these views and
>> > functions, for this reason.  There's no good reason to continue to
>> > expose bare Xids to userspace, we should use them only for storage.
>>
>> +1, that would help a lot.
>>
>> > But I think James' point is precisely that it's not easy to know where
>> > to look for things that keep Xmin from advancing.  Currently it's
>> > backends, replication slots, prepared transactions, and replicas with
>> > hot_standby_feedback.  If you forget to monitor just one of these, your
>> > vacuums might be useless and you won't notice until disaster strikes.
>>
>> Agreed, but just knowing what the oldest xmin is doesn't help you
>> find *where* it is.  Maybe what we need is a view showing all of
>> these potential sources of an old xmin.
>
>
>  Strongly agree.
>
> https://www.postgresql.org/message-id/CAMsr+YGSS6JBHmEHbxqMdc1XJ7sobDSq62YwaEkOHN-KBQYr-A@mail.gmail.com
>
> I was aiming to write such a view, but folks seemed opposed. I still think it'd be a very good thing to have built-in
asPg grows more complex.
 

Did you by any chance prototype anything/are you still interested?

This sounds extremely valuable to me, and while I don't want to
resurrect the old thread (it seemed like a bit of a tangent there
anyway), in my view this kind of basic diagnostic capability is
exactly the kind of thing that *has* to be in core, and then other
monitoring packages can take advantage of it.

Finding things holding back xmin from advancing is easily one of the
single biggest operational things we care about. We need to
investigate quickly when an issue occurs, so being able to do so
directly on the server (and having it be up-to-date with any new
features as they're released) is essential. And it's also one of the
areas where in my experience tracking things down is the hardest [with
capabilities in core]; you basically need to have this list in your
head of all of the things you need to check.

James

Hi,

On 2020-04-01 19:57:32 -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > On 2020-Apr-01, Tom Lane wrote:
> >> The fact that I had to use max(age(...)) in that sample query
> >> hints at one reason: it's really hard to do arithmetic correctly
> >> on raw XIDs.  Dealing with wraparound is a problem, and knowing
> >> what's past or future is even harder.  What use-case do you
> >> foresee exactly?
>
> > Maybe it would make sense to start exposing fullXids in these views and
> > functions, for this reason.  There's no good reason to continue to
> > expose bare Xids to userspace, we should use them only for storage.
>
> +1, that would help a lot.

I agree.


> > But I think James' point is precisely that it's not easy to know where
> > to look for things that keep Xmin from advancing.  Currently it's
> > backends, replication slots, prepared transactions, and replicas with
> > hot_standby_feedback.  If you forget to monitor just one of these, your
> > vacuums might be useless and you won't notice until disaster strikes.
>
> Agreed, but just knowing what the oldest xmin is doesn't help you
> find *where* it is.  Maybe what we need is a view showing all of
> these potential sources of an old xmin.

+1.  This would be extermely useful. It's a very common occurance to
have to ask for a number of nontrivial queries when debugging xmin
related bloat issues.

There's the slight complexity that one of the various xmin horizons is
database specific...

Which different xmin horizons, and which sources do we have? I can think
of:

- global xmin horizon from backends (for shared tables)
- per-database xmin horizon from backends (for local tables)
- catalog xmin horizon (from logical replication slots)
- data xmin horizon (from physical replication slots)
- streaming replication xmin horizon


Having a view that lists something like:

- shared xmin horizon
- pid of backend with oldest xmin across all backends

- database xmin horizon of current database
- pid of backend with oldest xmin in current database

- catalog xmin of oldest slot by catalog xmin
- name of oldest slot by catalog xmin

- data xmin of oldest slot by data xmin
- name of oldest slot by data xmin

- xid of oldest prepared transaction
- gid of oldest prepared transaction
- database of oldest transaction?

- xmin of oldest walsender with hot_standby_feedback active
- pid of oldest ...

would be awesome. I think it'd make sense to also add the database with
the oldest datfrozenxid, the current database's relation with the oldest
relfrozenxid.

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> On 2020-04-01 19:57:32 -0400, Tom Lane wrote:
>> Agreed, but just knowing what the oldest xmin is doesn't help you
>> find *where* it is.  Maybe what we need is a view showing all of
>> these potential sources of an old xmin.

> +1.  This would be extermely useful. It's a very common occurance to
> have to ask for a number of nontrivial queries when debugging xmin
> related bloat issues.

> Having a view that lists something like:

> - shared xmin horizon
> - pid of backend with oldest xmin across all backends

I was envisioning a view that would show you *all* the active processes
and their related xmins, then more entries for all active replication
slots, prepared xacts, etc etc.  Picking out the ones causing trouble is
then the user's concern.  If the XID column is actually fullXid then
sorting, aggregating, etc. is easy.

The point about database-local vs not is troublesome.  Maybe two
such views would be needed?

            regards, tom lane