maria-developers team mailing list archive

[Markus Pilman <mpilman@xxxxxxxxxxx>]

Hello all,

At ETH Zurich we are working on a new storage engine, that allows us
to test several new architectures for transactional databases. So far
we worked with MySQL, but we had massive performance issues. After
some investigation we figured out, that MySQL generates different
query plans for InnoDB than for our engine. One query which killed our
performance was the following (this is a query from the TPC-W
benchmark):

SELECT ol2.ol_i_id, SUM(ol2.ol_qty) AS sum_ol
FROM order_line ol, order_line ol2, (SELECT o_id FROM orders ORDER BY
o_date DESC LIMIT 10000) AS t
WHERE ol.ol_o_id = t.o_id AND ol.ol_i_id = 10 AND ol2.ol_o_id = t.o_id
AND ol2.ol_i_id <> 10
GROUP BY ol2.ol_i_id ORDER BY sum_ol DESC LIMIT 0,5

MySQL generated the following plan for InnoDB:

+----+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+
| id | select_type | table      | type  | possible_keys
 | key            | key_len | ref             | rows  | Extra
                 |
+----+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL
 | NULL           | NULL    | NULL            | 10000 | Using
temporary; Using filesort |
|  1 | PRIMARY     | ol         | ref   |
orderline_o_id,orderline_i_id | orderline_o_id | 8       | t.o_id
    |     1 | Using where                     |
|  1 | PRIMARY     | ol2        | ref   |
orderline_o_id,orderline_i_id | orderline_o_id | 8       |
tpcw.ol.OL_O_ID |     1 | Using where                     |
|  2 | DERIVED     | orders     | index | NULL
 | orders_o_date  | 4       | NULL            | 10000 | Using index
                 |
+----+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+

while it generated the following one for our storage engine:

 +----+-------------+------------+-------+-------------------------------+----------------+---------+-------+-------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys
 | key            | key_len | ref   | rows  | Extra
                    |
+----+-------------+------------+-------+-------------------------------+----------------+---------+-------+-------+----------------------------------------------+
|  1 | PRIMARY     | ol         | ref   |
orderline_o_id,orderline_i_id | orderline_i_id | 5       | const |
10 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | ol2        | range |
orderline_o_id,orderline_i_id | orderline_i_id | 5       | NULL  |
20 | Using where; Using join buffer               |
|  1 | PRIMARY     | <derived2> | ALL   | NULL
 | NULL           | NULL    | NULL  | 10000 | Using where; Using join
buffer               |
|  2 | DERIVED     | orders     | index | NULL
 | orders_o_date  | 4       | NULL  | 10000 |
                    |
+----+-------------+------------+-------+-------------------------------+----------------+---------+-------+-------+----------------------------------------------+

The second one is obviously a very bad one. So we decided to try with
MariaDB, which generates the following query plan:

+------+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+
| id   | select_type | table      | type  | possible_keys
   | key            | key_len | ref             | rows  | Extra
                   |
+------+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+
|    1 | PRIMARY     | ol         | ref   |
orderline_o_id,orderline_i_id | orderline_i_id | 5       | const
    |    10 | Using temporary; Using filesort |
|    1 | PRIMARY     | ol2        | ref   |
orderline_o_id,orderline_i_id | orderline_o_id | 8       |
test.ol.OL_O_ID |    11 | Using where                     |
|    1 | PRIMARY     | <derived2> | ref   | key0
   | key0           | 8       | test.ol.OL_O_ID |    10 |
                   |
|    2 | DERIVED     | orders     | index | NULL
   | orders_o_date  | 4       | NULL            | 10000 |
                   |
+------+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+

The query plan from MariaDB looks sane to me, and the numbers approve
this (the query runs on a middle sized data set about 200 times faster
with MariaDB than with MySQL). So we will continue our work with
MariaDB. But I have a question to these query plans: why are we
getting this differences in MySQL between our storage engine and
InnoDB? Is there a feature in our storage engine missing (we first
thought we need the ability to support HA_KEYREAD_ONLY - but
implementing this feature did not change the query plan)? Or does
MySQL some kind of "cheating"? We should understand this issue to be
able to present our results we get later (may be we will compare
MariaDB and MySQL, but in a paper we would have to explain why MySQL
sucks that much).

And btw: good work with MariaDB!! The optimizer seems to do a much
better job than MySQL - even with InnoDB/XtraDB (we had to rewrite
some queries in MySQL to force it to generate sane query plans - with
MariaDB this does not seem to be necessary anymore).

Thanks for your help in advance and best regards

Markus