- developers - lists.mariadb.org

[Maria-developers] Rev 2773: BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off in file:///home/psergey/dev/maria-5.3-subqueries-r7/
by Sergey Petrunya 07 Mar '10

07 Mar '10

At file:///home/psergey/dev/maria-5.3-subqueries-r7/ ------------------------------------------------------------ revno: 2773 revision-id: psergey(a)askmonty.org-20100307154145-ksby2b1l0sqm1xne parent: igor(a)askmonty.org-20100306191455-g2srime08w9u6l49 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r7 timestamp: Sun 2010-03-07 18:41:45 +0300 message: BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off - The problem was that DuplicateWeedout strategy setup code wasn't aware of the fact that join buffering will be used and applied optimization that doesn't work together with join buffering. Fixed by making DuplicateWeedout setup code to have a pessimistic check about whether there is a chance that join buffering will be used. - Make JOIN_CACHE_BKA::init() correctly process Copy_field elements that denote saving current rowids in the join buffer. === modified file 'mysql-test/r/subselect_sj2.result' --- a/mysql-test/r/subselect_sj2.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_sj2.result 2010-03-07 15:41:45 +0000 @@ -264,8 +264,8 @@ from t0 where a in (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary drop table t0, t1,t2,t3; === modified file 'mysql-test/r/subselect_sj2_jcl6.result' --- a/mysql-test/r/subselect_sj2_jcl6.result 2010-03-05 18:54:48 +0000 +++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-03-07 15:41:45 +0000 @@ -268,8 +268,8 @@ from t0 where a in (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary drop table t0, t1,t2,t3; @@ -421,20 +421,23 @@ where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and t1.b=t2.b); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary; Using join buffer +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Start temporary +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using join buffer 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary; Using join buffer Warnings: Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t0`.`a`) and (`test`.`t2`.`a` = `test`.`t0`.`a`)) update t1 set a=3, b=11 where a=4; update t2 set b=11 where a=3; - +# Not anymore: # The following query gives wrong result due to Bug#49129 select * from t0 where t0.a in (select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b); a 0 +1 +2 +3 drop table t0, t1, t2; CREATE TABLE t1 ( id int(11) NOT NULL, === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-03-05 18:54:48 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-03-07 15:41:45 +0000 @@ -875,6 +875,37 @@ DROP TABLE t1, t2, t3; DROP VIEW v2, v3; # End of Bug#49198 +# +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off +# +CREATE TABLE t0 (a INT); +INSERT INTO t0 VALUES (0),(1),(2),(3),(4); +CREATE TABLE t1 (a INT, b INT, KEY(a)); +INSERT INTO t1 SELECT a, a from t0; +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a)); +INSERT INTO t2 SELECT * FROM t1; +UPDATE t1 SET a=3, b=11 WHERE a=4; +UPDATE t2 SET b=11 WHERE a=3; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='firstmatch=off'; +The following should use a join order of t0,t1,t2, with DuplicateElimination: +explain +SELECT * FROM t0 WHERE t0.a IN +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Start temporary +1 PRIMARY t1 ref a a 5 test.t0.a 1 Using join buffer +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary; Using join buffer +SELECT * FROM t0 WHERE t0.a IN +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b); +a +0 +1 +2 +3 +set optimizer_switch=@save_optimizer_switch; +drop table t0, t1, t2; +# End set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value === modified file 'mysql-test/t/subselect_sj2.test' --- a/mysql-test/t/subselect_sj2.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_sj2.test 2010-03-07 15:41:45 +0000 @@ -583,7 +583,7 @@ if (`select @@join_cache_level=6`) { - --echo + --echo # Not anymore: --echo # The following query gives wrong result due to Bug#49129 } select * from t0 where t0.a in === modified file 'mysql-test/t/subselect_sj_jcl6.test' --- a/mysql-test/t/subselect_sj_jcl6.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_sj_jcl6.test 2010-03-07 15:41:45 +0000 @@ -7,5 +7,33 @@ --source t/subselect_sj.test +--echo # +--echo # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off +--echo # +CREATE TABLE t0 (a INT); +INSERT INTO t0 VALUES (0),(1),(2),(3),(4); +CREATE TABLE t1 (a INT, b INT, KEY(a)); +INSERT INTO t1 SELECT a, a from t0; +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a)); +INSERT INTO t2 SELECT * FROM t1; +UPDATE t1 SET a=3, b=11 WHERE a=4; +UPDATE t2 SET b=11 WHERE a=3; + +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='firstmatch=off'; + +--echo The following should use a join order of t0,t1,t2, with DuplicateElimination: +explain +SELECT * FROM t0 WHERE t0.a IN + (SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b); + +SELECT * FROM t0 WHERE t0.a IN + (SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b); + +set optimizer_switch=@save_optimizer_switch; +drop table t0, t1, t2; + +--echo # End + set join_cache_level=default; show variables like 'join_cache_level'; === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2010-02-25 08:09:10 +0000 +++ b/sql/opt_subselect.cc 2010-03-07 15:41:45 +0000 @@ -3035,10 +3035,24 @@ forwards, but do not destroy other duplicate elimination methods. */ uint first_table= i; + uint join_cache_level= join->thd->variables.join_cache_level; for (uint j= i; j < i + pos->n_sj_tables; j++) { - if (join->best_positions[j].use_join_buffer && j <= no_jbuf_after) + /* + When we'll properly take join buffering into account during + join optimization, the below check should be changed to + "if (join->best_positions[j].use_join_buffer && + j <= no_jbuf_after)". + For now, use a rough criteria: + */ + JOIN_TAB *js_tab=join->join_tab + j; + if (j != join->const_tables && js_tab->use_quick != 2 && + j <= no_jbuf_after && + ((js_tab->type == JT_ALL && join_cache_level != 0) || + (join_cache_level > 4 && (tab->type == JT_REF || + tab->type == JT_EQ_REF)))) { + /* Looks like we'll be using join buffer */ first_table= join->const_tables; break; } @@ -3116,7 +3130,12 @@ JOIN_TAB *j, *jump_to= tab-1; for (j= tab; j != tab + pos->n_sj_tables; j++) { - if (!tab->emb_sj_nest) + /* + NOTE: this loop probably doesn't do the right thing for the case + where FirstMatch's duplicate-generating range is interleaved with + "unrelated" tables (as specified in WL#3750, section 2.2). + */ + if (!j->emb_sj_nest) jump_to= tab; else { === modified file 'sql/sql_join_cache.cc' --- a/sql/sql_join_cache.cc 2010-03-06 19:14:55 +0000 +++ b/sql/sql_join_cache.cc 2010-03-07 15:41:45 +0000 @@ -608,7 +608,12 @@ copy_end= cache->field_descr+cache->fields; for (copy= cache->field_descr+cache->flag_fields; copy < copy_end; copy++) { - if (copy->field->table == tab->table && + /* + (1) - when we store rowids for DuplicateWeedout, they have + copy->field==NULL + */ + if (copy->field && // (1) + copy->field->table == tab->table && bitmap_is_set(key_read_set, copy->field->field_index)) { *copy_ptr++= copy; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-03-05 18:54:48 +0000 +++ b/sql/sql_select.cc 2010-03-07 15:41:45 +0000 @@ -5632,7 +5632,11 @@ uint blob_length=(uint) (join_tab->table->file->stats.mean_rec_length- (join_tab->table->s->reclength-rec_length)); rec_length+=(uint) max(4,blob_length); - } + } + /* + psergey-todo: why we don't count here rowid that we might need to store + when using DuplicateElimination? + */ join_tab->used_fields=fields; join_tab->used_fieldlength=rec_length; join_tab->used_blobs=blobs;

