- developers - lists.mariadb.org

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2776)
by knielsen＠knielsen-hq.org 27 Nov '09

27 Nov '09

#At lp:maria 2776 knielsen(a)knielsen-hq.org 2009-11-27 After-merge fixes for MySQL 5.1.41 merge into MariaDB: more fixes for Buildbot problems. modified: mysql-test/mysql-test-run.pl mysql-test/suite/rpl/r/rpl_temporary_errors.result mysql-test/suite/rpl/t/rpl_temporary_errors.test sql/sql_select.cc per-file messages: mysql-test/mysql-test-run.pl Manually apply similar patch to the one in Bug#47983. mysql-test/suite/rpl/r/rpl_temporary_errors.result Fix wrong failure with warning in error log due to per-test suppressions not being active during server shutdown. mysql-test/suite/rpl/t/rpl_temporary_errors.test Fix wrong failure with warning in error log due to per-test suppressions not being active during server shutdown. sql/sql_select.cc Manually cherry-pick fix for Bug#45989. For some reason, that fix was reverted in MySQL 5.1, even though it is necessary to plug the memory leak. === modified file 'mysql-test/mysql-test-run.pl' --- a/mysql-test/mysql-test-run.pl 2009-11-26 08:02:33 +0000 +++ b/mysql-test/mysql-test-run.pl 2009-11-27 13:20:59 +0000 @@ -3994,6 +3994,11 @@ sub extract_warning_lines ($) { qr/Slave SQL thread retried transaction/, qr/Slave $additional info$/, qr/Incorrect information in file/, + qr/Slave I\/O: Get master SERVER_ID failed with error:.*/, + qr/Slave I\/O: Get master clock failed with error:.*/, + qr/Slave I\/O: Get master COLLATION_SERVER failed with error:.*/, + qr/Slave I\/O: Get master TIME_ZONE failed with error:.*/, + qr/Slave I\/O: error reconnecting to master '.*' - retry-time: [1-3] retries/, ); my $match_count= 0; === modified file 'mysql-test/suite/rpl/r/rpl_temporary_errors.result' --- a/mysql-test/suite/rpl/r/rpl_temporary_errors.result 2008-10-13 18:33:08 +0000 +++ b/mysql-test/suite/rpl/r/rpl_temporary_errors.result 2009-11-27 13:20:59 +0000 @@ -82,3 +82,4 @@ Last_SQL_Error DROP TABLE t1; **** On Master **** DROP TABLE t1; +STOP SLAVE; === modified file 'mysql-test/suite/rpl/t/rpl_temporary_errors.test' --- a/mysql-test/suite/rpl/t/rpl_temporary_errors.test 2008-10-13 18:33:08 +0000 +++ b/mysql-test/suite/rpl/t/rpl_temporary_errors.test 2009-11-27 13:20:59 +0000 @@ -31,3 +31,12 @@ DROP TABLE t1; --echo **** On Master **** connection master; DROP TABLE t1; + +# We must wait for the slave to stop. +# Otherwise the warnings in the error log about deadlock may be written to +# the error log only during shutdown, and currently the suppression of +# "Deadlock found" set in this test case is not effective during server +# shutdown. +connection slave; +STOP SLAVE; +--source include/wait_for_slave_to_stop.inc === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2009-11-16 20:54:33 +0000 +++ b/sql/sql_select.cc 2009-11-27 13:20:59 +0000 @@ -2311,7 +2311,7 @@ JOIN::destroy() tab->cleanup(); } tmp_join->tmp_join= 0; - tmp_table_param.copy_field= 0; + tmp_table_param.cleanup(); DBUG_RETURN(tmp_join->destroy()); } cond_equal= 0;

1 0

