maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

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'.

Control 
-------
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)

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