Okay. Let's start. So my name is Levant. I'm with MySQL Optimizer team and our job is
as you know to create performance issues and get complaints from you and maybe fix them.
And what are tools we use a lot is explain. I guess is I want to hear don't use
explain regularly. No, maybe a few. So since we're using so much we're also working
on expanding the features and all that. So I wanted to talk about the current explain
features. We added a few in the latest like since eight or what is. Then there are some
restrictions on what you can do, how you can combine them and stuff. And that will
become clear when you look at how explain actually works. And then we'll look into the
crispable and see if we can predict something about the future of of explain. So this is what
explain looks like. We have explain a lot of options and a statement on the end. And this
doesn't work. Yeah, very good. The statement can be select insert update lead to replace
or table. If you don't know table, it's like a sextor. And if you do explain statements,
it will parse and prepare an optimized query, but not execute it. I think Ruben is not
in room. He had an example of things actually being evaluated when doing explain. He did
that at pre-fossil. So maybe go back and watch that video if you weren't there. So yeah,
in some cases things may be evaluated, but obviously they're not executed. I'll get back
to that when they explain how explain actually works. And you can explain what statement
that gives you just the query plan. Explain analyze. So it will actually execute this
thing and give you the query plan. And information about the execution, like how many
roles and how long time everything took. And like to me, it was up early to talk about profiling
CPU and memory. This is like an SQL level profiling of your query. We have different
formats. We have traditional, we have JSON and we have tree. I guess you know this already,
but traditional is this tabular format with one role per table and JSON is JSON. And then
the tree formats is this where you have like a nice ASCII art tree has one string. And yeah,
this is one line, one node of the tree per operation, the query. So this is one role per table,
this is one node and three for each operation. So there's a different thing in how to express
things. So if you get tired of saying format equals tree, all the time like I do, in
eight or third or two, we introduce a system variable called explain formats. It can be default
traditional JSON or tree, default just means traditional. At these other moment, it may
change in the future, but if you said explicitly, you can do your explains without having
to say form equals tree as I do. We also have one more variable called explain JSON format
version. I think the Fred talked about a love of long system variable names. You can
set in one or two, one is defaults. This was new in eight three oh, so it's quite recent. This
applies if you're using the JSON format of course. So we actually JSON is not one format,
we have two JSON formats. version one is what we had since I think five seven. Basically,
it's a JSON objects with one sub-object per table and lots of options about it, but yeah,
an option, an object for table and a table name and then lots of attributes on a table.
version two is like this. This is from nine to zero. There is an object for, it says
queer plan, that's the attribute, and then we have one object per operation in the queer plan.
So the whole tree that we saw in the tree format is represented as one JSON object for each
node in that preview. Actually, when we implement this, the format equals tree is a projection
of some of the attributes in this new format. Again, this is one object per operation.
And we're a bit late in adding this. We added the new JSON format of a three, but
only nine two did we add the actual version number inside the object. But now you can actually
look at this object and see that this is version two. It says two point all. It's a
semantic version. So hopefully we never change two, but as we add stuff, we will go to two one,
two, two, two, three, and so on. That was formats. Let's look at Analyze. That wasn't even
a to 18. I hope you all have used that. Analyze is not compatible with the traditional formats
or with JSON version one. You have to use three and you have to use JSON version two.
So you can't really do Analyze without those formats. So that's another reason to start
explaining format two to three.
Um, into was introduced in eight one. You may not have used this one before, but basically
instead of returning the result to you, it puts it in a, in a user variable. That is not compatible
with traditional or the free format format, but it is actually compatible with the JSON version one format.
This is an ice matrix here of compatibility. Um, but really this is this for tooling that needs to
or wants to analyze the output of this on the server side. So you can of course just do explain,
pull back to your application analysis there, but if you want to do some analysis on the server,
this is what you do. We use this a lot in our own testing. If you ever use empty ore, it's
an SQL driven. So we do explain it into a variable and then we use lots of JSON functions
and that to extract all the information we need to record in our test results.
About the JSON port. Yeah. There's no way to set the format on to the explain statement itself.
Is there a reason for that? Let's go back and discuss that later on. Yeah.
For schema was introduced in 82. If you have a query that is not for qualified in, like
have full qualified templates with schema dots table everywhere, then if you are trying to explain a
statement, you have from your application and copied pasted it and we're not in the same schema
that the application was in, then you can use force schema. It's just said that
pretend that this was executed with the current schema of all this. Again, this is for tooling that
has schema name and queries separately. There's one option that's not mentioned here. That is
something introduced in 572. That is explained for connection. For connection based on your
basis, it's a statement because you're explaining something that is already running. There's a
query running in that connection. That is not compatible with anything. For schema is obvious.
It's already executing. There's no point in setting which schema.
In two, it's a bit worse. This is really no reason why intuition works. It's on my list.
The reason it doesn't work today is that we reduce the scope when we introduced into
and we haven't gone around to completing it yet. Analyze, I will get back to why Analyze doesn't work.
I will show you a nice figure explaining why Analyze doesn't work.
That was it. I think that was all the options we have. Can I one think of any options I haven't mentioned?
Like the grammar is quite big, but yeah. For storage, you set a port for the prepared statement.
You're able to use or connect the idea to get something from the
So the question is that for prepared statement, can you get an explain for connection and get
something about the reasonable back? I hope so. I don't think you get something we're doing.
I would really, really expect that it's the normal one. There's no reason why it shouldn't.
I can't test right now, but it should be the same thing. There's no reason it shouldn't.
We can see that, I guess, in the next figure. This is query processing, my goal,
9-0-1, and on, maybe earlier as well. We parse a query, prepare it, so your prepared statement
would prepare and then when you come to execute, you start here again and continue.
It's a bit special in my skill as moment we have two optimizers. If you're in the cloud,
you can use the hypergraph of my server. If you use the heat,
when you're always using the hypergraph of my server, you don't have choice.
And the old optimizer is the one you're used to. The one you're probably using at
moment. It has an old plan format. When we created the hypergraph of my server,
it can express plans that your old optimizer can't. So we created a new plan format.
So the hypergraph of my server has this as its native format. The old optimizer
can transform the plan into that format. The reason for having only one format at this level
is that once we start executing, we take this plan and we generate the iterators that we're
going to iterate over as we execute the query. The operations that we are going to do.
They say one-to-one mapping between the objects in the plan and the objects that we
execute over. It's a very simple mapping there. So by having converting this plan before we go
to execution means that we have only one format to convert there. So traditional and JSON format
are actually just a dump of the old plan format. JSON version one. While JSON version two and
three is a dump of this plan format. We see the new plan format on the optimizer trace.
I don't think you get a plan dump in the optimizer. You don't get actually explained plan in the
off-master trace. But you may add that. That's a good point. I'm thinking about that myself
that we should just dump the plan or anyways the way it is. And then that will be the new one
at this moment. Yeah. I don't think we're going to ever add new stuff using that one.
So this is why they are different. This plan is table oriented. You all have much to
only do left if join. So everything you do is joining in one more table. The hypergraph one
is you can do bush plants. You can join in other things and single tables. So we need something
as operation oriented. Yeah. And this means also that if you use the hypergraph of
mention you can't use these formats at all because it only produces the new plan format which is
explained here. Let's explain analyze. When you do that before you start executing, you create
a plan. When we generate this one to one mapping, we wrap all the iterators in what we call
a timing iterator which counts number of roles in and out and it measures time spent inside
that iterator. So that when the query has been executed, like this is where we generate.
When we have executed, we do the explain. We need a plan and we append all the timing and
row information that we got from these timing iterators. So if you do an explain for connection,
you're coming from this side. You're just from another thread going in and reading these plans
and dumping it in one of these formats. But you're already down here executing iterators.
There's no way for us to go back and add timing iterators around these things.
So we can't do explain analyze for a connection. Then I guess the question would be,
well, why don't we just add timing iterators all the time but they are too expensive to execute today.
I guess row counts could maybe be added for everything without the huge overhead but timing
is actually, it does affect the query execution. So I don't want to add it as it is today.
We need to find a way to make it much cheaper to get the current time stop.
Okay. That is how explain works. Then there's a question of how it could work.
So if we try to look into the future, take my crystal wall and I see something like this.
Don't like these are my thoughts about how this will be or what could happen here.
I can't guarantee anything of this will be implemented or that it's, yeah, the future is a future.
It's hard to predict. But we have this structure today with two optimizers and
basically my team has been taking twice the amount of code that we did before, like two optimizers.
So maybe we will remove this one at some points. The hype grab office is currently cloud only but
if we remove all one of course it will have to be in community edition and enterprise edition as well.
We're still in 9x innovation releases. So we can do a lot of moving around but at some point
we get to 9.7 and we need to decide. And before you ask me it, there hasn't been any decision yet,
so we don't know when this will happen.
When we remove that one, of course the old format's go away.
So you can't use those anymore.
It also means that when you're using the hype grab office, we are getting some,
how's it? We are getting some plans that are different for what you're seeing today.
So if you're, like you could have birthday plans, so options, combinations of operations that
you never have seen before. So if you're trying to parse these JSON plans for instance,
things, or let's say you're just already using JSON version 2 and trying to parse this,
you will see combinations of operations that you've never seen before. The hype grab office
are just things that the old office can't do. Not only Bush and joins but also other operations
that are ordered differently. So talk a bit about this first one already.
We don't really have any dates for removal or all the openmaster. You're on jury tire, okay?
Then that's a good point, Fred. But also traditional or JSON version 1 hasn't been deprecated yet.
Currently I'm thinking if we're going to deprecate them, that will be around the time where we
make the hype grab for optimized available or something like that. That's a good time to
deprecate them formally and say that they will be removed at some points. As that into for connection,
we should just add it. There's no reason why we should. In general, all these
options that should be orthogonal should be independent and be usable wherever it makes sense.
Also, if you are using explain JSON, do you expect more attributes we added? I do expect
that count to 2.0 will be 2.1 before you can probably adapt it as formats. It will count
probably with at the moment it looks like it's counting one change per release.
So at the moment in innovation releases, we are going to add more stuff. We have requests for
from modern uses that we should add stuff and some things we may want to add ourselves.
I'm thinking, personally, I'm also looking into things to add to explain analyze like
did this hash donor sort go to disk that type of thing. So we can actually start to
look a bit more into why a source suddenly took more time than you expected.
Maybe you see how many files it played on disk and how much disk space it used in that type of thing.
More profiling information.
Changing JSON format to 3, so from 3 to 2, that's 2 to 3.
I don't see any reason why we should do that, currently. We have a new format that we are
quite happy with. It's not perfect, but it's pretty good. So don't be afraid of that.
And people are, some people really like the traditional formats.
Questions do we need more formats on that? I'm open to a tabular format if someone has a good
reason why it would actually be. Of course, it would have to be based on the same thing as
it's JSON and 3 format, so it would be a 3 put into a table.
But if it are good reasons, or if there's a model machinery before formats that we should support,
that's a good suggestion. Daniel was up here earlier talking about how we haven't properly
specified the protocol, the documentation and stuff. I think if we create a JSON schema for
explain, at least we're forcing ourselves to maintain it. If we just verify to explain false
JSON schema, we can make sure to keep our documentation somewhat up to date.
So that is some, at least we have a very actually published these JSON schemas for what we
haven't had. It's also a service that's some kind of documentation for what it means.
I have discussed with people before about automatically explaining queries and dumping it somewhere.
The performance scheme is one option. Some people have asked about open telemetry.
I'm kind of open to that as well if you have some kind of way of specifying that.
Basically, we are redirecting explain up to wherever it's necessary, but there's of course a
cost for explain analysis and that analyzes, at least. But if you have requests for how you want
to get your explains, like doing an explain on in production of all queries running there and dumping
in the performance scheme where you can grab them or open telemetry. It sounds like a good idea. It
kind of came from some of you guys. Dumping the explain somewhere, at least in performance
schema, we would also help us a bit with this explain for connection where you could, instead of
grabbing into the brains of the other thread, we can just go to problem scheme on planets. You
don't need to actually do cross thread operations, then we can just read it. So you will not be
discerning thread at all. So that's saying a option we will consider.
So what should you do now? If you're like me, you don't really like the traditional formats
finding hard to read. So switch to tree. That's the only human readable format that works with
the hard graph optimizer as well. So at some point I think you will have to adapt to it.
It also kind of max the execution. The way we execute now is it's very, very close to this
formats. The way we used to execute before was maybe because of the traditional format, but early
NATO series we changed our execution as well. If you're doing any type of automatic
explain stuff, switch to the version 2 format of JSON. We are adding more information there.
It has a version number that you can check. If you do that, be aware that we have semantic
versioning. So check the major version number and expect you have to be added so that the
minor number will be the bumps at least in the innovation releases. I hope that when we get to
97 things will calm down a bit and we will stop doing lots of changes. So yeah, with that I think
I've covered what we are predicting at the moment and I think we'll wrap it up there and
open for questions if there are any. Yeah Daniel. So my school work branch is a facial
explain. Yeah. Any sense for the future for that? I'm not working work branch, but
I know there's some team at Oracle working on a visual explain. I think it is cloud based,
so maybe not for you. But a visual explain is it's not like we are working people that do visual
explain, so for my side it's the JSON format that is the thing and we are very much
accommodating things that are necessary for visual explain. And also if you implement some kind
of visual explain, please look at this thing. There may be new attributes that you don't know
how to show your graph, maybe you should have a way of visualizing that as well.
Yeah, anything else? Make what? What about visual explain in the next code?
That sounds more likely. Thank you. Thank you for reminding me about that. So shall for
be as cold? Yeah. That sounds like a good place for a visual explain. A really good place.
Sorry? Yes, there is a visual explain in the work branch today. It uses this old JSON version
one format that is expected away. That still works, so but if we will not work with the new format,
it will not, for instance, be able to display all the plans that the hyper graph helped
by some companies. Other questions? Yeah. So, I assume you combine the plans to
wear it? No, you can't pin grave lands. It would be some kind of an extension for
explain to say that well, if you have these hints then you might actually get these queries
Oh, that's quite some work to analyze that, I think. But yeah, that's interesting thing.
I have had master students prototyping, queer depending in my role, so we kind of had some
ideas around it. So yeah, it is possible to do, but
that's what used to happen, right? Yeah. Yeah. The problem with pinning is that you pin one plan,
and then maybe your query has several options that would be like depending on the parameters,
do you query? Sometimes you would do an index loop or sometimes you would like an index range
and sometimes you would do a table scan. And if you pin you really want to have the option for
a few alternatives. So, but of course, if you print what you see in an explain,
that could be a way to do that. The problem with that, if you go back to, to, at this thing,
we do explain at this point, but actually the decision is made up here, right? So, up here,
we know why we are choosing the plan, we are picturing. What we're doing is just to dump the plan,
actually this one. So, we would then have to try to remember why we chose what it did.
Yeah, I think you need to optimize the trace, because, well, you saw my presentation at the
pre-fossilum days about how to optimize your works. We throw away a lot of the plans that we don't
use, and we need to kind of know why we didn't choose those. So, I think you need a trace, yeah.
Sorry? That's why you would need a DBA nowadays, still for FB. Yes, yes, you would not have
a work that would be simply DBAs, yeah, yeah.
There, yeah, in the back. Sorry, dumping the, like this.
Yeah, but this is, this is explaining what's going on in this connection at the moment.
Well, yeah, yeah, yeah. Yeah, yeah, yeah, sure, yeah, let's go back there. Yeah, that is
quite easy, because as we see, we have the plan at this point, we just dumping before it's already
executing. I think that would be a good idea, but this is to figure out where to do it.
But I think that's a good idea. Then, of course, this memory uses in performance scheme or for
how many plans you keep, and all that, we need to figure that out. But I think dumping it there is a,
it doesn't cost as much. So, I think that's a good idea. One question. Yeah, we told about adding
these strategies from about removing stuff. For example, I like to just add the cost when
I keep the cost of the queries. And now I need to put out both the Gs of 2 into a variable,
and then that variable is selected, and we will choose on them to just have the cost kind of
the cost. So, the question was, how about removing stuff? We're like, yeah, when you do the
explaining to, you get it, then you have a lot of data on functions to extract it.
Would having a function, predefined function that extracts like the cost of things would be that
we're getting an option for you? Yeah. Like, we can just create a, like, unique, great function,
and then you can store your full contact with the question. Thank you.