1 0

[Maria-developers] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2772) Bug#51092
by Igor Babaev 06 Mar '10

06 Mar '10

#At lp:maria/5.2 based on revid:igor@askmonty.org-20100305185448-satt36ksg5xxkwji 2772 Igor Babaev 2010-03-06 Fixed bug #51092. The function JOIN_CACHE::read_all_record_fields could return 0 for an incremental join cache in two cases: 1. there were no more records in the associated join buffer 2. there was no table fields stored in the join buffer. As a result the function JOIN_CACHE::get_record() could return prematurely and did not read all needed fields from join buffers into the record buffer. Now the function JOIN_CACHE::read_all_record_fields returns -1 if there are no more records in the associated join buffer. modified: mysql-test/r/join_cache.result mysql-test/t/join_cache.test sql/sql_join_cache.cc === modified file 'mysql-test/r/join_cache.result' --- a/mysql-test/r/join_cache.result 2010-02-11 21:59:32 +0000 +++ b/mysql-test/r/join_cache.result 2010-03-06 19:14:55 +0000 @@ -4142,3 +4142,46 @@ c1 c2 c1 c2 LENGTH(t2.c1) LENGTH(t2.c2) 2 2 tt uu 2 2 set join_cache_level=default; DROP TABLE t1,t2; +# +# Bug #51092: linked join buffer is used for a 3-way cross join query +# that selects only records of the first table +# +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2); +create table t2 (a int, b int); +insert into t2 values (1,1),(2,2); +create table t3 (a int, b int); +insert into t3 values (1,1),(2,2); +explain select t1.* from t1,t2,t3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using join buffer +select t1.* from t1,t2,t3; +a b +1 1 +2 2 +1 1 +2 2 +1 1 +2 2 +1 1 +2 2 +set join_cache_level=2; +explain select t1.* from t1,t2,t3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using join buffer +select t1.* from t1,t2,t3; +a b +1 1 +2 2 +1 1 +2 2 +1 1 +2 2 +1 1 +2 2 +set join_cache_level=default; +drop table t1,t2,t3; === modified file 'mysql-test/t/join_cache.test' --- a/mysql-test/t/join_cache.test 2009-12-21 02:26:15 +0000 +++ b/mysql-test/t/join_cache.test 2010-03-06 19:14:55 +0000 @@ -1823,3 +1823,27 @@ SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH set join_cache_level=default; DROP TABLE t1,t2; + +--echo # +--echo # Bug #51092: linked join buffer is used for a 3-way cross join query +--echo # that selects only records of the first table +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2); +create table t2 (a int, b int); +insert into t2 values (1,1),(2,2); +create table t3 (a int, b int); +insert into t3 values (1,1),(2,2); + +explain select t1.* from t1,t2,t3; +select t1.* from t1,t2,t3; + +set join_cache_level=2; + +explain select t1.* from t1,t2,t3; +select t1.* from t1,t2,t3; + +set join_cache_level=default; + +drop table t1,t2,t3; === modified file 'sql/sql_join_cache.cc' --- a/sql/sql_join_cache.cc 2010-03-05 18:54:48 +0000 +++ b/sql/sql_join_cache.cc 2010-03-06 19:14:55 +0000 @@ -31,6 +31,8 @@ #include "sql_select.h" #include "opt_subselect.h" +#define NO_MORE_RECORDS_IN_BUFFER (uint)(-1) + /***************************************************************************** * Join cache module @@ -1237,7 +1239,7 @@ bool JOIN_CACHE::get_record() prev_rec_ptr= prev_cache->get_rec_ref(pos); } curr_rec_pos= pos; - if (!(res= read_all_record_fields() == 0)) + if (!(res= read_all_record_fields() == NO_MORE_RECORDS_IN_BUFFER)) { pos+= referenced_fields*size_of_fld_ofs; if (prev_cache) @@ -1326,7 +1328,8 @@ bool JOIN_CACHE::get_match_flag_by_pos(u read data. RETURN - length of the data read from the join buffer + (-1) - if there is no more records in the join buffer + length of the data read from the join buffer - otherwise */ uint JOIN_CACHE::read_all_record_fields() @@ -1334,7 +1337,7 @@ uint JOIN_CACHE::read_all_record_fields( uchar *init_pos= pos; if (pos > last_rec_pos || !records) - return 0; + return NO_MORE_RECORDS_IN_BUFFER; /* First match flag, read null bitmaps and null_row flag for each table */ read_flag_fields();

1 0

