Обсуждение: 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, 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.
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 as Pg grows more complex.
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