← Back to team overview

maria-developers team mailing list archive

Re: DS-MRR improvements patch ready for review


Hi Philip,

On Fri, Aug 20, 2010 at 09:12:19AM +0200, Sergey Petrunya wrote:
> Please find attached the combined patch of DS-MRR for clustered PKs and key
> sorting.
> The tree is in launchpad and buildbot also:
> https://code.launchpad.net/~maria-captains/maria/5.3-dsmrr-cpk
> and all observed buildbot failures in the tree are known to occur without the
> new code as well.
The above mentioned tree has DS-MRR improvements that are ready for testing.

Overview of new functionality
The tree has implementations of MWL#121, MWL#123, MWL#124, MWL#125. 

All of those are additional sub-strategies of DS-MRR. They are only applicable
when MRR is used by BKA (and not by range access). 

Checking if new code is used
Execution passes through the new code if the EXPLAIN has tables that have 
type=[eq_]ref, Extra has 'Using join buffer'.

Both before and after this work: BKA is not enabled by default, see 
http://askmonty.org/wiki/Manual:Batched_Key_Access for values of 
@@join_cache_level that enable it. 

There is no way to completely turn on/off the new behaviour. However, one can

- Run SET join_cache_level=0 and then re-run the query without join buffering,
  which will give you the correct result (but rows most likely will be in
  different order).
- Run the query with bigger/smaller value of @@join_buffer_size (this is where
  I've found and fixed a big number of problems already).

- Compare with lp:~maria-captains/maria/5.3. 5.3-dsmrr-cpk has been recently
  merged with 5.3, the only difference between them is the code that needs to
  be tested.

What to test
(first, see above for how to hit the new code). 

Suppose we have the following query (to fix table names):

MariaDB [j3]> explain select * from t0,t1 where t1.a=t0.a; 
| id | select_type | table | type | possible_keys | key  | key_len | ref     | rows | Extra             |
|  1 | SIMPLE      | t0    | ALL  | NULL          | NULL | NULL    | NULL    | 10 |                   |
|  1 | SIMPLE      | t1    | ref  | key1          | key1 | 5       | j3.t0.a | 1 | Using join buffer |
2 rowe in set (0.01 sec)

Then, interesting cases to test are:
- where for various records of t0, table t1 has zero, one, or multiple matches.
- where table t1 is an innodb table and is accessed through [a prefix of a] 
  clustered primary key.
- where t1.key1 is a simple/composite key made of different types (but this is
  more of a checklist test)
- where the join buffer is exhausted at various stages of accessing table t1 (need
  to try with different key sizes, join buffer sizes, and many-table joins)

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

Follow ups