Added feedback provided by Igor over skype: On Wed, Dec 15, 2010 at 06:31:52PM +0300, Sergey Petrunya wrote: > 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 | > +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+ The above will not have "Sort keys", key sorting is done only when working with BKA.
> > 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 counters should also be collected as per-table statistics (probably that happens automatically, but we'll need to check that) > > 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. The name is too long and hard to remember. Tentative suggestion: "Handler_mrr_init_count", or "Handler_mrr_refill_count". > 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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
