MariaDB 10.0: EXPLAIN in the slow query log

MariaDB can now print query’s EXPLAIN into the slow query log! The feature is based on SHOW EXPLAIN technology and its output looks like this:

# Time: 131017 21:26:02
# User@Host: root[root] @ localhost []
# Thread_id: 2  Schema: dbt3sf1  QC_hit: No
# Query_time: 5.535819  Lock_time: 0.000350  Rows_sent: 1  Rows_examined: 65633
## <explain>
##   id select_type     table   type    possible_keys   key     key_len ref     rows    Extra
##   1  SIMPLE  nation  ref     PRIMARY,n_name  n_name  26      const   1       Using where; Using index
##   1  SIMPLE  customer        ref     PRIMARY,i_c_nationkey   i_c_nationkey   5       dbt3sf1.nation.n_nationkey      25124   Using index
##   1  SIMPLE  orders  ref     i_o_custkey     i_o_custkey     5       dbt3sf1.customer.c_custkey      7       Using index
## </explain>
SET timestamp=1382030762;
select count(*) from customer, orders, nation where c_custkey=o_custkey and c_nationkey=n_nationkey and n_name='GERMANY';

By default, EXPLAIN is not printed to the slow query log, one needs to enable it manually like this (either from SQL or from my.cnf):

set log_slow_verbosity='explain‘;

Now, I’d like to find time to hack pt-query-digest to make use of EXPLAINs. For start, let it show the number of different query plans. Then, show query plans on the response time distribution… so that one can tell which ones were fast or slow… The stopping factor here is that pt-query-digest is 500KB of unfamiliar Perl code.

Posted in mysql on October 18th, 2013 by spetrunia | |

2 Responses to ' MariaDB 10.0: EXPLAIN in the slow query log '

Subscribe to comments with RSS or TrackBack to ' MariaDB 10.0: EXPLAIN in the slow query log '.

  1. dveeden said,

    on October 19th, 2013 at 1:54 pm

    Would it also be possible to get the explain output in JSON format? Then writing some script to analyze/combine the information would be easier.

  2. spetrunia said,

    on October 21st, 2013 at 11:58 am

    Do you mean info like the above in JSON format, or output like MySQL 5.6’s “EXPLAIN FORMAT=JSON” feature?

    Printing the above info in JSON form is easy. As for printing output like EXPLAIN FORMAT=JSON, it will cause the slow query log to be much bigger (because it has more info). Some of the pieces of EXPLAIN FORMAT=JSON are also computationally expensive to produce.

Leave a reply

You must be logged in to post a comment.