[Maria-developers] Rev 2822: LPBUG#524025 Running RQG outer_join test leads to crash in file:///home/psergey/dev/maria-5.1/
by Sergey Petrunya 06 Mar '10

06 Mar '10

At file:///home/psergey/dev/maria-5.1/ ------------------------------------------------------------ revno: 2822 revision-id: psergey(a)askmonty.org-20100306120902-0pu47w0ahogzi0fd parent: sergii(a)pisem.net-20100223120458-fv2gp4qjxpfcqibj committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.1 timestamp: Sat 2010-03-06 15:09:02 +0300 message: LPBUG#524025 Running RQG outer_join test leads to crash Save no-records constant tables in JOIN::const_table_map before we invoke eliminate_tables(). Failure to do so caused crash when the same table was marked as constant two times === modified file 'mysql-test/r/table_elim.result' --- a/mysql-test/r/table_elim.result 2010-02-18 12:03:52 +0000 +++ b/mysql-test/r/table_elim.result 2010-03-06 12:09:02 +0000 @@ -1,4 +1,4 @@ -drop table if exists t0, t1, t2, t3; +drop table if exists t0, t1, t2, t3, t4, t5, t6; drop view if exists v1, v2; create table t1 (a int); insert into t1 values (0),(1),(2),(3); @@ -464,3 +464,74 @@ HAVING field1 < 216; field1 DROP TABLE t1, t2; +# +# LPBUG#524025 Running RQG outer_join test leads to crash +# +CREATE TABLE t0 ( +pk int(11) NOT NULL AUTO_INCREMENT, +PRIMARY KEY (pk) +); +CREATE TABLE t1 ( +col_int int(11) DEFAULT NULL, +col_int_key int(11) DEFAULT NULL, +pk int(11) NOT NULL AUTO_INCREMENT, +col_varchar_10_latin1 varchar(10) DEFAULT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (5,5,1,'t'), (NULL,NULL,2,'y'); +CREATE TABLE t2 ( +col_int int(11) DEFAULT NULL +); +INSERT INTO t2 VALUES (8), (4); +CREATE TABLE t3 ( +pk int(11) NOT NULL AUTO_INCREMENT, +PRIMARY KEY (pk) +); +INSERT INTO t3 VALUES (1),(8); +CREATE TABLE t4 ( +pk int(11) NOT NULL AUTO_INCREMENT, +col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, +col_int int(11) DEFAULT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t4 VALUES (1,'o',1), (2,'w',2); +CREATE TABLE t5 ( +col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL, +col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, +col_varchar_10_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL, +pk int(11) NOT NULL AUTO_INCREMENT, +col_int_key int(11) DEFAULT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t5 VALUES ('k','a','z',1,2),('x','a','w',2,7); +CREATE TABLE t6 ( +col_int int(11) DEFAULT NULL, +col_int_key int(11) DEFAULT NULL +); +INSERT INTO t6 VALUES (6,1),(8,3); +SELECT +table3.col_int AS field1, +table1.col_int AS field2, +table1.col_int_key AS field3, +table1.pk AS field4, +table1.col_int AS field5, +table2.col_int AS field6 +FROM +t1 AS table1 +LEFT OUTER JOIN +t4 AS table2 +LEFT JOIN t6 AS table3 +RIGHT JOIN t3 AS table4 +LEFT JOIN t5 AS table5 ON table4.pk = table5.pk +LEFT JOIN t0 AS table6 ON table5.col_int_key = table6.pk +ON table3.col_int_key = table5.pk +ON table2.col_varchar_1024_latin1_key = table5.col_varchar_10_utf8_key +LEFT JOIN t6 AS table7 ON table2.pk = table7.col_int +ON table1.col_varchar_10_latin1 = table5.col_varchar_1024_latin1_key +LEFT JOIN t2 AS table8 ON table3.col_int = table8.col_int +WHERE +table1.col_int_key < table2.pk +HAVING +field4 != 6; +field1 field2 field3 field4 field5 field6 +drop table t0,t1,t2,t3,t4,t5,t6; === modified file 'mysql-test/t/table_elim.test' --- a/mysql-test/t/table_elim.test 2010-02-18 12:03:52 +0000 +++ b/mysql-test/t/table_elim.test 2010-03-06 12:09:02 +0000 @@ -2,7 +2,7 @@ # Table elimination (MWL#17) tests # --disable_warnings -drop table if exists t0, t1, t2, t3; +drop table if exists t0, t1, t2, t3, t4, t5, t6; drop view if exists v1, v2; --enable_warnings @@ -387,3 +387,83 @@ DROP TABLE t1, t2; + +--echo # +--echo # LPBUG#524025 Running RQG outer_join test leads to crash +--echo # + +CREATE TABLE t0 ( + pk int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (pk) +); + +CREATE TABLE t1 ( + col_int int(11) DEFAULT NULL, + col_int_key int(11) DEFAULT NULL, + pk int(11) NOT NULL AUTO_INCREMENT, + col_varchar_10_latin1 varchar(10) DEFAULT NULL, + PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (5,5,1,'t'), (NULL,NULL,2,'y'); + +CREATE TABLE t2 ( + col_int int(11) DEFAULT NULL +); +INSERT INTO t2 VALUES (8), (4); + +CREATE TABLE t3 ( + pk int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (pk) +); +INSERT INTO t3 VALUES (1),(8); + +CREATE TABLE t4 ( + pk int(11) NOT NULL AUTO_INCREMENT, + col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, + col_int int(11) DEFAULT NULL, + PRIMARY KEY (pk) +); +INSERT INTO t4 VALUES (1,'o',1), (2,'w',2); + +CREATE TABLE t5 ( + col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL, + col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, + col_varchar_10_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL, + pk int(11) NOT NULL AUTO_INCREMENT, + col_int_key int(11) DEFAULT NULL, + PRIMARY KEY (pk) +); +INSERT INTO t5 VALUES ('k','a','z',1,2),('x','a','w',2,7); + +CREATE TABLE t6 ( + col_int int(11) DEFAULT NULL, + col_int_key int(11) DEFAULT NULL +); +INSERT INTO t6 VALUES (6,1),(8,3); + +SELECT + table3.col_int AS field1, + table1.col_int AS field2, + table1.col_int_key AS field3, + table1.pk AS field4, + table1.col_int AS field5, + table2.col_int AS field6 +FROM + t1 AS table1 + LEFT OUTER JOIN + t4 AS table2 + LEFT JOIN t6 AS table3 + RIGHT JOIN t3 AS table4 + LEFT JOIN t5 AS table5 ON table4.pk = table5.pk + LEFT JOIN t0 AS table6 ON table5.col_int_key = table6.pk + ON table3.col_int_key = table5.pk + ON table2.col_varchar_1024_latin1_key = table5.col_varchar_10_utf8_key + LEFT JOIN t6 AS table7 ON table2.pk = table7.col_int + ON table1.col_varchar_10_latin1 = table5.col_varchar_1024_latin1_key + LEFT JOIN t2 AS table8 ON table3.col_int = table8.col_int +WHERE + table1.col_int_key < table2.pk +HAVING + field4 != 6; + +drop table t0,t1,t2,t3,t4,t5,t6; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-01-15 15:27:55 +0000 +++ b/sql/sql_select.cc 2010-03-06 12:09:02 +0000 @@ -2562,6 +2562,7 @@ JOIN_TAB *stat,*stat_end,*s,**stat_ref; KEYUSE *keyuse,*start_keyuse; table_map outer_join=0; + table_map no_rows_const_tables= 0; SARGABLE_PARAM *sargables= 0; JOIN_TAB *stat_vector[MAX_TABLES+1]; DBUG_ENTER("make_join_statistics"); @@ -2622,6 +2623,7 @@ #endif { // Empty table s->dependent= 0; // Ignore LEFT JOIN depend. + no_rows_const_tables |= table->map; set_position(join,const_count++,s,(KEYUSE*) 0); continue; } @@ -2658,6 +2660,7 @@ !table->fulltext_searched && !join->no_const_tables) { set_position(join,const_count++,s,(KEYUSE*) 0); + no_rows_const_tables |= table->map; } } stat_vector[i]=0; @@ -2703,9 +2706,10 @@ ~outer_join, join->select_lex, &sargables)) goto error; - join->const_table_map= 0; + join->const_table_map= no_rows_const_tables; join->const_tables= const_count; eliminate_tables(join); + join->const_table_map &= ~no_rows_const_tables; const_count= join->const_tables; found_const_table_map= join->const_table_map;