[Maria-developers] Updated (by Knielsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 27 Nov '09

27 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen CATEGORY.......: Server-RawIdeaBin TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Fri, 27 Nov 2009, 13:30)=-=- Observers changed: Knielsen -=-=(Psergey - Sun, 16 Aug 2009, 11:08)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.12485 2009-08-16 11:08:33.000000000 +0300 +++ /tmp/wklog.47.new.12485 2009-08-16 11:08:33.000000000 +0300 @@ -1 +1,6 @@ +First suggestion: + +> I think for this we would actually need a new binlog event type +> (Comment_log_event?). Unless we want to log an empty statement Query_log_event +> containing only a comment (a bit of a hack). -=-=(Psergey - Sun, 16 Aug 2009, 00:02)=-=- Dependency created: 39 now depends on 47 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: First suggestion: > I think for this we would actually need a new binlog event type > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Knielsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 27 Nov '09

27 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen CATEGORY.......: Server-RawIdeaBin TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Fri, 27 Nov 2009, 13:30)=-=- Observers changed: Knielsen -=-=(Psergey - Sun, 16 Aug 2009, 11:08)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.12485 2009-08-16 11:08:33.000000000 +0300 +++ /tmp/wklog.47.new.12485 2009-08-16 11:08:33.000000000 +0300 @@ -1 +1,6 @@ +First suggestion: + +> I think for this we would actually need a new binlog event type +> (Comment_log_event?). Unless we want to log an empty statement Query_log_event +> containing only a comment (a bit of a hack). -=-=(Psergey - Sun, 16 Aug 2009, 00:02)=-=- Dependency created: 39 now depends on 47 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: First suggestion: > I think for this we would actually need a new binlog event type > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Knielsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 27 Nov '09

27 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen CATEGORY.......: Server-RawIdeaBin TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Fri, 27 Nov 2009, 13:30)=-=- Observers changed: Knielsen -=-=(Psergey - Sun, 16 Aug 2009, 11:08)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.12485 2009-08-16 11:08:33.000000000 +0300 +++ /tmp/wklog.47.new.12485 2009-08-16 11:08:33.000000000 +0300 @@ -1 +1,6 @@ +First suggestion: + +> I think for this we would actually need a new binlog event type +> (Comment_log_event?). Unless we want to log an empty statement Query_log_event +> containing only a comment (a bit of a hack). -=-=(Psergey - Sun, 16 Aug 2009, 00:02)=-=- Dependency created: 39 now depends on 47 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: First suggestion: > I think for this we would actually need a new binlog event type > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Timour): Subquery optimization: Efficient NOT IN execution with NULLs (68)
by worklog-noreply＠askmonty.org 27 Nov '09

