maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

Hello,

Below are some ideas on how to make DS-MRR/BKA easier to work with for the
users (= those who don't run mysqld under debugger). Questions are marked with
'Q:' but any comments are welcome.

Better EXPLAIN
--------------
Philip has complained numerous times that it is not shown in EXPLAIN whether
DS-MRR will use key sorting.

Currently, MRR alone is show like this:

MariaDB [test]> explain select * from t1 where key1<30;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                            |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+
|  1 | SIMPLE      | t1    | range | key1          | key1 | 5       | NULL |    1 | Using index condition; Using MRR |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+

MRR with BKA are shown like this:
MariaDB [test]> explain select * from t2,t1 where t2.a=t1.key1;
+----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra                              |
+----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------+
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL      |   10 | Using where                        |
|  1 | SIMPLE      | t1    | ref  | key1          | key1 | 5       | test.t2.a |    1 | Using join buffer (flat, BKA join) |
+----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------+

The suggestion is to
- get rid of "Using MRR" word,
- instead, show "Sort rowids" and/or "Sort keys".
- Show the above two whenever rowid-ordered and/or key-ordered retrieval is
  performed.

The above examples will look as follows:

MariaDB [test]> explain select * from t1 where key1<30;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                         |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+
|  1 | SIMPLE      | t1    | range | key1          | key1 | 5       | NULL | 1    | Using index condition; Sort keys; Sort rowids |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+

MRR with BKA are shown like this:
MariaDB [test]> explain select * from t2,t1 where t2.a=t1.key1;
+----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra                                                      |
+----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------------------------------+
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL      |   10 | Using where                                                |
|  1 | SIMPLE      | t1    | ref  | key1          | key1 | 5       | test.t2.a |    1 | Using join buffer (flat, BKA join); Sort keys; Sort rowids |
+----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------------------------------+
Q: any comments?

Counters
--------
Like it is done with other kinds of table accesses, we want to have
counter-based way of analyzing of what has been happening around MRR. 

The most obvious are:

1. Handler_multi_range_read_init_count status variable
This will tell how many multi_range_read_init() calls have been made that
used non-default MRR implementation, i.e. one will be able to see how many
times real MRR scans were performed.
Q: this counter doesn't show how many times key sorting/rowid sorting/both
strategies were used. Is that ok? One could argue that information about
strategy choice is not in high demand as MRR strategy choice is based mostly 
on system settings and DDLs.


2. Handler_multi_range_read_next_count status variable
This will tell how many records were returned by MRR to the upper layer.
Q: MRR does index and rnd_pos scans under the hood, and these scans do
increase counters. This means that, for a single row returned by MRR,
multiple counters will be incremented.


Less obvious suggestions:

3. It would be useful to have an idea about whether DS-MRR had sufficient 
buffer space to operate. One can get a rough picture by adding counters of 
key/rowid sort operations:
- Handler_mrr_rowid_sort_count
- Handler_mrr_key_sort_count

If Handler_mrr_key_sort_count == Handler_multi_range_read_init_count then we've
had enough buffer space. If key_sort_count is two times greater than the number
of init operations, then one could conclude that on average two buffer refills
were needed and increasing buffer size up to 2x would be beneficial.


4. Other possible things
- Index condition pushdown check count, true/false ratio
- Some statistics to globally check if the optimizer's guesses about required
  sizes for key/rowid parts of the buffer were any good.
Q: do we see the need for any of the above?


BR
 Sergey
-- 
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog