- developers - lists.mariadb.org

[Maria-developers] Updated (by Psergey): MRR backport (67)
by worklog-noreply＠askmonty.org 26 Nov '09

26 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MRR backport CREATION DATE..: Thu, 26 Nov 2009, 15:19 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 67 (http://askmonty.org/worklog/?tid=67) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Thu, 26 Nov 2009, 19:06)=-=- High-Level Specification modified. --- /tmp/wklog.67.old.6449 2009-11-26 19:06:04.000000000 +0200 +++ /tmp/wklog.67.new.6449 2009-11-26 19:06:04.000000000 +0200 @@ -1,4 +1,3 @@ - <contents> 1. Requirements 2. Required actions @@ -44,6 +43,7 @@ http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=index_condi… http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=mrr +http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=icp 2.2 Backport DS-MRR code to MariaDB 5.2 --------------------------------------- -=-=(Psergey - Thu, 26 Nov 2009, 18:15)=-=- High-Level Specification modified. --- /tmp/wklog.67.old.4161 2009-11-26 18:15:36.000000000 +0200 +++ /tmp/wklog.67.new.4161 2009-11-26 18:15:36.000000000 +0200 @@ -1,3 +1,17 @@ + +<contents> +1. Requirements +2. Required actions +2.1 Fix DS-MRR/InnoDB bugs +2.2 Backport DS-MRR code to MariaDB 5.2 +2.3 Introduce control variables +2.4 Other backport issues +2.5 Make MRR code more of a module +2.6 Improve the cost model +2.7 Let DS-MRR support clustered primary keys +</contents> + + 1. Requirements =============== @@ -63,4 +77,28 @@ and the only modularization we see is to move #1 into a separate file which won't achieve much. +2.6 Improve the cost model +-------------------------- +At the moment DS-MRR cost formula re-uses non-MRR scan costs, which uses +records_in_range() calls, followed by index_only_read_time() or read_time() +calls to produce the estimate for read cost. + + We should change this (TODO sort out how exactly) + +Note: this means that the query plans will change from MariaDB 5.2. + +2.7 Let DS-MRR support clustered primary keys +--------------------------------------------- +At the moment DS-MRR is not supported for clustered primary keys. It is not +needed when MRR is used for range access, because range access is done over +an ordered list of ranges, but it is useful for BKA. + +TODO: + it's useful for BKA because BKA makes MRR scans over un-orderered + non-disjoint lists of ranges. Then we can sort these and do ordered scans. + There is still no use for DS-MRR over clustered primary key for range + access, where the ranges are disjoint and ordered. + How about postponing this item until BKA is backported? + + -=-=(Guest - Thu, 26 Nov 2009, 16:52)=-=- High-Level Specification modified. --- /tmp/wklog.67.old.694 2009-11-26 14:52:53.000000000 +0000 +++ /tmp/wklog.67.new.694 2009-11-26 14:52:53.000000000 +0000 @@ -1 +1,66 @@ +1. Requirements +=============== + +We need the following: + +1. Latest MRR interface support, including extensions to support ICP when + using BKA. +2. Let DS-MRR support clustered primary keys (needed when using BKA). +3. Remove conditions used for key access from the condition pushed to index + (ATM this manifests itself as "Using index condition" appearing where there + was no "Using where". TODO: example of this?) +4. Introduce a separate @@optimizer_switch flag for turning on/out ICP (atm it + is switched on/off by @@engine_condition_pushdown) +5. Introduce a separate @@mrr_buffer_size variable to control MRR buffer size + for range+MRR scans. ATM it is controlled by @@read_rnd_size flag and that + makes it unobvious for a number of users. +6. Rename multi_range_read_info_const() to look like it is not a part of MRR + interface. +8. Try to make MRR to be more of a module +7. Improve MRR's cost model. + +2. Required actions +=================== + +Roughly in the order in which it will be done: + +2.1 Fix DS-MRR/InnoDB bugs +-------------------------- +We need to fix the bugs listed here: + +http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=index_condition_pushdown +http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=mrr + +2.2 Backport DS-MRR code to MariaDB 5.2 +--------------------------------------- +The easiest way seems to be to to manually move the needed code from mysql-6.0 +(or whatever it's called now) to MariaDB. + +2.3 Introduce control variables +------------------------------- +Act on items #4 and #5 from the requirements. Should be easy as +@@optimizer_switch is supported in 5.1 codebase. + +2.4 Other backport issues +------------------------- +* Figure out what to do with NDB/MRR. 5.1 codebase has "old" NDB/MRR + implementation. mysql-6.0 (and NDB's branch) have the updated NDB/MRR + but merging it into 5.1 can be very labor-intensive. + Will it be ok to disable NDB/MRR altogether? + + +2.5 Make MRR code more of a module +---------------------------------- +Some code in handler.cc can be moved to separate file. +But changes in opt_range.cc can't. +TODO: Sort out how much we really can do here. Initial guess is not much as the +code consists of: +- Default MRR implementation in handler.cc +- Changes in opt_range.cc to use MRR instead of multiple records_in_range() + calls. These rely on opt_range.cc's internal structures like SEL_ARG trees and + so there is not much point in moving them out. +- DS-MRR implementations which are spread over storage engines. +and the only modularization we see is to move #1 into a separate file which +won't achieve much. + DESCRIPTION: Backport DS-MRR into MariaDB-5.2 codebase, also adding certain extra features to make it more usable. HIGH-LEVEL SPECIFICATION: <contents> 1. Requirements 2. Required actions 2.1 Fix DS-MRR/InnoDB bugs 2.2 Backport DS-MRR code to MariaDB 5.2 2.3 Introduce control variables 2.4 Other backport issues 2.5 Make MRR code more of a module 2.6 Improve the cost model 2.7 Let DS-MRR support clustered primary keys </contents> 1. Requirements =============== We need the following: 1. Latest MRR interface support, including extensions to support ICP when using BKA. 2. Let DS-MRR support clustered primary keys (needed when using BKA). 3. Remove conditions used for key access from the condition pushed to index (ATM this manifests itself as "Using index condition" appearing where there was no "Using where". TODO: example of this?) 4. Introduce a separate @@optimizer_switch flag for turning on/out ICP (atm it is switched on/off by @@engine_condition_pushdown) 5. Introduce a separate @@mrr_buffer_size variable to control MRR buffer size for range+MRR scans. ATM it is controlled by @@read_rnd_size flag and that makes it unobvious for a number of users. 6. Rename multi_range_read_info_const() to look like it is not a part of MRR interface. 8. Try to make MRR to be more of a module 7. Improve MRR's cost model. 2. Required actions =================== Roughly in the order in which it will be done: 2.1 Fix DS-MRR/InnoDB bugs -------------------------- We need to fix the bugs listed here: http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=index_condi… http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=mrr http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=icp 2.2 Backport DS-MRR code to MariaDB 5.2 --------------------------------------- The easiest way seems to be to to manually move the needed code from mysql-6.0 (or whatever it's called now) to MariaDB. 2.3 Introduce control variables ------------------------------- Act on items #4 and #5 from the requirements. Should be easy as @@optimizer_switch is supported in 5.1 codebase. 2.4 Other backport issues ------------------------- * Figure out what to do with NDB/MRR. 5.1 codebase has "old" NDB/MRR implementation. mysql-6.0 (and NDB's branch) have the updated NDB/MRR but merging it into 5.1 can be very labor-intensive. Will it be ok to disable NDB/MRR altogether? 2.5 Make MRR code more of a module ---------------------------------- Some code in handler.cc can be moved to separate file. But changes in opt_range.cc can't. TODO: Sort out how much we really can do here. Initial guess is not much as the code consists of: - Default MRR implementation in handler.cc - Changes in opt_range.cc to use MRR instead of multiple records_in_range() calls. These rely on opt_range.cc's internal structures like SEL_ARG trees and so there is not much point in moving them out. - DS-MRR implementations which are spread over storage engines. and the only modularization we see is to move #1 into a separate file which won't achieve much. 2.6 Improve the cost model -------------------------- At the moment DS-MRR cost formula re-uses non-MRR scan costs, which uses records_in_range() calls, followed by index_only_read_time() or read_time() calls to produce the estimate for read cost. We should change this (TODO sort out how exactly) Note: this means that the query plans will change from MariaDB 5.2. 2.7 Let DS-MRR support clustered primary keys --------------------------------------------- At the moment DS-MRR is not supported for clustered primary keys. It is not needed when MRR is used for range access, because range access is done over an ordered list of ranges, but it is useful for BKA. TODO: it's useful for BKA because BKA makes MRR scans over un-orderered non-disjoint lists of ranges. Then we can sort these and do ordered scans. There is still no use for DS-MRR over clustered primary key for range access, where the ranges are disjoint and ordered. How about postponing this item until BKA is backported? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): MRR backport (67)
by worklog-noreply＠askmonty.org 26 Nov '09

26 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MRR backport CREATION DATE..: Thu, 26 Nov 2009, 15:19 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 67 (http://askmonty.org/worklog/?tid=67) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Thu, 26 Nov 2009, 18:15)=-=- High-Level Specification modified. --- /tmp/wklog.67.old.4161 2009-11-26 18:15:36.000000000 +0200 +++ /tmp/wklog.67.new.4161 2009-11-26 18:15:36.000000000 +0200 @@ -1,3 +1,17 @@ + +<contents> +1. Requirements +2. Required actions +2.1 Fix DS-MRR/InnoDB bugs +2.2 Backport DS-MRR code to MariaDB 5.2 +2.3 Introduce control variables +2.4 Other backport issues +2.5 Make MRR code more of a module +2.6 Improve the cost model +2.7 Let DS-MRR support clustered primary keys +</contents> + + 1. Requirements =============== @@ -63,4 +77,28 @@ and the only modularization we see is to move #1 into a separate file which won't achieve much. +2.6 Improve the cost model +-------------------------- +At the moment DS-MRR cost formula re-uses non-MRR scan costs, which uses +records_in_range() calls, followed by index_only_read_time() or read_time() +calls to produce the estimate for read cost. + + We should change this (TODO sort out how exactly) + +Note: this means that the query plans will change from MariaDB 5.2. + +2.7 Let DS-MRR support clustered primary keys +--------------------------------------------- +At the moment DS-MRR is not supported for clustered primary keys. It is not +needed when MRR is used for range access, because range access is done over +an ordered list of ranges, but it is useful for BKA. + +TODO: + it's useful for BKA because BKA makes MRR scans over un-orderered + non-disjoint lists of ranges. Then we can sort these and do ordered scans. + There is still no use for DS-MRR over clustered primary key for range + access, where the ranges are disjoint and ordered. + How about postponing this item until BKA is backported? + + -=-=(Guest - Thu, 26 Nov 2009, 16:52)=-=- High-Level Specification modified. --- /tmp/wklog.67.old.694 2009-11-26 14:52:53.000000000 +0000 +++ /tmp/wklog.67.new.694 2009-11-26 14:52:53.000000000 +0000 @@ -1 +1,66 @@ +1. Requirements +=============== + +We need the following: + +1. Latest MRR interface support, including extensions to support ICP when + using BKA. +2. Let DS-MRR support clustered primary keys (needed when using BKA). +3. Remove conditions used for key access from the condition pushed to index + (ATM this manifests itself as "Using index condition" appearing where there + was no "Using where". TODO: example of this?) +4. Introduce a separate @@optimizer_switch flag for turning on/out ICP (atm it + is switched on/off by @@engine_condition_pushdown) +5. Introduce a separate @@mrr_buffer_size variable to control MRR buffer size + for range+MRR scans. ATM it is controlled by @@read_rnd_size flag and that + makes it unobvious for a number of users. +6. Rename multi_range_read_info_const() to look like it is not a part of MRR + interface. +8. Try to make MRR to be more of a module +7. Improve MRR's cost model. + +2. Required actions +=================== + +Roughly in the order in which it will be done: + +2.1 Fix DS-MRR/InnoDB bugs +-------------------------- +We need to fix the bugs listed here: + +http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=index_condition_pushdown +http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=mrr + +2.2 Backport DS-MRR code to MariaDB 5.2 +--------------------------------------- +The easiest way seems to be to to manually move the needed code from mysql-6.0 +(or whatever it's called now) to MariaDB. + +2.3 Introduce control variables +------------------------------- +Act on items #4 and #5 from the requirements. Should be easy as +@@optimizer_switch is supported in 5.1 codebase. + +2.4 Other backport issues +------------------------- +* Figure out what to do with NDB/MRR. 5.1 codebase has "old" NDB/MRR + implementation. mysql-6.0 (and NDB's branch) have the updated NDB/MRR + but merging it into 5.1 can be very labor-intensive. + Will it be ok to disable NDB/MRR altogether? + + +2.5 Make MRR code more of a module +---------------------------------- +Some code in handler.cc can be moved to separate file. +But changes in opt_range.cc can't. +TODO: Sort out how much we really can do here. Initial guess is not much as the +code consists of: +- Default MRR implementation in handler.cc +- Changes in opt_range.cc to use MRR instead of multiple records_in_range() + calls. These rely on opt_range.cc's internal structures like SEL_ARG trees and + so there is not much point in moving them out. +- DS-MRR implementations which are spread over storage engines. +and the only modularization we see is to move #1 into a separate file which +won't achieve much. + DESCRIPTION: Backport DS-MRR into MariaDB-5.2 codebase, also adding certain extra features to make it more usable. HIGH-LEVEL SPECIFICATION: <contents> 1. Requirements 2. Required actions 2.1 Fix DS-MRR/InnoDB bugs 2.2 Backport DS-MRR code to MariaDB 5.2 2.3 Introduce control variables 2.4 Other backport issues 2.5 Make MRR code more of a module 2.6 Improve the cost model 2.7 Let DS-MRR support clustered primary keys </contents> 1. Requirements =============== We need the following: 1. Latest MRR interface support, including extensions to support ICP when using BKA. 2. Let DS-MRR support clustered primary keys (needed when using BKA). 3. Remove conditions used for key access from the condition pushed to index (ATM this manifests itself as "Using index condition" appearing where there was no "Using where". TODO: example of this?) 4. Introduce a separate @@optimizer_switch flag for turning on/out ICP (atm it is switched on/off by @@engine_condition_pushdown) 5. Introduce a separate @@mrr_buffer_size variable to control MRR buffer size for range+MRR scans. ATM it is controlled by @@read_rnd_size flag and that makes it unobvious for a number of users. 6. Rename multi_range_read_info_const() to look like it is not a part of MRR interface. 8. Try to make MRR to be more of a module 7. Improve MRR's cost model. 2. Required actions =================== Roughly in the order in which it will be done: 2.1 Fix DS-MRR/InnoDB bugs -------------------------- We need to fix the bugs listed here: http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=index_condi… http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=mrr 2.2 Backport DS-MRR code to MariaDB 5.2 --------------------------------------- The easiest way seems to be to to manually move the needed code from mysql-6.0 (or whatever it's called now) to MariaDB. 2.3 Introduce control variables ------------------------------- Act on items #4 and #5 from the requirements. Should be easy as @@optimizer_switch is supported in 5.1 codebase. 2.4 Other backport issues ------------------------- * Figure out what to do with NDB/MRR. 5.1 codebase has "old" NDB/MRR implementation. mysql-6.0 (and NDB's branch) have the updated NDB/MRR but merging it into 5.1 can be very labor-intensive. Will it be ok to disable NDB/MRR altogether? 2.5 Make MRR code more of a module ---------------------------------- Some code in handler.cc can be moved to separate file. But changes in opt_range.cc can't. TODO: Sort out how much we really can do here. Initial guess is not much as the code consists of: - Default MRR implementation in handler.cc - Changes in opt_range.cc to use MRR instead of multiple records_in_range() calls. These rely on opt_range.cc's internal structures like SEL_ARG trees and so there is not much point in moving them out. - DS-MRR implementations which are spread over storage engines. and the only modularization we see is to move #1 into a separate file which won't achieve much. 2.6 Improve the cost model -------------------------- At the moment DS-MRR cost formula re-uses non-MRR scan costs, which uses records_in_range() calls, followed by index_only_read_time() or read_time() calls to produce the estimate for read cost. We should change this (TODO sort out how exactly) Note: this means that the query plans will change from MariaDB 5.2. 2.7 Let DS-MRR support clustered primary keys --------------------------------------------- At the moment DS-MRR is not supported for clustered primary keys. It is not needed when MRR is used for range access, because range access is done over an ordered list of ranges, but it is useful for BKA. TODO: it's useful for BKA because BKA makes MRR scans over un-orderered non-disjoint lists of ranges. Then we can sort these and do ordered scans. There is still no use for DS-MRR over clustered primary key for range access, where the ranges are disjoint and ordered. How about postponing this item until BKA is backported? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): MRR backport (67)
by worklog-noreply＠askmonty.org 26 Nov '09

26 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MRR backport CREATION DATE..: Thu, 26 Nov 2009, 15:19 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 67 (http://askmonty.org/worklog/?tid=67) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Thu, 26 Nov 2009, 18:15)=-=- High-Level Specification modified. --- /tmp/wklog.67.old.4161 2009-11-26 18:15:36.000000000 +0200 +++ /tmp/wklog.67.new.4161 2009-11-26 18:15:36.000000000 +0200 @@ -1,3 +1,17 @@ + +<contents> +1. Requirements +2. Required actions +2.1 Fix DS-MRR/InnoDB bugs +2.2 Backport DS-MRR code to MariaDB 5.2 +2.3 Introduce control variables +2.4 Other backport issues +2.5 Make MRR code more of a module +2.6 Improve the cost model +2.7 Let DS-MRR support clustered primary keys +</contents> + + 1. Requirements =============== @@ -63,4 +77,28 @@ and the only modularization we see is to move #1 into a separate file which won't achieve much. +2.6 Improve the cost model +-------------------------- +At the moment DS-MRR cost formula re-uses non-MRR scan costs, which uses +records_in_range() calls, followed by index_only_read_time() or read_time() +calls to produce the estimate for read cost. + + We should change this (TODO sort out how exactly) + +Note: this means that the query plans will change from MariaDB 5.2. + +2.7 Let DS-MRR support clustered primary keys +--------------------------------------------- +At the moment DS-MRR is not supported for clustered primary keys. It is not +needed when MRR is used for range access, because range access is done over +an ordered list of ranges, but it is useful for BKA. + +TODO: + it's useful for BKA because BKA makes MRR scans over un-orderered + non-disjoint lists of ranges. Then we can sort these and do ordered scans. + There is still no use for DS-MRR over clustered primary key for range + access, where the ranges are disjoint and ordered. + How about postponing this item until BKA is backported? + + -=-=(Guest - Thu, 26 Nov 2009, 16:52)=-=- High-Level Specification modified. --- /tmp/wklog.67.old.694 2009-11-26 14:52:53.000000000 +0000 +++ /tmp/wklog.67.new.694 2009-11-26 14:52:53.000000000 +0000 @@ -1 +1,66 @@ +1. Requirements +=============== + +We need the following: + +1. Latest MRR interface support, including extensions to support ICP when + using BKA. +2. Let DS-MRR support clustered primary keys (needed when using BKA). +3. Remove conditions used for key access from the condition pushed to index + (ATM this manifests itself as "Using index condition" appearing where there + was no "Using where". TODO: example of this?) +4. Introduce a separate @@optimizer_switch flag for turning on/out ICP (atm it + is switched on/off by @@engine_condition_pushdown) +5. Introduce a separate @@mrr_buffer_size variable to control MRR buffer size + for range+MRR scans. ATM it is controlled by @@read_rnd_size flag and that + makes it unobvious for a number of users. +6. Rename multi_range_read_info_const() to look like it is not a part of MRR + interface. +8. Try to make MRR to be more of a module +7. Improve MRR's cost model. + +2. Required actions +=================== + +Roughly in the order in which it will be done: + +2.1 Fix DS-MRR/InnoDB bugs +-------------------------- +We need to fix the bugs listed here: + +http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=index_condition_pushdown +http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=mrr + +2.2 Backport DS-MRR code to MariaDB 5.2 +--------------------------------------- +The easiest way seems to be to to manually move the needed code from mysql-6.0 +(or whatever it's called now) to MariaDB. + +2.3 Introduce control variables +------------------------------- +Act on items #4 and #5 from the requirements. Should be easy as +@@optimizer_switch is supported in 5.1 codebase. + +2.4 Other backport issues +------------------------- +* Figure out what to do with NDB/MRR. 5.1 codebase has "old" NDB/MRR + implementation. mysql-6.0 (and NDB's branch) have the updated NDB/MRR + but merging it into 5.1 can be very labor-intensive. + Will it be ok to disable NDB/MRR altogether? + + +2.5 Make MRR code more of a module +---------------------------------- +Some code in handler.cc can be moved to separate file. +But changes in opt_range.cc can't. +TODO: Sort out how much we really can do here. Initial guess is not much as the +code consists of: +- Default MRR implementation in handler.cc +- Changes in opt_range.cc to use MRR instead of multiple records_in_range() + calls. These rely on opt_range.cc's internal structures like SEL_ARG trees and + so there is not much point in moving them out. +- DS-MRR implementations which are spread over storage engines. +and the only modularization we see is to move #1 into a separate file which +won't achieve much. + DESCRIPTION: Backport DS-MRR into MariaDB-5.2 codebase, also adding certain extra features to make it more usable. HIGH-LEVEL SPECIFICATION: <contents> 1. Requirements 2. Required actions 2.1 Fix DS-MRR/InnoDB bugs 2.2 Backport DS-MRR code to MariaDB 5.2 2.3 Introduce control variables 2.4 Other backport issues 2.5 Make MRR code more of a module 2.6 Improve the cost model 2.7 Let DS-MRR support clustered primary keys </contents> 1. Requirements =============== We need the following: 1. Latest MRR interface support, including extensions to support ICP when using BKA. 2. Let DS-MRR support clustered primary keys (needed when using BKA). 3. Remove conditions used for key access from the condition pushed to index (ATM this manifests itself as "Using index condition" appearing where there was no "Using where". TODO: example of this?) 4. Introduce a separate @@optimizer_switch flag for turning on/out ICP (atm it is switched on/off by @@engine_condition_pushdown) 5. Introduce a separate @@mrr_buffer_size variable to control MRR buffer size for range+MRR scans. ATM it is controlled by @@read_rnd_size flag and that makes it unobvious for a number of users. 6. Rename multi_range_read_info_const() to look like it is not a part of MRR interface. 8. Try to make MRR to be more of a module 7. Improve MRR's cost model. 2. Required actions =================== Roughly in the order in which it will be done: 2.1 Fix DS-MRR/InnoDB bugs -------------------------- We need to fix the bugs listed here: http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=index_condi… http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=mrr 2.2 Backport DS-MRR code to MariaDB 5.2 --------------------------------------- The easiest way seems to be to to manually move the needed code from mysql-6.0 (or whatever it's called now) to MariaDB. 2.3 Introduce control variables ------------------------------- Act on items #4 and #5 from the requirements. Should be easy as @@optimizer_switch is supported in 5.1 codebase. 2.4 Other backport issues ------------------------- * Figure out what to do with NDB/MRR. 5.1 codebase has "old" NDB/MRR implementation. mysql-6.0 (and NDB's branch) have the updated NDB/MRR but merging it into 5.1 can be very labor-intensive. Will it be ok to disable NDB/MRR altogether? 2.5 Make MRR code more of a module ---------------------------------- Some code in handler.cc can be moved to separate file. But changes in opt_range.cc can't. TODO: Sort out how much we really can do here. Initial guess is not much as the code consists of: - Default MRR implementation in handler.cc - Changes in opt_range.cc to use MRR instead of multiple records_in_range() calls. These rely on opt_range.cc's internal structures like SEL_ARG trees and so there is not much point in moving them out. - DS-MRR implementations which are spread over storage engines. and the only modularization we see is to move #1 into a separate file which won't achieve much. 2.6 Improve the cost model -------------------------- At the moment DS-MRR cost formula re-uses non-MRR scan costs, which uses records_in_range() calls, followed by index_only_read_time() or read_time() calls to produce the estimate for read cost. We should change this (TODO sort out how exactly) Note: this means that the query plans will change from MariaDB 5.2. 2.7 Let DS-MRR support clustered primary keys --------------------------------------------- At the moment DS-MRR is not supported for clustered primary keys. It is not needed when MRR is used for range access, because range access is done over an ordered list of ranges, but it is useful for BKA. TODO: it's useful for BKA because BKA makes MRR scans over un-orderered non-disjoint lists of ranges. Then we can sort these and do ordered scans. There is still no use for DS-MRR over clustered primary key for range access, where the ranges are disjoint and ordered. How about postponing this item until BKA is backported? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (monty:2768) Bug#48357
by Michael Widenius 26 Nov '09

26 Nov '09

#At lp:maria based on revid:monty@askmonty.org-20091125231823-ausq10s23qyih61x 2768 Michael Widenius 2009-11-26 Fix for Bug #48357 SHOW BINLOG EVENTS: Wrong offset or I/O error modified: sql/log_event.cc per-file messages: sql/log_event.cc gcc 4.4.1 assumes that variables that you cast away will not change (strict-aliasing) The symptom was that mysql-test-run binlog.binglog_database got errors in the log === modified file 'sql/log_event.cc' --- a/sql/log_event.cc 2009-10-15 21:38:29 +0000 +++ b/sql/log_event.cc 2009-11-26 15:57:05 +0000 @@ -2133,7 +2133,7 @@ void Query_log_event::pack_info(Protocol /** Utility function for the next method (Query_log_event::write()) . */ -static void write_str_with_code_and_len(char **dst, const char *src, +static void write_str_with_code_and_len(uchar **dst, const char *src, int len, uint code) { /* @@ -2143,7 +2143,7 @@ static void write_str_with_code_and_len( DBUG_ASSERT(len <= 255); DBUG_ASSERT(src); *((*dst)++)= code; - *((*dst)++)= (uchar) len; + *((*dst)++)= len; bmove(*dst, src, len); (*dst)+= len; } @@ -2229,7 +2229,7 @@ bool Query_log_event::write(IO_CACHE* fi } if (catalog_len) // i.e. this var is inited (false for 4.0 events) { - write_str_with_code_and_len((char **)(&start), + write_str_with_code_and_len(&start, catalog, catalog_len, Q_CATALOG_NZ_CODE); /* In 5.0.x where x<4 masters we used to store the end zero here. This was @@ -2267,7 +2267,7 @@ bool Query_log_event::write(IO_CACHE* fi { /* In the TZ sys table, column Name is of length 64 so this should be ok */ DBUG_ASSERT(time_zone_len <= MAX_TIME_ZONE_NAME_LENGTH); - write_str_with_code_and_len((char **)(&start), + write_str_with_code_and_len(&start, time_zone_str, time_zone_len, Q_TIME_ZONE_CODE); } if (lc_time_names_number)

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2768: Apply the strict aliasing patch from http://lists.mysql.com/commits/89843
by noreply＠launchpad.net 26 Nov '09

26 Nov '09

------------------------------------------------------------ revno: 2768 committer: Bo Thorsen <bo.thorsen(a)canonical.com> branch nick: trunk-local timestamp: Thu 2009-11-26 16:32:49 +0100 message: Apply the strict aliasing patch from http://lists.mysql.com/commits/89843 modified: sql/log_event.cc -- lp:maria https://code.launchpad.net/~maria-captains/maria/5.1 Your team Maria developers is subscribed to branch lp:maria. To unsubscribe from this branch go to https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription.

1 0

[Maria-developers] Updated (by Guest): MRR backport (67)
by worklog-noreply＠askmonty.org 26 Nov '09

26 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MRR backport CREATION DATE..: Thu, 26 Nov 2009, 15:19 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 67 (http://askmonty.org/worklog/?tid=67) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Thu, 26 Nov 2009, 16:52)=-=- High-Level Specification modified. --- /tmp/wklog.67.old.694 2009-11-26 14:52:53.000000000 +0000 +++ /tmp/wklog.67.new.694 2009-11-26 14:52:53.000000000 +0000 @@ -1 +1,66 @@ +1. Requirements +=============== + +We need the following: + +1. Latest MRR interface support, including extensions to support ICP when + using BKA. +2. Let DS-MRR support clustered primary keys (needed when using BKA). +3. Remove conditions used for key access from the condition pushed to index + (ATM this manifests itself as "Using index condition" appearing where there + was no "Using where". TODO: example of this?) +4. Introduce a separate @@optimizer_switch flag for turning on/out ICP (atm it + is switched on/off by @@engine_condition_pushdown) +5. Introduce a separate @@mrr_buffer_size variable to control MRR buffer size + for range+MRR scans. ATM it is controlled by @@read_rnd_size flag and that + makes it unobvious for a number of users. +6. Rename multi_range_read_info_const() to look like it is not a part of MRR + interface. +8. Try to make MRR to be more of a module +7. Improve MRR's cost model. + +2. Required actions +=================== + +Roughly in the order in which it will be done: + +2.1 Fix DS-MRR/InnoDB bugs +-------------------------- +We need to fix the bugs listed here: + +http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=index_condition_pushdown +http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=mrr + +2.2 Backport DS-MRR code to MariaDB 5.2 +--------------------------------------- +The easiest way seems to be to to manually move the needed code from mysql-6.0 +(or whatever it's called now) to MariaDB. + +2.3 Introduce control variables +------------------------------- +Act on items #4 and #5 from the requirements. Should be easy as +@@optimizer_switch is supported in 5.1 codebase. + +2.4 Other backport issues +------------------------- +* Figure out what to do with NDB/MRR. 5.1 codebase has "old" NDB/MRR + implementation. mysql-6.0 (and NDB's branch) have the updated NDB/MRR + but merging it into 5.1 can be very labor-intensive. + Will it be ok to disable NDB/MRR altogether? + + +2.5 Make MRR code more of a module +---------------------------------- +Some code in handler.cc can be moved to separate file. +But changes in opt_range.cc can't. +TODO: Sort out how much we really can do here. Initial guess is not much as the +code consists of: +- Default MRR implementation in handler.cc +- Changes in opt_range.cc to use MRR instead of multiple records_in_range() + calls. These rely on opt_range.cc's internal structures like SEL_ARG trees and + so there is not much point in moving them out. +- DS-MRR implementations which are spread over storage engines. +and the only modularization we see is to move #1 into a separate file which +won't achieve much. + DESCRIPTION: Backport DS-MRR into MariaDB-5.2 codebase, also adding certain extra features to make it more usable. HIGH-LEVEL SPECIFICATION: 1. Requirements =============== We need the following: 1. Latest MRR interface support, including extensions to support ICP when using BKA. 2. Let DS-MRR support clustered primary keys (needed when using BKA). 3. Remove conditions used for key access from the condition pushed to index (ATM this manifests itself as "Using index condition" appearing where there was no "Using where". TODO: example of this?) 4. Introduce a separate @@optimizer_switch flag for turning on/out ICP (atm it is switched on/off by @@engine_condition_pushdown) 5. Introduce a separate @@mrr_buffer_size variable to control MRR buffer size for range+MRR scans. ATM it is controlled by @@read_rnd_size flag and that makes it unobvious for a number of users. 6. Rename multi_range_read_info_const() to look like it is not a part of MRR interface. 8. Try to make MRR to be more of a module 7. Improve MRR's cost model. 2. Required actions =================== Roughly in the order in which it will be done: 2.1 Fix DS-MRR/InnoDB bugs -------------------------- We need to fix the bugs listed here: http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=index_condi… http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=mrr 2.2 Backport DS-MRR code to MariaDB 5.2 --------------------------------------- The easiest way seems to be to to manually move the needed code from mysql-6.0 (or whatever it's called now) to MariaDB. 2.3 Introduce control variables ------------------------------- Act on items #4 and #5 from the requirements. Should be easy as @@optimizer_switch is supported in 5.1 codebase. 2.4 Other backport issues ------------------------- * Figure out what to do with NDB/MRR. 5.1 codebase has "old" NDB/MRR implementation. mysql-6.0 (and NDB's branch) have the updated NDB/MRR but merging it into 5.1 can be very labor-intensive. Will it be ok to disable NDB/MRR altogether? 2.5 Make MRR code more of a module ---------------------------------- Some code in handler.cc can be moved to separate file. But changes in opt_range.cc can't. TODO: Sort out how much we really can do here. Initial guess is not much as the code consists of: - Default MRR implementation in handler.cc - Changes in opt_range.cc to use MRR instead of multiple records_in_range() calls. These rely on opt_range.cc's internal structures like SEL_ARG trees and so there is not much point in moving them out. - DS-MRR implementations which are spread over storage engines. and the only modularization we see is to move #1 into a separate file which won't achieve much. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): MRR backport (67)
by worklog-noreply＠askmonty.org 26 Nov '09

26 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MRR backport CREATION DATE..: Thu, 26 Nov 2009, 15:19 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 67 (http://askmonty.org/worklog/?tid=67) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Thu, 26 Nov 2009, 16:52)=-=- High-Level Specification modified. --- /tmp/wklog.67.old.694 2009-11-26 14:52:53.000000000 +0000 +++ /tmp/wklog.67.new.694 2009-11-26 14:52:53.000000000 +0000 @@ -1 +1,66 @@ +1. Requirements +=============== + +We need the following: + +1. Latest MRR interface support, including extensions to support ICP when + using BKA. +2. Let DS-MRR support clustered primary keys (needed when using BKA). +3. Remove conditions used for key access from the condition pushed to index + (ATM this manifests itself as "Using index condition" appearing where there + was no "Using where". TODO: example of this?) +4. Introduce a separate @@optimizer_switch flag for turning on/out ICP (atm it + is switched on/off by @@engine_condition_pushdown) +5. Introduce a separate @@mrr_buffer_size variable to control MRR buffer size + for range+MRR scans. ATM it is controlled by @@read_rnd_size flag and that + makes it unobvious for a number of users. +6. Rename multi_range_read_info_const() to look like it is not a part of MRR + interface. +8. Try to make MRR to be more of a module +7. Improve MRR's cost model. + +2. Required actions +=================== + +Roughly in the order in which it will be done: + +2.1 Fix DS-MRR/InnoDB bugs +-------------------------- +We need to fix the bugs listed here: + +http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=index_condition_pushdown +http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=mrr + +2.2 Backport DS-MRR code to MariaDB 5.2 +--------------------------------------- +The easiest way seems to be to to manually move the needed code from mysql-6.0 +(or whatever it's called now) to MariaDB. + +2.3 Introduce control variables +------------------------------- +Act on items #4 and #5 from the requirements. Should be easy as +@@optimizer_switch is supported in 5.1 codebase. + +2.4 Other backport issues +------------------------- +* Figure out what to do with NDB/MRR. 5.1 codebase has "old" NDB/MRR + implementation. mysql-6.0 (and NDB's branch) have the updated NDB/MRR + but merging it into 5.1 can be very labor-intensive. + Will it be ok to disable NDB/MRR altogether? + + +2.5 Make MRR code more of a module +---------------------------------- +Some code in handler.cc can be moved to separate file. +But changes in opt_range.cc can't. +TODO: Sort out how much we really can do here. Initial guess is not much as the +code consists of: +- Default MRR implementation in handler.cc +- Changes in opt_range.cc to use MRR instead of multiple records_in_range() + calls. These rely on opt_range.cc's internal structures like SEL_ARG trees and + so there is not much point in moving them out. +- DS-MRR implementations which are spread over storage engines. +and the only modularization we see is to move #1 into a separate file which +won't achieve much. + DESCRIPTION: Backport DS-MRR into MariaDB-5.2 codebase, also adding certain extra features to make it more usable. HIGH-LEVEL SPECIFICATION: 1. Requirements =============== We need the following: 1. Latest MRR interface support, including extensions to support ICP when using BKA. 2. Let DS-MRR support clustered primary keys (needed when using BKA). 3. Remove conditions used for key access from the condition pushed to index (ATM this manifests itself as "Using index condition" appearing where there was no "Using where". TODO: example of this?) 4. Introduce a separate @@optimizer_switch flag for turning on/out ICP (atm it is switched on/off by @@engine_condition_pushdown) 5. Introduce a separate @@mrr_buffer_size variable to control MRR buffer size for range+MRR scans. ATM it is controlled by @@read_rnd_size flag and that makes it unobvious for a number of users. 6. Rename multi_range_read_info_const() to look like it is not a part of MRR interface. 8. Try to make MRR to be more of a module 7. Improve MRR's cost model. 2. Required actions =================== Roughly in the order in which it will be done: 2.1 Fix DS-MRR/InnoDB bugs -------------------------- We need to fix the bugs listed here: http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=index_condi… http://bugs.mysql.com/search.php?cmd=display&status=Active&tags=mrr 2.2 Backport DS-MRR code to MariaDB 5.2 --------------------------------------- The easiest way seems to be to to manually move the needed code from mysql-6.0 (or whatever it's called now) to MariaDB. 2.3 Introduce control variables ------------------------------- Act on items #4 and #5 from the requirements. Should be easy as @@optimizer_switch is supported in 5.1 codebase. 2.4 Other backport issues ------------------------- * Figure out what to do with NDB/MRR. 5.1 codebase has "old" NDB/MRR implementation. mysql-6.0 (and NDB's branch) have the updated NDB/MRR but merging it into 5.1 can be very labor-intensive. Will it be ok to disable NDB/MRR altogether? 2.5 Make MRR code more of a module ---------------------------------- Some code in handler.cc can be moved to separate file. But changes in opt_range.cc can't. TODO: Sort out how much we really can do here. Initial guess is not much as the code consists of: - Default MRR implementation in handler.cc - Changes in opt_range.cc to use MRR instead of multiple records_in_range() calls. These rely on opt_range.cc's internal structures like SEL_ARG trees and so there is not much point in moving them out. - DS-MRR implementations which are spread over storage engines. and the only modularization we see is to move #1 into a separate file which won't achieve much. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): MRR backport (67)
by worklog-noreply＠askmonty.org 26 Nov '09

26 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MRR backport CREATION DATE..: Thu, 26 Nov 2009, 15:19 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 67 (http://askmonty.org/worklog/?tid=67) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Backport DS-MRR into MariaDB-5.2 codebase, also adding certain extra features to make it more usable. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): MRR backport (67)
by worklog-noreply＠askmonty.org 26 Nov '09

26 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MRR backport CREATION DATE..: Thu, 26 Nov 2009, 15:19 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 67 (http://askmonty.org/worklog/?tid=67) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Backport DS-MRR into MariaDB-5.2 codebase, also adding certain extra features to make it more usable. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Help with memory leak in EXPLAIN
by Kristian Nielsen 26 Nov '09

26 Nov '09

Hi Sergey, I encountered a memory leak while working on fixing the MySQL 5.1.41 merge. The memory leak is repeatable with the following test case: ------------------------------ cut here ------------------------------ # # Bug#45989 memory leak after explain encounters an error in the query # CREATE TABLE t1(a LONGTEXT); INSERT INTO t1 VALUES (repeat('a',@@global.max_allowed_packet)); INSERT INTO t1 VALUES (repeat('b',@@global.max_allowed_packet)); --error ER_BAD_FIELD_ERROR EXPLAIN SELECT DISTINCT 1 FROM t1, (SELECT DISTINCTROW a AS away FROM t1 GROUP BY a WITH ROLLUP) as d1 WHERE t1.a = d1.a; DROP TABLE t1; ------------------------------ cut here ------------------------------ perl mysql-test-run.pl --valgrind kn.test Errors/warnings were found in logfiles during server shutdown after running the following sequence(s) of tests: main.kn ==14709== 1,048,584 bytes in 1 blocks are possibly lost in loss record 7 of 7 ==14709== at 0x4C22FAB: malloc (vg_replace_malloc.c:207) ==14709== by 0xB1DDD0: my_malloc (my_malloc.c:37) ==14709== by 0x6691C1: String::real_alloc(unsigned) (sql_string.cc:43) ==14709== by 0x5D7BFF: String::alloc(unsigned) (sql_string.h:210) ==14709== by 0x669835: String::copy(String const&) (sql_string.cc:192) ==14709== by 0x7E8BB0: do_save_blob(Copy_field*) (field_conv.cc:296) ==14709== by 0x7E6922: do_copy_null(Copy_field*) (field_conv.cc:207) ==14709== by 0x6EB28B: copy_fields(TMP_TABLE_PARAM*) (sql_select.cc:15374) ==14709== by 0x6F7F1B: end_write_group(JOIN*, st_join_table*, bool) (sql_select.cc:12715) ==14709== by 0x6F4415: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:11514) ==14709== by 0x6F464E: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:11399) ==14709== by 0x705B1E: do_select(JOIN*, List<Item>*, st_table*, Procedure*) (sql_select.cc:11155) ==14709== by 0x7161C2: JOIN::exec() (sql_select.cc:1809) ==14709== by 0x7127B6: mysql_select(THD*, Item***, TABLE_LIST*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:2425) ==14709== by 0x82EEEB: mysql_derived_filling(THD*, st_lex*, TABLE_LIST*) (sql_derived.cc:294) ==14709== by 0x82EC8B: mysql_handle_derived(st_lex*, bool (*)(THD*, st_lex*, TABLE_LIST*)) (sql_derived.cc:56) This bug is also in main lp:maria MariaDB trunk, and also in current MySQL (there is a MySQL bug, http://bugs.mysql.com/bug.php?id=45989) Could you take a look, and see if it is something you/we could fix, or if you have any other suggestions for how to deal with this? - Kristian.

2 1