Now, we already spoke here a little bit about developers and especially the front-end developers.
One purpose of this talk for me is to really sort of this kind of a bridge, the gap, which
I often see between the people who really have a database as a center of their at least
professional life.
Any people who are writing an application and database just of them like a thing.
It's like a toilet.
You do your business and you move on with your life.
Something like that.
For those people, the database is typically like a black box.
There is this black box and what I want is I connect to the service point which is provided
to me.
I connect it quickly.
I run my queries and that's all I care about and all that kind of change buffer combaya.
Never heard about it.
What about queries?
What would you as a developer think about queries?
Well, these are actually pretty simple things.
When you connect it to a service point, you are queries.
You want them to complete if no errors.
You want them to provide you correct result set because if you wouldn't, we could alter
over my school tables to black hole and get a fantastic performance.
No errors too.
And also you want them to make sure they complete in that response time what your application
expects.
I think that is a very important thing to understand.
If you look at from the individual developer standpoint, like Ryan application, hey, performance
response time for my queries is all I care about.
And how that's internal database kitchen works, somebody else's problem.
Now if you think about the response time from the database point of view, that is often
seen like, well, I see that response time for a query is in average or whatever distribution.
We'll talk about that later.
But that is different from what business cares about.
If you think about the business point of view, you think about, well, do my user have outstanding
experience in terms of performance with all the application interactions?
That means like a search should work and place an order should work and whatever.
And the database is important part of that, of course, but not is their complete part.
What is interesting in this case is what as database engineers, we often talk about those
kind of different events, kind of like a bad performance and the downtime.
And say, well, you know, no, no, we weren't down, it just was taken 15 minutes to run
my very basic query.
Well, from user standpoint, the bad performance, very bad performance isn't distinguishable
from downtime.
Because A, we don't have parents, then even if people are very patient, then the browser
or some other timeout will happen and nobody gives a shit about that query which may still
continue to run.
Another thing to understand about query performance is you do not want to focus on the averages.
I like this kind of one saying, but there was one sleeve demand to try to cross the
river in the average one meter deep.
That is same applies to the query.
If your average query time is X, that means pretty much nothing.
You need to understand more about that.
And I like in this case to look at their percentiles and even more to make sure you can look
at a specific distribution of your query response time.
If you have that, that gives you a lot more insight.
Now one thing to understand about the percentile, you may be looking and saying, well, great.
My queries have this decent 99 percentile, but that does not mean on a business side
what 99 percent of your users have a good or acceptable experience.
Why is that?
Well, because guess what?
The single user interaction can correspond to a lot of queries sequentially, which all
add up and typically through their joining user has a number of those interactions.
So I would say even 99 percentile that may all well, depending on your application, only
correspond to like 50 percent of user session.
So if you really see the complicated large environments, they are really focused on either
relatively short SLA or rather high percentiles.
Another thing that I would encourage to pay attention to is errors.
And make sure you are measuring response time for those as well, because errors actually
can be offered two kinds, fast errors and slow errors.
In certain cases, let's say if your table doesn't exist, you may be like, get the response
time straight away, and if you put all your error queries and actually normal queries
in the same bucket, you may say, oh my gosh, my response times are doing kind of so well.
But on the other hand, if your query is, for example, error is a lock weight time out,
then that is a slow error.
It actually will have a higher response time than the normal cases.
That is why I always suggest to make sure we measure response time for normal queries
and for queries with problems differently.
Another thing which is very important is looking at response time over time, because traffic
changes, a lot of things are going on in the system and just saying, hey, I'll have a response
time of x over some long period of time, it's not very helpful.
Also what you would see in many cases, you still start those like a small performance
problems, maybe like SLA violations, which are if unfixed, they convert in the downtime.
For example, in my SQL world, you may say, well, I have forgotten this kind of runaway
query, and my history accumulates.
It will slowly increase and increase your response time.
If you measure that over time and say, well, something is not trending in the right directions,
you probably can fix it before that will be seen as a downtime by your users.
If you are not, then not so much.
This is example what we have here, what you often may see something like this as well,
where all the queries have like a spike in the response time, which you often may correspond
to something external happening in the environment.
I think here is what is very interesting, especially for us running in the cloud, we
only have limited observability to environment.
If there is some shit going on on the Amazon backend, they're not going to tell us that.
Oh, you know what, we had, let's say, some free hard drives failed, which back our EBS
and we had to some rebalance, yada, yada.
The other question I would ask is where we want to measure response time from a queries.
In my opinion, both application view and database you are in the combinations are very helpful
because the application can see real thing.
If your network, for example, is adding some latency or whatever, and you will see that
from application, not so much in the database, because it's only sees from, hey, I got response
to, then it's sent the data back.
But the database view allows you often to see a lot more inside about what have been
going on inside, where from application side, we often can just capture the query time,
maybe some very basic additional parameters.
So what we spoke from our business view, right?
Well, we already said what that all users have outstanding performance experience of
all the application interactions, right?
Let's now try to break it down a little bit more, right, to what that may mean.
In this case, I want to introduce this little project or flag from you.
This is SQL Commenter project by Google, right, I mean, which is pretty cool in terms of what
it allows to pass you the metadata, right, which you understand as developer all the way
to SQL query.
They implemented that support from a number of frameworks, right, and it's also supported
in their Google Cloud monitoring environment, right?
And I wouldn't very much see that developed more, right, and for at least kind of us come
to some sort of shared standards between the databases, right, to wherever, how we can augment
query information with sort of like a tags, values, right, which users care about.
So what are possibilities which can be quite helpful in this regard?
Well, finding, for example, who is our actual user tenant, who is query, corresponds, right,
because we often may have, you know, different performance issues, right, finding the application,
like some sort of like a subset of application functionality where many of them may be hitting
the application, right, version information, maybe information about like an, their engineer
of a team who is responsible.
I often see DBAs or SRAs team having problem, like, oh, I see this nasty query which was
shipped yesterday, I know because, shipped today because I know it wasn't very yesterday,
right, but now having to find out who a hell introduced that stupid query, maybe problematic
in a large environment.
Now a lot of focus, and I think the core of the query-based observability may be about
the query.
But the query, I mean, obviously like a query with sort of like it's, which are same except
different parameters, and that is very helpful because, well, obviously they have a different
complexity, different expected SLA, and so on and so forth.
The next way also to break things down for me would be to look at the schema or database,
and why is that interesting?
How?
I just noticed right now what it's been cut a bit, you see, well, anyway, life is life.
I'm just not going to be lucky in this room, right, yes, yeah, but, well, we can blame
our windows, right, on this conference we can and should blame windows.
Okay, well, why schema and database are also good because often we would separate in the
multi-tenant applications different tenants by schema, right, and in that case that gives
us a good profiling for performance of their different schemas, right, like we can see here
in the example with PMM tool.
Another thing what I found is very helpful to find a way to separate the data by different
tables, right, in many cases you want to say, hey, you know what, how a query is hitting
given table is affected, especially if it did some change which relates to the table.
Hey, you know what, I changed the indexing on this table, let me see how all the queries
hitting this table is impacted, very helpful because there may be some surprising differences.
Database users, that is another thing which is quite helpful because that often allows
us to identify the service application, right, if you're following good security practices
you would not let all your applications, right, just use one username, you know, not a good
idea, right, and also find human troublemakers, right, which are doing, having direct access,
right, and so many times you'll find somebody, you know, running the query, right, and say,
okay, well, yeah, it's slow but wherever I'll go for lunch, you know, I have time, well,
you may have time but your database may not, right, so we also, like here's example how
we provide that. I also mentioned database hosts and indexes in many instances, in many
cases that is very helpful because even if you may think, oh, my different database instance
should perform the same, well, world is a messy place and world in the cloud is even
messy place, right, they may not exactly have the same performance due to, you know, some
strange configuration differently, having a bad day, right, or even maybe having a different
load, right, and that is a good to be able to break it down, right, when you see some
of your queries are not performing very well. I would also look at the same stuff from a
web server or application server instance because, again, if you have, like, maybe like
a hundred nodes, you deploy the same application, you may think, hey, we're all going to perform
the same, hitting the database, well, that is not always the case, right, they have seen
changes from people saying one FM is misconfigured or for some reason cannot connect the cache,
so it's, you know, hitting, you know, ten times more queries, right, on the database
than it should be, or the application rollout didn't go well, where UV eliminated nasty
query on 99 of application instance but not some others, right, it's a very good to actually
be able to validate that because what you would see or, like, again, from a DBA standpoint,
you know, developers, sysadmins, storage people, they are going to tell you shit, right, but
they are going to lie, right, they are going to lie, right, maybe not intentionally, maybe
because of their ignorance and limitation of their tool but as a DBA, a city or something,
you want to point them out to their shit and say, look, I have evidence, right, evidence
is good, right, so clients costs, custom tags is very helpful if you can extend, that is
what we spoke about, the SQL commenters, something else which I find very helpful which we cannot
quite easily get with MySQL but being able to separate the query by the query plans, right,
often you may have a query which looks the same but it may take different execution
plans, right, and often that may be correlated to its performance.
In certain cases, it is totally fine, right, very different situations, sometimes MySQL
optimizer may get a little bit, you know, crazy just and has that optimizer plan drift
for no good reason which may not be very easy to catch, right, and will be helpful to do.
What I also would like to highlight is when you find the specific query and say, hey,
this query has nasty performance, right, we often want to understand where that query
response time comes from, right, and that is some of their things, right, where it can
come from, certain of them are relatively easy to find out, right, certain are not very
well, right, for example, wherever query has waited on available CPU, right, because system
was already saturated, well, you can't really see on per query basics, right, you can only
see those things, well, my CPU was kind of like a super packed, right, on a period of
time.
Okay, here are a couple of other things to consider when you're looking at the queries.
One, you want to really look at separately the bad queries, right, versus victims, because
sometimes you will see, oh, queries are getting slower, but it's not because of FAM, it's
about some other nasty queries, right, maybe that is your Java developer who thought, well,
you know, to solve my problems, I will just launch with 200 threads, right, and make sure
I am good, but everything else is kind of slowed down, right, and that's maybe tricky.
One thing is what you should not forget the currently running queries.
In many cases, like if you look in performance schema queries by dash address, that gives
you what happened in the past, but believe me, if you start, you know, 50 instances
of some very bad query, which continues to run, well, that may be the reason of your
problem, not the past, right, and to connect to that, I think it is less problem in my
skill right now, right, if you're using query timeouts, which is a very good practice, right,
because if you say, hey, you know what, for all my interactive queries, by default, I
set the timeout of, let's say, 15 seconds, then you should not care too much about your
past queries because, well, you know what, everything gets killed after 15 minutes.
Also, 50 seconds, right, you should not ignore the stuff which is invisible from a query
standpoint, right, databases do a lot of shit in the background, you may also do things
or your operation teams like, well, backups or provisioning another node for cloning, right,
for the clouds or wherever your VM system may need to do something in the background,
it may not be directly visible, but that can impact the query performance, right, so sometimes,
well, when you observe a query impact and you can't really see what is causing that,
it's possible.
I also would encourage to avoid what I would call like a biased something.
I see people sometimes would say, hey, you know what, we will set long query time to
one second and only look at the queries which are more than one second in length, well,
you may be only focusing on the outliers, right, and missing the possibility to optimize
other queries, right, or actually even focusing on the queries which provide, which are responsible
for providing that bad experience, right, for your users.
Okay, we find another thing like a last minute I have or something, I wanted to say, hey,
what I would like to see from my skill to do better, who is Kenny, no Kenny?
Yes, he's always hiding, right, he probably wanted to get another sandwich, damn it.
Okay, so here are some things that I would like to see.
One is better support of prepared statements, right, and right now it's kind of, you know,
not done in the same way, right, which is, I think, is a problem, right.
Now I would say consider grouping data by time in certain cases, right now you get like
all the statements in one table, right, and you have a lot of statement variety, that
table tends to overflow, right, which is not really helpful, right, and if you have to
kind of reset your queries all the time, that is not very, you know, good practice in my
opinion.
Provide list of tables query touches, right, that is very helpful because, well, my skill
parser already knows it, right, it knows tables query touches, but it's very hard to parse
it out from a query, especially if you consider views, right.
I don't know by looking at the query alone, wherever something is a table or a view, right,
so, in this case.
Information about plan ID, right, I would like to see for the query, right, some sort
of plan hash or something, so I know then query is using something like that, and also
what I would call like a top weight summary, right.
Right now we have information about the weights in my skill performance query and about query,
but I cannot see and say, oh, that query was slow because it spent XYZ amount of weight
on something or whatever, right, or at least kind of like some small class of queries,
right, I don't think that's convenient.
Well, with that, that's all I had to say, hope that will help you to avoid tuning your
indexes by, by the credit card, and yes, oh, I have a time for questions, you told me
like, Peter, five minutes, oh, to answer, I have a time for questions, yes, any questions,
no, oh, yeah, what's the difference or advantages of this SQL commenter thing compared to what
open tracing standards people start tracing the whole thing, what's the difference of
SQL commenter?
Well, what I would say in this case, yes, I mean, there is obviously open tracing framework,
right, which you can use, this gets specifically to the database and specifically in every query,
right, if you look at the open tracing framework, I think, you know, getting every query, right,
maybe a lot of, a lot of volume out there, right, and again, I also think, well, the
good thing if also SQL commenter, right, is what that does it automatically, if you will,
right, that does not require you to take an extra integration.
Okay, anybody else, yeah, I mean, it works with MariaDB as well, yes, well, there are
not practices, there are no good practices, right, like you can, there is a lot of optimizer
hints you can use, right, so you can actually force the query to go like this particular
stuff, right, but that also prevents optimizer choosing different plan if better plan becomes
available.
Yeah.
Never use forced index, always use ignore index, okay, well, then thank you, folks.