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
