Evaluate EXPLAINs automatically
benjamin.s...@googlemail.com
for my application I would like to write a piece of code that
automatically runs EXPLAIN queries for all regular queries I send to
the database.
Technically I know how to do this but my question is how I can let my
code determine whether an EXPLANE result is good or bad.
What are the main indicators I can use for that?
I am looking forward to any hints you can provide me.
Thanks in advance
Benjamin
Jerry Stuckle
Define "good" or "bad".
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
benjamin.s...@googlemail.com
> ==================
Good = I can leave everything as it is
Bad = I need to review the query and the table structure
Axel Schwenke
>>
>> > for my application I would like to write a piece of code that
>> > automatically runs EXPLAIN queries for all regular queries I send to
>> > the database.
>> > Technically I know how to do this but my question is how I can let my
>> > code determine whether an EXPLANE result is good or bad.
>> > What are the main indicators I can use for that?
>>
>
> Bad = I need to review the query and the table structure
The EXPLAIN result is not sufficient for that decision. You must
at least know, if a certain query is run often or seldom.
A better (IMHO) approach would be to use the slow query log. Then
use the 'mysqldumpslow' tool to make a priority list of queries
from the log and run EXPLAIN for the most pressing ones.
<shameless advertisement>
The MySQL Enterprise Monitor comes with the "query analyzer"
feature. QA collects, classifies and analyzes your queries in
realtime. A 30-day-free-trial is available.
See: http://www.mysql.com/products/enterprise/query.html
</advertisement>
XL
Jerry Stuckle
> On Nov 19, 4:32 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> benjamin.schneider...@googlemail.com wrote:
>>> Dear community,
>>> for my application I would like to write a piece of code that
>>> automatically runs EXPLAIN queries for all regular queries I send to
>>> the database.
>>> Technically I know how to do this but my question is how I can let my
>>> code determine whether an EXPLANE result is good or bad.
>>> What are the main indicators I can use for that?
>>> I am looking forward to any hints you can provide me.
>>> Thanks in advance
>>> Benjamin
>> Define "good" or "bad".
>>
>
> Bad = I need to review the query and the table structure
According to what criteria? How do you determine if you should leave it
or modify it?
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Erick T. Barkhuis
>benjamin.s...@googlemail.com wrote:
>>On Nov 19, 4:32 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>benjamin.schneider...@googlemail.com wrote:
>>>>Dear community,
>>>>for my application I would like to write a piece of code that
>>>>automatically runs EXPLAIN queries for all regular queries I
>>>>send to the database.
>>>>Technically I know how to do this but my question is how I can
>>>>let my code determine whether an EXPLANE result is good or bad.
>>>>What are the main indicators I can use for that?
>>>Define "good" or "bad".
>>
>>Good = I can leave everything as it is
>>Bad = I need to review the query and the table structure
>
>According to what criteria? How do you determine if you should leave
>it or modify it?
I wouldn't know either, but isn't that exactly what he likes to know?
The OP's question was: "What are the main indicators I can use for
that?", so obviously, he would like to know how to derive from the
explain results what actions, if any, should be taken.
My guess is, that OP wouldn't know how to tell if an explain result is
"good" or "bad" from a non-automated explain query, either.
--
Erick
Jerry Stuckle
> Jerry Stuckle:
>
>> benjamin.s...@googlemail.com wrote:
>>> On Nov 19, 4:32 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>> benjamin.schneider...@googlemail.com wrote:
>>>>> Dear community,
>>>>> for my application I would like to write a piece of code that
>>>>> automatically runs EXPLAIN queries for all regular queries I
>>>>> send to the database.
>>>>> Technically I know how to do this but my question is how I can
>>>>> let my code determine whether an EXPLANE result is good or bad.
>>>>> What are the main indicators I can use for that?
>
>>>> Define "good" or "bad".
>
>>> Good = I can leave everything as it is
>>> Bad = I need to review the query and the table structure
>> According to what criteria? How do you determine if you should leave
>> it or modify it?
>
> I wouldn't know either, but isn't that exactly what he likes to know?
> The OP's question was: "What are the main indicators I can use for
> that?", so obviously, he would like to know how to derive from the
> explain results what actions, if any, should be taken.
>
No, his question was " how I can let my
code determine whether an EXPLANE result is good or bad."
To determine whether a query is "good" or "bad", you have to first
define what makes a query "good" or "bad".
Whether to keep the query or not is the result of that determination,
not the definition.
> My guess is, that OP wouldn't know how to tell if an explain result is
> "good" or "bad" from a non-automated explain query, either.
>
>
That's possible, or he could have hundreds of queries he wanted to check
(not that that would tell him much, either).
Jerry Stuckle
> "benjamin.s...@googlemail.com" <benjamin.s...@googlemail.com> wrote:
>>>> for my application I would like to write a piece of code that
>>>> automatically runs EXPLAIN queries for all regular queries I send to
>>>> the database.
>>>> Technically I know how to do this but my question is how I can let my
>>>> code determine whether an EXPLANE result is good or bad.
>>>> What are the main indicators I can use for that?
>>> Define "good" or "bad".
>> Good = I can leave everything as it is
>> Bad = I need to review the query and the table structure
>
> The EXPLAIN result is not sufficient for that decision. You must
> at least know, if a certain query is run often or seldom.
>
Not at all. Whether a query is "good" or "bad" is not related to how
often it is run.
When optimizing the code (including queries), it is generally much more
efficient use of one's time to optimize queries which are run ten times
a second vs. once every ten days.
> A better (IMHO) approach would be to use the slow query log. Then
> use the 'mysqldumpslow' tool to make a priority list of queries
> from the log and run EXPLAIN for the most pressing ones.
>
>
> XL
Which won't help if you're running a query ten times a second and it's
taking 25 ms. each time. Even shaving two ms. off the query can result
in a big boost in overall system performance. The slow query log is
fine if you have queries which take ten seconds to execute. But not for
short queries which are run quite often.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Peter H. Coffin
> Axel Schwenke wrote:
>> "benjamin.s...@googlemail.com" <benjamin.s...@googlemail.com> wrote:
>>>>> for my application I would like to write a piece of code that
>>>>> automatically runs EXPLAIN queries for all regular queries I send to
>>>>> the database.
>>>>> Technically I know how to do this but my question is how I can let my
>>>>> code determine whether an EXPLANE result is good or bad.
>>>>> What are the main indicators I can use for that?
>>>> Define "good" or "bad".
>>> Good = I can leave everything as it is
>>> Bad = I need to review the query and the table structure
>>
>> The EXPLAIN result is not sufficient for that decision. You must
>> at least know, if a certain query is run often or seldom.
>
> Not at all. Whether a query is "good" or "bad" is not related to how
> often it is run.
Ah, but where "Good = I can leave everything as it is", it's probably
simpler to think of the options as "good enough" and "unacceptable".
> When optimizing the code (including queries), it is generally much more
> efficient use of one's time to optimize queries which are run ten times
> a second vs. once every ten days.
But that IS knowing "if a certain query is run often or seldom"...
>> A better (IMHO) approach would be to use the slow query log. Then
>> use the 'mysqldumpslow' tool to make a priority list of queries
>> from the log and run EXPLAIN for the most pressing ones.
>>
> >
> > XL
>
> Which won't help if you're running a query ten times a second and it's
> taking 25 ms. each time. Even shaving two ms. off the query can result
> in a big boost in overall system performance. The slow query log is
> fine if you have queries which take ten seconds to execute. But not for
> short queries which are run quite often.
True, but that ain't gonna help the question of automatically parsing
EXPLAIN output. Just collecting the EXPLAIN data will be enough
additional overhead to spoil the answers to the questions you're asking.
Some grinding on the EXPLAIN during the development of the sort and
focus you're talking about is an excellent thing, but it's not something
that I'd expect to be part of an ongoing monitoring process because of
that bit about even doing the monitoring can slow down the gains already
made.
--
"The bullets are just his way of saying 'Keep it down, I've got a
hangover.'"
Kiki to Dr. Schlock, as seen in
http://pics.sluggy.com/comics/000108a.gif
Dikkie Dik
As was already suggested, use the slow query log. But there is nothing
like a defined "optimal". Neither is there a "good" or "bad". For
instance, you can log "queries not using indexes" in the slow query log
(it's a setting you can activate). But is that bad? It probably is when
you want to select a record from a large live-data table. It is not
necessarily bad when that is a one-time query on a log table (where
indexes are omitted for INSERT speed). Off course, MySQL itself does not
know what you use your tables for. It is just doing its job, not spying
on you. It also is definitely not bad when you want to read an entire
small lookup table into memory (to fill more than one drop-down widget,
for example).
Furthermore, you should not only check if the queries themselves are bad
(if you can define it at all), but if the entire traffic is bad. For
instance, you can fire a lot of select-by-primary-key-value queries, or
just one with an IN clause. You could define the lots of single queries
as bad. But they are not necessarily so.
All depends. If the database is contacted over a slow network, try to
minimize the queries. If the server is running on the same machine, the
speed difference is probably not noticeable.
So I would suggest that you also enable the "normal" query log on your
development machine and monitor it. There are log viewers that even
support nice colours so you can see different kinds of queries at a
glance. Just see what queries are fired by your application. Is the same
query fired twice or more? Now that can be bad. But even that is not bad
by definition.
I think common sense and a good view on the query log does more good
than monitoring automatically with EXPLAIN queries. And keep in mind
that premature optimization usually does not lead to faster code, but
often to less legible code.
Good luck,
Dikkie.
Jerry Stuckle
> On Thu, 19 Nov 2009 07:37:18 -0500, Jerry Stuckle wrote:
>> Axel Schwenke wrote:
>>> "benjamin.s...@googlemail.com" <benjamin.s...@googlemail.com> wrote:
>>>>>> for my application I would like to write a piece of code that
>>>>>> automatically runs EXPLAIN queries for all regular queries I send to
>>>>>> the database.
>>>>>> Technically I know how to do this but my question is how I can let my
>>>>>> code determine whether an EXPLANE result is good or bad.
>>>>>> What are the main indicators I can use for that?
>>>>> Define "good" or "bad".
>>>> Good = I can leave everything as it is
>>>> Bad = I need to review the query and the table structure
>>> The EXPLAIN result is not sufficient for that decision. You must
>>> at least know, if a certain query is run often or seldom.
>> Not at all. Whether a query is "good" or "bad" is not related to how
>> often it is run.
>
> Ah, but where "Good = I can leave everything as it is", it's probably
> simpler to think of the options as "good enough" and "unacceptable".
>
No, "I can leave everything as it is" is the *result* of a query being
"good". It is not the criteria as to whether the query should be left
as is or not.
>> When optimizing the code (including queries), it is generally much more
>> efficient use of one's time to optimize queries which are run ten times
>> a second vs. once every ten days.
>
> But that IS knowing "if a certain query is run often or seldom"...
>
And it has nothing to do with how "good" a query is or not. Just how
much time you should spend trying to optimize the query.
>>> A better (IMHO) approach would be to use the slow query log. Then
>>> use the 'mysqldumpslow' tool to make a priority list of queries
>>> from the log and run EXPLAIN for the most pressing ones.
>>>
>>>
>>> XL
>> Which won't help if you're running a query ten times a second and it's
>> taking 25 ms. each time. Even shaving two ms. off the query can result
>> in a big boost in overall system performance. The slow query log is
>> fine if you have queries which take ten seconds to execute. But not for
>> short queries which are run quite often.
>
> True, but that ain't gonna help the question of automatically parsing
> EXPLAIN output. Just collecting the EXPLAIN data will be enough
> additional overhead to spoil the answers to the questions you're asking.
> Some grinding on the EXPLAIN during the development of the sort and
> focus you're talking about is an excellent thing, but it's not something
> that I'd expect to be part of an ongoing monitoring process because of
> that bit about even doing the monitoring can slow down the gains already
> made.
>
Not part of an ongoing monitoring process, no. But something which
should be evaluated occasionally. The results of EXPLAIN will often
vary wildly between a development environment (with minimal data in the
tables) and a production environment (with lots of data). It can even
change after several days or months of operation as the data
distribution changes.