maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

On Thu, Dec 09, 2010 at 11:34:05AM +0200, Philip Stoev wrote:
> I have a few questions with respect to the feature so that I can test it  
> better (we may have discussed those previously but I can not seem to find 
> the email):
>
> - apart from mrr_sort_keys ON and OFF and engine_condition_pushdown = ON 
> | OFF   and all join_cache_levels , is there any other optimizer switch 
> that I should fiddle with? Is there any switch that I can set to ON or 
> OFF in order to drive more queries to use your code?

@@engine_condition_pushdown variable does not have any effect with the current
code. It controls table condition pushdown, which is only supported by NDB.
XtraDB/MyISAM/Maria have *index* condition pushdown, which can be turned on and
off by using SET @@optimizer_switch='index_condition_pushdown=on|off';

Overall, DS-MRR behavior can be modified by:

- @@join_cache_level setting (need to be such that BKA is enabled in order for 
  new code to wor)

- @@join_buffer_size (sets size of the buffer that will be shared between BKA
  module and DS-MRR module)

- mrr_sort_keys=on|off @@optimizer_switch flag

- index_condition_pushdown=on|off @@optimizer_switch flag.

> - apart from mrr_buffer_size , is there any other mysqld option I should 
> be conserned with. What values of mrr_buffer_size should be tested?
> - what table sizes should I use and also how many tables should I join? 
> Is up to 1000 rows per table and 2-3-table joins sufficient? Or should I 
> go for the DBT-2 data set?

I think that we should first test with up to 1K rows per table and 2-4 way
joins. 

I was quite impressed by the issue you've found in BUG#671340 (it was a
specific interaction of key sorting and rowid sorting, which could occur only
when we're scanning a particular kind of data and exhaust the buffer at a
certain point).  The testcase for that bug was rather small, I take the
original testcase found by RQG wasn't big either?

> - how about multipart and prefix indexes?

Yes. I don't expect bugs to be there, but the code does work with index tuples,
so it would be nice to check how scanning on multipart indexes (and their
prefixes) work.  Prefix indexes can be checked, too.

> - should I test enum/set/bit and other marginal data types?

I can't think of realistic examples with those types, so we better focus on
something else.

> - are there any engine specific code or considerations?

XtraDB is different in the regard that it has clustered primary keys, and
MWL#121-125 adds support for MRR/BKA to work for clustered primary keys.

Maria/MyISAM should be checked too, they both have very similar DS-MRR handling.

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