Michael: Hello, and welcome to Postgres film, a weekly show about all things Postgres.
I am Michael founder of PG mustard, and this is my cohost Nicola, founder of Postgres AI.
Hey Nilay and what we are talking about today.
Nikolay: Hi, Michael.
Uh, Today we are going to talk monitoring.
And I think, uh, we are touching very, very, wide wide areas, uh, of Postgres.
And probably we will revisit each one of them trying to go in some, uh, specific sub areas.
Right.
But every project every project should have.
Michael: Well, do you mean?
So let's check that first.
So we naturally for large enterprises, we, we definitely want monitoring.
We definitely want database specific things being
Nikolay: Sometimes couple, couple of tools.
So like sometimes I saw big projects using three monitoring and all three tools were monitoring Postgres.
None of them were perfect to be enough alone.
Right?
So we needed to combine parts from three places.
It's it happens.
I think by, by the way, it's not a problem.
If you have a couple of tools used, if you know that overhead is low, right?
Michael: Yeah, absolutely
Nikolay: we are talking about Pogs monitoring.
This is huge topic.
And, my main statement is, there is no good product developed.
Michael: no perfect product maybe or no good product.
Nikolay: if even, okay.
Okay.
Good.
Is maybe, okay.
No great products.
I don't see great products because, , I understand what should be present in any Postgres monitoring.
And it's, it's not my point of view.
We, a couple of years ago, we started some community work.
So we started to discuss various tools.
We, we had several, sessions in Russian community.
We had several sessions reviewing various tools, both open source and commercial, and then we created called monitoring checklist, post
this monitoring checklist, collecting ideas what should be present in monitoring and what should be, must have, or nice to have and so on.
And, uh, then some people, outside of rush community joined as well.
And, and eventually we, I think we, we've got very good understanding what should be present like a hundred
percent, uh, if you want your monitoring be very useful.
And, I don't see any product, commercial or open source or maybe even in house, sometimes in house products I see very good ones, but none
of them meet these criteria even 80%, they usually it's below 50.
Michael: So, yeah.
So we are talking about things that you consider probably almost a must have for most projects that people care about the uptime of.
Making sure they have each of those covered in, is it monitoring, monitoring and alerts?
How, like, do you count that as the same thing?
Nikolay: So well alerts, it's like a, a slightly different topic.
I'm talking about troubleshooting sessions when we open monitoring and try to understand what's happening, what's wrong because
we have some complaints from users or from our coworkers.
And we.
Something wrong with POS happening, and we need to understand what exactly is wrong.
And I see two big areas which must present in any monitoring.
First area is of course we should have physical metrics like CPU, memory, disco network.
It should be covered.
And almost always it's it's covered.
And it's like, it's separate.
PSG specifics.
And here we can recall, uh, Alex Soki P starts do deaf, this interactive picture, which shows you architecture of PSGs and
what system views and extensions are, or tools are responsible for monitoring particular part of Postgres architecture.
It's it's a great tool.
We will provide link in description I'm sure.
So, this should be present in monitoring.
And this first part, I call dashboard number one.
The goal of dashboard number one is like you have like 30 or maximum 60 seconds and you need to perform very wide
and very shallow overview of all Pogo components, all Pogs, uh, , areas and understand which area looks not well.
So we need to go deeper using other dashboards probably, or maybe manually looking at Pogs.
So, and second component is, is query monitoring.
Query monitoring it's like not a king, it's a queen of, of, uh, observability, right?
It should be present always.
It's it's this whole different topic.
It should be present in any monitoring.
And again, I don't see all like some, some systems.
Improving for example, data though currently is very well improving in this area, particularly, and by the way, they do it for all databases.
Not only for PSGs.
I just see how well they are compared to two years ago, but they have big mistakes as well.
Like not mistakes, but disadvantages like missing parts, which don't allow me to work with it, but probably we
should, postpone a discussion about, query analysis.
It's like maybe different talk, but let's focus on this first, like wide and shallow solo dashboard.
Like I want everything quickly help of each component.
Michael: So the use case is, my pager's just gone off or I've got a notification or, or a user mentioned something that doesn't sound good.
And I want to look at something and see, where is the problem?
Like what, where do I even start
Nikolay: Right.
So either some people say there are errors related to Pogo or some performance degradation and we want like to have 10
or like dozen of like 12 metrics present on single dashboard.
So we quickly understand are we okay compared to like one hour ago or one day ago or one week ago.
Or we are not okay in particular areas.
So let's dive deeper.
So let's probably quickly have some overview of these metrics because I have a list and I also, we can attach this list to our episode, right?
Michael: Yep.
So what's the top of your list?
Nikolay: At the top of the list, there's, two major things which represent the behavior of any multi-user system, throughput and Latin.
Not only much user, but where network is also, involved.
So throughput in throughput, uh, if we talk about databases per particularly can be measured in several ways, but two ways are most interesting to us.
It's TPS and QS transactions per second and queries per second.
And unfortunately posts doesn't allow to measure the second one really well.
TPS are present in PPG star database or PPG database.
I always mix it exact commit and exact rollback.
We observe these two numbers and some of how fast is increasing.
This is our TPS.
Michael: Or decreasing, right.
If it's, if there's some problems where it
Nikolay: It cannot decrease it's accumulative.
It's just a counter of committed transactions and rolled back transactions.
Michael: Sorry.
I thought you were talking about TPS still.
Nikolay: So right.
What can decrease if we divided by the duration of like, we, we need two snapshots as always, right.
And we need to understand how many seconds between them.
So we divide and of course, this like speed can go up and down.
TPSs can go up and down, but Q PS, unfortunately, Postgres, system use don't.
And, uh, usual trick is to use statements, but it's not exact number, unfortunately, because statements, as I remember do
max, uh, statements dot max parameter by default is 5,000.
if I'm not mistaken, it can be tuned to 10,000 or hundred.
It means that we don't register all queries.
So we probably see only the tip of the iceberg and QPS can be wrong if we use this approach.
If we use calls metric from PJI segments.
When I have doubts, I usually usually like most of the cases we have P bouncer, so I check P bouncer logs and it reports Q PS like fair
number of QPS, but this is like maybe already too deeper about it.
Michael: Yeah, let's focus on the things we can, uh, can measure in Postgres.
Nikolay: Right.
This, this is throughput, Latin seeds, query duration.
Again, usually it's measured from PC statements timing.
And we like why it's important because we, we need to understand, for example, if QPS and TPS dropped significantly, maybe it's
just a problem with application Aeros on application cost.
Uh, like our load dropped.
Maybe it's a problem related to like, uh, CPU a hundred, all CPUs are a hundred percent who have situation or
this situation on that's why we process fewer T TPS.
And latency of course, very important.
And I see in many cases we don't have it in monitoring.
These two metrics are.
It's it's our general health of our database should be always like we have problem let's check CPS and latency, average duration of queries.
So next, , very, very, very, very important.
And I don't also see like more than 50 systems are not good with, with this it's connection monitoring.
My ideal is just one single stacked graph of connections, active item, transaction idle for, I don't remember.
So, but these three already tell them, tell everything.
Right.
So active.
We process something.
If active goes above number of CPU, it's already bed sign.
So if you have, uh, 12 course, and we see active 20, well probably already too much work to do for this server.
And probably we have already huge degradation in performance.
It's not always so, but in many cases, so, adults and connections, adult transaction connections also very important, especially before Pogs.
13 or 14.
were optimizations 14.
Right?
Right.
So I saw, sometimes people say, okay, we have a couple of thousand more idol connections.
Don't worry.
They are idol.
But you know, overhead is big and I think Andre had a very good couple of posts.
Also.
We should provide links.
Right.
explaining overhead
Michael: I think he did a lot of the work in 14.
I think it was.
Nikolay: optimizing.
It, it was related not to memory consumption as I remember, but, uh, how work with snapshots is organized.
Usually people think a couple of thousand connections give us a lot of memory overhead.
No, it's more about how work with snapshots organized.
But what I like about stack graphs is that we can quickly understand the total as well.
Right?
And, uh, sometimes people split to multiple charts, multiple graphs.
It's also fine, but, uh, I like to see connection and overview in one place.
So, and, and of course I don't, I don't in transaction.
sessions or connections.
Connections sessions is the same, basically.
They are, very dangerous sometimes because if application started transaction and went to talk to some external API, for example,
or to do some other work, it's, it can be very dangerous.
So we should also understand like, we should have some threshold and not, not allow system to go.
Michael: So, yeah.
So at this point, I guess we're talking about long transactions.
Nikolay: No, well, sometimes these things correlate, but not always because we can have, for example, at each moment we have 200 in transaction sessions.
Right.
But duration of each transaction is less than one.
It might happen.
Very brief transactions, but they do something and keep connections, idle it also possible.
But next item is long transactions actually.
And, this is actually a big topic as well, because usually long transactions have two dangers.
One danger is to lock something and block others.
It's one point, another point is, uh, disturb auto vacuum work.
But in the letter we should talk about not long transactions, but about XME horizon, it should be different.
And I didn't see it.
Never like no money talking distinguishes these two things yet.
Of course I didn't see all existing systems of course, but over the last couple of years, observing various systems, like dozens of them.
Michael: Should we talk about that then?
Straight away.
So the X Xmen horizon being to avoid, transaction ID wraparound, which is, you know, there've been some famous blog posts that I can include on that.
Nikolay: Right, transaction idea around is one from horizon being stuck in the, in very like past, but also accumulating
a lot of, deads and then it converts to blo also a problem.
Right.
Michael: See previous episode.
Nikolay: Right, right, exactly.
We discussed it already, but, again, like simple graph.
What is the maximum age of the longest action right now?
Excluding vacuum because actually regular vacuum it's by the way, it's tricky.
a regular vacuum doesn't hold extreme horizon.
It doesn't block anyone except we've analyzed it.
Hold XME horizon.
And if the vacuum is working to prevent transaction idea around it can block others.
So in each area.
There are interesting, ifs, right?
If then, if then, so it's quite difficult to build ideal monitoring of course.
But anyway, like I want to have a graph.
do we understand the maximum edge of transaction?
Right.
usually people say, I don't know.
Michael: what kind of number are you worried about there?
Nikolay: right, right.
In TP systems.
And by the way, we forgot to mention that we, again, discuss TP systems monitoring for them.
Right.
Because for analytical systems, things are different.
for OTP, I would like to have everything below, ideally , under one second.
again, it's related to human perception, which is like we can distinguish 200 milliseconds roughly.
So one second feels slow, but of course, , sometimes we need slightly longer transactions, a few seconds, but transaction one
minute, if it can block someone else, it's already a problem.
We should split.
To batches, but,
uh, we, we discussed it briefly last time.
If you create an index, uh, unfortunately you hold the extreme horizon and of course, if your table is big, need to, to spend
like sometimes an hour or maybe even more to build an index.
And that's why partitioning is needed again, to have this time say.
Michael: So we are, about halfway through our, nearly halfway through our, uh, dashboard.
Nikolay: Good.
So next thing is, um, , the ratio of error.
So it's can measure.
We, we already discussed this exact, , from PTA database, exact commit exec roll bank.
How many transactions are committed?
How many transactions are canceled and, uh, we can have a ratio.
To understand the percentage of successful transactions.
And it's interesting sometimes, I see like system produces like only 50%, or even fewer successful transactions.
But developers said this is an intentional.
this is by design and it triggers interesting questions.
What negative consequences can be if application uses roll back too often.
It's like separate
Michael: But the main reason you want this ratio is to see if it changes from your baseline.
So if you've got normally a very high ratio, you just want to tell
Nikolay: Right.
99%.
And suddenly we have 70%, obviously it means we have a spike of errors.
So we need to go to logs probably, and understand which errors, or if you use, extension called log errors, you can have counters, right inside database.
So you can select.
Aggregated by error type.
So next thing is, uh, transaction idea up around, uh, separate metric, here people always like, okay, not always
in 90% of cases, people forget about not exact, , idea up.
they just don't check it.
Like it's more exotic, like transaction idea around is exotic, but when it happens, it's a big disaster, but multi exec idea up
around it's even more, it's even like very, very rare, very unusual,
Michael: Is this the thing that you mentioned in the GitLab, blog post about, sub transactions?
Nikolay: uh, briefly.
Yes briefly there is connection to it.
Yes, because, , if we use select for, share and sometimes select for update, we might have, increasing, uh, multi, exact ideas and
risks of, uh, well, if we use foreign keys, we have not exact ideas.
That's it?
Michael: Of course.
So are we talking about two graphs here, then one for like, what do we actually need for our dashboard?
Nikolay: It can be one, but just the idea is we, we should not forget about this second ID and it also can be wrapped around.
It has the same risk.
So we should monitor both risks and, uh, alert, when it's already.
Above threshold and so on.
Okay.
Then, the health of replication, again, like for this dashboard, we need only brief analysis.
Like, do we have at least one standby, which is legging physical or logical?
We, we should define our thresholds and.
That's it like if we see a spike or leg also a problem you can get out of disc space event or something not good.
Then, a few things on the left.
By the way I realized we, in this list, we will provide this list.
As I've said, we don't have, vacuum behavior autobi behavior and we don't have checkpoint or, and visual writer behavior.
We have it, uh, in our systems.
We have it on D.
Two, but I think we probably should move it to dashboard one as well, because these two big components of Pogo, vacuuming and chick pointing.
Right.
So we should have it here.
Michael: before you move on this one, I see you also had unused replication slots, which I thought was really interesting.
I've heard of some issues where, like if, if you've got a static number of those, if, if it decreases, if, if something
ends up using one of those replication slots that you weren't expecting, that's that could be sign of a problem as well.
Right?
Nikolay: The problem is that like, right.
So we don't have leg per se here, but, uh, if slot is unused, it's accumulating.
so it's easy to be out of this space soon.
So, and use the, uh, slots are dangerous, definitely, but it can be, everything can be here combined on one or couple of graphs.
So, but as I've said, we want to keep this, dashboard quite small so we can quickly overview everything.
And then like, after application, I'd like to talk about legs related to archiving.
Very often, people don't have it.
They don't monitor the leg.
Like how many walls are not yet archived?
Why is this important?
If you try to stop positives, we restart or just stop.
And it has a lot of archived.
you know, like when we shut down POG, there is so called implicit shut down checkpoint, and the same way PSG tries to archive them mold.
So trans archive command of all painting walls and can take our, if we accumulate a thousands of walls, it happens.
You wait
Michael: Well, it's not just if you try, if you want to upgrade or something, it could also be if there's a crash, right.
That it's how, how long is it gonna take you to recover?
Nikolay: Well, recovery is different.
If it crashed, it'll recover quite soon.
It just, replay, redo, what's needed.
And that's it.
I'm talking about like, intentional restart or, or shut down, for example, if, uh, Petroni wants to perform switch or, or
failover due to some reasons it tries to shut down the primary.
until some version not long ago, because I told KKI about it.
And it was a surprise because we had it on production in some system.
So Petro failed to perform fell over because a lot of walls were, were, painting to be archived.
and, uh, co KKU can fix it, in, in the recent versions of Petron upgrade, upgrade.
It's quite, it's quite new thing.
Uh, quite new bug fix.
Well, it's not bug fix.
It's like, uh, improvement, Petron.
Doesn't wait, and like tries to perform fell over.
There is a trade door because what what to do with these walls.
I don't remember details there because if we don't archive those, this drops our, , Dr.
Strategy disaster recovery strategy, right.
We need to keep backups in good shape.
So this is interesting question as well, but, , anyway, monitoring should have this number of painting walls, or also maybe the rate of our kit.
how, how, like how many walls we archive per second, for example, it's very, very interesting metric as well, to understand like our.
Michael: You've got that next on the list.
Nikolay: Ah, okay.
Yeah.
Okay.
Well generation rates and, uh, yeah, as I recently learned, if you have few walls per second, probably you won't be able to
use logical replication because wall center will, will be, will set rate single CPU, , core and finally locks and deadlocks.
Uh, well maybe they should not go together because.
There's different things.
Right.
But, and locks, of course we like sometimes people draw both, um, exclusive and Sherlocks there, there are many kinds of locks here.
We talk about, , like heavy locks, not lightweight locks or latches and so heavy locks.
So we are locking rows and locking, tables.
And here, sometimes people try to draw everything, but we should focus on, uh, maybe only on exclusive locks,
which can be reasonable for blocking others first reason.
Well, it's also like there are many , problems there.
This, this chart alone can be difficult to implement properly.
But, uh, anyway, we, we want to have something, I, I just saw sometimes application engineers.
They say, oh, we had a spike of locks.
You look at it or you have had spike of share locks.
So it's okay because you had some deployment it's normal.
It's not a problem.
So.
this chart is also like interesting.
What, what should be there?
And that locks, of course, we want to understand how many deadlocks, so many other things are missing here, but I'm, I'm not a big fan of
combining physical and this, this logical, like, for example, in data, doc, if you check database monitoring, you will see CPU combined with TPS.
It's a mix of everything.
It's Not not right.
Host stats.
I would like to see separately from database stats.
What do you think.
Michael: Interesting.
Well, I think in the early days I've seen a lot of teams get really far looking at application stats and then adding some database things into it.
They don't have some of these like real scaling issues, I guess.
Uh, I think a lot of people implement monitoring after a big issue after a big outage.
They do they think, ah, maybe we do need it now.
so probably people put this in, in general, uh, a little bit too.
but yeah, I can see, I can see an argument for keeping it where the application monitoring is, especially from the, the query performance
point of view, at least, especially from the performance monitoring.
Maybe not so much from the troubleshooting, we've had an outage perspective.
Nikolay: Right.
Well, well, again, we just didn't discuss here, uh, query analysis of it's another half an hour, at least it's
like, it's very interesting and very deep topic, but.
I also like, forgot to mention that, uh, we have, we took PG watch too, from cyber tech.
And, uh, I remember when we first, uh, reviewed it, it didn't feel well like strange dashboards to us.
Like very, very, like, Like you explain, oh, why, why not add this?
Why not add that?
But the fact that it's open source, good components like Grafana and so on.
Like, it can be, it can use Pogs and timescale.
So we.
Vitality from it team implemented, the, initially the set of dashboards following this philosophy.
So dashboard number one is for quick and very shallow troubleshooting, understanding which areas need further analysis dashboard.
Number two.
More interesting things for DBA dashboard.
Number three is query analysis and it's available.
So it's open source available.
And, uh, recently another contributor helped us to support, , timescale version.
So POG and timescale can be used as storage.
And I like to.
Like I encourage everyone to consider it as a second secondary monitor probably already have something like data doc.
Right.
Which is like usually, or, or anything else which usually like
Michael: APM.
Nikolay: like organization already adopted some tool.
For everything, not for databases, not for Pogo.
And of course it's worth having it everywhere because it's like unified solution, but, , it's missing a lot of things.
And I hope, uh, I convinced our listeners today that there are many things that should be present there.
So pitch watch two can be added like a secondary monitor.
Very gentle without big observer effect.
and that can provide a lot of insights for database experts and actually not, not only experts this TPS QPS plus latency, this, throughput plus
latency it's it's alone already, something very good, which many, systems
missing.
Michael: Yeah, absolutely.
So yeah, so I guess if you don't have some of these things, , maybe look into how, how much work it would be to add them
and it, hopefully it would save you or make it a lot easier.
Next time you have a big issue, to spot what's going wrong.
Nikolay: Right.
And if you develop, uh, your tool in, in house or your developer of monitoring this, uh, list, it's a product of community.
like several months of work we discussed, I discussed it on Twitter either during our sessions in Russian community.
And so on, this list was like, I think it's very, very useful for like checklist for everyone who wants to develop great progress monitoring.
So please use it as a reference.
What should be added.
Michael: Awesome.
Nikolay: I think it's, I think it will be useful, right.
Probably one day we will discuss query monitoring as
Michael: Yep.
Nikolay: I have something to say about it too.
Michael: L definitely part two coming.
Brilliant.
Well, thanks everyone for joining us again.
Uh, we really appreciate it.
Let us know if there's anything else you want to make sure we covered or anything you think we missed.
Uh, be very helpful.
And yeah, check out the show notes for, well, I I'll include links to all the tools we mentioned or, various things as well, including this list.
Nikolay: Yeah.
And thank you again for feedback feedback.
Very important thing to have Twitter and other places.
And please subscribe like and share, share, share everywhere in, in your social networks and groups where you discuss engineering and databases.
And so,
Michael: Wonderful.
Well, thank you, Nick.
Eli.
good to see uh, speak to you next
Nikolay: Thank you Michael, till next time.
Bye.
Michael: Cheers.
Bye.