MySQL 5.6: no testcases for non-crashing bugs, either (and an incomplete bugfix)

I normally don’t write such posts, but this time I’ll do a little whining. I was looking at MySQL’s Bug#69581, which was also filed against MariaDB as MDEV-5102. With some effort (little for this particular bug), one can find out that Bug#69581’s counterpart in Oracle’s internal bug database is Bug#16862316, and the fix is in revision 5366 in MySQL 5.6.14.

There are two problems, though:

  • The fix doesn’t include a testcase. We know, Oracle doesn’t publish testcases for crashing bugs, with a justification that it’s for security reasons. However, this bug is not a crashing one. I’m wondering what was the justification for not including testcase for it?
  • Luckily, the bug entry at bugs.mysql.com has the testcase, so I was able to play with it. As a result, I have found that MySQL’s fix is incomplete, and filed another bug, Bug#70703.

I think, the poor bugfix made by Oracle is not a big deal. Although, this is not the first time we at MariaDB see a poor bugfix by Oracle (Can the forks keep up? Well, they would, if they could trust upstream’s patches and just copy them, without having to check whether the fixes are real fixes or band-aids).

The lack of testcases is a much bigger concern. Has Percona Server merged fix for MySQL Bug#69581 correctly? Well, they don’t have any features related to partitioned tables, so most likely, the answer is Yes. But won’t it be nicer if mysql-test test suite had a testcase for this, and they could check it by running tests? It’s not only about forks/branches. Everybody who changes MySQL/MariaDB/Percona source code benefits from mysql-test testsuite having a good test coverage.

Now, are there any volunteers who could run through MySQL 5.6 changelog and find out whether missing testcase for non-crashing bug was an isolated occurrence, or it is a new trend?

Posted in Uncategorized, mysql on October 24th, 2013 by spetrunia | | 0 Comments

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 Comments

EXPLAIN UPDATE/DELETE/INSERT in MySQL and MariaDB

MySQL 5.6 added support for EXPLAIN INSERT/UPDATE/DELETE. MySQL 5.7 made some improvements to EXPLAIN code. Also, 5.7 added support for EXPLAIN FOR CONNECTION, which looks very similar to MariaDB’s SHOW EXPLAIN. I was working on putting EXPLAIN INSERT/UPDATE/DELETE into MariaDB 10.0, so I’ve dug up some details and thought I’d share them:

EXPLAIN UPDATE/DELETE

EXPLAIN UPDATE looks like regular EXPLAIN SELECT:

MySQL [dbt3sf1]> explain update orders set o_orderpriority='1-URGENT' where o_orderDATE='1998-07-21';
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | orders | range | i_o_orderdate | i_o_orderdate | 4       | const |  628 | Using where |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+

EXPLAIN PARTITIONS shows partitions. EXPLAIN EXTENDED shows the “filtered” column, but doesn’t produce a warning with query after rewrites.

In MySQL 5.7, select_type changes from SIMPLE to UPDATE.

MySQL [dbt3sf1]> explain update orders set o_orderpriority='3-MEDIUM' where o_orderDATE='1998-07-21';
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
|  1 | UPDATE      | orders | range | i_o_orderdate | i_o_orderdate | 4       | const |  628 | Using where |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+

for multi-table UPDATEs (or DELETEs), select_type=UPDATE (or DELETE) only for tables that are being updated.

Inside, there are some issues. Best-release-ever still suffers from an old MySQL design where execution path taken by EXPLAIN is different from execution path taken by the actual query. If you have something in two places, it goes out of sync in no time, and I have alreay found one example EXPLAIN will show “Using join buffer” where the execution doesn’t use it (filed as BUG#70553). A shameless plug: in MariaDB 10.0, we use one execution path, so bugs like this one are not possible.

Another peculiarity is Using temporary. You can run a query like this:

MySQL [dbt3sf1]> explain update orders set o_orderDATE='1998-07-22' where o_orderDATE='1998-07-21';
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+------------------------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref   | rows | Extra                        |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+------------------------------+
|  1 | UPDATE      | orders | range | i_o_orderdate | i_o_orderdate | 4       | const |  628 | Using where; Using temporary |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+------------------------------+

and see Using temporary. This looks weird, why would this query need a temporary table? Running the UPDATE and checking Created_tmp_tables shows that indeed no temporary tables were created. The reason for “Using temporary” is that the UPDATE modifies columns in the index that is used to find rows. When that happens, the optimizer needs to collect record ids in a buffer before doing any updates. But, records are collected in a buffer, not in a temporary table. We in MariaDB decided to denote this with Using buffer instead:

MariaDB [dbt3sf1]> explain update orders set o_orderDATE='1998-07-22' where o_orderDATE='1998-07-21';
+------+-------------+--------+-------+---------------+---------------+---------+------+------+---------------------------+
| id   | select_type | table  | type  | possible_keys | key           | key_len | ref  | rows | Extra                     |
+------+-------------+--------+-------+---------------+---------------+---------+------+------+---------------------------+
|    1 | SIMPLE      | orders | range | i_o_orderdate | i_o_orderdate | 4       | NULL |  628 | Using where; Using buffer |
+------+-------------+--------+-------+---------------+---------------+---------+------+------+---------------------------+

EXPLAIN INSERT

This one is weird - INSERT doesn’t have a query plan, why would one need EXPLAIN for it? One could argue that EXPLAIN INSERT ... SELECT makes sense, as the SELECT part has a query plan. However, using that logic, EXPLAIN CREATE TABLE … SELECT should have been supported also, but it isn’t.

In MySQL 5.6, EXPLAIN INSERT seems to be totally useless:

MySQL [dbt3sf1]> explain insert into orders (o_custkey) values ('32423');
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

In MySQL 5.7, it shows the table that we insert into:

MySQL [dbt3sf1]> explain insert into orders (o_custkey) values ('32423');
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | INSERT      | orders | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL  |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+

“type=ALL” is misleading, because no full table scan will take place. The only scenario when EXPLAIN INSERT can be useful is when one is doing an INSERT into partitioned table. In that case, EXPLAIN PARTITIONS will show the result of partition pruning. Note that partition pruning for INSERTs works in a rather peculiar way - depending on the number of rows inserted, it may choose not to perform partition pruning and will show that all partitions will be used.

I think this post is getting long, so I will talk about SHOW EXPLAIN and EXPLAIN FOR CONNECTION in a separate post.

Posted in Uncategorized on October 17th, 2013 by spetrunia | | 5 Comments

Webinar recording: MariaDB Query Optimizer - Improving query performance

Last week, we at SkySQL had a webinar titled MariaDB Query Optimizer - Improving Query Performance with yours truly as the author and presenter. The recording is now available online at the above link.

The webinar is an overview of all important parts of the MySQL/MariaDB query optimizer. Special attention is given to the new optimizer features and differences between MySQL and MariaDB.

Posted in Uncategorized, mysql on October 9th, 2013 by spetrunia | | 0 Comments