So, welcome to the Post-Quest Girl Dev Room.
If you weren't here before, can I please ask you to silence your phones and extend a very
warm welcome to Peter Zaitsev.
Okay, well, thank you.
We are going to talk about query performance today.
But before that, let me understand a little bit who do we have here.
Now, which of you would mostly see yourself as DBA, SRE, CSADMIN, kind of on the operations
side?
Just, you know, can we have a...
Okay.
Now, which of you are developers?
Ooh, lots of developers.
Okay.
Now, in terms of developers, right, now if you do it again, but now for sort of like
a front-end developers, right, something not, you know, database kernel or something, you
know, other complicated stuff, but something more simple.
Front-end developers.
Any?
Yeah!
Hello!
Okay.
Well, anyway, so one of the good points of this talk for me, right, is really to try to
bridge the gap what I see a lot between how their operations people, right, the people
who are deeply vested in a databases, right, development, think about them versus people
who are just happened to use those databases for the application, right?
And often their relationship to the database is, well, really quite different, right?
As a database kernel developers, we often deeply care about all those kind of internal
algorithms, have a, you know, discussion, what is the best way to implement these and
that cases.
But for many developers writing applications, well, you know, we think about databases, you
know, as you think about like a plumbing, right, well, it just got to work, you don't
want to think about it, well, it just, if it doesn't, then that becomes a problem, right?
They think about database in many cases as a black box.
And I think that is increasingly happening now, especially when we have so many databases
which are deployed in a cloud as a database as a service, right?
Because in this case, especially, well, you just have a database and somebody else focuses
on the other stuff.
So what does that database mean from developer standpoint in many cases?
Well, that means you get some sort of service point, you should use in your application,
right, you can connect to that service point and get that quickly with no problem, right?
And then you run the queries you need to run, right, of a database or maybe even what
your RAM framework, right, or something generates.
Now what do you want from those queries?
Well, as a selfish developer, you want those queries to run with no errors.
You want to make sure they get your correct results, right?
And you want to make sure you run them within response time, which is appropriate for your
application and for query time and for query kind.
And I think that is very important to understand here what if I am looking as a developer and
a database from performance standpoint, I understand that as how quickly that database
responds to my queries, right?
Now if you think about their software design in general, right, and I think especially maybe
not developers, but architects often have to care about a whole bunch of other things
beyond just the performance.
For example, we often have to care about security, right?
And typically security costs stuff, right?
It comes with overhead, both in terms of performance overhead and, you know, organizational overhead
and so on and so forth, right?
That's done to factor authentication always takes another couple of seconds, right?
But that makes us more secure.
Availability is also important, as well as things like costs.
I think that is especially important, again, in the modern age when they have a lot of
cloud, which is elastic, right?
But that elasticity comes also with spend, right?
You often can say, hey, you know what, if I just need my queries to run faster, I can
blow up my instant size, right, or something else.
But well, guess what, that also will be expensive, right, if you're not doing efficiently.
And there is, let's say, a bunch of other things you want to consider about, right?
So I don't want to simplify that, let's say, to what everything is also only about query
performance, but that is what I am going to focus in my talk.
Now when you think about response time from the database standpoint, we often think about
that from a query context, right?
Well I see my database responds to the queries XYZ, you know, in average or something, right?
You think about that query basics.
But if you really look at from a business standpoint, right, how your boss or boss is
boss is boss, right, where it thinks about that, it's mostly about the users which are
using your applications, right?
And I typically would define it what really folks are after is what the users of your
applications, right, and all users, right, have outstanding experience in terms of performance
for all their interactions.
Because in application, you often may have different interactions, right, and I want
to make sure I have a search which is fast and place in an order which is fast, right,
and whatever other things all working quickly.
Now as database engineers, we often want to talk about performance and availability as
a different thing, right, like saying, well, no, no, no, the database was up, it just was
overloaded, so that query took 15 seconds, oh, 15 minutes, right, or something like that,
right?
But the reality is for the user, their very bad performance is really indistinguishable
from downtime, because, well, A, people have a limited experience, right, and if something
is taking too long, we'll just go into closer page, and even if you have some, something
of unlimited patience, there is going to be a whole bunch of timeouts, including your
browser timeouts which will, you know, show you what the page cannot load well before
15 minutes, right?
So I think that is another important thing which I find also important talking to some,
maybe business people about why spend resources on performance, query performance optimization,
and so on and so forth, right, because, well, you know what, if it doesn't perform, it is
down, right?
Another thing what I would point out, right, is in many cases, you see people talking about
the averages, right, while the query performance was so many, you know, milliseconds or something
in average, right, and while it may be helpful for comparison standpoint compared to yesterday,
really, it is not very helpful, right, because, well, the average maybe what you're looking
for may be way too many queries which are too slow, right, just balanced by the queries
which are high, right, and as I wrote here, I really like this saying, we won't leave
the man who tried to cross a river in average one meter deep, right, where once leave the
man.
So in this regard, I think it's very helpful to look at things like a percentile response
time at the very least, right, if you want to look at one number because you're looking
at simplicity, 99 percentile for query response time is much better than average response time.
What is even better is, of course, is to look some sort of distribution, you know, query
histogram distribution and how it changes over time.
That often can give you a lot of insight.
The thing from percentile, though, is it's interesting how it works as you go from that
query to the user experience, right, you spoke about, because think about this, right, typically
when you may have a single user interaction as a page view, it may require multiple sequential
queries, right, or even maybe some queries run in parallel, right, which all need to
be fast in order for user to get the outcome they're looking for, right, and then typically
user through his session will have a multiple of those page views, right, so that 99 percentile
being excellent may only translate to half the users having that kind of outstanding
experience through all the session, right, that is why if you look at companies which
have a large number of users, they would either have some very high percentiles, like 99.9
percentile response time as a goal, right, or would have those tolerances, you know,
rather high, right, so there is a, well, additional sort of accommodation for if there's going
to be many, many queries, and I think to consider when you measure query performance is how
you relate to errors, right, in certain cases I've seen people saying, well, you know,
we only go into either measure response time for only successful queries, or we're going
to put successful queries and queries which are completed with errors in the same bucket,
right, which really can really, you know, change a picture for you a lot.
Why is that?
Well, because actually if you think about the errors, they can be both fast errors and
slow errors, right, imagine, for example, table was dropped for some reason, well, then
all the queries hitting that table will return the error and vary very quickly, right, because
well, there's nothing they can do, on the other hand, if there is something, let's say
some data is locked, right, and some timeouts happen, that may take quite a while before
error is returned, right, and you better not to mix those with the rest of your successful
queries but to be able to, you know, look at that separately.
You also want to look at the query performance not just as an overall number but how it changes
over response time with a reasonably high resolution.
Why is that important?
One thing is what in many cases you would see query performance kind of slowly drops
before it goes so bad what that seems like downtime or really, you know, really incident
for all kind of reasons, right, maybe you have some application which has a bad query,
right, and then you had the one instance of that query running, two, three, four, five,
now you have a hundred instances of that bad query running, right, so saturating all the
system resources, guess what, all the other query performance, right, is going down.
If you are able to, if you are going to notice that what some queries are out of bounds,
right, and maybe alert on it or something, you are able to take an action before the
small problem becomes, basically because of downtime.
The other reason, of course, there is shit that is always going on, right, there is something
that database doesn't have a background, if you have a cloud there is so sort of other
things happening which you may not even know anything about it.
Like for example, block, elastic block storage, right, similar stuff, right, well, guess what,
it doesn't always have uniform performance, you know, sometimes something is happening
at like Amazon back end but you know what, you don't really know anything about that.
They don't tell you each time they have to replace a hard drive, right, somewhere, right,
or, you know, rebalance the load for some reason, right, but those things they can pop
up.
Often you may see something like, oh, I have that like a spike in a query response time
which I can see for all queries on my, all instances, and wow, that's very likely like
something is environmental.
Now when you look at the query instrumentation, one of the questions I see people asking is
where do you want to instrument the query, right, and we can instrument the query on
the application data point, right, an application issues that query, right, and we often have
some, you know, tools like, you know, new relic insights which are doing, you know, just
that.
And hey, that query took this amount of response time and this is very good data because it
actually includes real response time as application observed it.
If there was some, let's say, network delay, right, or for whatever reason, that is included
in that response time where if you just measure from the time database received the query
since it pushed the result in the network, right, that is not included.
But measuring on a database gives you a lot of other valuable stuff like you can get a
lot more insight about what has been going on on the database size right while the query
was executed.
Most typically when you get the query result and you get response time, maybe, you know,
some other little additional information like, oh, this query returns so many rows, so many
bytes, right, but not specifically, you know, how much CPU it uses, right, and all the other
important things you may want to use, okay.
So let's go back to our definition of a response time from a business point of view, right,
and we can say, well, what we are looking for have our old users to have an outstanding
experience of all of their applications, right, great.
Now how do we translate that to the database, right, and kind of maybe breed that gap without
what the boss wants and what DBA is able to answer.
Now I think there is some great work in this regard done by Google which have been working
on this L-square commenter project which allows to pass a lot of metadata, right, from your
application all the way down to your query.
The cool thing they've done is also integrating that directly with some of the frameworks,
right, so it's kind of, hey, you know, you need to do nothing, right, and you just get
that automatic information.
What could be valuable query metadata possibilities, right, if you ask me, well, here is a bunch,
right, there is this actual user and tenant which we can do application or functionality,
right, often single database is used by a lot of applications, right, and we want to
know where the query comes from, right.
I see a lot of DBAs, especially from a large company, say, well, you know what, here is
this nasty query came in.
It was not here yesterday, but it's very hard to figure out who is responsible for introducing
that and how you can come and hit his head with something heavy, right.
That's maybe hard, right, without proper instrumentation.
You also, as a primary breakdown, want to look at the query, and I mean by query in
this case, query of all parameters, you know, normalized, because often you would see the
different queries responsible for different functions, and through that have a different
response time tolerances, right, let's say some very quick lookup queries, you often
want them to complete in a fraction of millisecond as acceptable stuff, while some of you search
queries write to some reports, well, may take a few seconds, and that will be quite
acceptable, and it's good not to mix those all together, right, in this case.
In many cases, when you have the SAS applications, we would have a multiple user, so what often
calls like multiple tenants, like one of the ways you split them is to have a different
schemas or different databases for all of them, and that is also, I find, very helpful to
being able to separate that, so you can see, oh, this query is not slow for everybody,
but when we drill down, we can see only that particular tenant is slow, and vice is slow,
because unlike other, he has five million images in his album, right, if you would think about
some, you know, for the hosting application, so that's just an example.
Another thing what we find very helpful is being able to go for a query, right, or to
look to understand what tables it touches and reverse to find out all the queries which
touches specific table.
Why is that helpful?
Well in many cases, our database operations are table specific, right, you may think,
hey, you know what, I'm dropping this index, as I don't need it, or maybe I add an index,
I add a column, right, you do some sort of maybe kind of partition table, right, you
can do a lot of things with a table in scope, right, and then it would be very interesting
to understand how that particular, how all the queries which touch that table have been
affected, because we are much likely to be affected by that change compared to everybody
else, right, I find that's a pretty, pretty cool feature.
Database user is another one.
If you do not have something like a squirrel command to enable, you often do not really
see very well from what application given query comes in.
But one of the practice you may follow at least is having a different application touching
the same database using different user names, right, different users with different privileges,
right, if nothing else that is a very good security practice, right, and that is where
filtering and breakdown allows that.
In a large, large, short environment, we also want to make sure we aggregate the data from
a many database hosts, right, and can compare between each other.
Typically when you have a short application, you want the load and hence response time
between different database hosts to be kind of similar.
But often it is not, right, it's often hard to achieve a perfect balance in between the
nodes as one cause of the differences, but also things may just, you know, happen, you
know, like you may have a settings which drift away on different nodes, you may have
some, you know, differences, right, in the performance, especially in the cloud, which,
you know, happen virtually from nowhere, right, I mean, I know a lot of people work
in the cloud, you know, you know, sometimes you just get a lemon, right, or just like
a bad node, which for some reason doesn't perform as well as its peers, right, and just
want to, you know, maybe toss it and get another one, better one, right, but to do that you
better understand what that is not performing particularly well.
And the same also applies to their application server or web server.
Again, like if you deploy application on, let's say, 100 application servers or web
nodes, right, you may say, well, it's all should be the same.
I have my, you know, automation which takes care of that.
But again, well, things are not always as they should be.
In many cases, you have something which doesn't work out.
I have seen so many cases when people say, well, you know what, I already fixed that
nasty query and I deployed the fix.
When you look at that, well, it's actually was not deployed all the instances for whatever
reason.
Or you may say, well, you know what, my, I'm using a caching to reduce the query load
on the database, but that caching is misconfigured to otherwise inaccessible on some of their
web nodes, right.
A lot of stuff can happen.
Or maybe you're lucky and one of your web nodes was actually hacked and is also getting
some additional queries to, you know, download your data and send it to someone.
So I find making sure you can look at the query patterns separated by the different
client hosts very, are something very valuable.
I already mentioned with a SQL commenter which allows you to extend some additional
metadata, which I think can be quite cool, right.
And you can find the usage for custom tags in many cases.
I've seen people, for example, tagging different instance types when I'm saying, well, you
know what, this kind of new generation instance looks good.
So let me put some of them in production and being able to compare.
Well, is it actually working better?
Sometimes yes.
Sometimes, you know, no.
The database version, right, maybe you're running out when you, minor Postgres release, you
want to do it like on some subset of the nodes and to make sure there's no, no regressions,
right.
I mean, I think it's always good in this case to practice, you know, trust by verify, right,
because sometimes you do run into unexpected changes, you know, you can validate the configuration
changes this way and so on and so forth.
Query plan is another area which I think is quite, quite interesting.
In many cases, you'll find the same query depending on the parameters, right, or some
other situations will have different plans.
And if that different plans may have different query performance, and it is a very helpful
if you can break down the performance by the different plans a query has, so you can understand
if that is a plan issue or not, right.
Otherwise, you may be looking at the query and say, well, you know what, something is
fast, something is slow, you know, why is that, not very clear, their plans give us
a very good information.
Now when you find the query and see that as a problematic and you need to make it go fast,
in this case, it's very good to understand there is that response time developers care
so much about is coming from.
And there are quite a few possibilities here.
Some of them are instrumented better than others.
For example, if you're looking at data crunch and disk IO, right, those are typically pretty
well instrumented, you can find how much, you know, of CPU query consumes or that does.
In terms of contention, that is typically more problematic, right, to say, hey, you
know, what exactly those kind of internal synchronization object query had to wait,
right, that is more tricky.
You know, waits on CPU availability is even more tricky, right.
And what I mean by this is this, right, so if you have a system which has much more
runnable threads, runnable processes, right, than available CPU, then they will spend a
lot of time waiting for available CPU, right, and that is very hard to see on its impact
to the query response time.
You typically can see that from the general node stats, like, hey, my CPU is back, I have
like a ton of runnable CPU, right, CPU is also in recent kernels, you can see the information
about their run queue latency, which is very cool, right, that tells you how long the processes
had to wait to be scheduled on CPU after they are ready to start running.
So a whole bunch of stuff here, some of them are easy, some of their work is still remaining.
Now, from our standpoint, with all this kind of view on approach to the query monitor,
we have been working at the extension for my square, oh, for Postgres, sorry, called
the PgStat monitor, well, and look, we specifically built it for Postgres, not for MySQL, even
though we had a lot more experience with MySQL, because Postgres SQL extension interface is
awesome and much more powerful than MySQL, right, so you can read a little bit about
this here, and this is extension which allows a lot more insights and getting kind of such
slicing and dicing, which I mentioned, right, if you think about the traditional Postgres
SQL extension PgStats statements, it really aggregates all the data from the start, right,
which is very helpful to be used directly, what we look at the modern observability system
through where we expect to have many Postgres SQL instances anyway, right, and some system
getting that stuff constantly and considerate at that, so that means we are capturing a
lot of information but keep it only on for a relatively short time in a Postgres SQL
instance, right, and that allows to get much more granular information without requiring
a huge amount of resources, which would be required if you would have it for a time,
so you can, you know, read more about what that does on the web pages.
Now some folks asked me, saying, well, folks, like, why do you work on a separate extension
of the PgStats monitors, and my answer to that is we really wanted to experiment with
different approaches, right, to find what works, what doesn't, how users do, and that
is always easy to do in a separate extension, right, and then if something is liked by the
community, then we can see how we can get that in an official list of extensions, so
that is their feedback, is very valuable.
And also if you look in this case while we are providing PgStats statements compatibility,
right, so you can get that view from the same extension instead of getting another two extensions
with additional overhead, PgStat monitor has kind of different ways to aggregate and present
the data, right, which kind of, well, you cannot get in the same, in the same view.
Okay, now as I spoke about the query performance, I wanted to highlight a couple of other things
which are quite interesting to consider when you are looking at the queries where I see
a number of issues.
One is what I would call the bad queries versus victims, right.
In certain cases, or like in many cases, right, you may see even your otherwise good queries
like, hey, this is just a lookup by the primary key starting to be a lot slower than it usually
is, not because something changes the relation to that query, but because of some other bad
queries, right, have been running in parallel.
And imagine that if you will oversaturate your node, right, the hundreds of bad queries
running at the same time, right, well, then everything will become slow.
And I think that's important to understand what if you are seeing some query being slow,
you cannot just think about that as that query problem, it may be entirely something else.
The next thing to consider is currently running queries.
That is also rather interesting, right, because they may not be reflected in the log, right,
or something which say, oh, that query completed and it was, you know, five minutes response
time or 15 seconds, whatever, right.
But running queries can be a problem.
And in many cases, that is actually how things start to snowball, right, you have some application
or even kind of user starts a lot of, you know, bad queries, you know, forgot like
a where clause and a join, right, or something like that, and they just, you know, run for
a long time, right, so you want to make sure you're paying attention to that as well.
The next is to consider what not all activities are directly visible from a query standpoint.
The database often tend to do a bunch of background activities, right.
Additionally you may have something else, like maybe you are taking a snapshot, right,
or taking a backup in the other way, which use also the system resources, right, which
are not seen from query standpoint, but same important.
You also have a lot of things which can be happening on the cloud level, right, again,
which can be, you know, completely invisible for us.
And wherever you are looking, again, at the query performance, it's important to consider
where, you know, maybe something going on, right, additionally what those queries tell
you.
Next question is about, or last thing I would say, is about sampling.
In certain cases I see people saying, well, you know what, let us only capture queries
over X time.
A lot of APM frameworks, right, for example, you know, like New Relics and such may be very
focused on that, saying, hey, you know what, we are going to also give you some examples
of the queries which take more than, you know, one second or whatever execution time.
So focus on those.
Well, and yes, looking at those queries may make sense, right, if they take a long time,
that may be a problem, but it is often what your medium of performance queries, right,
I would say are creating a whole bunch of load on your system, and they contribute the
greatest response time to user application, right, and ignoring those can be problematic.
Well, that is the main overview, right, I hope, what that was, that was helpful, right,
and my main goal here is to make sure maybe to give you some thinking tools, right, as
you noticed, that is not like particularly technical talk, right, which tells you how
exactly to find out which indexes to create or something, but hopefully you get some tools
in this case, how to start, how to approach that, which can prevent you from tuning by
the credit card, you know, scaling the instances to inappropriate sizes, because hey, that
is good for both your wallet as well as good for environment, right, we do not need those
servers generating more heat than absolutely needed.
Well, with that, it is all I have, and I would be happy to take some questions.
Hey, thank you very much for your talk, my question is about when do you have to increase
the box, as a developer, you are in front of a situation where you need to decide between
optimizing or asking the CEO to just pay more, because you have a time constraint, so do
you have the thumb rules where in front of a problem you would say, okay, better to optimize
or better to increase the box, you know, when, how can you decide with me?
The question is to, like, wherever it is better to increase the box size, right, or optimize
the query.
Well, and I think it is interesting, right, that it is not often either a question, right,
I think the time in this case is also often essence, and many cases I have seen people
saying if they have a problem, right, in this case, and they absolutely need to get like
a application up, scale the box, right, and then kind of can currently work on the query
optimization, right, and to bring it back and scale down.
I think that is a very, very reasonable approach, right, because, well, it gives you kind of
more briefing room.
What is important in this case, as in many things in life, is not to be lazy, right,
like you don't want to just, you know, scale the box and forget about that, you want to
scale the box, optimize the queries and so on, right.
Now I often, when I look at the queries, right, as you look at that, you can see which
of them are low-hanging fruits, right, or when a query is already optimized pretty well,
right.
If you are saying, well, you know what, actually, majority of a workload is driven by lookups
for, by the primary key for a table which is already in memory, you can say, well, you
know what, there is very little I can do to optimize this thing, right.
If you are saying, oh, that is a query which does massive join, if no indexes, well, totally
different store, right, you may be able to make that to run thousand times faster, right,
with relatively easy index add.
Any other question?
Hi, so as part of your slice and dice approach to monitoring queries, would you advise that
concurrently queries in the, on the application side are never written as dynamic queries
or as like anonymous prepared statements and only follow, say, named prepared statements
so that you know we have a fixed set of queries that are always the same?
Well, the question is, I would say, like it's kind of like a cart in the horse, right, like
from, from my standpoint, right, like you can of course talk about those kind of practices,
but developers like to do what is there, what keeps them productive, right, and in many
cases saying, well, you know what, oh, you don't use like, or and frameworks, right,
on the device and that, that is complicated, right.
Now even if you're using dynamic queries, typically, they're still going to be at the,
relate to a limited number of variations, right, and especially limited number of most
important for variations which are going to be generated, you will still see that from
the query type, right.
So in many cases, like if you look at that, I would say like a whole set of queries,
you would find, well, this application has, let's say, 10,000 of the distant queries,
but if I look at top 20, that will be responsible like for 90, 99 percent response time, right,
and that of course can change, right, but often focusing on those firsts, right, as well
as maybe taking care of outliers, right, is a good kind of practice, how then you deal
with that information that you have, makes sense.
Any other question?
Hello, thank you for the talk.
What is the overhead of, to collect this statistic, because if you have, like, very, very much
of, that is a good question, right, of course there is, I would say it varies, right, typically
there is more overhead if you have like this, like a very simple fast queries, right, if
you have like a logic queries for, which takes, you know, many seconds for them, it's
less like, our design goal, right, which we are able to get is being similar to PGSTAT
statements, right, and, you know, be a couple of percent or so, right, which I think in
my opinion, right, many people when they think about that observability, you will tend to
obsess about the overhead, right, but really often having that insights, right, often allow
you to get so many things optimized when they matter, right, what the benefits are far outweighed.
Do you have any advice for catching bad queries before they reach production and kind of like
guarding these things?
Oh yeah, absolutely.
Like missing indexes or whatever, before they even.
That is a very good question, right, so I didn't talk about this, but it's also a question
where, right, in my opinion, and I think that's also what is very helpful with the open source
solution, right, what you can really deploy it everywhere in, including your kind of CI,
CD environment, right, because what I often see people saying, well, you know what, data
dog, right, is expensive, it's only in production, right, what you want to do is make sure you
have solutions in development so you can catch bad queries before they hit in production,
but also assume you're not going to catch all the bad queries, right, some queries
will only maybe misbehave in production, right, the other good practice which comes to that
is you make sure you're like a test environment is good, right, so you can test a variety
of queries relevant to your application and you have a good data set, right, for that.
I think in this regard, there is like some cool features coming out from Neon, for example,
like giving like branches, branching, right, then you can get like, oh, the full copy of
production database, you know, mess with it, run tests on it on a full-size data set, right,
instead of testing on, you know, table with 100 rows, right, which is kind of useless.
Cool. Any other question? Okay, thank you very much.
Okay, thank you.
Thank you very much.