Michael: Hello and welcome to PostgresFM, a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard, and this is my co-host,
Nikolay, founder of Postgres.ai.
Welcome back, Nikolay.
How are you getting on and what would you like to talk about
today?
Nikolay: Hi, Michael.
First of all, thank you for keeping the, how is it called, the
ball rolling, or how to say, long time no see or long time no
hear.
Yeah.
Thank you for very interesting interviews.
I liked listening to them while I was slightly off, but maybe
it's time to return to our regular format or have a mix, I don't
know what we will decide next, but today we have a regular format,
right?
Michael: Yeah.
Nikolay: Yeah, and the topic I brought this topic is overhead
of observability tools, the most popular ones.
Well, 1 is the most popular one, pg_stat_statements.
And usually people, like I remember exactly when we didn't have
pg_stat_statements, we had only log-based analysis.
We didn't have wait events, didn't have pg_stat_statements like
15 years ago, right?
And we only had log-based analysis and there was a tool written
in PHP called pgFouine, and then another tool replaced it, written
in Perl called pgBadger.
And the idea was, like, people always, like, first thing to figure
out that, oh, usually we only see only a tip of the iceberg only
we see only like some queries which are the slowest ones but
you cannot properly perform like full holistic analysis if you
don't see the fast queries, because fast queries might be consuming
even more resources than slow queries.
So what people did usually, they experienced DBAs usually said,
okay, I'm going to switch off all query logging for a few minutes
to collect everything and then analyze.
In very many cases it worked well.
Yes, there is an observer effect because we probably put our
Postgres down completely if we log all queries, but not always.
Sometimes it depends.
So yeah, this is quite understandable.
But then pg_stat_statements were created and some DBAs, I remember,
we were saying, oh, you know, like we still need to study its
overhead.
And then somehow there was like, there were some benchmarks,
I don't remember any benchmarks I would say I trust them.
But I remember many experienced folks started saying, oh, you
know, yes, there is overhead, but it's below 10%.
And since this thing is measuring everything, it's observing
the whole workload, it's worth keeping it enabled.
Let's have it enabled.
I remember also there was some number, like, people, many people
saying, you know, pg_stat_statements overhead is 7%.
It's kind of strange, but kind of 7%.
7%?
7%.
I don't remember any benchmarks that proved it, but I remember
it was like the consensus was below 10%, so we are all good.
If we try to find a good benchmark from these early days of
pg_stat_statements, it would be great.
I don't think it exists.
But honestly, we as a community should perform good benchmarks
there.
I don't remember that.
It doesn't mean they don't exist, of course, but...
Michael: I did a little bit of searching beforehand because I
was interested in if there was any up-to-date ones as well.
I couldn't find anything great, but there were a few trustworthy
sources.
One was PG Analyze, a commercial monitoring tool that uses pg_stat_statements.
statements.
In their frequently asked questions, they gave an estimate of
approximately 1% of CPU time, which I thought was interesting
that they're telling their customers.
Anyway, as we're going to discuss, this is all very dependent
on workload, right but at least that's interesting and
I trust them enough to say that's probably based on some tests
on their side even if I didn't see the actual benchmarking, and
then the other things I've seen are a series of, well, there's
a question on the DBA Stack Exchange and somebody's quoting various
Postgres authors and a benchmarking series.
One that was like a more realistic workload that was about 0.5%
measured overhead and another that was a more stress test that
measured it at about 10%.
So those are numbers, but that's a wide range of different numbers.
And as we're going to discuss later, you've done some work that
shows even that it can be very different, which is cool.
Nikolay: I hope by the end of this episode, people will start
having some ideas why this range can be so wide.
But I can imagine like 0.5% or 10%.
In both cases, it can be so.
I mean, it depends on workload, actually.
Very well depends on the workload.
And also not only about workload, we will talk about it later,
right?
So, yeah, okay.
But when I say overhead, of course I mean CPU overhead, first
of all, because we don't think about disk I/O or disk usage, disk
space usage here, network, nothing like that, only pure CPU overhead.
Michael: By the
Nikolay: way, when guys like RDS and others, probably inheriting
this from Oracle, when they consider wait event being null in
pg_stat_activity as CPU, marking it as green, CPU green in performance
insights.
Do they mean other wait events, all of them are not CPU?
I don't understand this.
Michael: I don't know.
Nikolay: Because many of, for example, lightweight locks, spin
locks, for example, they are purely CPU.
Michael: I know we have several listeners from AWS, so maybe
they can let us know.
Nikolay: We have listeners, yeah.
And on the other hand, if you say CPU instead of null, null means
unknown.
And according to PostgreSQL documentation and source code, it means
no wait.
In reality, it means either really no wait and maybe some kind
of CPU work, but also in many cases it means a wait event which
is not yet created and code is not covered with this.
For example, PostgreSQL 16, we recently had some benchmarks and
we saw wait event which you see in PostgreSQL 16 but you don't
see it in PostgreSQL 14 because it was not yet created there.
So I'm thinking, okay, it means in performance insights on RDS
and I think in many other systems maybe in CloudSQL as well
or this PgBouncer ad hoc tool written in Java, it also likes
to use green color and say this is CPU, but it's actually not
CPU, it's null.
Because CPU doesn't exist in the list of wait events in pg_stat_activity.
of Activity.
So if you mark it CPU for 14 because it's what was null, but
then you start distinguishing it for 16.
Something is not right here.
So I'm asking, is it really CPU?
So interesting question, but it's slightly off topic.
Back to PgStatStatements.
Let's talk about benchmarks we had.
So, the idea was we got some credits from Google Cloud.
And it's great, this year we will be performing, I mean, we,
I mean, Postgres.ai team, we will be performing a lot of benchmarks.
I'm going to do a lot of post...
We already had a lot of benchmarks, I think thousands of them
if you count each iteration, and hundreds of them if you count
the whole benchmark consisting of many iterations.
So we were curious how many TPS we can squeeze from PostgreSQL
16 on various big machines, Intel, AMD, and there was a good
article from 2016 from Alexander Korotkov, when both PostgreSQL
and MySQL, I remember, teamed up.
It was interesting.
I think, I don't remember if somebody from Percona was also working
on MySQL, the goal was to demonstrate that both systems can show
million TPS.
And during that work, some contention issues were improved, fixed
in Postgres.
So, Postgres reached a million TPS on some strong machines.
And I like that benchmark because it's simple, it's pgBench,
select only.
So, it's only selects, very simple ones.
So we just repeated the same benchmark.
And as usual for such benchmarks, it's actually stress testing,
because you are exploring this edge.
It's not what you need to do for your application, for example,
unless you're on purpose studying it.
I hate this behavior being default in pgBench.
It should not be so, because it's provoking to perform stress
tests instead of regular load tests and exploring normal situation,
for example, 25 or 50% of CPU load.
But in our case, we just want to squeeze as much as we can.
So we did this, and I published a blog post about our new AI
bot and it has details and links to details how we did it.
So we took the newest Intel, fourth generation Intel scalable
Sapphire Rapids it's called, right?
And also 4th generation AMD EPYC, C3 and C3D instances on GCP.
One has, the biggest number is 176 vCPUs, and AMD has 360 vCPUs.
And both have more than a terabyte of RAM.
So it's insane, but we use spots.
AWS has spots, GCP has spot instances, it's cool.
You pay a couple of bucks for such an experiment, honestly.
Like it's not...
I realized even if we didn't have credits, I would probably pay
for myself, it's interesting to explore these things.
Because we just provision temporary machines for 1 or 2 hours
and spot means huge discount.
Yeah, so, this kind of experiment, it's a classic experiment for
stress testing.
You first start with 1 connection, then more and more connections,
and you control...
We chose Aleksandr Korotkov's approach.
In pgBench both -c and -j are the same.
So we start from 1, then we put 50.
With such huge machines, this step is reasonable.
Jumping to 50 right away.
Then 100, then 150, 200, and so on until, I think, 500.
Exceeding the number of vCPUs in both cases.
So since we use PostgreSQL cluster, which is a project, it's
open source project, It's a very good thing, Ansible kind
of set of playbooks to provision Patroni clusters with a lot
of things.
It's maintained by Vitaliy, who is working in my team.
So it's a great tool and it has a lot of things, including those
we over years discussed together and he just added into PostgreSQL
cluster.
So we used it and because of that we had a lot of observability
tools including those for query analysis, **pg_stat_statements**, **pg_stat_kcache**,
much less popular, and **pg_wait_sampling** for weight event analysis.
And then we saw a very strange picture.
Until 50 we grow in terms of TPS, kind of approaching a million
TPS, but then go down very quickly.
And on Intel we went down even more, but it's a different story.
And thanks to **pg_wait_sampling**, we saw that we have number 1
weight event is related to **pg_stat_kcache**.
So we immediately realized this is an observed effect from this
**pg_stat_kcache**.
For those who don't know, **pg_stat_kcache** is an additional extension
to **pg_stat_statements** extension which provides physical level metrics,
user CPU, sys CPU, real disk IO at physical level, context switches.
So it's very useful to understand real CPU usage, unlike this
where to when equals null, which I think is wrongly presented
in RDS performance insights.
This thing, trustworthy, we use it many years, a few big companies
use it as well.
It's not super popular, **RDS** doesn't have it, **CloudSQL** doesn't
have it, most managed Postgres providers don't have it.
I know **Yandex Cloud** has it.
I think they might be the
Michael: only ones that have it.
Nikolay: Maybe, But also 1 of the biggest e-commerce companies
we worked with also has it for many years and they have very
critical systems running on Postgres, so it's...
But in this case, overhead was so huge, like, what's happening?
And I immediately published it on Twitter and Vitaliy created
an issue in **pg_stat_kcache**, GitHub, a repository, and then maintainers,
they created a fix in 4 hours, I think.
So I think they expected something.
There was some additional lock which was removed in this fix
with the idea this lock is something old and it's not really
needed, but in our case it was slowing everything down when contention
is high, when many, many sessions fight...
Not fight, basically what's happening with **pgbench** you have by
default only 4 queries, right?
If you say select only dash uppercase S, It's only a single query,
only 1 select to the **pgbench_accounts** table, that's it.
In this case, like hundreds of sessions try to increment metrics
in a single record of **pg_stat_kcache**, right?
And due to that lock, which was removed in the latest version,
it was not good.
I mean, the more sessions we have, the biggest contention, the
bigger overhead is and we see it as we increase the number of
clients, number of connections and jobs, the dash C, dash J in
pgBench parameters, TPS go down.
So we have more, we have room, we have more vCPUs, right?
But we cannot use them properly because all of them try to execute
basically the same normalized query.
Parameters don't matter here.
Michael: Yeah, I pulled the chart up from your tweet and just
to give people an idea, at 50 clients it's about 550,000 TPS.
At 100 clients it does go up to about 700,000 TPS, but at 150
we're down below the 50 client rate and it's only 400,000.
Then it settles at about 200 clients and above, at about 300,000
TPS, which is less than we had at 50.
So it's a super interesting curve and cool to hear that you got
so quickly to the root cause and that the team was able to fix
it so quickly.
Nikolay: Yeah, in a few hours.
Well, let's split the story here into 2 paths.
First path is purely pg_stat_kcache and the second path is without
it, because when we saw this overhead we continued our discovery
of maximum TPS on modern Intel and modern AMD without pg_stat_kcache,
of course.
Because we didn't expect it to be fixed so quickly.
But when the fix was ready, we asked the bot to repeat this benchmark.
It was interesting to convince the bot that it's safe to download
fresh code from GitHub and compile it.
It hallucinated, saying it's against policy.
When I asked which policy, It said PostgreSQL policy.
So it was funny, like I went checking, do we have such policy?
I checked all our docs because the bot does know our documents.
So probably like, but it was just pure hallucination.
So then we convinced it, verified and indeed we saw that the
fix indeed resolves the problem and no more such overhead.
So it was good and next week the new release of pg_stat_kcache was
issued.
But interesting question, like, as I mentioned, I trust those
people, and also not just trust, I worked with some of them, and I
touched production systems with my hands, so I didn't see such
problems for years on production.
But here we saw obviously a very big overhead.
Why so?
Why have we survived with this on production?
This is an interesting question. Let's return to it after we
explore the second path.
Second path, okay, forget about pg_stat_kcache, we only have pg_stat
statements, pg_wait_sampling, we know the overhead of both is not
so big.
And then we just explore from 1 to 500 connections on both modern
Intel platform and modern AMD, 176 vCPUs on one and 360 vCPUs on
another.
And a huge surprise was Intel behaved...
Again, the same workload, pgBench -S, so select only, single query,
very simple, sub-millisecond latency.
Huge surprise was Intel behaves not well at all.
It behaves similar to what we had with pg_stat_kcache enabled.
But higher, yes, higher, it reached 1 million I think or so,
maybe slightly below it, and then went down.
While AMD, like this going down, even before you reach a number
of vCPUs, increasing number of connections and jobs in pgBench.
Bench.
It's not normal.
Some kind of problem, obviously.
While AMD also was not good, but it didn't go down.
It went down slightly, right?
It demonstrated almost a plateau.
Very different behavior.
And we started studying what's happening here, and obviously
the problem was pg_stat_statements in this case.
And we saw it, I think, from wait events by pg_wait_sampling, but
we also collected flame graphs and we obtained 2 very different
pictures for these platforms.
Everything is the same, Ubuntu 22.04, Postgres 16 latest version,
everything is like, some kind of tuning applied, kind of default
tuning we usually apply to new clusters under our control.
Nothing special, nothing fancy, but very different behavior.
And on flame graphs we could see that in case of Intel, these
few repits, we see that PgStatStatements_Nextval had a huge S_log function call consumed a lot of time.
And in case of AMD this call is much, like, smaller.
So in case of Intel it was like 75% of whole time spent by CPU
in FlameGraph.
Like, Why?
What's happening?
Something not normal at all.
And I talked to a few guys, talked to Andrey Borodin and Alexander
Korotkov, and interestingly, they both mentioned the idea that
probably pg_stat_statements needs sampling here.
Right?
Sampling.
So, it...
Michael: Yeah, so it's an interesting idea.
We don't currently have a parameter.
Yeah, we don't...
Like for a lot of the other things we have, like for logging,
for example, because there's such overhead to logging, we have
parameters for that kind of thing.
We can sample to only measure 1 in 10 or 1 in 100.
Yeah, yeah.
Well, I'm thinking actually, I know we've discussed this before,
but from log_min_duration_statement, yeah, I think since more recently,
but for auto_explain from a long time ago, for example, but the
the 75% makes sense as well just to give people an idea of the
exact numbers I've pulled up that chart as well On Intel with
pg_stat_statements.
It does get to very nearly a million tps without pg_stat_kcache
and then drops a little bit less rapidly but still it drops down
to 500,000 150 and then down to about 300,000 by the time you
get into the hundreds of clients whereas AMD with pg_stat_statements
continues to rise at 100 clients to about 1.2 million, gets above
1.5 million at about 150 clients, then seems saturated and it's
mostly a plateau, slight decline as you get more.
So that's about 5 times more by the time you get to, you know,
300,000 versus 1.5 million.
Starts to make sense that that's like 75% overhead, I guess.
Nikolay: Yeah, so in all cases when you reach some usual number
of vCPU, you go down.
But normally you go down slowly, right?
But the picture demonstrated on Intel, like very acute, like
acutely going down, right?
Going down very fast.
And this is not normal.
And of course, we, to confirm that PgSentinel Attachments involved,
and also to get numbers we wanted, we wanted big numbers, right?
When I posted it on Twitter, of course, people started liking
and reposting.
Like, with AMD, I think we've got, without Pages of Atonement,
just removing it, we've got 2.5 million TPS, right?
On AMD.
Michael: Nearly, yeah.
Nikolay: Almost, almost, right.
About 2 million TPS.
My tweet was 2 million TPS on PostgreSQL 16.
Right.
Well, yeah, It was funny to see the reaction from CEO of PlanetScale.
Like, it's not real workload.
Well, no, it's not real workload.
It's select-only.
It's purely synthetic workload.
We're just exploring some edge of what system can provide.
Of course, it's not realistic, it's some select, that's it.
Well, maybe there are some systems which need mostly this kind
of workload.
And this is an interesting question, because if they have such
workload, they suffer from bigger problems from pg_stat_statements settings.
I think this is the main idea of today's episode.
Look at your workload and understand the nature of it.
But obviously, CEO of PlanetScale is not interested in single-node
performance, because their main thing is sharding.
So single-node should not provide millions of TPS, right?
It's not normal, right?
Michael: It doesn't help the marketing.
Nikolay: Right, right.
Everyone realizes it's select only, everything is cached in memory,
but still, 2.5 million TPS, wow.
On a machine you can just rent easily.
Well, it's an expensive machine, I think.
If it's not spot, if it's normal, without any discounts, it's
above $10,000 per month.
So it's an expensive machine.
Yeah.
Of course.
But it's possible.
And this is just Postgres with minimal tuning, right?
It's good.
Like 2 million TPS, whoo-hoo.
Of course, I'm slightly sad comparing to 2016, how many years
have already passed?
Like 8?
3,000,000?
Maybe we can do more, we can squeeze maybe more, but it's a separate
question.
We will probably think about it.
But returning to pg_stat_statements, what's happening?
Again, a lot of sessions, they compete trying to update the same
record in the pg_stat_statements, just single record, select, query, that's
it.
If it was different queries, it would be okay.
So, I even started calling this workload pathological.
But then I say, okay, what about all SaaS systems and social
media?
Do they have something, some query which is executed, which should
be fast and it's executed in many cases?
And the answer is yes.
Usually, if people work with your system, you need to select...
It can be cached, of course, but I saw it many times, some primary
key lookup to tables like users or posts or projects or blogs
or something.
And you see most of sessions, I mean not only database sessions,
but for example, like session in terms of HTTP communication,
web sessions.
Most of them need this query, right?
In this case, you probably...
You might have this problem.
You might have this observer effect.
And, of course, the solution would be to start caching them,
probably, right?
And so on.
But, this is still an issue.
So, if you have, for example, a thousand TPS or more of some
primary key lookup, probably you already might have this problem,
overhead from pg_stat_statements, which maybe for the whole workload which
you have is not so big, as we mentioned, can be 1 or 0.5%.
But if you zoom into this part of the workload, primary key lookup,
probably there this overhead is bigger and maybe guys I talked
to, maybe they are right and maybe sampling would be a good solution.
Maybe pg_stat_statements could guess, oh, this is high frequency
query, a lot of QPS are happening here, the call's number is high,
maybe I just need to stop sampling, Maybe, I don't know.
It's an interesting question.
And of course, another interesting question was why Intel?
And I don't have an answer yet.
We see these flame graphs, we realize, okay, this code is running
much longer than on Intel.
Right now there is an idea to explore older Intels' Cascade Lake
and maybe even older Xeons, which may be used much more in production
systems.
And maybe also older Epycs, third and second generation maybe.
We also have an issue, not related, but we also observe an issue
with, we discussed it, Lightweight Locks Log manager contention,
there AMD behaves worse than Intel.
So it's kind of interesting.
But what we need to understand, if a lot of sessions run the
same query, it can be bad in terms of lock manager.
So the solution would be to get rid of planning time using prepared
statements, this is ideal.
Or just reduce frequency and you won't notice this, right?
Or make sure fast path is true always and it means that you have
only a few indexes and partition pruning works in plans.
And also, you have an observer effect in the pg_stat_statements
in this case.
And second, Intel versus AMD.
I don't have answers here, but it's interesting to just dig into
it and understand it.
Michael: The thing I've heard you say multiple times in the past
is when you're doing this kind of work, or when you're looking
at your system, it's trying to understand where the bottleneck
is.
Something I really like about this piece of work that you've
done is you first identified that pg_stat_kcache was a bottleneck
and then switched that off and then tried to work out what the
next bottleneck was, looks like it actually might be pg_stat_statements,
even though maybe in your head you were thinking, it probably
isn't, but let's try turning it off and see if that makes a difference.
That made a big difference.
So it's each time, like even when you're thinking about maybe
trying to get above that 2.5 million or maybe 3 million, we have
to work out what's the current bottleneck.
Like that's how we...
Yeah, exactly.
So that's a really nice thing that I don't see enough people
thinking about on their own workloads, but also when they're
benchmarking, what are we currently limited by?
Nikolay: Right, right, right.
So when we perform any performance research, like benchmarks,
analysis, root cause analysis after some incidents on production,
or we try to reproduce problems, so we perform, like all these
database systems is a complex thing, and workload usually is
quite complex.
So to study the whole, we need to apply this, like I mentioned
before we had this call, I mentioned René Descartes or how to
pronounce it in English.
Michael: I think that's, well, he's French, right?
Nikolay: He's French, yes.
Michael: Yeah.
I like René Descartes.
Right.
So the idea
Nikolay: is we need to properly split the whole into segments and
then try to study each segment separately.
When you study each segment separately and know how each of it
behaves.
For example, okay, there is a high frequency select.
Let's study how it behaves without anything else.
We know how it usually behaves without anything else, in like
an emptiness, right?
By the way, when we study it, we also can divide it into smaller
pieces.
For example, okay, let's remove this, let's remove that extension.
So go deeper, deeper, so basically minimal pieces.
We start, it takes time, of course, right?
But then we know small pieces, how they behave, we can try to
compose it back to a complex workload and study an ensemble
of it as a whole.
This is a regular scientific approach, I think one of the oldest
ones, but we must do it here, and I cannot agree with PlanetScale
CEO.
It's not normal, it's not, but we study it because it's presented
in our complex production workloads, right?
For example, primary key lookups with 1,000 or more QPS.
It's not uncommon.
Michael: Yeah.
Well, but to be clear, I'm still a big fan of encouraging every
workload I've ever seen, or every setup I've ever seen, especially
we're talking about SaaS companies, like that kind of thing,
I would still encourage them to have pg_stat_statements, unless
they've done some testing that somehow is affecting their workload.
Nikolay: I agree.
Even if it was 30%, I would say in many cases we still need to
have it.
Because without it, we are blind.
Michael: Yeah.
But if it was 30%, if we did find that out, it would be cool.
to have sampling.
If we then took 1 in 10, we could reduce that maybe to 3 percent.
Also, maybe it's not quite linear, but you know.
Nikolay: But sampling here should be smart.
It should be applied only to high frequency queries. So anyway,
I think we're almost out of time.
We are.
The bottom line, yeah, the bottom line, check...
I think we should also check ARM platform as well and see how
it behaves.
So there is difference in pg_stat_statements behavior on regular
queries versus regular slow queries like updates or deletes versus
high frequent very fast selects like primary key lookups, and
there is difference between AMD and Intel.
So this is the bottom line.
And I'm excited to see the results of our further investigation of
what's happening.
We plan to understand details here.
Michael: Nice one.
Thanks so much, Nikolay.
Thank you everyone for listening and catch you soon.