Re: [Maria-developers] Proposals for improving BKA/MRR visibility in EXPLAIN and diagnostics
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 key130; ++-+---+---+---+--+-+--+--+--+ | 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 key130; ++-+---+---+---+--+-+--+--+---+ | 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,
[Maria-developers] Proposals for improving BKA/MRR visibility in EXPLAIN and diagnostics
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 key130; ++-+---+---+---+--+-+--+--+--+ | 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 key130; ++-+---+---+---+--+-+--+--+---+ | 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