27 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Efficient NOT IN execution with NULLs CREATION DATE..: Fri, 27 Nov 2009, 13:22 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 68 (http://askmonty.org/worklog/?tid=68) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Timour - Fri, 27 Nov 2009, 13:23)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.17140 2009-11-27 11:23:17.000000000 +0000 +++ /tmp/wklog.68.new.17140 2009-11-27 11:23:17.000000000 +0000 @@ -1 +1,72 @@ +This a copy of the initial algorithm proposed by Igor: +====================================================== +For each left side tuple (v_1,...,v_n) we have to find the following set +of rowids for the temp table containing N rows as the result of +materialization of the subquery: + + R= INTERSECT (rowid{a_i=v_i} UNION rowid{a_i is null} where i runs +trough all indexes from [1..n] such that v_i is not null. + +Bear in mind the following specifics of this intersection: + (1) For each i: rowid{a_i=v_i} and rowid{a_i is null} are disjoint + (2) For each i: rowid{a_i is null} is the same for each tuple + +Due to (2) it makes sense to build rowid{a_i is null} only once. +A good representation for such sets would be bitmaps: +- it requires minimum memory: not more than N*n bits in total +- search of an element in a set is extremely cheap + +Taken all above into account I could suggest the following algorithm to +build R: + + Using indexes (read about them below) for each column participating in the + intersection, + merge ordered sets rowid{a_i=v_i} in the following manner. + If a rowid r has been encountered maximum in k sets +rowid{a_i1=v_i1},...,rowid(a_ik=v_ik), + then it has to be checked against all rowid{a_i=v_i} such that i is +not in {i1,...,ik}. + As soon as we fail to find r in one of these sets we discard it. + If r has been found in all of them then r belongs to the set R. + +Here we use the property (1): any r from rowid{a_i=v_i} UNION rowid{a_i +is null} is either +belongs to rowid{a_i=v_i} or to rowid{a_i is null}. From this we can +infer that for any r from R +indexes a_i can be uniquely divided into two groups: one contains +indexes a_i where r belongs to +the sets rowid{a_i=v_i}, the other contains indexes a_j such that r +belongs to rowid{a_j is null}. + +Now let's talk how to get elements from rowid{a_i=v_i} in a sorted order +needed for the merge procedure. We could use BTREE indexes for temp +table. But they are rather expensive and +take a lot of memory as the are implemented with RB trees. +I would suggest creating for each column from the temporary table just +an array of rowids sorted by the value from column a. +Index lookup in such an array is cheap. It's also rather cheap to check +that the next rowid refers to a row with a different value in column a. +The array can be created on demand. + +Other consideration that may be taken into account: +1. If columns a_j1,...,a_jm do not contain null values in the temporary +table at all, create for them only one index array (and of course do not +create any bitmaps for them). +2. Consider the ratio d(a_i)=N'/ V(a_i), where N' is the number of rows +where a_i is not null and V(a_i) is the number of distinct values for +a_i excluding nulls. + If d(a_i) is close to 1 then do not create any index array: check +whether there is a match running through the records that have been +filtered in. Anyway if d(a_i) is close to 1 then a intersection with +rowid{a_i=v_i} would not reduce the number of remaining rowids +significantly. + If additionally N-N' is small do not create a bitmap for this column +either. +3. If for a column a_i d(a_i) is not close to 1, but N-N' is small a +sorted array of rowids from the set rowid{a_i is null} can be used +instead of a bitmap. +4. We always have a match if R0= INTERSECT rowid{a_i is null} is not +empty. Here i runs through all indexes from [1..n] such that v_i is not +null. For a given subset of columns this fact has to be checked only +once. It can be easily done with bitmap intersection. DESCRIPTION: The goal of this task is to implement efficient execution of NOT IN subquery predicates of the form: <oe_1,...,oe_n> NOT IN <non_correlated subquery> when either some oe_i, or some subqury result column contains NULLs. The problem with such predicates is that it is possible to use index lookups only when neither argument of the predicate contains NULLs. If some argument contains a NULL, then due to NULL semantics, it plays the role of a wildcard. If we were to use regular index lookups, then we would get 'no match' for some outer tuple (thus the predicate evaluates to FALSE), while the SQL semantics means 'partial match', and the predicate should evaluate to NULL. This task implements an efficient algorithm to compute such 'parial matches', where a NULL matches any value. HIGH-LEVEL SPECIFICATION: This a copy of the initial algorithm proposed by Igor: ====================================================== For each left side tuple (v_1,...,v_n) we have to find the following set of rowids for the temp table containing N rows as the result of materialization of the subquery: R= INTERSECT (rowid{a_i=v_i} UNION rowid{a_i is null} where i runs trough all indexes from [1..n] such that v_i is not null. Bear in mind the following specifics of this intersection: (1) For each i: rowid{a_i=v_i} and rowid{a_i is null} are disjoint (2) For each i: rowid{a_i is null} is the same for each tuple Due to (2) it makes sense to build rowid{a_i is null} only once. A good representation for such sets would be bitmaps: - it requires minimum memory: not more than N*n bits in total - search of an element in a set is extremely cheap Taken all above into account I could suggest the following algorithm to build R: Using indexes (read about them below) for each column participating in the intersection, merge ordered sets rowid{a_i=v_i} in the following manner. If a rowid r has been encountered maximum in k sets rowid{a_i1=v_i1},...,rowid(a_ik=v_ik), then it has to be checked against all rowid{a_i=v_i} such that i is not in {i1,...,ik}. As soon as we fail to find r in one of these sets we discard it. If r has been found in all of them then r belongs to the set R. Here we use the property (1): any r from rowid{a_i=v_i} UNION rowid{a_i is null} is either belongs to rowid{a_i=v_i} or to rowid{a_i is null}. From this we can infer that for any r from R indexes a_i can be uniquely divided into two groups: one contains indexes a_i where r belongs to the sets rowid{a_i=v_i}, the other contains indexes a_j such that r belongs to rowid{a_j is null}. Now let's talk how to get elements from rowid{a_i=v_i} in a sorted order needed for the merge procedure. We could use BTREE indexes for temp table. But they are rather expensive and take a lot of memory as the are implemented with RB trees. I would suggest creating for each column from the temporary table just an array of rowids sorted by the value from column a. Index lookup in such an array is cheap. It's also rather cheap to check that the next rowid refers to a row with a different value in column a. The array can be created on demand. Other consideration that may be taken into account: 1. If columns a_j1,...,a_jm do not contain null values in the temporary table at all, create for them only one index array (and of course do not create any bitmaps for them). 2. Consider the ratio d(a_i)=N'/ V(a_i), where N' is the number of rows where a_i is not null and V(a_i) is the number of distinct values for a_i excluding nulls. If d(a_i) is close to 1 then do not create any index array: check whether there is a match running through the records that have been filtered in. Anyway if d(a_i) is close to 1 then a intersection with rowid{a_i=v_i} would not reduce the number of remaining rowids significantly. If additionally N-N' is small do not create a bitmap for this column either. 3. If for a column a_i d(a_i) is not close to 1, but N-N' is small a sorted array of rowids from the set rowid{a_i is null} can be used instead of a bitmap. 4. We always have a match if R0= INTERSECT rowid{a_i is null} is not empty. Here i runs through all indexes from [1..n] such that v_i is not null. For a given subset of columns this fact has to be checked only once. It can be easily done with bitmap intersection. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Timour): Subquery optimization: Efficient NOT IN execution with NULLs (68)
by worklog-noreply＠askmonty.org 27 Nov '09

27 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Efficient NOT IN execution with NULLs CREATION DATE..: Fri, 27 Nov 2009, 13:22 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 68 (http://askmonty.org/worklog/?tid=68) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Timour - Fri, 27 Nov 2009, 13:23)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.17140 2009-11-27 11:23:17.000000000 +0000 +++ /tmp/wklog.68.new.17140 2009-11-27 11:23:17.000000000 +0000 @@ -1 +1,72 @@ +This a copy of the initial algorithm proposed by Igor: +====================================================== +For each left side tuple (v_1,...,v_n) we have to find the following set +of rowids for the temp table containing N rows as the result of +materialization of the subquery: + + R= INTERSECT (rowid{a_i=v_i} UNION rowid{a_i is null} where i runs +trough all indexes from [1..n] such that v_i is not null. + +Bear in mind the following specifics of this intersection: + (1) For each i: rowid{a_i=v_i} and rowid{a_i is null} are disjoint + (2) For each i: rowid{a_i is null} is the same for each tuple + +Due to (2) it makes sense to build rowid{a_i is null} only once. +A good representation for such sets would be bitmaps: +- it requires minimum memory: not more than N*n bits in total +- search of an element in a set is extremely cheap + +Taken all above into account I could suggest the following algorithm to +build R: + + Using indexes (read about them below) for each column participating in the + intersection, + merge ordered sets rowid{a_i=v_i} in the following manner. + If a rowid r has been encountered maximum in k sets +rowid{a_i1=v_i1},...,rowid(a_ik=v_ik), + then it has to be checked against all rowid{a_i=v_i} such that i is +not in {i1,...,ik}. + As soon as we fail to find r in one of these sets we discard it. + If r has been found in all of them then r belongs to the set R. + +Here we use the property (1): any r from rowid{a_i=v_i} UNION rowid{a_i +is null} is either +belongs to rowid{a_i=v_i} or to rowid{a_i is null}. From this we can +infer that for any r from R +indexes a_i can be uniquely divided into two groups: one contains +indexes a_i where r belongs to +the sets rowid{a_i=v_i}, the other contains indexes a_j such that r +belongs to rowid{a_j is null}. + +Now let's talk how to get elements from rowid{a_i=v_i} in a sorted order +needed for the merge procedure. We could use BTREE indexes for temp +table. But they are rather expensive and +take a lot of memory as the are implemented with RB trees. +I would suggest creating for each column from the temporary table just +an array of rowids sorted by the value from column a. +Index lookup in such an array is cheap. It's also rather cheap to check +that the next rowid refers to a row with a different value in column a. +The array can be created on demand. + +Other consideration that may be taken into account: +1. If columns a_j1,...,a_jm do not contain null values in the temporary +table at all, create for them only one index array (and of course do not +create any bitmaps for them). +2. Consider the ratio d(a_i)=N'/ V(a_i), where N' is the number of rows +where a_i is not null and V(a_i) is the number of distinct values for +a_i excluding nulls. + If d(a_i) is close to 1 then do not create any index array: check +whether there is a match running through the records that have been +filtered in. Anyway if d(a_i) is close to 1 then a intersection with +rowid{a_i=v_i} would not reduce the number of remaining rowids +significantly. + If additionally N-N' is small do not create a bitmap for this column +either. +3. If for a column a_i d(a_i) is not close to 1, but N-N' is small a +sorted array of rowids from the set rowid{a_i is null} can be used +instead of a bitmap. +4. We always have a match if R0= INTERSECT rowid{a_i is null} is not +empty. Here i runs through all indexes from [1..n] such that v_i is not +null. For a given subset of columns this fact has to be checked only +once. It can be easily done with bitmap intersection. DESCRIPTION: The goal of this task is to implement efficient execution of NOT IN subquery predicates of the form: <oe_1,...,oe_n> NOT IN <non_correlated subquery> when either some oe_i, or some subqury result column contains NULLs. The problem with such predicates is that it is possible to use index lookups only when neither argument of the predicate contains NULLs. If some argument contains a NULL, then due to NULL semantics, it plays the role of a wildcard. If we were to use regular index lookups, then we would get 'no match' for some outer tuple (thus the predicate evaluates to FALSE), while the SQL semantics means 'partial match', and the predicate should evaluate to NULL. This task implements an efficient algorithm to compute such 'parial matches', where a NULL matches any value. HIGH-LEVEL SPECIFICATION: This a copy of the initial algorithm proposed by Igor: ====================================================== For each left side tuple (v_1,...,v_n) we have to find the following set of rowids for the temp table containing N rows as the result of materialization of the subquery: R= INTERSECT (rowid{a_i=v_i} UNION rowid{a_i is null} where i runs trough all indexes from [1..n] such that v_i is not null. Bear in mind the following specifics of this intersection: (1) For each i: rowid{a_i=v_i} and rowid{a_i is null} are disjoint (2) For each i: rowid{a_i is null} is the same for each tuple Due to (2) it makes sense to build rowid{a_i is null} only once. A good representation for such sets would be bitmaps: - it requires minimum memory: not more than N*n bits in total - search of an element in a set is extremely cheap Taken all above into account I could suggest the following algorithm to build R: Using indexes (read about them below) for each column participating in the intersection, merge ordered sets rowid{a_i=v_i} in the following manner. If a rowid r has been encountered maximum in k sets rowid{a_i1=v_i1},...,rowid(a_ik=v_ik), then it has to be checked against all rowid{a_i=v_i} such that i is not in {i1,...,ik}. As soon as we fail to find r in one of these sets we discard it. If r has been found in all of them then r belongs to the set R. Here we use the property (1): any r from rowid{a_i=v_i} UNION rowid{a_i is null} is either belongs to rowid{a_i=v_i} or to rowid{a_i is null}. From this we can infer that for any r from R indexes a_i can be uniquely divided into two groups: one contains indexes a_i where r belongs to the sets rowid{a_i=v_i}, the other contains indexes a_j such that r belongs to rowid{a_j is null}. Now let's talk how to get elements from rowid{a_i=v_i} in a sorted order needed for the merge procedure. We could use BTREE indexes for temp table. But they are rather expensive and take a lot of memory as the are implemented with RB trees. I would suggest creating for each column from the temporary table just an array of rowids sorted by the value from column a. Index lookup in such an array is cheap. It's also rather cheap to check that the next rowid refers to a row with a different value in column a. The array can be created on demand. Other consideration that may be taken into account: 1. If columns a_j1,...,a_jm do not contain null values in the temporary table at all, create for them only one index array (and of course do not create any bitmaps for them). 2. Consider the ratio d(a_i)=N'/ V(a_i), where N' is the number of rows where a_i is not null and V(a_i) is the number of distinct values for a_i excluding nulls. If d(a_i) is close to 1 then do not create any index array: check whether there is a match running through the records that have been filtered in. Anyway if d(a_i) is close to 1 then a intersection with rowid{a_i=v_i} would not reduce the number of remaining rowids significantly. If additionally N-N' is small do not create a bitmap for this column either. 3. If for a column a_i d(a_i) is not close to 1, but N-N' is small a sorted array of rowids from the set rowid{a_i is null} can be used instead of a bitmap. 4. We always have a match if R0= INTERSECT rowid{a_i is null} is not empty. Here i runs through all indexes from [1..n] such that v_i is not null. For a given subset of columns this fact has to be checked only once. It can be easily done with bitmap intersection. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Timour): Subquery optimization: Efficient NOT IN execution with NULLs (68)
by worklog-noreply＠askmonty.org 27 Nov '09

27 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Efficient NOT IN execution with NULLs CREATION DATE..: Fri, 27 Nov 2009, 13:22 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 68 (http://askmonty.org/worklog/?tid=68) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: The goal of this task is to implement efficient execution of NOT IN subquery predicates of the form: <oe_1,...,oe_n> NOT IN <non_correlated subquery> when either some oe_i, or some subqury result column contains NULLs. The problem with such predicates is that it is possible to use index lookups only when neither argument of the predicate contains NULLs. If some argument contains a NULL, then due to NULL semantics, it plays the role of a wildcard. If we were to use regular index lookups, then we would get 'no match' for some outer tuple (thus the predicate evaluates to FALSE), while the SQL semantics means 'partial match', and the predicate should evaluate to NULL. This task implements an efficient algorithm to compute such 'parial matches', where a NULL matches any value. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Timour): Subquery optimization: Efficient NOT IN execution with NULLs (68)
by worklog-noreply＠askmonty.org 27 Nov '09

27 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Efficient NOT IN execution with NULLs CREATION DATE..: Fri, 27 Nov 2009, 13:22 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 68 (http://askmonty.org/worklog/?tid=68) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: The goal of this task is to implement efficient execution of NOT IN subquery predicates of the form: <oe_1,...,oe_n> NOT IN <non_correlated subquery> when either some oe_i, or some subqury result column contains NULLs. The problem with such predicates is that it is possible to use index lookups only when neither argument of the predicate contains NULLs. If some argument contains a NULL, then due to NULL semantics, it plays the role of a wildcard. If we were to use regular index lookups, then we would get 'no match' for some outer tuple (thus the predicate evaluates to FALSE), while the SQL semantics means 'partial match', and the predicate should evaluate to NULL. This task implements an efficient algorithm to compute such 'parial matches', where a NULL matches any value. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Monty): Provide key cache statistics (58)
by worklog-noreply＠askmonty.org 27 Nov '09

27 Nov '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Provide key cache statistics CREATION DATE..: Thu, 22 Oct 2009, 12:28 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 58 (http://askmonty.org/worklog/?tid=58) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Monty - Fri, 27 Nov 2009, 12:36)=-=- High Level Description modified. --- /tmp/wklog.58.old.15108 2009-11-27 10:36:13.000000000 +0000 +++ /tmp/wklog.58.new.15108 2009-11-27 10:36:13.000000000 +0000 @@ -1,8 +1,7 @@ -Provide the key cache -statistics available for the default cache (key blocks used, -unflushed, etc.) for all named caches. +Provide the same key cache statistics for all MyISAM key caches as we provide +for the default key cache. -Monty answers in an email: +Background: Monty answers in an email to customer: This is a much simpler task and something that is important to get done. We would have to introduce a 'show keycache statistics' command @@ -11,3 +10,44 @@ The raw coding is probably 1-2 days, but on top if this we need testing, a test environment and building of biniaries for you. Henrik/Bo and Igor will come back to you with a more exact estimate. + +----------------- +Currently we have: + +show status like "key%"; ++------------------------+-------+ +| Variable_name | Value | ++------------------------+-------+ +| Key_blocks_not_flushed | 0 | +| Key_blocks_unused | 13389 | +| Key_blocks_used | 7 | +| Key_read_requests | 22 | +| Key_reads | 7 | +| Key_write_requests | 0 | +| Key_writes | 0 | ++------------------------+-------+ + +Sergei suggested we introduce for each new key cache a set of variables prefixed +with the keycache name: + +show status like "keycache1.%"; +Which should show: + ++----------------------------------+-------+ +| Variable_name | Value | ++----------------------------------+-------+ +| keycache1.Key_blocks_not_flushed | 0 | +| keycache1.Key_blocks_unused | 13389 | +| keycache1.Key_blocks_used | 7 | +| keycache1.Key_read_requests | 22 | +| keycache1.Key_reads | 7 | +| keycache1.Key_write_requests | 0 | +| keycache1.Key_writes | 0 | ++----------------------------------+-------+ + +The task would thus to be to automatically introduce new variables when we +create a new key cache and automatically remove these variables when the cache +is deleted. + +The other option would be to add an information schema where we populate the +information schema with data from all existing key caches. DESCRIPTION: Provide the same key cache statistics for all MyISAM key caches as we provide for the default key cache. Background: Monty answers in an email to customer: This is a much simpler task and something that is important to get done. We would have to introduce a 'show keycache statistics' command that would iterate over all keycaches and provide the statistics. The raw coding is probably 1-2 days, but on top if this we need testing, a test environment and building of biniaries for you. Henrik/Bo and Igor will come back to you with a more exact estimate. ----------------- Currently we have: show status like "key%"; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 13389 | | Key_blocks_used | 7 | | Key_read_requests | 22 | | Key_reads | 7 | | Key_write_requests | 0 | | Key_writes | 0 | +------------------------+-------+ Sergei suggested we introduce for each new key cache a set of variables prefixed with the keycache name: show status like "keycache1.%"; Which should show: +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | keycache1.Key_blocks_not_flushed | 0 | | keycache1.Key_blocks_unused | 13389 | | keycache1.Key_blocks_used | 7 | | keycache1.Key_read_requests | 22 | | keycache1.Key_reads | 7 | | keycache1.Key_write_requests | 0 | | keycache1.Key_writes | 0 | +----------------------------------+-------+ The task would thus to be to automatically introduce new variables when we create a new key cache and automatically remove these variables when the cache is deleted. The other option would be to add an information schema where we populate the information schema with data from all existing key caches. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Test failures in PBXT after merging with MySQL-5.1.41
by Kristian Nielsen 27 Nov '09

27 Nov '09

Hi Paul, Vladimir, We are working on merging latest MySQL (5.1.41) into MariaDB. After the merge, I see some test failures in the PBXT test suite. See here: https://askmonty.org/buildbot/builders/hardy-amd64-fulltest/builds/118/step… Or if you prefer, see here for a search for these failures in the build history database: http://askmonty.org/buildbot/reports/cross_reference#branch=5.1-merge&revis… >From a quick look, it seems to me most/all of these are just the need to adjust result files for changes/bugfixes in MySQL. But before spending time on fixing, I though it was better to coordinate with you, to not duplicate effort. So perhaps you already did the similar changes for the PBXT testsuite against MySQL 5.1.41? Is there maybe a newer version of PBXT that we should merge? Or alternatively, do you want me to fix the testsuite, and then send you a patch for merging into PBXT to update to 5.1.41? - Kristian.

3 11