- developers - lists.mariadb.org

[Maria-developers] Patch to fix the dbug printout crash in 5.3-subqueries
by Sergey Petrunya 20 Apr '10

20 Apr '10

Hi Sanja, Here is the fix: === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2010-03-29 20:09:40 +0000 +++ sql/sql_select.cc 2010-04-19 17:59:46 +0000 @@ -5214,7 +5214,7 @@ greedy_search(JOIN *join, 'join->best_positions' contains a complete optimal extension of the current partial QEP. */ - DBUG_EXECUTE("opt", print_plan(join, join->tables, + DBUG_EXECUTE("opt", print_plan(join, n_tables, record_count, read_time, read_time, "optimal");); DBUG_RETURN(FALSE); feel free to apply/push or I'll do it today night. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Getting a stable Windows host into Buildbot
by Kristian Nielsen 15 Apr '10

15 Apr '10

[Cc:ing maria-developers@, in case anyone can help with suggestions or is just generally interested. For background see http://askmonty.org/wiki/BuildBot::package] Hi Bo, Just a quick summary of our discussions about setting up a Windows Buildbot host in Buildbot using KVM to get things running stably. As our discussions in the booth at the MySQL conference did tend to get a bit fragmented due to constant interuptions :-) We need to set up a Windows installation (version to be determined, windows 7 or XP I guess). I guess having both 32 and 64 bit would be good. We need the following images: 1) Base image. A plain installation of Windows with no compilers etc. Setup to get internet access from dhcp. It needs to be running in some kind of 'server mode', we will be using it headless without any interactive desktop login. Don't know if anything special is required to set this up. It needs an account `buildbot' with a passwordless login via ssh using this public key: ssh-dss AAAAB3NzaC1kc3MAAACBAPYQjJkZ/R+77yr8snKPgndVCdJub3DDYYc3HP8lNlI1hOHlbsDbkJCe62HkXxhHKpRbrCdmb4U5Zr0gLT4IN2jq+CdQr9tDZ7bxhJBWAopq8MBMvgDgeAAzXxtQkXHnR9D4vq3Bo2q/KT1bihWKc71B5q6hvhrCSx+OyRdyiZPxAAAAFQDqRhD5nEWjABWl+wVDZVjHBRjkiQAAAIEA8umFvbGb5g/k0SlrG39OXIxzG/uy/+igIKuqSDaF90fFA/ynI9PwHKoGr2+T/aoMeJ/bsbxdR+dj5ifTX7hIelhmRWcp4933hEu5Ownz3mzQA7mDjKOFiPwtMJuWClafxlORDg29gRBmuaRlciV7a6VrVbIsQpsiV/Oseqkg2ekAAACAdMfzaIWO4a1V/zX0eIZSDZgozVZ0xkKugAonJ7UP/EJz3R9plAclnMgnHH74P8d1EbtQN4GeeEa6iIRQyBaVEHHr25DsOIdIfgz8RVm9aqU+AAuZGhMzTTGRlaG9kj6ECga74fD8evvTRxaKWWq9SHK1qZt3L9IRaVxBJ82bhSM= buildbot@thor Any ssh implementation ought to work, I know cygwin is a possibility, but if there is something better that should be ok as well. 2) Build image, to run the build. This is a copy-on-write clone of the base image, with the addition of all compilers and tools needed to build MariaDB. Just like the existing native Windows machine we have in Buildbot currently. 3) Install image, to test installing the binaries. This is again a copy-on-write clone of the base image, with addition of any extra stuff needed to install MariaDB. I am guessing that there might not be anything extra needed at all (in particular it should have no compiler tools installed). As we discussed, the actual installation should be done using a recent KVM (our Buildbot is using the one from Ubuntu 9.10/karmic). There is a lot of details about how the existing (Linux) images were installed here: http://askmonty.org/wiki/BuildBot:vm-setup#Detail_of_each_vm_image The basic idea should be something like this: qemu-img create -f qcow2 vm-win-i386-base.qcow2 20G kvm -m 2047 -hda vm-win-amd64-base.qcow2 -cdrom <install ISO> -redir tcp:2222::22 -boot d -cpu qemu32 and then install the base system. For the Build vm image, something like this to make the copy-on-write clone: qemu-img create -b vm-win-i386-base.qcow2 -f qcow2 vm-win-i386-build.qcow2 kvm -m 2047 -hda vm-win-i386-build.qcow2 -cdrom <install ISO> -redir tcp:2222::22 -boot d -cpu qemu32 and then install compilers etc. Similarly for the install vm image qemu-img create -b vm-win-i386-base.qcow2 -f qcow2 vm-win-i386-install.qcow2 kvm -m 2047 -hda vm-win-i386-install.qcow2 -cdrom <install ISO> -redir tcp:2222::22 -boot d -cpu qemu32 Hope you/we will be able to work things out from here. - Kristian.

1 0

[Maria-developers] Rev 2790: MWL#66 Subquery cache (IN & single rsult) in file:///Users/bell/maria/bzr/work-maria-5.3-subqueries-cache/
by sanja＠askmonty.org 14 Apr '10

14 Apr '10

At file:///Users/bell/maria/bzr/work-maria-5.3-subqueries-cache/ ------------------------------------------------------------ revno: 2790 revision-id: sanja(a)askmonty.org-20100414203404-8bw8wicortsfmga5 parent: timour(a)askmonty.org-20100405211515-istsgehaz7zafg0l committer: sanja(a)askmonty.org branch nick: work-maria-5.3-subqueries-cache timestamp: Wed 2010-04-14 23:34:04 +0300 message: MWL#66 Subquery cache (IN & single rsult) === modified file 'libmysqld/Makefile.am' --- a/libmysqld/Makefile.am 2010-03-20 12:01:47 +0000 +++ b/libmysqld/Makefile.am 2010-04-14 20:34:04 +0000 @@ -80,7 +80,8 @@ sqlsources = derror.cc field.cc field_co sql_tablespace.cc \ rpl_injector.cc my_user.c partition_info.cc \ sql_servers.cc event_parse_data.cc opt_table_elimination.cc \ - multi_range_read.cc opt_index_cond_pushdown.cc + multi_range_read.cc opt_index_cond_pushdown.cc \ + sql_subquery_cache.cc libmysqld_int_a_SOURCES= $(libmysqld_sources) nodist_libmysqld_int_a_SOURCES= $(libmysqlsources) $(sqlsources) === added file 'mysql-test/r/subquery_cache.result' --- a/mysql-test/r/subquery_cache.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/r/subquery_cache.result 2010-04-14 20:34:04 +0000 @@ -0,0 +1,369 @@ +set optimizer_switch='subquery_cache=on'; +create table t1 (a int, b int); +insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5); +create table t2 (c int, d int); +insert into t2 values (2,3),(3,4),(5,6); +#single value subquery test +select a, (select d from t2 where b=c) + 1 from t1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +#single value subquery test (PS) +prepare stmt1 from 'select a, (select d from t2 where b=c) + 1 from t1'; +execute stmt1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 12 +Subquery_cache_miss 8 +execute stmt1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 18 +Subquery_cache_miss 12 +deallocate prepare stmt1; +#single value subquery test (SP) +CREATE PROCEDURE p1() select a, (select d from t2 where b=c) + 1 from t1; +call p1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +call p1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +drop procedure p1; +#IN subquery test +flush status; +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +select a, b , b in (select d from t2) as SUBS from t1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +insert into t1 values (7,8),(9,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +7 8 0 +9 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 12 +Subquery_cache_miss 10 +insert into t2 values (8,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +7 8 NULL +9 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 18 +Subquery_cache_miss 16 +#IN subquery tesy (PS) +delete from t1 where a > 6; +delete from t2 where c > 6; +prepare stmt1 from 'select a, b , b in (select d from t2) as SUBS from t1'; +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 24 +Subquery_cache_miss 20 +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 30 +Subquery_cache_miss 24 +insert into t1 values (7,8),(9,NULL); +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 36 +Subquery_cache_miss 30 +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 42 +Subquery_cache_miss 36 +insert into t2 values (8,NULL); +execute stmt1; +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 48 +Subquery_cache_miss 42 +execute stmt1; +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 54 +Subquery_cache_miss 48 +deallocate prepare stmt1; +#IN subquery tesy (SP) +delete from t1 where a > 6; +delete from t2 where c > 6; +CREATE PROCEDURE p1() select a, b , b in (select d from t2) as SUBS from t1; +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 60 +Subquery_cache_miss 52 +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 66 +Subquery_cache_miss 56 +insert into t1 values (7,8),(9,NULL); +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 72 +Subquery_cache_miss 62 +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 78 +Subquery_cache_miss 68 +insert into t2 values (8,NULL); +call p1(); +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 84 +Subquery_cache_miss 74 +call p1(); +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 90 +Subquery_cache_miss 80 +drop procedure p1; +#clean up +drop table t1,t2; +set optimizer_switch='subquery_cache=default'; === added file 'mysql-test/t/subquery_cache.test' --- a/mysql-test/t/subquery_cache.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/subquery_cache.test 2010-04-14 20:34:04 +0000 @@ -0,0 +1,98 @@ + +set optimizer_switch='subquery_cache=on'; + +create table t1 (a int, b int); +insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5); +create table t2 (c int, d int); +insert into t2 values (2,3),(3,4),(5,6); + +--echo #single value subquery test +select a, (select d from t2 where b=c) + 1 from t1; + +show status like "subquery_cache%"; + +--echo #single value subquery test (PS) +prepare stmt1 from 'select a, (select d from t2 where b=c) + 1 from t1'; +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; +deallocate prepare stmt1; + +--echo #single value subquery test (SP) +CREATE PROCEDURE p1() select a, (select d from t2 where b=c) + 1 from t1; + +call p1; +call p1; + +drop procedure p1; + +--echo #IN subquery test +flush status; + +show status like "subquery_cache%"; +select a, b , b in (select d from t2) as SUBS from t1; +show status like "subquery_cache%"; + +insert into t1 values (7,8),(9,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +show status like "subquery_cache%"; + +insert into t2 values (8,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +show status like "subquery_cache%"; + +--echo #IN subquery tesy (PS) +delete from t1 where a > 6; +delete from t2 where c > 6; + +prepare stmt1 from 'select a, b , b in (select d from t2) as SUBS from t1'; +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; + +insert into t1 values (7,8),(9,NULL); +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; + +insert into t2 values (8,NULL); +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; + +deallocate prepare stmt1; + + +--echo #IN subquery tesy (SP) +delete from t1 where a > 6; +delete from t2 where c > 6; + +CREATE PROCEDURE p1() select a, b , b in (select d from t2) as SUBS from t1; + +call p1(); +show status like "subquery_cache%"; +call p1(); +show status like "subquery_cache%"; + +insert into t1 values (7,8),(9,NULL); +call p1(); +show status like "subquery_cache%"; +call p1(); +show status like "subquery_cache%"; + +insert into t2 values (8,NULL); +call p1(); +show status like "subquery_cache%"; +call p1(); +show status like "subquery_cache%"; + +drop procedure p1; + +--echo #clean up +drop table t1,t2; + +set optimizer_switch='subquery_cache=default'; === modified file 'sql/CMakeLists.txt' --- a/sql/CMakeLists.txt 2010-03-20 12:01:47 +0000 +++ b/sql/CMakeLists.txt 2010-04-14 20:34:04 +0000 @@ -78,7 +78,7 @@ SET (SQL_SOURCE rpl_rli.cc rpl_mi.cc sql_servers.cc sql_connect.cc scheduler.cc sql_profile.cc event_parse_data.cc opt_table_elimination.cc - ds_mrr.cc + ds_mrr.cc sql_subquery_cache.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.h ${PROJECT_SOURCE_DIR}/include/mysqld_error.h === modified file 'sql/Makefile.am' --- a/sql/Makefile.am 2010-03-20 12:01:47 +0000 +++ b/sql/Makefile.am 2010-04-14 20:34:04 +0000 @@ -80,7 +80,7 @@ noinst_HEADERS = item.h item_func.h item event_data_objects.h event_scheduler.h \ sql_partition.h partition_info.h partition_element.h \ contributors.h sql_servers.h \ - multi_range_read.h + multi_range_read.h sql_subquery_cache.h mysqld_SOURCES = sql_lex.cc sql_handler.cc sql_partition.cc \ item.cc item_sum.cc item_buff.cc item_func.cc \ @@ -130,7 +130,7 @@ mysqld_SOURCES = sql_lex.cc sql_handler. sql_servers.cc event_parse_data.cc \ opt_table_elimination.cc \ multi_range_read.cc \ - opt_index_cond_pushdown.cc + opt_index_cond_pushdown.cc sql_subquery_cache.cc nodist_mysqld_SOURCES = mini_client_errors.c pack.c client.c my_time.c my_user.c === modified file 'sql/item.cc' --- a/sql/item.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item.cc 2010-04-14 20:34:04 +0000 @@ -2273,6 +2273,13 @@ void Item_int::print(String *str, enum_q str->append(str_value); } +void Item_bool_cache::print(String *str, enum_query_type query_type) +{ + if (null_value) + str->append("NULL", 4); + else + Item_int::print(str, query_type); +} Item_uint::Item_uint(const char *str_arg, uint length): Item_int(str_arg, length) @@ -3646,12 +3653,17 @@ static bool mark_as_dependent(THD *thd, resolved_item->db_name : ""); const char *table_name= (resolved_item->table_name ? resolved_item->table_name : ""); + DBUG_ENTER("mark_as_dependent"); + DBUG_PRINT("enter", ("Field '%s.%s.%s in select %d resolved in %d", + db_name, table_name, + resolved_item->field_name, current->select_number, + last->select_number)); /* store pointer on SELECT_LEX from which item is dependent */ if (mark_item) mark_item->depended_from= last; if (current->mark_as_dependent(thd, last, /** resolved_item psergey-thu **/mark_item)) - return TRUE; + DBUG_RETURN(TRUE); if (thd->lex->describe & DESCRIBE_EXTENDED) { push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, @@ -3661,7 +3673,7 @@ static bool mark_as_dependent(THD *thd, resolved_item->field_name, current->select_number, last->select_number); } - return FALSE; + DBUG_RETURN(FALSE); } @@ -3726,6 +3739,7 @@ void mark_select_range_as_dependent(THD mark_as_dependent(thd, last_select, current_sel, resolved_item, dependent); } + return; } === modified file 'sql/item.h' --- a/sql/item.h 2010-03-20 12:01:47 +0000 +++ b/sql/item.h 2010-04-14 20:34:04 +0000 @@ -1922,8 +1922,31 @@ public: virtual void print(String *str, enum_query_type query_type); Item_num *neg (); uint decimal_precision() const { return max_length; } - bool check_partition_func_processor(uchar *bool_arg) { return FALSE;} - bool check_vcol_func_processor(uchar *arg) { return FALSE;} +}; + + +/** + Item represent TRUE/FALSE/NULL for subquery values +*/ + +class Item_bool_cache: public Item_int +{ +public: + Item_bool_cache(): Item_int(0, 1) + { + unsigned_flag= maybe_null= null_value= TRUE; + name= (char *)"bool chache"; + } + Item_bool_cache(my_bool val, my_bool null): Item_int(val, 1) + { + unsigned_flag= maybe_null= TRUE; + null_value= null; + name= (char *)"bool chache"; + } + Item *clone_item() { return new Item_bool_cache(value, null_value); } + uint decimal_precision() const { return 1; } + virtual void print(String *str, enum_query_type query_type); + void set(my_bool val, my_bool null) {value= test(val); null_value= null;} }; @@ -3146,7 +3169,8 @@ public: example(0), used_table_map(0), cached_field(0), cached_field_type(MYSQL_TYPE_STRING), value_cached(0) { - fixed= 1; + fixed= 1; + maybe_null= 1; null_value= 1; } Item_cache(enum_field_types field_type_arg): @@ -3154,6 +3178,7 @@ public: value_cached(0) { fixed= 1; + maybe_null= 1; null_value= 1; } === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item_cmpfunc.cc 2010-04-14 20:34:04 +0000 @@ -1736,6 +1736,12 @@ bool Item_in_optimizer::fix_fields(THD * used_tables_cache|= args[1]->used_tables(); not_null_tables_cache|= args[1]->not_null_tables(); const_item_cache&= args[1]->const_item(); + DBUG_ASSERT(scache == NULL); + if (thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE) + { + sub->depends_on.push_front(cache); + scache= new Subquery_cache_tmptable(thd, sub->depends_on, &result); + } fixed= 1; return FALSE; } @@ -1744,10 +1750,26 @@ bool Item_in_optimizer::fix_fields(THD * longlong Item_in_optimizer::val_int() { bool tmp; + DBUG_ENTER("Item_in_optimizer::val_int"); + DBUG_ASSERT(fixed == 1); cache->store(args[0]); cache->cache_value(); - + + /* check if result is in the cache */ + if (scache) + { + Subquery_cache_tmptable::result res; + Item *cached_value; + res= scache->check_value(&cached_value); + if (res == Subquery_cache_tmptable::HIT) + { + tmp= cached_value->val_int(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + } + if (cache->null_value) { /* @@ -1818,11 +1840,18 @@ longlong Item_in_optimizer::val_int() for (uint i= 0; i < ncols; i++) item_subs->set_cond_guard_var(i, TRUE); } - return 0; + DBUG_RETURN(0); } tmp= args[1]->val_bool_result(); null_value= args[1]->null_value; - return tmp; + + /* put result in the cache */ + if (scache) + { + result.set(tmp, null_value); + scache->put_value(&result); + } + DBUG_RETURN(tmp); } @@ -1839,6 +1868,11 @@ void Item_in_optimizer::cleanup() Item_bool_func::cleanup(); if (!save_cache) cache= 0; + if (scache) + { + delete scache; + scache= 0; + } DBUG_VOID_RETURN; } === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2010-03-20 12:01:47 +0000 +++ b/sql/item_cmpfunc.h 2010-04-14 20:34:04 +0000 @@ -215,6 +215,7 @@ public: class Item_cache; +class Subquery_cache; #define UNKNOWN ((my_bool)-1) @@ -237,6 +238,10 @@ class Item_in_optimizer: public Item_boo { protected: Item_cache *cache; + /* Subquery cache */ + Subquery_cache *scache; + /* result representation for the subquery cache */ + Item_bool_cache result; bool save_cache; /* Stores the value of "NULL IN (SELECT ...)" for uncorrelated subqueries: @@ -247,7 +252,7 @@ protected: my_bool result_for_null_param; public: Item_in_optimizer(Item *a, Item_in_subselect *b): - Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), + Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), scache(NULL), save_cache(0), result_for_null_param(UNKNOWN) {} bool fix_fields(THD *, Item **); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-04-02 14:27:06 +0000 +++ b/sql/item_subselect.cc 2010-04-14 20:34:04 +0000 @@ -1,4 +1,4 @@ -/* Copyright (C) 2000 MySQL AB +/* Copyrigh (C) 2000 MySQL AB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -34,11 +34,10 @@ Item_subselect::Item_subselect(): Item_result_field(), value_assigned(0), thd(0), substitution(0), - engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0), - const_item_cache(1), - inside_first_fix_fields(0), done_first_fix_fields(FALSE), - eliminated(FALSE), - engine_changed(0), changed(0), is_correlated(FALSE) + engine(0), old_engine(0), scache(0), used_tables_cache(0), + have_to_be_excluded(0), const_item_cache(1), inside_first_fix_fields(0), + done_first_fix_fields(FALSE), eliminated(FALSE), engine_changed(0), + changed(0), is_correlated(FALSE) { with_subselect= 1; reset(); @@ -116,6 +115,12 @@ void Item_subselect::cleanup() } if (engine) engine->cleanup(); + depends_on.empty(); + if (scache) + { + delete scache; + scache= 0; + } reset(); value_assigned= 0; DBUG_VOID_RETURN; @@ -148,6 +153,8 @@ void Item_in_subselect::cleanup() Item_subselect::~Item_subselect() { delete engine; + if (scache) + delete scache; } Item_subselect::trans_res @@ -746,9 +753,19 @@ enum_field_types Item_singlerow_subselec void Item_singlerow_subselect::fix_length_and_dec() { + DBUG_ENTER("Item_singlerow_subselect::fix_length_and_dec"); if ((max_columns= engine->cols()) == 1) { + DBUG_PRINT("info", ("one, elements: %u flag %u", + (uint)depends_on.elements, + (uint)test(thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE))); engine->fix_length_and_dec(row= &value); + if (depends_on.elements && optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE)) + { + DBUG_ASSERT(scache == NULL); + scache= new Subquery_cache_tmptable(thd, depends_on, value); + DBUG_PRINT("info", ("cache: 0x%lx", (ulong) scache)); + } } else { @@ -765,6 +782,7 @@ void Item_singlerow_subselect::fix_lengt */ if (engine->no_tables()) maybe_null= engine->may_be_null(); + DBUG_VOID_RETURN; } uint Item_singlerow_subselect::cols() @@ -797,46 +815,117 @@ void Item_singlerow_subselect::bring_val exec(); } + +Item *Item_singlerow_subselect::check_cache() +{ + DBUG_ENTER("Item_singlerow_subselect::check_cache"); + if (scache) + { + Subquery_cache_tmptable::result res; + Item *cached_value; + res= scache->check_value(&cached_value); + if (res == Subquery_cache_tmptable::HIT) + DBUG_RETURN(cached_value); + } + DBUG_RETURN(NULL); +} + double Item_singlerow_subselect::val_real() { + Item *cached_value; DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + double res= cached_value->val_real(); + if ((null_value= cached_value->null_value)) + { + reset(); + return 0; + } + else + return res; + } + if (!exec() && !value->null_value) { null_value= 0; + if (scache) + scache->put_value(value); return value->val_real(); } else { reset(); + if (scache) + scache->put_value(&const_null_value); return 0; } } longlong Item_singlerow_subselect::val_int() { + Item *cached_value; + DBUG_ENTER("Item_singlerow_subselect::val_int"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + longlong res= cached_value->val_int(); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + if (!exec() && !value->null_value) { null_value= 0; - return value->val_int(); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_int()); } else { reset(); - return 0; + if (scache) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } String *Item_singlerow_subselect::val_str(String *str) { + Item *cached_value; + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + String *res= cached_value->val_str(str); + if ((null_value= cached_value->null_value)) + { + reset(); + return 0; + } + else + return res; + } + if (!exec() && !value->null_value) { null_value= 0; + if (scache) + scache->put_value(value); return value->val_str(str); } else { reset(); + if (scache) + scache->put_value(&const_null_value); return 0; } } @@ -844,14 +933,33 @@ String *Item_singlerow_subselect::val_st my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value) { + Item *cached_value; + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + my_decimal *res= cached_value->val_decimal(decimal_value); + if ((null_value= cached_value->null_value)) + { + reset(); + return 0; + } + else + return res; + } + if (!exec() && !value->null_value) { null_value= 0; + if (scache) + scache->put_value(value); return value->val_decimal(decimal_value); } else { reset(); + if (scache) + scache->put_value(&const_null_value); return 0; } } @@ -859,14 +967,33 @@ my_decimal *Item_singlerow_subselect::va bool Item_singlerow_subselect::val_bool() { + Item *cached_value; + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + bool res= cached_value->val_bool(); + if ((null_value= cached_value->null_value)) + { + reset(); + return 0; + } + else + return res; + } + if (!exec() && !value->null_value) { null_value= 0; + if (scache) + scache->put_value(value); return value->val_bool(); } else { reset(); + if (scache) + scache->put_value(&const_null_value); return 0; } } === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-03-29 14:04:35 +0000 +++ b/sql/item_subselect.h 2010-04-14 20:34:04 +0000 @@ -27,6 +27,7 @@ class subselect_engine; class subselect_hash_sj_engine; class Item_bool_func2; class Cached_item; +class Subquery_cache; /* base class for subselects */ @@ -57,6 +58,10 @@ protected: subselect_engine *engine; /* old engine if engine was changed */ subselect_engine *old_engine; + /* subquery cache */ + Subquery_cache *scache; + /* null consrtant for caching */ + Item_null const_null_value; /* cache of used external tables */ table_map used_tables_cache; /* allowed number of columns (1 for single value subqueries) */ @@ -67,7 +72,7 @@ protected: bool have_to_be_excluded; /* cache of constant state */ bool const_item_cache; - + bool inside_first_fix_fields; bool done_first_fix_fields; public: @@ -88,13 +93,18 @@ public: */ List<Ref_to_outside> upper_refs; st_select_lex *parent_select; - - /* + + /** + List of items subquery depends on (externally resolved); + */ + List<Item> depends_on; + + /* TRUE<=>Table Elimination has made it redundant to evaluate this select (and so it is not part of QEP, etc) - */ + */ bool eliminated; - + /* changed engine indicator */ bool engine_changed; /* subquery is transformed */ @@ -202,6 +212,8 @@ class Item_singlerow_subselect :public I { protected: Item_cache *value, **row; + + Item *check_cache(); public: Item_singlerow_subselect(st_select_lex *select_lex); Item_singlerow_subselect() :Item_subselect(), value(0), row (0) {} === modified file 'sql/mysql_priv.h' --- a/sql/mysql_priv.h 2010-03-20 12:01:47 +0000 +++ b/sql/mysql_priv.h 2010-04-14 20:34:04 +0000 @@ -568,12 +568,13 @@ protected: #define OPTIMIZER_SWITCH_SEMIJOIN 256 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE 512 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN 1024 +#define OPTIMIZER_SWITCH_SUBQUERY_CACHE (1<<11) #ifdef DBUG_OFF -# define OPTIMIZER_SWITCH_LAST 2048 +# define OPTIMIZER_SWITCH_LAST (1<<12) #else -# define OPTIMIZER_SWITCH_TABLE_ELIMINATION 2048 -# define OPTIMIZER_SWITCH_LAST 4096 +# define OPTIMIZER_SWITCH_TABLE_ELIMINATION (1<<12) +# define OPTIMIZER_SWITCH_LAST (1<<13) #endif #ifdef DBUG_OFF @@ -588,7 +589,8 @@ protected: OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #else # define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \ OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ @@ -601,7 +603,8 @@ protected: OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #endif /* @@ -936,6 +939,7 @@ bool general_log_write(THD *thd, enum en #ifdef MYSQL_SERVER #include "sql_servers.h" #include "opt_range.h" +#include "sql_subquery_cache.h" #ifdef HAVE_QUERY_CACHE struct Query_cache_query_flags @@ -1269,6 +1273,10 @@ bool mysql_select(THD *thd, Item ***rref Item *having, ORDER *proc_param, ulonglong select_type, select_result *result, SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex); + +struct st_join_table *create_index_lookup_join_tab(TABLE *table); +int join_read_key2(THD *thd, struct st_join_table *tab, TABLE *table, + struct st_table_ref *table_ref); void free_underlaid_joins(THD *thd, SELECT_LEX *select); bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result); @@ -1288,6 +1296,7 @@ Field *create_tmp_field(THD *thd, TABLE bool table_cant_handle_bit_fields, bool make_copy_field, uint convert_blob_length); +bool open_tmp_table(TABLE *table); void sp_prepare_create_field(THD *thd, Create_field *sql_field); int prepare_create_field(Create_field *sql_field, uint *blob_columns, === modified file 'sql/mysqld.cc' --- a/sql/mysqld.cc 2010-03-20 12:01:47 +0000 +++ b/sql/mysqld.cc 2010-04-14 20:34:04 +0000 @@ -305,6 +305,7 @@ static const char *optimizer_switch_name "firstmatch","loosescan","materialization", "semijoin", "partial_match_rowid_merge", "partial_match_table_scan", + "subquery_cache", #ifndef DBUG_OFF "table_elimination", #endif @@ -325,6 +326,7 @@ static const unsigned int optimizer_swit sizeof("semijoin") - 1, sizeof("partial_match_rowid_merge") - 1, sizeof("partial_match_table_scan") - 1, + sizeof("subquery_cache") - 1, #ifndef DBUG_OFF sizeof("table_elimination") - 1, #endif @@ -404,8 +406,9 @@ static const char *sql_mode_str= "OFF"; static const char *optimizer_switch_str="index_merge=on,index_merge_union=on," "index_merge_sort_union=on," "index_merge_intersection=on," - "index_condition_pushdown=on" -#ifndef DBUG_OFF + "index_condition_pushdown=on," + "subquery_cache=on" +#ifndef DBUG_OFF ",table_elimination=on"; #else ; @@ -5872,7 +5875,9 @@ enum options_mysqld OPT_RECORD_RND_BUFFER, OPT_DIV_PRECINCREMENT, OPT_RELAY_LOG_SPACE_LIMIT, OPT_RELAY_LOG_PURGE, OPT_SLAVE_NET_TIMEOUT, OPT_SLAVE_COMPRESSED_PROTOCOL, OPT_SLOW_LAUNCH_TIME, - OPT_SLAVE_TRANS_RETRIES, OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, + OPT_SLAVE_TRANS_RETRIES, + OPT_SUBQUERY_CACHE, + OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, OPT_DEBUGGING, OPT_DEBUG_FLUSH, OPT_SORT_BUFFER, OPT_TABLE_OPEN_CACHE, OPT_TABLE_DEF_CACHE, OPT_THREAD_CONCURRENCY, OPT_THREAD_CACHE_SIZE, @@ -7164,7 +7169,7 @@ The minimum value for this variable is 4 {"optimizer_switch", OPT_OPTIMIZER_SWITCH, "optimizer_switch=option=val[,option=val...], where option={index_merge, " "index_merge_union, index_merge_sort_union, index_merge_intersection, " - "index_condition_pushdown" + "index_condition_pushdown, subquery_cache" #ifndef DBUG_OFF ", table_elimination" #endif @@ -7868,6 +7873,8 @@ SHOW_VAR status_vars[]= { {"Ssl_version", (char*) &show_ssl_get_version, SHOW_FUNC}, #endif /* HAVE_OPENSSL */ {"Syncs", (char*) &my_sync_count, SHOW_LONG_NOFLUSH}, + {"Subquery_cache_hit", (char*) &subquery_cache_hit, SHOW_LONG}, + {"Subquery_cache_miss", (char*) &subquery_cache_miss, SHOW_LONG}, {"Table_locks_immediate", (char*) &locks_immediate, SHOW_LONG}, {"Table_locks_waited", (char*) &locks_waited, SHOW_LONG}, #ifdef HAVE_MMAP @@ -8006,6 +8013,7 @@ static int mysql_init_variables(void) abort_loop= select_thread_in_use= signal_thread_in_use= 0; ready_to_exit= shutdown_in_progress= grant_option= 0; aborted_threads= aborted_connects= 0; + subquery_cache_miss= subquery_cache_hit= 0; delayed_insert_threads= delayed_insert_writes= delayed_rows_in_use= 0; delayed_insert_errors= thread_created= 0; specialflag= 0; === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_base.cc 2010-04-14 20:34:04 +0000 @@ -8062,6 +8062,10 @@ int setup_conds(THD *thd, TABLE_LIST *ta if (*conds) { thd->where="where clause"; + DBUG_EXECUTE("where", + print_where(*conds, + "WHERE in setup_conds", + QT_ORDINARY);); if ((!(*conds)->fixed && (*conds)->fix_fields(thd, conds)) || (*conds)->check_cols(1)) goto err_no_arena; === modified file 'sql/sql_class.cc' --- a/sql/sql_class.cc 2010-04-05 21:15:15 +0000 +++ b/sql/sql_class.cc 2010-04-14 20:34:04 +0000 @@ -3034,6 +3034,7 @@ void TMP_TABLE_PARAM::init() table_charset= 0; precomputed_group_by= 0; bit_fields_as_long= 0; + skip_create_table= 0; DBUG_VOID_RETURN; } === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2010-04-05 21:15:15 +0000 +++ b/sql/sql_class.h 2010-04-14 20:34:04 +0000 @@ -2786,6 +2786,11 @@ public: that MEMORY tables cannot index BIT columns. */ bool bit_fields_as_long; + /* + Whether to create or postpone actual creation of this temporary table. + TRUE <=> create_tmp_table will create only the TABLE structure. + */ + bool skip_create_table; TMP_TABLE_PARAM() :copy_field(0), group_parts(0), === modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_lex.cc 2010-04-14 20:34:04 +0000 @@ -1843,7 +1843,7 @@ void st_select_lex_unit::exclude_tree() bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency) { - + DBUG_ENTER("st_select_lex::mark_as_dependent"); DBUG_ASSERT(this != last); /* @@ -1868,15 +1868,22 @@ bool st_select_lex::mark_as_dependent(TH sl->uncacheable|= UNCACHEABLE_UNITED; } } + if (dependency && dependency->fixed) + { + s->master_unit()->item->depends_on.push_back(dependency); + DBUG_PRINT("info", ("depends_on: Select: %d added: %s", + s->select_number, + (dependency->name ? dependency->name : "<no name>"))); + } Item_subselect *subquery_expr= s->master_unit()->item; if (subquery_expr && subquery_expr->mark_as_dependent(thd, last, dependency)) - return TRUE; + DBUG_RETURN(TRUE); } while ((s= s->outer_select()) != last && s != 0); is_correlated= TRUE; this->master_unit()->item->is_correlated= TRUE; - return FALSE; + DBUG_RETURN(FALSE); } bool st_select_lex_node::set_braces(bool value) { return 1; } === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-03-29 20:09:40 +0000 +++ b/sql/sql_select.cc 2010-04-14 20:34:04 +0000 @@ -156,7 +156,6 @@ static int join_read_const_table(JOIN_TA static int join_read_system(JOIN_TAB *tab); static int join_read_const(JOIN_TAB *tab); static int join_read_key(JOIN_TAB *tab); -static int join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref); static void join_read_key_unlock_row(st_join_table *tab); static int join_read_always_key(JOIN_TAB *tab); static int join_read_last_key(JOIN_TAB *tab); @@ -7630,6 +7629,40 @@ make_join_readinfo(JOIN *join, ulonglong /** + Creates and fills JOIN_TAB for index look up in temporary table + + @param table The table where to look up + + @return JOIN_TAB object or NULL in case of error +*/ + +JOIN_TAB *create_index_lookup_join_tab(TABLE *table) +{ + JOIN_TAB *tab; + DBUG_ENTER("create_index_lookup_join_tab"); + + if (!((tab= new JOIN_TAB))) + DBUG_RETURN(NULL); + tab->read_record.table= table; + tab->read_record.file=table->file; + /*tab->read_record.unlock_row= rr_unlock_row;*/ + tab->next_select=0; + tab->sorted= 1; + + table->status= STATUS_NO_RECORD; + tab->read_first_record= join_read_key; + /*tab->read_record.unlock_row= join_read_key_unlock_row;*/ + tab->read_record.read_record= join_no_more_records; + if (table->covering_keys.is_set(tab->ref.key) && + !table->no_keyread) + { + table->key_read=1; + table->file->extra(HA_EXTRA_KEYREAD); + } + DBUG_RETURN(tab); +} + +/** Give error if we some tables are done with a full join. This is used by multi_table_update and multi_table_delete when running @@ -10783,6 +10816,7 @@ Field *create_tmp_field(THD *thd, TABLE case Item::REF_ITEM: case Item::NULL_ITEM: case Item::VARBIN_ITEM: + case Item::CACHE_ITEM: if (make_copy_field) { DBUG_ASSERT(((Item_result_field*)item)->result_field); @@ -11557,7 +11591,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA &param->recinfo, select_options)) goto err; } - if (open_tmp_table(table)) + DBUG_PRINT("info", ("skip_create_table: %d", (int)param->skip_create_table)); + if (!param->skip_create_table && open_tmp_table(table)) goto err; thd->mem_root= mem_root_save; @@ -11705,16 +11740,17 @@ error: bool open_tmp_table(TABLE *table) { int error; + DBUG_ENTER("open_tmp_table"); if ((error= table->file->ha_open(table, table->s->table_name.str, O_RDWR, HA_OPEN_TMP_TABLE | HA_OPEN_INTERNAL_TABLE))) { table->file->print_error(error,MYF(0)); /* purecov: inspected */ table->db_stat=0; - return(1); + DBUG_RETURN(1); } (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */ - return(0); + DBUG_RETURN(0); } @@ -12543,7 +12579,8 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi else { /* Do index lookup in the materialized table */ - if ((res= join_read_key2(join_tab, sjm->table, sjm->tab_ref)) == 1) + if ((res= join_read_key2(join_tab->join->thd, join_tab, + sjm->table, sjm->tab_ref)) == 1) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ if (res || !sjm->in_equality->val_int()) DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); @@ -13326,61 +13363,61 @@ join_read_const(JOIN_TAB *tab) static int join_read_key(JOIN_TAB *tab) { - return join_read_key2(tab, tab->table, &tab->ref); + return join_read_key2(tab->join->thd, tab, tab->table, &tab->ref); } -/* +/* eq_ref access handler but generalized a bit to support TABLE and TABLE_REF not from the join_tab. See join_read_key for detailed synopsis. */ -static int -join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) +int join_read_key2(THD *thd, JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) { int error; + DBUG_ENTER("join_read_key2"); if (!table->file->inited) { table->file->ha_index_init(table_ref->key, tab->sorted); } /* TODO: Why don't we do "Late NULLs Filtering" here? */ - if (cmp_buffer_with_ref(tab->join->thd, table, table_ref) || + if (cmp_buffer_with_ref(thd, table, table_ref) || (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW))) { if (table_ref->key_err) { table->status=STATUS_NOT_FOUND; - return -1; + DBUG_RETURN(-1); } /* Moving away from the current record. Unlock the row in the handler if it did not match the partial WHERE. */ - if (tab->ref.has_record && tab->ref.use_count == 0) + if (table_ref->has_record && table_ref->use_count == 0) { tab->read_record.file->unlock_row(); - tab->ref.has_record= FALSE; + table_ref->has_record= FALSE; } error=table->file->ha_index_read_map(table->record[0], table_ref->key_buff, make_prev_keypart_map(table_ref->key_parts), HA_READ_KEY_EXACT); if (error && error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE) - return report_error(table, error); + DBUG_RETURN(report_error(table, error)); if (! error) { - tab->ref.has_record= TRUE; - tab->ref.use_count= 1; + table_ref->has_record= TRUE; + table_ref->use_count= 1; } } else if (table->status == 0) { - DBUG_ASSERT(tab->ref.has_record); - tab->ref.use_count++; + DBUG_ASSERT(table_ref->has_record); + table_ref->use_count++; } table->null_row=0; - return table->status ? -1 : 0; + DBUG_RETURN(table->status ? -1 : 0); } === added file 'sql/sql_subquery_cache.cc' --- a/sql/sql_subquery_cache.cc 1970-01-01 00:00:00 +0000 +++ b/sql/sql_subquery_cache.cc 2010-04-14 20:34:04 +0000 @@ -0,0 +1,236 @@ + +#include "mysql_priv.h" +#include "sql_select.h" + +ulonglong subquery_cache_miss, subquery_cache_hit; + +/** + Creates structures which we need for index look up + + @retval FALSE OK + @retval TRUE Error +*/ + +static my_bool createtmp_table_search_structures(THD *thd, + TABLE *table, + List_iterator_fast<Item> &li, + TABLE_REF **ref) +{ + /* + Create/initialize everything we will need to index lookups into the + temptable. + */ + TABLE_REF *tab_ref; + KEY *tmp_key; /* The only index on the temporary table. */ + Item *item; + uint tmp_key_parts; /* Number of keyparts in tmp_key. */ + uint i; + + DBUG_ENTER("createtmp_table_search_structures"); + + tmp_key= table->key_info; + tmp_key_parts= tmp_key->key_parts; + + if (!(tab_ref= (TABLE_REF*) thd->alloc(sizeof(TABLE_REF)))) + DBUG_RETURN(TRUE); + + tab_ref->key= 0; /* The only temp table index. */ + tab_ref->key_length= tmp_key->key_length; + if (!(tab_ref->key_buff= + (uchar*) thd->calloc(ALIGN_SIZE(tmp_key->key_length) * 2)) || + !(tab_ref->key_copy= + (store_key**) thd->alloc((sizeof(store_key*) * + (tmp_key_parts + 1)))) || + !(tab_ref->items= + (Item**) thd->alloc(sizeof(Item*) * tmp_key_parts))) + DBUG_RETURN(TRUE); /* purecov: inspected */ + + tab_ref->key_buff2=tab_ref->key_buff+ALIGN_SIZE(tmp_key->key_length); + tab_ref->key_err=1; + tab_ref->null_rejecting= 1; + tab_ref->disable_cache= FALSE; + tab_ref->has_record= 0; + + KEY_PART_INFO *cur_key_part= tmp_key->key_part; + store_key **ref_key= tab_ref->key_copy; + uchar *cur_ref_buff= tab_ref->key_buff; + + for (i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++) + { + item= li++; + DBUG_ASSERT(item); + tab_ref->items[i]= item; + int null_count= test(cur_key_part->field->real_maybe_null()); + *ref_key= new store_key_item(thd, cur_key_part->field, + /* TODO: + the NULL byte is taken into account in + cur_key_part->store_length, so instead of + cur_ref_buff + test(maybe_null), we could + use that information instead. + */ + cur_ref_buff + null_count, + null_count ? tab_ref->key_buff : 0, + cur_key_part->length, tab_ref->items[i]); + cur_ref_buff+= cur_key_part->store_length; + } + *ref_key= NULL; /* End marker. */ + tab_ref->key_err= 1; + tab_ref->key_parts= tmp_key_parts; + *ref= tab_ref; + + DBUG_RETURN(FALSE); +} + + +Subquery_cache_tmptable::Subquery_cache_tmptable(THD *thd, + List<Item> &dependance, + Item *value) + :cache_table(NULL), table_thd(thd) +{ + ulonglong keymap; + List_iterator_fast<Item> li(dependance); + DBUG_ENTER("Subquery_cache_tmptable::Subquery_cache_tmptable"); + + if (!(ULONGLONG_MAX >> (dependance.elements + 1))) + { + DBUG_PRINT("info", ("Too many dependencies")); + DBUG_VOID_RETURN; + } + + cache_table= NULL; + list= &dependance; + + cache_table_param.init(); + /* dependance items and result */ + cache_table_param.field_count= dependance.elements + 1; + /* postpone table creation to index description */ + cache_table_param.skip_create_table= 1; + + + dependance.push_front(value); + if (!(cache_table= create_tmp_table(thd, &cache_table_param, + dependance, (ORDER*) NULL, + FALSE, FALSE, + thd->options | TMP_TABLE_ALL_COLUMNS, + HA_POS_ERROR, + (char *)"subquery-cache-table"))) + { + DBUG_PRINT("error", ("create_tmp_table failed, caching switched off")); + DBUG_VOID_RETURN; + } + + /* makes all bits set for keys */ + keymap= 1 << (dependance.elements); /* + 1 - 1 */ + if (!keymap) + keymap= ULONGLONG_MAX; + else + keymap--; + keymap&=~1; + + li++; + if (cache_table->alloc_keys(1) || + (cache_table->add_tmp_key(keymap, "cache-table-key") < 0) || + createtmp_table_search_structures(thd, cache_table, li, &tab_ref) || + !(tab= create_index_lookup_join_tab(cache_table))) + { + DBUG_PRINT("error", ("creating index failed")); + goto error; + } + cache_table->s->keys= 1; + cache_table->s->uniques= 1; + + if (open_tmp_table(cache_table)) + { + DBUG_PRINT("error", ("Opening (creating) temporary table failed")); + goto error; + } + + if (!(chached_result= new Item_field(cache_table->field[0]))) + { + DBUG_PRINT("error", ("Creating Item_field failed")); + goto error; + } + + DBUG_VOID_RETURN; + +error: + /* switch off cache */ + free_tmp_table(thd, cache_table); + cache_table= NULL; + DBUG_VOID_RETURN; +} + + +Subquery_cache_tmptable::~Subquery_cache_tmptable() +{ + if (cache_table) + free_tmp_table(table_thd, cache_table); +} + + +Subquery_cache::result Subquery_cache_tmptable::check_value(Item **value) +{ + int res; + DBUG_ENTER("Subquery_cache_tmptable::check_value"); + if (cache_table) + { + DBUG_PRINT("info", ("status: %u has_record %u", + (uint)cache_table->status, (uint)tab_ref->has_record)); + if ((res= join_read_key2(table_thd, tab, cache_table, tab_ref)) == 1) + DBUG_RETURN(ERROR); /* purecov: inspected */ + if (res) + { + subquery_cache_miss++; + DBUG_RETURN(MISS); + } + + subquery_cache_hit++; + *value= chached_result; + DBUG_RETURN(Subquery_cache::HIT); + } + DBUG_RETURN(Subquery_cache::MISS); +} + + +my_bool Subquery_cache_tmptable::put_value(Item *value) +{ + int error; + DBUG_ENTER("Subquery_cache_tmptable::put_value"); + if (!cache_table) + { + DBUG_PRINT("info", ("No table so behave as we successfully put value")); + DBUG_RETURN(FALSE); + } + + *(list->head_ref())= value; + fill_record(table_thd, cache_table->field, *list, 1); + if (table_thd->is_error()) + goto err;; + + if ((error= cache_table->file->ha_write_row(cache_table->record[0]))) + { + /* create_myisam_from_heap will generate error if needed */ + if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP) && + create_internal_tmp_table_from_heap(table_thd, cache_table, + cache_table_param.start_recinfo, + &cache_table_param.recinfo, + error, 1)) + goto err; + } + cache_table->status= 0; /* cache_table->record contains an existed record */ + tab_ref->has_record= TRUE; /* the same as above */ + DBUG_PRINT("info", ("has_record: TRUE status: 0")); + + DBUG_RETURN(FALSE); + +err: + free_tmp_table(table_thd, cache_table); + cache_table= NULL; + DBUG_RETURN(TRUE); +} + + +void Subquery_cache_tmptable::cleanup() +{ + cache_table->file->ha_delete_all_rows(); +} === added file 'sql/sql_subquery_cache.h' --- a/sql/sql_subquery_cache.h 1970-01-01 00:00:00 +0000 +++ b/sql/sql_subquery_cache.h 2010-04-14 20:34:04 +0000 @@ -0,0 +1,68 @@ +#ifndef _SQL_SUBQUERY_CACHE_H_ +#define _SQL_SUBQUERY_CACHE_H_ + +/** + Interface for subquery cache +*/ + +extern ulonglong subquery_cache_miss, subquery_cache_hit; + +class Subquery_cache :public Sql_alloc +{ +public: + enum result {ERROR, HIT, MISS}; + + Subquery_cache(){}; + virtual ~Subquery_cache() {}; + /** + Checks presence of the key (taken from cache owner) and if found return + it via value parameter + */ + virtual result check_value(Item **value)= 0; + /** + Puts value into this cache (key should be taken from cache owner) + */ + virtual my_bool put_value(Item *value)= 0; + /** + Cleans up and reset cache before reusing + */ + virtual void cleanup()= 0; +}; + +struct st_table_ref; +struct st_join_table; +//class Item_cache; +class Item_field; + +/** + Implementation of subquery cache over temporary table +*/ + +class Subquery_cache_tmptable :public Subquery_cache +{ +public: + Subquery_cache_tmptable(THD *thd, List<Item> &dependance, Item *value); + virtual ~Subquery_cache_tmptable(); + virtual result check_value(Item **value); + virtual my_bool put_value(Item *value); + virtual void cleanup(); + +private: + /* tmp table parameters */ + TMP_TABLE_PARAM cache_table_param; + /* temporary table to store this cache */ + TABLE *cache_table; + /* Thread handler for the temporary table */ + THD *table_thd; + /* tab_ref for index search */ + struct st_table_ref *tab_ref; + /* cache of subquery value to avoid evaluating it twice */ + //Item_cache *value_cache; + /* JOIN_TAB for index lookup */ + st_join_table *tab; + /* Chached result */ + Item_field *chached_result; + /* List of items */ + List<Item> *list; +}; +#endif === modified file 'sql/table.cc' --- a/sql/table.cc 2010-03-20 12:01:47 +0000 +++ b/sql/table.cc 2010-04-14 20:34:04 +0000 @@ -20,6 +20,7 @@ #include "sql_trigger.h" #include <m_ctype.h> #include "my_md5.h" +#include "my_bit.h" /* INFORMATION_SCHEMA name */ LEX_STRING INFORMATION_SCHEMA_NAME= {C_STRING_WITH_LEN("information_schema")}; @@ -5096,6 +5097,115 @@ void st_table::mark_virtual_columns_for_ file->column_bitmaps_signal(); } + +/** + @brief + Allocate space for keys + + @param key_count number of keys to allocate. + + @details + Allocate space enough to fit 'key_count' keys for this table. + + @return FALSE space was successfully allocated. + @return TRUE an error occur. +*/ + +bool TABLE::alloc_keys(uint key_count) +{ + DBUG_ASSERT(!s->keys); + key_info= s->key_info= (KEY*) my_malloc(sizeof(KEY)*key_count, MYF(0)); + max_keys= key_count; + return !(key_info); +} + + +/** + @brief Adds one key to a temporary table. + + @param key_parts bitmap of fields that take a part in the key. + @param key_name name of the key + + @details + Creates a key for this table from fields which corresponds the bits set to 1 + in the 'key_parts' bitmap. The 'key_name' name is given to the newly created + key. + + @return <0 an error occur. + @return >=0 number of newly added key. +*/ + +int TABLE::add_tmp_key(ulonglong key_parts, const char *key_name) +{ + DBUG_ASSERT(s->keys< max_keys); + + KEY* keyinfo; + Field **reg_field; + uint i; + bool key_start= TRUE; + uint key_part_count= my_count_bits(key_parts); + KEY_PART_INFO* key_part_info= + (KEY_PART_INFO*) my_malloc(sizeof(KEY_PART_INFO)* key_part_count, MYF(0)); + if (!key_part_info) + return -1; + keyinfo= key_info + s->keys; + keyinfo->key_part=key_part_info; + keyinfo->usable_key_parts=keyinfo->key_parts= key_part_count; + keyinfo->key_length=0; + keyinfo->algorithm= HA_KEY_ALG_UNDEF; + keyinfo->name= (char *)key_name; + keyinfo->flags= HA_GENERATED_KEY; + keyinfo->rec_per_key= (ulong*)my_malloc(sizeof(ulong)*key_part_count, MYF(0)); + if (!keyinfo->rec_per_key) + return -1; + bzero(keyinfo->rec_per_key, sizeof(ulong)*key_part_count); + for (i= 0, reg_field=field ; + *reg_field; + i++, reg_field++) + { + if (!(key_parts & (1 << i))) + continue; + if (key_start) + (*reg_field)->key_start.set_bit(s->keys); + key_start= FALSE; + (*reg_field)->part_of_key.set_bit(s->keys); + (*reg_field)->flags|= PART_KEY_FLAG; + key_part_info->null_bit= (*reg_field)->null_bit; + key_part_info->null_offset= (uint) ((*reg_field)->null_ptr - + (uchar*) record[0]); + key_part_info->field= *reg_field; + key_part_info->offset= (*reg_field)->offset(record[0]); + key_part_info->length= (uint16) (*reg_field)->pack_length(); + keyinfo->key_length+= key_part_info->length; + /* TODO: + The below method of computing the key format length of the + key part is a copy/paste from opt_range.cc, and table.cc. + This should be factored out, e.g. as a method of Field. + In addition it is not clear if any of the Field::*_length + methods is supposed to compute the same length. If so, it + might be reused. + */ + key_part_info->store_length= key_part_info->length; + + if ((*reg_field)->real_maybe_null()) + key_part_info->store_length+= HA_KEY_NULL_LENGTH; + if ((*reg_field)->type() == MYSQL_TYPE_BLOB || + (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR) + key_part_info->store_length+= HA_KEY_BLOB_LENGTH; + + key_part_info->type= (uint8) (*reg_field)->key_type(); + key_part_info->key_type = + ((ha_base_keytype) key_part_info->type == HA_KEYTYPE_TEXT || + (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT1 || + (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT2) ? + 0 : FIELDFLAG_BINARY; + key_part_info++; + } + set_if_bigger(s->max_key_length, keyinfo->key_length); + return ++s->keys - 1; +} + + /** @brief Check if this is part of a MERGE table with attached children. === modified file 'sql/table.h' --- a/sql/table.h 2010-03-20 12:01:47 +0000 +++ b/sql/table.h 2010-04-14 20:34:04 +0000 @@ -781,6 +781,7 @@ struct st_table { uint temp_pool_slot; /* Used by intern temp tables */ uint status; /* What's in record[0] */ uint db_stat; /* mode of file as in handler.h */ + uint max_keys; /* Size of allocated key_info array. */ /* number of select if it is derived table */ uint derived_select_number; int current_lock; /* Type of lock on table */ @@ -914,6 +915,8 @@ struct st_table { inline bool needs_reopen_or_name_lock() { return s->version != refresh_version; } bool is_children_attached(void); + bool alloc_keys(uint key_count); + int add_tmp_key(ulonglong key_parts, const char *key_name); }; enum enum_schema_table_state

1 0

[Maria-developers] Rev 2784: don't hide old_password_plugin from embedded, it may be compiled with grant support in http://bazaar.launchpad.net/~maria-captains/maria/5.2/
by serg＠askmonty.org 14 Apr '10

14 Apr '10

At http://bazaar.launchpad.net/~maria-captains/maria/5.2/ ------------------------------------------------------------ revno: 2784 revision-id: sergii(a)pisem.net-20100414192940-bw0kamoca39wv9cs parent: sergii(a)pisem.net-20100408210307-j82aap23b14h7j3t committer: Sergei Golubchik <sergii(a)pisem.net> branch nick: 5.2 timestamp: Wed 2010-04-14 12:29:40 -0700 message: don't hide old_password_plugin from embedded, it may be compiled with grant support === modified file 'sql/sql_acl.cc' --- a/sql/sql_acl.cc 2010-04-06 11:04:56 +0000 +++ b/sql/sql_acl.cc 2010-04-14 19:29:40 +0000 @@ -162,9 +162,7 @@ static LEX_STRING old_password_plugin_na LEX_STRING *default_auth_plugin_name= &native_password_plugin_name; static plugin_ref native_password_plugin; -#ifndef EMBEDDED_LIBRARY static plugin_ref old_password_plugin; -#endif /* Classes */

1 0

[Maria-developers] patches included in Percona server
by Vadim Tkachenko 14 Apr '10

14 Apr '10

Monty, maria-devs, In our new repo lp:percona-server you can find patches (for MySQL 5.1) we use to build Percona-Server http://bazaar.launchpad.net/~percona-dev/percona-server/main/files/head%3A/… Basically we looking they included in MariaDB 5.2, so we can build one of our next release on your tree. Thanks, Vadim

1 0

[Maria-developers] Rev 36: Re-arranged killing of monitoring processes. in file:///Users/hakan/work/monty_program/mariadb-tools/
by Hakan Kuecuekyilmaz 13 Apr '10

13 Apr '10

At file:///Users/hakan/work/monty_program/mariadb-tools/ ------------------------------------------------------------ revno: 36 revision-id: hakan(a)askmonty.org-20100412232832-2ylrw3ldo1cvdc5p parent: hakan(a)askmonty.org-20100412135900-1v7jsdxf3ahxvwrd committer: Hakan Kuecuekyilmaz <hakan(a)askmonty.org> branch nick: mariadb-tools timestamp: Tue 2010-04-13 01:28:32 +0200 message: Re-arranged killing of monitoring processes. === modified file 'sysbench/run-sysbench-myisam.sh' --- a/sysbench/run-sysbench-myisam.sh 2010-04-09 19:13:59 +0000 +++ b/sysbench/run-sysbench-myisam.sh 2010-04-12 23:28:32 +0000 @@ -380,7 +380,7 @@ SERVER_STATUS_PID=$! if [ $PROFILE_IT -eq 1 ]; then - $SUDO opcontrol --setup --separate=lib,kernel,thread --no-vmlinux + $SUDO opcontrol --setup --no-vmlinux --separate=lib,kernel,thread $SUDO opcontrol --start-daemon if [ $? != 0 ]; then echo "[WARNING]: Could not start oprofile daemonl." @@ -410,15 +410,15 @@ sync; sync; sync sleep 1 - - kill -9 $SERVER_STATUS_PID - kill -9 $MPSTAT_PID - kill -9 $IOSTAT_PID - + grep "write requests:" ${THIS_RESULT_DIR}/result${k}.txt | awk '{ print $4 }' | sed -e 's/(//' >> ${THIS_RESULT_DIR}/results.txt echo 'SELECT * FROM INFORMATION_SCHEMA.KEY_CACHES' | $MYSQL $MYSQL_OPTIONS > ${THIS_RESULT_DIR}/key_cache_stats${k}.txt + kill -9 $IOSTAT_PID + kill -9 $MPSTAT_PID + kill -9 $SERVER_STATUS_PID + k=$(($k + 1)) done === modified file 'sysbench/run-sysbench.sh' --- a/sysbench/run-sysbench.sh 2010-04-12 13:59:00 +0000 +++ b/sysbench/run-sysbench.sh 2010-04-12 23:28:32 +0000 @@ -371,7 +371,7 @@ SERVER_STATUS_PID=$! if [ $PROFILE_IT -eq 1 ]; then - $SUDO opcontrol --setup --separate=lib,kernel,thread --no-vmlinux + $SUDO opcontrol --setup --no-vmlinux --separate=lib,kernel,thread $SUDO opcontrol --start-daemon if [ $? != 0 ]; then echo "[WARNING]: Could not start oprofile daemonl." @@ -402,11 +402,11 @@ sync; sync; sync sleep 1 + grep "write requests:" ${THIS_RESULT_DIR}/result${k}.txt | awk '{ print $4 }' | sed -e 's/(//' >> ${THIS_RESULT_DIR}/results.txt + + kill -9 $IOSTAT_PID + kill -9 $MPSTAT_PID kill -9 $SERVER_STATUS_PID - kill -9 $MPSTAT_PID - kill -9 $IOSTAT_PID - - grep "write requests:" ${THIS_RESULT_DIR}/result${k}.txt | awk '{ print $4 }' | sed -e 's/(//' >> ${THIS_RESULT_DIR}/results.txt k=$(($k + 1)) done

1 0

[Maria-developers] Updated (by Hakan): Benchmark suite for sysbench (100)
by worklog-noreply＠askmonty.org 13 Apr '10

13 Apr '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...: 20 ESTIMATE.......: 20 (hours remain) ORIG. ESTIMATE.: 40 PROGRESS NOTES: -=-=(Hakan - Mon, 12 Apr 2010, 23:24)=-=- Low Level Design modified. --- /tmp/wklog.100.old.20140 2010-04-12 23:24:16.000000000 +0000 +++ /tmp/wklog.100.new.20140 2010-04-12 23:24:16.000000000 +0000 @@ -60,6 +60,8 @@ 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. + warm_up_time int unsigned, -- Warm up time in seconds. + warm_up_threads int unsigned, -- Number of threads used for warm up. PRIMARY KEY (id), KEY (host), KEY (run_date -=-=(Hakan - Mon, 12 Apr 2010, 21:06)=-=- Added: * OProfile hook * iostat hook * mpstat hook * Copy away .err file for crash detection * mysqladmin --sleep 10 status, for getting in test query statistics Worked 8 hours and estimate 20 hours remain (original estimate unchanged). -=-=(Hakan - Mon, 12 Apr 2010, 21:04)=-=- Low Level Design modified. --- /tmp/wklog.100.old.13854 2010-04-12 21:04:41.000000000 +0000 +++ /tmp/wklog.100.new.13854 2010-04-12 21:04:41.000000000 +0000 @@ -1,20 +1,24 @@ 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 +This is implemented in run-sysbench.sh. Currently we supports: + ** Optionally pulling of latest source from Launchpad and compile. + ** Starting and killing the server. ** Running each sysbench test for $LOOP_COUNT times and $RUN_TIME time. - ** Number of concurrent clients is hardcoded + ** Number of concurrent clients is currently hardcoded. ** Machine specific configuration like location of binaries and directories needed are in separate config files located at conf/<hostname>.inc - ** Set random seed of sysbench to have better comparison - ** Restart mysqld from scratch for each run and copy away + ** Setting random seed of sysbench to have better comparison. + ** Restarting 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) - ** Write out mysqld and sysbench options for reference. + ** Between each run, we run sync and clear file system caches with + echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc) + ** Writing out mysqld and sysbench options for reference. + ** OProfile hook. + ** iostat hook. + ** mpstat -u (CPU utilization) hook. + ** mysqladmin --sleep 10 status, for getting in test query statistics. The main loop of run-sysbench.sh is: @@ -32,9 +36,6 @@ done Open items: - ** OProfile hook - ** iostat hook - ** sar -u (CPU utilization) hook ** Crash detection ** Error detection @@ -45,9 +46,10 @@ ** Read result files from ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}/results.txt - ** Collect OProfile, iostat, cpu utilization, and machine info + ** Collect OProfile, iostat, cpu utilization, sysbench, per test query +statisticts and machine info. ** Detect errors and crashes. - ** Generate SQL INSERT strings for presentation usage + ** Generate SQL INSERT strings for presentation usage. The layout for storing the numbers is: CREATE TABLE sysbench_run ( @@ -97,7 +99,7 @@ sysbench test delete 121.52 144.77 117.70 115.15 100.48 75.39 66.56 mean value of runs - 1 first run + 1 first run (hover for in test query statistics) 2 second run 3 third run STDEV -=-=(Hakan - Tue, 09 Mar 2010, 14:10)=-=- * Added run-sysbench-myisam.sh for running MyISAM related benchmarks with sysbench. Worked 4 hours and estimate 28 hours remain (original estimate unchanged). -=-=(Hakan - Tue, 09 Mar 2010, 14:09)=-=- Low Level Design modified. --- /tmp/wklog.100.old.18803 2010-03-09 14:09:37.000000000 +0000 +++ /tmp/wklog.100.new.18803 2010-03-09 14:09:37.000000000 +0000 @@ -9,6 +9,12 @@ ** 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 + ** Set random seed of sysbench to have better comparison + ** 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) + ** Write out mysqld and sysbench options for reference. The main loop of run-sysbench.sh is: @@ -31,12 +37,6 @@ ** 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) - ** Write out mysqld and sysbench options for reference. * Analyze numbers This is implemented in analyze-sysbench.php -=-=(Hakan - Tue, 09 Mar 2010, 14:06)=-=- Added: ** Set random seed of sysbench to have better comparison ** 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) ** Write out mysqld and sysbench options for reference. Worked 8 hours and estimate 32 hours remain (original estimate unchanged). -=-=(Hakan - Mon, 08 Mar 2010, 12:27)=-=- Low Level Design modified. --- /tmp/wklog.100.old.21404 2010-03-08 12:27:08.000000000 +0000 +++ /tmp/wklog.100.new.21404 2010-03-08 12:27:08.000000000 +0000 @@ -36,6 +36,7 @@ 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) + ** Write out mysqld and sysbench options for reference. * Analyze numbers This is implemented in analyze-sysbench.php -=-=(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 we supports: ** Optionally pulling of latest source from Launchpad and compile. ** Starting and killing the server. ** Running each sysbench test for $LOOP_COUNT times and $RUN_TIME time. ** Number of concurrent clients is currently hardcoded. ** Machine specific configuration like location of binaries and directories needed are in separate config files located at conf/<hostname>.inc ** Setting random seed of sysbench to have better comparison. ** Restarting mysqld from scratch for each run and copy away DATA_DIR of the database for faster starts. ** Between each run, we run sync and clear file system caches with echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc) ** Writing out mysqld and sysbench options for reference. ** OProfile hook. ** iostat hook. ** mpstat -u (CPU utilization) hook. ** mysqladmin --sleep 10 status, for getting in test query statistics. 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: ** Crash detection ** Error detection * 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, sysbench, per test query statisticts 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. warm_up_time int unsigned, -- Warm up time in seconds. warm_up_threads int unsigned, -- Number of threads used for warm up. 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 (hover for in test query statistics) 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 Hakan): Benchmark suite for sysbench (100)
by worklog-noreply＠askmonty.org 13 Apr '10

13 Apr '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...: 20 ESTIMATE.......: 20 (hours remain) ORIG. ESTIMATE.: 40 PROGRESS NOTES: -=-=(Hakan - Mon, 12 Apr 2010, 23:24)=-=- Low Level Design modified. --- /tmp/wklog.100.old.20140 2010-04-12 23:24:16.000000000 +0000 +++ /tmp/wklog.100.new.20140 2010-04-12 23:24:16.000000000 +0000 @@ -60,6 +60,8 @@ 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. + warm_up_time int unsigned, -- Warm up time in seconds. + warm_up_threads int unsigned, -- Number of threads used for warm up. PRIMARY KEY (id), KEY (host), KEY (run_date -=-=(Hakan - Mon, 12 Apr 2010, 21:06)=-=- Added: * OProfile hook * iostat hook * mpstat hook * Copy away .err file for crash detection * mysqladmin --sleep 10 status, for getting in test query statistics Worked 8 hours and estimate 20 hours remain (original estimate unchanged). -=-=(Hakan - Mon, 12 Apr 2010, 21:04)=-=- Low Level Design modified. --- /tmp/wklog.100.old.13854 2010-04-12 21:04:41.000000000 +0000 +++ /tmp/wklog.100.new.13854 2010-04-12 21:04:41.000000000 +0000 @@ -1,20 +1,24 @@ 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 +This is implemented in run-sysbench.sh. Currently we supports: + ** Optionally pulling of latest source from Launchpad and compile. + ** Starting and killing the server. ** Running each sysbench test for $LOOP_COUNT times and $RUN_TIME time. - ** Number of concurrent clients is hardcoded + ** Number of concurrent clients is currently hardcoded. ** Machine specific configuration like location of binaries and directories needed are in separate config files located at conf/<hostname>.inc - ** Set random seed of sysbench to have better comparison - ** Restart mysqld from scratch for each run and copy away + ** Setting random seed of sysbench to have better comparison. + ** Restarting 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) - ** Write out mysqld and sysbench options for reference. + ** Between each run, we run sync and clear file system caches with + echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc) + ** Writing out mysqld and sysbench options for reference. + ** OProfile hook. + ** iostat hook. + ** mpstat -u (CPU utilization) hook. + ** mysqladmin --sleep 10 status, for getting in test query statistics. The main loop of run-sysbench.sh is: @@ -32,9 +36,6 @@ done Open items: - ** OProfile hook - ** iostat hook - ** sar -u (CPU utilization) hook ** Crash detection ** Error detection @@ -45,9 +46,10 @@ ** Read result files from ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}/results.txt - ** Collect OProfile, iostat, cpu utilization, and machine info + ** Collect OProfile, iostat, cpu utilization, sysbench, per test query +statisticts and machine info. ** Detect errors and crashes. - ** Generate SQL INSERT strings for presentation usage + ** Generate SQL INSERT strings for presentation usage. The layout for storing the numbers is: CREATE TABLE sysbench_run ( @@ -97,7 +99,7 @@ sysbench test delete 121.52 144.77 117.70 115.15 100.48 75.39 66.56 mean value of runs - 1 first run + 1 first run (hover for in test query statistics) 2 second run 3 third run STDEV -=-=(Hakan - Tue, 09 Mar 2010, 14:10)=-=- * Added run-sysbench-myisam.sh for running MyISAM related benchmarks with sysbench. Worked 4 hours and estimate 28 hours remain (original estimate unchanged). -=-=(Hakan - Tue, 09 Mar 2010, 14:09)=-=- Low Level Design modified. --- /tmp/wklog.100.old.18803 2010-03-09 14:09:37.000000000 +0000 +++ /tmp/wklog.100.new.18803 2010-03-09 14:09:37.000000000 +0000 @@ -9,6 +9,12 @@ ** 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 + ** Set random seed of sysbench to have better comparison + ** 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) + ** Write out mysqld and sysbench options for reference. The main loop of run-sysbench.sh is: @@ -31,12 +37,6 @@ ** 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) - ** Write out mysqld and sysbench options for reference. * Analyze numbers This is implemented in analyze-sysbench.php -=-=(Hakan - Tue, 09 Mar 2010, 14:06)=-=- Added: ** Set random seed of sysbench to have better comparison ** 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) ** Write out mysqld and sysbench options for reference. Worked 8 hours and estimate 32 hours remain (original estimate unchanged). -=-=(Hakan - Mon, 08 Mar 2010, 12:27)=-=- Low Level Design modified. --- /tmp/wklog.100.old.21404 2010-03-08 12:27:08.000000000 +0000 +++ /tmp/wklog.100.new.21404 2010-03-08 12:27:08.000000000 +0000 @@ -36,6 +36,7 @@ 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) + ** Write out mysqld and sysbench options for reference. * Analyze numbers This is implemented in analyze-sysbench.php -=-=(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 we supports: ** Optionally pulling of latest source from Launchpad and compile. ** Starting and killing the server. ** Running each sysbench test for $LOOP_COUNT times and $RUN_TIME time. ** Number of concurrent clients is currently hardcoded. ** Machine specific configuration like location of binaries and directories needed are in separate config files located at conf/<hostname>.inc ** Setting random seed of sysbench to have better comparison. ** Restarting mysqld from scratch for each run and copy away DATA_DIR of the database for faster starts. ** Between each run, we run sync and clear file system caches with echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc) ** Writing out mysqld and sysbench options for reference. ** OProfile hook. ** iostat hook. ** mpstat -u (CPU utilization) hook. ** mysqladmin --sleep 10 status, for getting in test query statistics. 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: ** Crash detection ** Error detection * 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, sysbench, per test query statisticts 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. warm_up_time int unsigned, -- Warm up time in seconds. warm_up_threads int unsigned, -- Number of threads used for warm up. 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 (hover for in test query statistics) 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] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (knielsen:2764)
by knielsen＠knielsen-hq.org 13 Apr '10

13 Apr '10

#At lp:maria/5.2 2764 knielsen(a)knielsen-hq.org 2010-04-12 MBug#524625: OQGraph error with binary logging enabled Set the correct engine flags for OQGraph to get binlogging working. removed: mysql-test/suite/oqgraph/t/basic-master.opt added: mysql-test/suite/oqgraph/r/binlog.result mysql-test/suite/oqgraph/t/binlog.test mysql-test/suite/oqgraph/t/suite.opt modified: storage/oqgraph/ha_oqgraph.cc === added file 'mysql-test/suite/oqgraph/r/binlog.result' --- a/mysql-test/suite/oqgraph/r/binlog.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/oqgraph/r/binlog.result 2010-04-12 23:23:51 +0000 @@ -0,0 +1,18 @@ +drop table if exists graph; +CREATE TABLE graph ( +latch SMALLINT UNSIGNED NULL, +origid BIGINT UNSIGNED NULL, +destid BIGINT UNSIGNED NULL, +weight DOUBLE NULL, +seq BIGINT UNSIGNED NULL, +linkid BIGINT UNSIGNED NULL, +KEY (latch, origid, destid) USING HASH, +KEY (latch, destid, origid) USING HASH +) ENGINE=OQGRAPH; +SET binlog_format = row; +insert into graph(origid, destid) values (1,3), (3,1); +SET binlog_format = statement; +insert into graph(origid, destid) values (3,4), (4,3); +SET binlog_format = mixed; +insert into graph(origid, destid) values (3,5), (5,3); +drop table graph; === removed file 'mysql-test/suite/oqgraph/t/basic-master.opt' --- a/mysql-test/suite/oqgraph/t/basic-master.opt 2010-01-04 08:27:50 +0000 +++ b/mysql-test/suite/oqgraph/t/basic-master.opt 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -$OQGRAPH_PLUGIN_OPT -$OQGRAPH_PLUGIN_LOAD === added file 'mysql-test/suite/oqgraph/t/binlog.test' --- a/mysql-test/suite/oqgraph/t/binlog.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/oqgraph/t/binlog.test 2010-04-12 23:23:51 +0000 @@ -0,0 +1,28 @@ +-- source suite/oqgraph/include/have_oqgraph_engine.inc +-- source include/have_log_bin.inc + +--disable_warnings +drop table if exists graph; +--enable_warnings + +CREATE TABLE graph ( + latch SMALLINT UNSIGNED NULL, + origid BIGINT UNSIGNED NULL, + destid BIGINT UNSIGNED NULL, + weight DOUBLE NULL, + seq BIGINT UNSIGNED NULL, + linkid BIGINT UNSIGNED NULL, + KEY (latch, origid, destid) USING HASH, + KEY (latch, destid, origid) USING HASH + ) ENGINE=OQGRAPH; + +# MBug#524625: OQGraph error with binary logging enabled +# Test that OQGraph works with different binlogging modes. +SET binlog_format = row; +insert into graph(origid, destid) values (1,3), (3,1); +SET binlog_format = statement; +insert into graph(origid, destid) values (3,4), (4,3); +SET binlog_format = mixed; +insert into graph(origid, destid) values (3,5), (5,3); + +drop table graph; === added file 'mysql-test/suite/oqgraph/t/suite.opt' --- a/mysql-test/suite/oqgraph/t/suite.opt 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/oqgraph/t/suite.opt 2010-04-12 23:23:51 +0000 @@ -0,0 +1,2 @@ +$OQGRAPH_PLUGIN_OPT +$OQGRAPH_PLUGIN_LOAD === modified file 'storage/oqgraph/ha_oqgraph.cc' --- a/storage/oqgraph/ha_oqgraph.cc 2010-01-04 13:32:42 +0000 +++ b/storage/oqgraph/ha_oqgraph.cc 2010-04-12 23:23:51 +0000 @@ -357,7 +357,8 @@ ulong ha_oqgraph::table_flags() const #endif { return (HA_NO_BLOBS | HA_NULL_IN_KEY | - HA_REC_NOT_IN_SEQ | HA_CAN_INSERT_DELAYED); + HA_REC_NOT_IN_SEQ | HA_CAN_INSERT_DELAYED | + HA_BINLOG_STMT_CAPABLE | HA_BINLOG_ROW_CAPABLE); } ulong ha_oqgraph::index_flags(uint inx, uint part, bool all_parts) const

1 0

[Maria-developers] Progress (by Hakan): Benchmark suite for sysbench (100)
by worklog-noreply＠askmonty.org 12 Apr '10

12 Apr '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...: 20 ESTIMATE.......: 20 (hours remain) ORIG. ESTIMATE.: 40 PROGRESS NOTES: -=-=(Hakan - Mon, 12 Apr 2010, 21:06)=-=- Added: * OProfile hook * iostat hook * mpstat hook * Copy away .err file for crash detection * mysqladmin --sleep 10 status, for getting in test query statistics Worked 8 hours and estimate 20 hours remain (original estimate unchanged). -=-=(Hakan - Mon, 12 Apr 2010, 21:04)=-=- Low Level Design modified. --- /tmp/wklog.100.old.13854 2010-04-12 21:04:41.000000000 +0000 +++ /tmp/wklog.100.new.13854 2010-04-12 21:04:41.000000000 +0000 @@ -1,20 +1,24 @@ 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 +This is implemented in run-sysbench.sh. Currently we supports: + ** Optionally pulling of latest source from Launchpad and compile. + ** Starting and killing the server. ** Running each sysbench test for $LOOP_COUNT times and $RUN_TIME time. - ** Number of concurrent clients is hardcoded + ** Number of concurrent clients is currently hardcoded. ** Machine specific configuration like location of binaries and directories needed are in separate config files located at conf/<hostname>.inc - ** Set random seed of sysbench to have better comparison - ** Restart mysqld from scratch for each run and copy away + ** Setting random seed of sysbench to have better comparison. + ** Restarting 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) - ** Write out mysqld and sysbench options for reference. + ** Between each run, we run sync and clear file system caches with + echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc) + ** Writing out mysqld and sysbench options for reference. + ** OProfile hook. + ** iostat hook. + ** mpstat -u (CPU utilization) hook. + ** mysqladmin --sleep 10 status, for getting in test query statistics. The main loop of run-sysbench.sh is: @@ -32,9 +36,6 @@ done Open items: - ** OProfile hook - ** iostat hook - ** sar -u (CPU utilization) hook ** Crash detection ** Error detection @@ -45,9 +46,10 @@ ** Read result files from ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}/results.txt - ** Collect OProfile, iostat, cpu utilization, and machine info + ** Collect OProfile, iostat, cpu utilization, sysbench, per test query +statisticts and machine info. ** Detect errors and crashes. - ** Generate SQL INSERT strings for presentation usage + ** Generate SQL INSERT strings for presentation usage. The layout for storing the numbers is: CREATE TABLE sysbench_run ( @@ -97,7 +99,7 @@ sysbench test delete 121.52 144.77 117.70 115.15 100.48 75.39 66.56 mean value of runs - 1 first run + 1 first run (hover for in test query statistics) 2 second run 3 third run STDEV -=-=(Hakan - Tue, 09 Mar 2010, 14:10)=-=- * Added run-sysbench-myisam.sh for running MyISAM related benchmarks with sysbench. Worked 4 hours and estimate 28 hours remain (original estimate unchanged). -=-=(Hakan - Tue, 09 Mar 2010, 14:09)=-=- Low Level Design modified. --- /tmp/wklog.100.old.18803 2010-03-09 14:09:37.000000000 +0000 +++ /tmp/wklog.100.new.18803 2010-03-09 14:09:37.000000000 +0000 @@ -9,6 +9,12 @@ ** 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 + ** Set random seed of sysbench to have better comparison + ** 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) + ** Write out mysqld and sysbench options for reference. The main loop of run-sysbench.sh is: @@ -31,12 +37,6 @@ ** 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) - ** Write out mysqld and sysbench options for reference. * Analyze numbers This is implemented in analyze-sysbench.php -=-=(Hakan - Tue, 09 Mar 2010, 14:06)=-=- Added: ** Set random seed of sysbench to have better comparison ** 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) ** Write out mysqld and sysbench options for reference. Worked 8 hours and estimate 32 hours remain (original estimate unchanged). -=-=(Hakan - Mon, 08 Mar 2010, 12:27)=-=- Low Level Design modified. --- /tmp/wklog.100.old.21404 2010-03-08 12:27:08.000000000 +0000 +++ /tmp/wklog.100.new.21404 2010-03-08 12:27:08.000000000 +0000 @@ -36,6 +36,7 @@ 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) + ** Write out mysqld and sysbench options for reference. * Analyze numbers This is implemented in analyze-sysbench.php -=-=(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 we supports: ** Optionally pulling of latest source from Launchpad and compile. ** Starting and killing the server. ** Running each sysbench test for $LOOP_COUNT times and $RUN_TIME time. ** Number of concurrent clients is currently hardcoded. ** Machine specific configuration like location of binaries and directories needed are in separate config files located at conf/<hostname>.inc ** Setting random seed of sysbench to have better comparison. ** Restarting mysqld from scratch for each run and copy away DATA_DIR of the database for faster starts. ** Between each run, we run sync and clear file system caches with echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc) ** Writing out mysqld and sysbench options for reference. ** OProfile hook. ** iostat hook. ** mpstat -u (CPU utilization) hook. ** mysqladmin --sleep 10 status, for getting in test query statistics. 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: ** Crash detection ** Error detection * 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, sysbench, per test query statisticts 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 (hover for in test query statistics) 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