Re: [Maria-developers] Philip please test maria-5.3-mwl128-dsmrr-cpk
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
Thanks for the comprehensive responses! I meant index_condition_pushdown at all times. All testing scripts manipulate index_condition_pushdown and not engine_condition_pushdown. Philip Stoev ----- Original Message ----- From: "Sergey Petrunya" <psergey@askmonty.org> To: "Philip Stoev" <pstoev@askmonty.org> Cc: <maria-developers@lists.launchpad.net> Sent: Thursday, December 09, 2010 9:36 PM Subject: Re: [Maria-developers] Philip please test maria-5.3-mwl128-dsmrr-cpk
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
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Hi, Your code is good to go. There are no crashes or valgrind warnings. There are no result differences with mrr_sort_keys=on,off and there are no result differences between your tree and 5.3-wl128. The three however does exhibit the sporadic result differences that are seen on all 5.3 trees, such as 665669, so someone needs to fix this bug in case there are others hiding behind it. Philip Stoev ----- Original Message ----- From: "Sergey Petrunya" <psergey@askmonty.org> To: "Philip Stoev" <pstoev@askmonty.org> Cc: <maria-developers@lists.launchpad.net> Sent: Thursday, December 09, 2010 9:36 PM Subject: Re: Philip please test maria-5.3-mwl128-dsmrr-cpk
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
participants (2)
-
Philip Stoev -
Sergey Petrunya