1 0

[Maria-developers] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2771) Bug#45191
by Igor Babaev 05 Mar '10

05 Mar '10

#At lp:maria/5.2 based on revid:psergey@askmonty.org-20100225080910-38wapbgw5vpm6nax 2771 Igor Babaev 2010-03-05 Corrected Evgen's fix for bug #45191. Made sure that join buffers could be used for inner tables of any semi-join when the first match strategy is employed. modified: mysql-test/r/subselect_sj2_jcl6.result mysql-test/r/subselect_sj_jcl6.result sql/sql_join_cache.cc sql/sql_select.cc sql/sql_select.h === modified file 'mysql-test/r/subselect_sj2_jcl6.result' --- a/mysql-test/r/subselect_sj2_jcl6.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-03-05 18:54:48 +0000 @@ -713,9 +713,9 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2); Using join buffer drop table t2, t3; set join_cache_level=default; show variables like 'join_cache_level'; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-02-24 11:33:42 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-03-05 18:54:48 +0000 @@ -374,8 +374,8 @@ WHERE PNUM IN (SELECT PNUM FROM PROJ)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY STAFF ALL NULL NULL NULL NULL 5 -1 PRIMARY PROJ ALL NULL NULL NULL NULL 6 -1 PRIMARY WORKS ALL NULL NULL NULL NULL 12 Using where; FirstMatch(STAFF) +1 PRIMARY PROJ ALL NULL NULL NULL NULL 6 Using join buffer +1 PRIMARY WORKS ALL NULL NULL NULL NULL 12 Using where; FirstMatch(STAFF); Using join buffer SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN === modified file 'sql/sql_join_cache.cc' --- a/sql/sql_join_cache.cc 2010-02-15 21:53:06 +0000 +++ b/sql/sql_join_cache.cc 2010-03-05 18:54:48 +0000 @@ -407,8 +407,10 @@ void JOIN_CACHE::set_constants() However at this moment we don't know whether we have referenced fields for the cache or not. Later when a referenced field is registered for the cache we adjust the value of the flag 'with_length'. - */ - with_length= is_key_access() || with_match_flag; + */ + with_length= is_key_access() || + join_tab->is_inner_table_of_semi_join_with_first_match() || + join_tab->is_inner_table_of_outer_join(); /* At this moment we don't know yet the value of 'referenced_fields', but in any case it can't be greater than the value of 'fields'. @@ -1304,7 +1306,7 @@ bool JOIN_CACHE::get_match_flag_by_pos(u uchar *prev_rec_ptr= prev_cache->get_rec_ref(rec_ptr); return prev_cache->get_match_flag_by_pos(prev_rec_ptr); } - DBUG_ASSERT(1); + DBUG_ASSERT(0); return FALSE; } @@ -1538,12 +1540,12 @@ bool JOIN_CACHE::read_referenced_field(C bool JOIN_CACHE::skip_record_if_match() { - DBUG_ASSERT(with_match_flag && with_length); + DBUG_ASSERT(with_length); uint offset= size_of_rec_len; if (prev_cache) offset+= prev_cache->get_size_of_rec_offset(); /* Check whether the match flag is on */ - if (test(*(pos+offset))) + if (get_match_flag_by_pos(pos+offset)) { pos+= size_of_rec_len + get_rec_length(pos); return TRUE; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-02-17 21:59:41 +0000 +++ b/sql/sql_select.cc 2010-03-05 18:54:48 +0000 @@ -6352,10 +6352,17 @@ make_outerjoin_info(JOIN *join) } if (!tab->first_inner) tab->first_inner= nested_join->first_nested; + if (tab->table->reginfo.not_exists_optimize) + tab->first_inner->table->reginfo.not_exists_optimize= 1; if (++nested_join->counter < nested_join->n_tables) break; /* Table tab is the last inner table for nested join. */ nested_join->first_nested->last_inner= tab; + if (tab->first_inner->table->reginfo.not_exists_optimize) + { + for (JOIN_TAB *join_tab= tab->first_inner; join_tab <= tab; join_tab++) + join_tab->table->reginfo.not_exists_optimize= 1; + } } } DBUG_VOID_RETURN; @@ -7109,18 +7116,14 @@ uint check_join_cache_usage(JOIN_TAB *ta if (tab->use_quick == 2) goto no_join_cache; /* - Use join cache with FirstMatch semi-join strategy only when semi-join - contains only one table. - */ - if (tab->is_inner_table_of_semi_join_with_first_match() && - !tab->is_single_inner_of_semi_join_with_first_match()) - goto no_join_cache; - /* Non-linked join buffers can't guarantee one match */ - if (force_unlinked_cache && - (tab->is_inner_table_of_outer_join() && - !tab->is_single_inner_of_outer_join())) + if (force_unlinked_cache && + (!tab->type == JT_ALL || cache_level <= 4) && + ((tab->is_inner_table_of_semi_join_with_first_match() && + !tab->is_single_inner_of_semi_join_with_first_match()) || + (tab->is_inner_table_of_outer_join() && + !tab->is_single_inner_of_outer_join()))) goto no_join_cache; /* === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2010-02-15 21:53:06 +0000 +++ b/sql/sql_select.h 2010-03-05 18:54:48 +0000 @@ -321,8 +321,8 @@ typedef struct st_join_table { } bool check_only_first_match() { - return last_sj_inner_tab == this || - (first_inner && first_inner->last_inner == this && + return is_inner_table_of_semi_join_with_first_match() || + (is_inner_table_of_outer_join() && table->reginfo.not_exists_optimize); } bool is_last_inner_table()

1 0

[Maria-developers] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2771) Bug#45191
by Igor Babaev 05 Mar '10

05 Mar '10

#At lp:maria/5.2 based on revid:psergey@askmonty.org-20100225080910-38wapbgw5vpm6nax 2771 Igor Babaev 2010-03-04 Corrected Evgen's fix for bug #45191. Made sure that join buffers could be used for inner tables of any semi-join when the first match strategy is employed. modified: mysql-test/r/subselect_sj2_jcl6.result mysql-test/r/subselect_sj_jcl6.result sql/sql_join_cache.cc sql/sql_select.cc === modified file 'mysql-test/r/subselect_sj2_jcl6.result' --- a/mysql-test/r/subselect_sj2_jcl6.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-03-05 03:46:31 +0000 @@ -713,9 +713,9 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2); Using join buffer drop table t2, t3; set join_cache_level=default; show variables like 'join_cache_level'; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-02-24 11:33:42 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-03-05 03:46:31 +0000 @@ -374,8 +374,8 @@ WHERE PNUM IN (SELECT PNUM FROM PROJ)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY STAFF ALL NULL NULL NULL NULL 5 -1 PRIMARY PROJ ALL NULL NULL NULL NULL 6 -1 PRIMARY WORKS ALL NULL NULL NULL NULL 12 Using where; FirstMatch(STAFF) +1 PRIMARY PROJ ALL NULL NULL NULL NULL 6 Using join buffer +1 PRIMARY WORKS ALL NULL NULL NULL NULL 12 Using where; FirstMatch(STAFF); Using join buffer SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN === modified file 'sql/sql_join_cache.cc' --- a/sql/sql_join_cache.cc 2010-02-15 21:53:06 +0000 +++ b/sql/sql_join_cache.cc 2010-03-05 03:46:31 +0000 @@ -407,8 +407,10 @@ void JOIN_CACHE::set_constants() However at this moment we don't know whether we have referenced fields for the cache or not. Later when a referenced field is registered for the cache we adjust the value of the flag 'with_length'. - */ - with_length= is_key_access() || with_match_flag; + */ + with_length= is_key_access() || + join_tab->last_sj_inner_tab == join_tab || + join_tab->first_inner == join_tab; /* At this moment we don't know yet the value of 'referenced_fields', but in any case it can't be greater than the value of 'fields'. @@ -1304,7 +1306,7 @@ bool JOIN_CACHE::get_match_flag_by_pos(u uchar *prev_rec_ptr= prev_cache->get_rec_ref(rec_ptr); return prev_cache->get_match_flag_by_pos(prev_rec_ptr); } - DBUG_ASSERT(1); + DBUG_ASSERT(0); return FALSE; } @@ -1538,12 +1540,12 @@ bool JOIN_CACHE::read_referenced_field(C bool JOIN_CACHE::skip_record_if_match() { - DBUG_ASSERT(with_match_flag && with_length); + DBUG_ASSERT(with_length); uint offset= size_of_rec_len; if (prev_cache) offset+= prev_cache->get_size_of_rec_offset(); /* Check whether the match flag is on */ - if (test(*(pos+offset))) + if (get_match_flag_by_pos(pos+offset)) { pos+= size_of_rec_len + get_rec_length(pos); return TRUE; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-02-17 21:59:41 +0000 +++ b/sql/sql_select.cc 2010-03-05 03:46:31 +0000 @@ -7109,18 +7109,14 @@ uint check_join_cache_usage(JOIN_TAB *ta if (tab->use_quick == 2) goto no_join_cache; /* - Use join cache with FirstMatch semi-join strategy only when semi-join - contains only one table. - */ - if (tab->is_inner_table_of_semi_join_with_first_match() && - !tab->is_single_inner_of_semi_join_with_first_match()) - goto no_join_cache; - /* Non-linked join buffers can't guarantee one match */ - if (force_unlinked_cache && - (tab->is_inner_table_of_outer_join() && - !tab->is_single_inner_of_outer_join())) + if (force_unlinked_cache && + (!tab->type == JT_ALL || cache_level <= 4) && + ((tab->is_inner_table_of_semi_join_with_first_match() && + !tab->is_single_inner_of_semi_join_with_first_match()) || + (tab->is_inner_table_of_outer_join() && + !tab->is_single_inner_of_outer_join()))) goto no_join_cache; /*

1 0

[Maria-developers] Updated (by Guest): Benchmark suite for sysbench (100)
by worklog-noreply＠askmonty.org 04 Mar '10

04 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Benchmark suite for sysbench CREATION DATE..: Thu, 04 Mar 2010, 17:46 SUPERVISOR.....: Igor IMPLEMENTOR....: Hakan COPIES TO......: CATEGORY.......: Other TASK ID........: 100 (http://askmonty.org/worklog/?tid=100) VERSION........: Benchmarks-3.0 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 40 (hours remain) ORIG. ESTIMATE.: 40 PROGRESS NOTES: -=-=(Guest - Thu, 04 Mar 2010, 18:15)=-=- Low Level Design modified. --- /tmp/wklog.100.old.2695 2010-03-04 18:15:46.000000000 +0000 +++ /tmp/wklog.100.new.2695 2010-03-04 18:15:46.000000000 +0000 @@ -1 +1,112 @@ +All scripts can be found at lp:mariadb-tools/sysbench +* Run sysbench tests on a machine and collect numbers +This is implemented in run-sysbench.sh. Currently it supports: + ** Optionally pull of latest source from Launchpad and compile + ** Starting the server + ** Running each sysbench test for $LOOP_COUNT times and + $RUN_TIME time. + ** Number of concurrent clients is hardcoded + ** Machine specific configuration like location of binaries and + directories needed are in separate config files located at conf/<hostname>.inc + +The main loop of run-sysbench.sh is: + +start_mysqld +for SYSBENCH_TEST in $SYSBENCH_TESTS + for THREADS in $NUM_THREADS + while [ $k -lt $LOOP_COUNT ] + drop schema sbtest + create schema sbtest + + $SYSBENCH $SYSBENCH_OPTIONS prepare + $SYSBENCH $SYSBENCH_OPTIONS run + done + done +done + +Open items: + ** OProfile hook + ** iostat hook + ** sar -u (CPU utilization) hook + ** Crash detection + ** Error detection + ** Set random seed of sysbench to have better comparision + ** Restart mysqld from scratch for each run and copy away + DATA_DIR of the database for faster starts. + ** Between each run, run sync and clear file system caches with + echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc) + +* Analyze numbers +This is implemented in analyze-sysbench.php + +Open items: + ** Read result files from + ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}/results.txt + + ** Collect OProfile, iostat, cpu utilization, and machine info + ** Detect errors and crashes. + ** Generate SQL INSERT strings for presentation usage + +The layout for storing the numbers is: + CREATE TABLE sysbench_run ( + id int unsigned NOT NULL auto_increment, + host varchar(80), -- Hostname we ran the test on. + run_date date, -- The day we ran the test. + sysbench_version varchar(32), -- Version of sysbench we used. + test_name varchar(32), -- Name of the sysbench test. + run_time int unsigned, -- Run time in seconds. + runs int unsigned, -- Number of iterations of the test. + PRIMARY KEY (id), + KEY (host), + KEY (run_date + ); + + CREATE TABLE sysbench_comment ( + id int unsigned NOT NULL auto_increment, + sysbench_run_id int unsigned NOT NULL, -- FK pointing to sysbench_run. + compile_info text, -- Compile options we used. + machine_info text, -- Details about the hardware. + sysbench_options text, -- The sysbench options we used. + PRIMARY KEY (id), + KEY (sysbench_run_id) + ); + + CREATE TABLE sysbench_result ( + id int unsigned NOT NULL auto_increment, + sysbench_run_id int unsigned NOT NULL, -- FK pointing to sysbench_run. + concurrency int unsigned, -- Concurrency level we used. + result decimal(7,2), -- The actual result. + io varchar(80), -- The IO from iostat. + cpu varchar(80), -- CPU utilization. + profile text, -- Profiling information. + error text, -- Error messages and stack traces. + PRIMARY KEY (id), + KEY (sysbench_run_id + ); + +* Generate a report out of the numbers +This script will generate a HTML version for putting up on the web and a +txt version for email usage. + +Open items: + ** Generate an overview table in the form of + Number of threads + 1 4 8 16 32 64 128 +sysbench test + delete 121.52 144.77 117.70 115.15 100.48 75.39 66.56 + mean value of runs + 1 first run + 2 second run + 3 third run + STDEV + STDEV in % of mean + CPU utilization (usr/sys/wait/idle) + IO (read/write) + +For HTML version additionally generate a graph with JPGraph. + +* Get machine(s) and run the test on a weekly basis and for each release +comparing with the prior release. + +* Email weekly reports and blog about it. DESCRIPTION: Create a benchmark suite for running sysbench * Run sysbench tests on a machine and collect numbers * Analyze numbers * Generate a report out of the numbers LOW-LEVEL DESIGN: All scripts can be found at lp:mariadb-tools/sysbench * Run sysbench tests on a machine and collect numbers This is implemented in run-sysbench.sh. Currently it supports: ** Optionally pull of latest source from Launchpad and compile ** Starting the server ** Running each sysbench test for $LOOP_COUNT times and $RUN_TIME time. ** Number of concurrent clients is hardcoded ** Machine specific configuration like location of binaries and directories needed are in separate config files located at conf/<hostname>.inc The main loop of run-sysbench.sh is: start_mysqld for SYSBENCH_TEST in $SYSBENCH_TESTS for THREADS in $NUM_THREADS while [ $k -lt $LOOP_COUNT ] drop schema sbtest create schema sbtest $SYSBENCH $SYSBENCH_OPTIONS prepare $SYSBENCH $SYSBENCH_OPTIONS run done done done Open items: ** OProfile hook ** iostat hook ** sar -u (CPU utilization) hook ** Crash detection ** Error detection ** Set random seed of sysbench to have better comparision ** Restart mysqld from scratch for each run and copy away DATA_DIR of the database for faster starts. ** Between each run, run sync and clear file system caches with echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc) * Analyze numbers This is implemented in analyze-sysbench.php Open items: ** Read result files from ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}/results.txt ** Collect OProfile, iostat, cpu utilization, and machine info ** Detect errors and crashes. ** Generate SQL INSERT strings for presentation usage The layout for storing the numbers is: CREATE TABLE sysbench_run ( id int unsigned NOT NULL auto_increment, host varchar(80), -- Hostname we ran the test on. run_date date, -- The day we ran the test. sysbench_version varchar(32), -- Version of sysbench we used. test_name varchar(32), -- Name of the sysbench test. run_time int unsigned, -- Run time in seconds. runs int unsigned, -- Number of iterations of the test. PRIMARY KEY (id), KEY (host), KEY (run_date ); CREATE TABLE sysbench_comment ( id int unsigned NOT NULL auto_increment, sysbench_run_id int unsigned NOT NULL, -- FK pointing to sysbench_run. compile_info text, -- Compile options we used. machine_info text, -- Details about the hardware. sysbench_options text, -- The sysbench options we used. PRIMARY KEY (id), KEY (sysbench_run_id) ); CREATE TABLE sysbench_result ( id int unsigned NOT NULL auto_increment, sysbench_run_id int unsigned NOT NULL, -- FK pointing to sysbench_run. concurrency int unsigned, -- Concurrency level we used. result decimal(7,2), -- The actual result. io varchar(80), -- The IO from iostat. cpu varchar(80), -- CPU utilization. profile text, -- Profiling information. error text, -- Error messages and stack traces. PRIMARY KEY (id), KEY (sysbench_run_id ); * Generate a report out of the numbers This script will generate a HTML version for putting up on the web and a txt version for email usage. Open items: ** Generate an overview table in the form of Number of threads 1 4 8 16 32 64 128 sysbench test delete 121.52 144.77 117.70 115.15 100.48 75.39 66.56 mean value of runs 1 first run 2 second run 3 third run STDEV STDEV in % of mean CPU utilization (usr/sys/wait/idle) IO (read/write) For HTML version additionally generate a graph with JPGraph. * Get machine(s) and run the test on a weekly basis and for each release comparing with the prior release. * Email weekly reports and blog about it. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Benchmark suite for sysbench (100)
by worklog-noreply＠askmonty.org 04 Mar '10

04 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Benchmark suite for sysbench CREATION DATE..: Thu, 04 Mar 2010, 17:46 SUPERVISOR.....: Igor IMPLEMENTOR....: Hakan COPIES TO......: CATEGORY.......: Other TASK ID........: 100 (http://askmonty.org/worklog/?tid=100) VERSION........: Benchmarks-3.0 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 40 (hours remain) ORIG. ESTIMATE.: 40 PROGRESS NOTES: -=-=(Guest - Thu, 04 Mar 2010, 18:15)=-=- Low Level Design modified. --- /tmp/wklog.100.old.2695 2010-03-04 18:15:46.000000000 +0000 +++ /tmp/wklog.100.new.2695 2010-03-04 18:15:46.000000000 +0000 @@ -1 +1,112 @@ +All scripts can be found at lp:mariadb-tools/sysbench +* Run sysbench tests on a machine and collect numbers +This is implemented in run-sysbench.sh. Currently it supports: + ** Optionally pull of latest source from Launchpad and compile + ** Starting the server + ** Running each sysbench test for $LOOP_COUNT times and + $RUN_TIME time. + ** Number of concurrent clients is hardcoded + ** Machine specific configuration like location of binaries and + directories needed are in separate config files located at conf/<hostname>.inc + +The main loop of run-sysbench.sh is: + +start_mysqld +for SYSBENCH_TEST in $SYSBENCH_TESTS + for THREADS in $NUM_THREADS + while [ $k -lt $LOOP_COUNT ] + drop schema sbtest + create schema sbtest + + $SYSBENCH $SYSBENCH_OPTIONS prepare + $SYSBENCH $SYSBENCH_OPTIONS run + done + done +done + +Open items: + ** OProfile hook + ** iostat hook + ** sar -u (CPU utilization) hook + ** Crash detection + ** Error detection + ** Set random seed of sysbench to have better comparision + ** Restart mysqld from scratch for each run and copy away + DATA_DIR of the database for faster starts. + ** Between each run, run sync and clear file system caches with + echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc) + +* Analyze numbers +This is implemented in analyze-sysbench.php + +Open items: + ** Read result files from + ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}/results.txt + + ** Collect OProfile, iostat, cpu utilization, and machine info + ** Detect errors and crashes. + ** Generate SQL INSERT strings for presentation usage + +The layout for storing the numbers is: + CREATE TABLE sysbench_run ( + id int unsigned NOT NULL auto_increment, + host varchar(80), -- Hostname we ran the test on. + run_date date, -- The day we ran the test. + sysbench_version varchar(32), -- Version of sysbench we used. + test_name varchar(32), -- Name of the sysbench test. + run_time int unsigned, -- Run time in seconds. + runs int unsigned, -- Number of iterations of the test. + PRIMARY KEY (id), + KEY (host), + KEY (run_date + ); + + CREATE TABLE sysbench_comment ( + id int unsigned NOT NULL auto_increment, + sysbench_run_id int unsigned NOT NULL, -- FK pointing to sysbench_run. + compile_info text, -- Compile options we used. + machine_info text, -- Details about the hardware. + sysbench_options text, -- The sysbench options we used. + PRIMARY KEY (id), + KEY (sysbench_run_id) + ); + + CREATE TABLE sysbench_result ( + id int unsigned NOT NULL auto_increment, + sysbench_run_id int unsigned NOT NULL, -- FK pointing to sysbench_run. + concurrency int unsigned, -- Concurrency level we used. + result decimal(7,2), -- The actual result. + io varchar(80), -- The IO from iostat. + cpu varchar(80), -- CPU utilization. + profile text, -- Profiling information. + error text, -- Error messages and stack traces. + PRIMARY KEY (id), + KEY (sysbench_run_id + ); + +* Generate a report out of the numbers +This script will generate a HTML version for putting up on the web and a +txt version for email usage. + +Open items: + ** Generate an overview table in the form of + Number of threads + 1 4 8 16 32 64 128 +sysbench test + delete 121.52 144.77 117.70 115.15 100.48 75.39 66.56 + mean value of runs + 1 first run + 2 second run + 3 third run + STDEV + STDEV in % of mean + CPU utilization (usr/sys/wait/idle) + IO (read/write) + +For HTML version additionally generate a graph with JPGraph. + +* Get machine(s) and run the test on a weekly basis and for each release +comparing with the prior release. + +* Email weekly reports and blog about it. DESCRIPTION: Create a benchmark suite for running sysbench * Run sysbench tests on a machine and collect numbers * Analyze numbers * Generate a report out of the numbers LOW-LEVEL DESIGN: All scripts can be found at lp:mariadb-tools/sysbench * Run sysbench tests on a machine and collect numbers This is implemented in run-sysbench.sh. Currently it supports: ** Optionally pull of latest source from Launchpad and compile ** Starting the server ** Running each sysbench test for $LOOP_COUNT times and $RUN_TIME time. ** Number of concurrent clients is hardcoded ** Machine specific configuration like location of binaries and directories needed are in separate config files located at conf/<hostname>.inc The main loop of run-sysbench.sh is: start_mysqld for SYSBENCH_TEST in $SYSBENCH_TESTS for THREADS in $NUM_THREADS while [ $k -lt $LOOP_COUNT ] drop schema sbtest create schema sbtest $SYSBENCH $SYSBENCH_OPTIONS prepare $SYSBENCH $SYSBENCH_OPTIONS run done done done Open items: ** OProfile hook ** iostat hook ** sar -u (CPU utilization) hook ** Crash detection ** Error detection ** Set random seed of sysbench to have better comparision ** Restart mysqld from scratch for each run and copy away DATA_DIR of the database for faster starts. ** Between each run, run sync and clear file system caches with echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc) * Analyze numbers This is implemented in analyze-sysbench.php Open items: ** Read result files from ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}/results.txt ** Collect OProfile, iostat, cpu utilization, and machine info ** Detect errors and crashes. ** Generate SQL INSERT strings for presentation usage The layout for storing the numbers is: CREATE TABLE sysbench_run ( id int unsigned NOT NULL auto_increment, host varchar(80), -- Hostname we ran the test on. run_date date, -- The day we ran the test. sysbench_version varchar(32), -- Version of sysbench we used. test_name varchar(32), -- Name of the sysbench test. run_time int unsigned, -- Run time in seconds. runs int unsigned, -- Number of iterations of the test. PRIMARY KEY (id), KEY (host), KEY (run_date ); CREATE TABLE sysbench_comment ( id int unsigned NOT NULL auto_increment, sysbench_run_id int unsigned NOT NULL, -- FK pointing to sysbench_run. compile_info text, -- Compile options we used. machine_info text, -- Details about the hardware. sysbench_options text, -- The sysbench options we used. PRIMARY KEY (id), KEY (sysbench_run_id) ); CREATE TABLE sysbench_result ( id int unsigned NOT NULL auto_increment, sysbench_run_id int unsigned NOT NULL, -- FK pointing to sysbench_run. concurrency int unsigned, -- Concurrency level we used. result decimal(7,2), -- The actual result. io varchar(80), -- The IO from iostat. cpu varchar(80), -- CPU utilization. profile text, -- Profiling information. error text, -- Error messages and stack traces. PRIMARY KEY (id), KEY (sysbench_run_id ); * Generate a report out of the numbers This script will generate a HTML version for putting up on the web and a txt version for email usage. Open items: ** Generate an overview table in the form of Number of threads 1 4 8 16 32 64 128 sysbench test delete 121.52 144.77 117.70 115.15 100.48 75.39 66.56 mean value of runs 1 first run 2 second run 3 third run STDEV STDEV in % of mean CPU utilization (usr/sys/wait/idle) IO (read/write) For HTML version additionally generate a graph with JPGraph. * Get machine(s) and run the test on a weekly basis and for each release comparing with the prior release. * Email weekly reports and blog about it. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Hakan): Benchmark suite for sysbench (100)
by worklog-noreply＠askmonty.org 04 Mar '10

04 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Benchmark suite for sysbench CREATION DATE..: Thu, 04 Mar 2010, 17:46 SUPERVISOR.....: Igor IMPLEMENTOR....: Hakan COPIES TO......: CATEGORY.......: Other TASK ID........: 100 (http://askmonty.org/worklog/?tid=100) VERSION........: Benchmarks-3.0 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 40 (hours remain) ORIG. ESTIMATE.: 40 PROGRESS NOTES: DESCRIPTION: Create a benchmark suite for running sysbench * Run sysbench tests on a machine and collect numbers * Analyze numbers * Generate a report out of the numbers ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Hakan): Benchmark suite for sysbench (100)
by worklog-noreply＠askmonty.org 04 Mar '10

04 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Benchmark suite for sysbench CREATION DATE..: Thu, 04 Mar 2010, 17:46 SUPERVISOR.....: Igor IMPLEMENTOR....: Hakan COPIES TO......: CATEGORY.......: Other TASK ID........: 100 (http://askmonty.org/worklog/?tid=100) VERSION........: Benchmarks-3.0 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 40 (hours remain) ORIG. ESTIMATE.: 40 PROGRESS NOTES: DESCRIPTION: Create a benchmark suite for running sysbench * Run sysbench tests on a machine and collect numbers * Analyze numbers * Generate a report out of the numbers ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] LOCK_plugin in pbxt_init()
by Sergei Golubchik 04 Mar '10

04 Mar '10

Hi, Paul, I was backporting libservices to MariaDB, and with a related test case found a bug in MariaDB - mutexes were locked in the wrong order. I've fixed that some time ago in mysql-next, so I took the fix from there - the fix was to not lock LOCK_plugin over the plugin initialization code. After that pbxt started crashing here: #if MYSQL_VERSION_ID < 60014 myxt_mutex_unlock(&LOCK_plugin); #endif as it was unlocking the mutex that was not locked. For now I simply commented out your locking/unlocking of this mutex in the MariaDB tree. But probably you may want a better fix, may be even your FREERER-HANG problem will go away too ? I've submitted http://bugs.mysql.com/51591 hopefully it will be fixed in MySQL too. Regards, Sergei

3 3