- developers - lists.mariadb.org

[Maria-developers] Changes to table_statistics for mariadb 5.2
by Eric Bergen 26 Aug '11

26 Aug '11

Hi, I've been working on some improvements to the table and index statistics in mariadb that allow tracking on the session and query level. I've been pushing the changes to lp:~provenscaling-eric/maria/tivo The long form version of where I'm at in my hacking is at http://ebergen.net/wordpress/2011/07/28/second-update-of-modifying-table-st… I want to poll this list for ides on a few places I'm stuck and get the review process started to hopefully get some of these changes merged back into mainline mariadb. My first issue is around allocation of query ids for both show profiles and show table_statistics; Table statistics are enabled by userstat and profiles are controlled by profiling. Enabling these options at different times makes some confusing results: mysql> use test; Database changed mysql> set query_statistics_history_size=5; Query OK, 0 rows affected (0.00 sec) mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> select * from t limit 5; +-------+--------+ | t | u | +-------+--------+ | 14515 | 282874 | | 14521 | 258653 | | 14573 | 113276 | | 14577 | 826475 | | 14585 | 444645 | +-------+--------+ 5 rows in set (0.00 sec) mysql> show profiles; +----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 7 | 0.00052300 | select * from t limit 5 | +----------+------------+-------------------------+ 1 row in set (0.00 sec) mysql> set profiling=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from t limit 10; +-------+--------+ | t | u | +-------+--------+ | 14515 | 282874 | | 14521 | 258653 | | 14573 | 113276 | | 14577 | 826475 | | 14585 | 444645 | | 14612 | 792545 | | 14626 | 483300 | | 14842 | 447267 | | 15325 | 38865 | | 15340 | 744424 | +-------+--------+ 10 rows in set (0.00 sec) mysql> show query table_statistics; +----------+--------------+------------+-----------+--------------+-------------------------+ | Query_id | Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_x_#indexes | +----------+--------------+------------+-----------+--------------+-------------------------+ | 2 | #temp# | #temp# | 15 | 15 | 15 | | 7 | test | t | 5 | 0 | 0 | | 10 | test | t | 10 | 0 | 0 | +----------+--------------+------------+-----------+--------------+-------------------------+ 3 rows in set (0.00 sec) mysql> show profiles; +----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 7 | 0.00052300 | select * from t limit 5 | +----------+------------+-------------------------+ 1 row in set (0.00 sec) My current thought is to create a show query history command that unifies both show profiles and user stats but only table stats or profiles will be available for any given query depending on the flags set when it ran. I can also create a separate show queries command only for table statistics. Any ideas on how to unify the two different profiling methods or split up the syntax? -- Eric Bergen eric.bergen(a)gmail.com http://www.ebergen.net

3 4

Re: [Maria-developers] [Commits] Rev 3156: MWL#182: Explain running statements
by Sergei Golubchik 24 Aug '11

24 Aug '11

Hi, Sergey! On Aug 23, Sergey Petrunya wrote: > MWL#182: Explain running statements > First code > - "Asynchronous procedure call" system I would like to review that part (at least), could you please tell me when it's ready for a review? > - new THD::check_killed() that serves APC request is called from > within most important loops > - put THD::check_killed() call into every loop where we could spend > significant amount of time This shouldn't be necessary, by trying to keep all long operations killable we've already put a "killed" check everywhere. If some long time consuming code needs a "killed" check but doesn't have it - it's a bug on itself (some code does not respond to KILL). Regards, Sergei

2 1

Re: [Maria-developers] [Commits] Rev 3165: Fix bug lp:825095 in file:///home/tsk/mprog/src/5.3/
by Sergey Petrunya 23 Aug '11

23 Aug '11

Hi Timour, ok to push. On Tue, Aug 23, 2011 at 12:00:29AM +0300, timour(a)askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3/ > > ------------------------------------------------------------ > revno: 3165 > revision-id: timour(a)askmonty.org-20110822210013-egubev0wgyi00wjt > parent: igor(a)askmonty.org-20110820040205-1suikiokqfn8s81j > fixes bug(s): https://launchpad.net/bugs/825095 > committer: timour(a)askmonty.org > branch nick: 5.3 > timestamp: Tue 2011-08-23 00:00:13 +0300 > message: > Fix bug lp:825095 > BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] Rev 3166: Fixed bug lp:825018 in file:///home/tsk/mprog/src/5.3/
by Sergey Petrunya 23 Aug '11

23 Aug '11

Hi Timour, Ok to push. On Tue, Aug 23, 2011 at 03:41:55PM +0300, timour(a)askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3/ > > ------------------------------------------------------------ > revno: 3166 > revision-id: timour(a)askmonty.org-20110823123915-jxf9hvxq2hy03rr7 > parent: timour(a)askmonty.org-20110822210013-egubev0wgyi00wjt > fixes bug(s): https://launchpad.net/bugs/825018 > committer: timour(a)askmonty.org > branch nick: 5.3 > timestamp: Tue 2011-08-23 15:39:15 +0300 > message: > Fixed bug lp:825018 > > Analysis: > During the first execution of the query through the stored > procedure, the optimization phase calls > substitute_for_best_equal_field(), which calls > Item_in_optimizer::transform(). The latter replaces > Item_in_subselect::left_expr with args[0] via assignment. > In this test case args[0] is an Item_outer_ref which is > created/deallocated for each re-execution. As a result, > during the second execution Item_in_subselect::left_expr > pointed to freed memory, which resulted in a crash. > > Solution: > The solution is to use change_item_tree(), so that the > origianal left expression is restored after each execution. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MariaDB 5.2.8 Release Notes and Changelog
by Daniel Bartholomew 18 Aug '11

18 Aug '11

All, The files for MariaDB 5.2.8 were uploaded to the primary mirror last night and at this time we are waiting on only a handful of mirrors before we're ready to go live with this release. The release notes and changelog for this release are here: http://kb.askmonty.org/en/mariadb-528-release-notes http://kb.askmonty.org/en/mariadb-528-changelog If you have a minute, please review and suggest (or make) any necessary additions/modifications/etc... Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] WL#236 New (by Monty): Add MVCC to UPDATE in Aria, part 1
by worklog-noreply＠askmonty.org 15 Aug '11

15 Aug '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add MVCC to UPDATE in Aria, part 1 CREATION DATE..: Mon, 15 Aug 2011, 22:17 SUPERVISOR.....: Monty IMPLEMENTOR....: Monty COPIES TO......: CATEGORY.......: Maria-RawIdeaBin TASK ID........: 236 (http://askmonty.org/worklog/?tid=236) VERSION........: Server-5.5 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 50 (hours remain) ORIG. ESTIMATE.: 50 PROGRESS NOTES: DESCRIPTION: Add MVCC to UPDATE, part 1 Do a working proof-of-concept to better be able to estimate how long the full WL#235 task will take and to be able to estimate the initial performance of the first version of the work. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#235 New (by Monty): Add MVCC to UPDATE and DELETE
by worklog-noreply＠askmonty.org 15 Aug '11

15 Aug '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add MVCC to UPDATE and DELETE CREATION DATE..: Mon, 15 Aug 2011, 22:11 SUPERVISOR.....: Monty IMPLEMENTOR....: Monty COPIES TO......: CATEGORY.......: Maria-RawIdeaBin TASK ID........: 235 (http://askmonty.org/worklog/?tid=235) VERSION........: Server-5.5 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 600 (hours remain) ORIG. ESTIMATE.: 600 PROGRESS NOTES: DESCRIPTION: Add MVCC to UPDATE and DELETE, which will allow concurrent SELECT, INSERT, UPDATE and DELETE's to be done on an ARIA table. This is sub task for making Aria fully transactional. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] [Commits] Rev 3008: MWL#234: Support for marking binlog events ...
by Michael Widenius 12 Aug '11

12 Aug '11

Hi! >>>>> "knielsen" == knielsen <knielsen(a)knielsen-hq.org> writes: <cut> We agree to change the name of replicate-ignore-do-not-replicate variable to just 'replicate' with a default value of 1. (Double negatives are hard, especially if you test them with ! in the code..) knielsen> MWL#234: Support for marking binlog events to not be replicated, and for telling slaves not to replicate events with such mark knielsen> === modified file 'client/mysqlbinlog.cc' knielsen> --- a/client/mysqlbinlog.cc 2011-05-03 16:10:10 +0000 knielsen> +++ b/client/mysqlbinlog.cc 2011-08-11 09:38:52 +0000 knielsen> @@ -671,6 +671,31 @@ print_use_stmt(PRINT_EVENT_INFO* pinfo, knielsen> /** knielsen> + Print "SET do_not_replicate=..." statement when needed. knielsen> + knielsen> + Not all servers support this (only MariaDB from some version on). So we knielsen> + mark the SET to only execute from the version of MariaDB that supports it, knielsen> + and also only output it if we actually see events with the flag set, to not knielsen> + get spurious errors on MySQL@Oracle servers of higher version that do not knielsen> + support the flag. knielsen> + knielsen> + So we start out assuming @@do_not_replicate is 0, and only output a SET knielsen> + statement when it changes. knielsen> +*/ I have now added to MariaDB 5.3 the following new comment syntax to allow one to do MariaDB specific executable comments: /* !M##### */ <cut> knielsen> @@ -927,6 +958,7 @@ Exit_status process_event(PRINT_EVENT_IN knielsen> if (!shall_skip_database(exlq->db)) knielsen> { knielsen> print_use_stmt(print_event_info, exlq); knielsen> + print_do_not_replicate_statement(print_event_info, ev); knielsen> if (fname) knielsen> { Move the print_do_not_replicate_statement to just before exlq->print(result_file, print_event_info, fname); knielsen> convert_path_to_forward_slashes(fname); knielsen> @@ -1030,6 +1062,12 @@ Exit_status process_event(PRINT_EVENT_IN knielsen> } knielsen> /* FALL THROUGH */ knielsen> } knielsen> + case INTVAR_EVENT: knielsen> + case RAND_EVENT: knielsen> + case USER_VAR_EVENT: knielsen> + case XID_EVENT: knielsen> + print_do_not_replicate_statement(print_event_info, ev); knielsen> + /* Fall through ... */ knielsen> default: knielsen> ev->print(result_file, print_event_info); knielsen> } Would it not be safer to have print_do_not_replicate_statement(print_event_info, ev); after the default so that we don't miss anything. (Having a few sporadic 'do_not_replicate=#' events is better than missing something. knielsen> === modified file 'configure.in' knielsen> --- a/configure.in 2011-06-11 11:28:37 +0000 knielsen> +++ b/configure.in 2011-08-11 09:38:52 +0000 knielsen> @@ -13,7 +13,7 @@ dnl When changing the major version numb knielsen> dnl statement in mysqlbinlog::check_master_version(). You may also need knielsen> dnl to update version.c in ndb. knielsen> -AC_INIT([MariaDB Server], [5.2.7-MariaDB], [], [mysql]) knielsen> +AC_INIT([MariaDB Server], [5.4.0-MariaDB], [], [mysql]) 5.4, that was intersting ;) (Should probably not be part of the patch you send the customer) knielsen> --- a/sql/mysql_priv.h 2011-07-21 10:15:09 +0000 <cut> knielsen> @@ -2064,6 +2065,7 @@ extern my_bool opt_old_style_user_limits knielsen> extern uint opt_crash_binlog_innodb; knielsen> extern char *shared_memory_base_name, *mysqld_unix_port; knielsen> extern my_bool opt_enable_shared_memory; knielsen> +extern my_bool opt_replicate_ignore_do_not_replicate; Not a perfect name (as we both agree upon :) <cut> knielsen> +++ b/sql/mysqld.cc 2011-08-11 09:38:52 +0000 knielsen> @@ -553,6 +553,8 @@ uint opt_large_page_size= 0; knielsen> uint opt_debug_sync_timeout= 0; knielsen> #endif /* defined(ENABLED_DEBUG_SYNC) */ knielsen> my_bool opt_old_style_user_limits= 0, trust_function_creators= 0; knielsen> +my_bool opt_replicate_ignore_do_not_replicate; knielsen> + knielsen> /* knielsen> True if there is at least one per-hour limit for some user, so we should knielsen> check them before each query (and possibly reset counters when hour is knielsen> @@ -6085,7 +6087,8 @@ enum options_mysqld knielsen> OPT_IGNORE_BUILTIN_INNODB, knielsen> OPT_BINLOG_DIRECT_NON_TRANS_UPDATE, knielsen> OPT_DEFAULT_CHARACTER_SET_OLD, knielsen> - OPT_MAX_LONG_DATA_SIZE knielsen> + OPT_MAX_LONG_DATA_SIZE, knielsen> + OPT_REPLICATE_IGNORE_DO_NOT_REPLICATE Add the option somewhere close to other replication options. Benefits: - Only one row changed - Will not conflict if MySQL add more options or if we add more options in later MariaDB versions. knielsen> === modified file 'sql/slave.cc' knielsen> --- a/sql/slave.cc 2011-05-03 16:10:10 +0000 knielsen> +++ b/sql/slave.cc 2011-08-11 09:38:52 +0000 knielsen> @@ -1176,6 +1176,38 @@ when it try to get the value of TIME_ZON knielsen> } knielsen> } knielsen> + /* knielsen> + Request the master to filter away events with the @@do_not_replicate flag knielsen> + set, if we are running with --replicate-ignore-do_not_replicate=1. knielsen> + */ knielsen> + if (opt_replicate_ignore_do_not_replicate) knielsen> + { knielsen> + if (!mysql_real_query(mysql, STRING_WITH_LEN("SET do_not_replicate=1"))) knielsen> + { knielsen> + err_code= mysql_errno(mysql); knielsen> + if (is_network_error(err_code)) knielsen> + { knielsen> + mi->report(ERROR_LEVEL, err_code, knielsen> + "Setting master-side filtering of @@do_not_replicate failed " knielsen> + "with error: %s", mysql_error(mysql)); knielsen> + goto network_err; knielsen> + } knielsen> + else if (err_code == ER_UNKNOWN_SYSTEM_VARIABLE) knielsen> + { knielsen> + /* knielsen> + The master is older than the slave and does not support the knielsen> + @@do_not_replicate feature. knielsen> + This is not a problem, as such master will not generate events with knielsen> + the @@do_not_replicate flag set in the first place. We will still knielsen> + do slave-side filtering of such events though, to handle the (rare) knielsen> + case of downgrading a master and receiving old events generated from knielsen> + before the downgrade with the @@do_not_replicate flag set. knielsen> + */ knielsen> + DBUG_PRINT("info", ("Old master does not support master-side filtering " knielsen> + "of @@do_not_replicate events.")); knielsen> + } knielsen> + } knielsen> + } Wouldn't it be better to do: if (err_code == ER_UNKNOWN_SYSTEM_VARIABLE) ... else ... Now you don't print an error if there was some other unknown problem with setting the variable. What other errors can we get other than ER_UNKNOWN_SYSTEM_VARIABLE that would allow us to continue ? knielsen> err: knielsen> if (errmsg) knielsen> { knielsen> @@ -2114,6 +2146,8 @@ int apply_event_and_update_pos(Log_event knielsen> thd->lex->current_select= 0; knielsen> if (!ev->when) knielsen> ev->when= my_time(0); knielsen> + thd->options= (thd->options & ~OPTION_DO_NOT_REPLICATE) | knielsen> + (ev->flags & LOG_EVENT_DO_NOT_REPLICATE_F ? OPTION_DO_NOT_REPLICATE : 0); knielsen> ev->thd = thd; // because up to this point, ev->thd == 0 knielsen> int reason= ev->shall_skip(rli); knielsen> @@ -3582,6 +3616,7 @@ static int queue_event(Master_info* mi,c knielsen> { knielsen> int error= 0; knielsen> ulong inc_pos; knielsen> + ulong event_pos; knielsen> Relay_log_info *rli= &mi->rli; knielsen> pthread_mutex_t *log_lock= rli->relay_log.get_log_lock(); knielsen> DBUG_ENTER("queue_event"); knielsen> @@ -3667,6 +3702,23 @@ static int queue_event(Master_info* mi,c knielsen> } knielsen> /* knielsen> + If we filter events master-side (eg. @@do_not_replicate), we will see holes knielsen> + in the event positions from the master. If we see such a hole, adjust knielsen> + mi->master_log_pos accordingly so we maintain the correct position (for knielsen> + reconnect, MASTER_POS_WAIT(), etc.) knielsen> + */ knielsen> + if (inc_pos > 0 && knielsen> + event_len >= LOG_POS_OFFSET+4 && knielsen> + (event_pos= uint4korr(buf+LOG_POS_OFFSET)) > mi->master_log_pos + inc_pos) knielsen> + { knielsen> + inc_pos= event_pos - mi->master_log_pos; knielsen> + DBUG_PRINT("info", ("Adjust master_log_pos %lu->%lu to account for " knielsen> + "master-side filtering", knielsen> + (unsigned long)(mi->master_log_pos + inc_pos), knielsen> + event_pos)); knielsen> + } <irc discussion about> is 'inc_pos= event_pos - mi->master_log_pos' really secure and should we add DBUG_ASSERT(mi->master_log_pos == uint4korr(buf+LOG_POS_OFFSET) || inc_len == 0) after mi->master_log_pos+= inc_pos; to ensure the correctness. For the moment we decided to not do this. <cut> knielsen> +++ b/sql/sql_repl.cc 2011-08-11 09:38:52 +0000 knielsen> @@ -338,6 +338,41 @@ Increase max_allowed_packet on master"; knielsen> /* knielsen> + Helper function for mysql_binlog_send() to write an event down the slave knielsen> + connection. knielsen> + knielsen> + Returns NULL on success, error message string on error. knielsen> +*/ knielsen> +static const char * knielsen> +send_event_to_slave(THD *thd, NET *net, String* const packet) knielsen> +{ knielsen> + thd_proc_info(thd, "Sending binlog event to slave"); knielsen> + knielsen> + /* knielsen> + Skip events with the @@do_not_replicate flag set, if slave requested knielsen> + skipping of such events. knielsen> + */ knielsen> + if (thd->options & OPTION_DO_NOT_REPLICATE) knielsen> + { knielsen> + uint16 flags= uint2korr(&((*packet)[FLAGS_OFFSET+1])); Please add a comment to explain the +1 above <cut> Looks good! Regards, Monty

2 1

[Maria-developers] Fwd: MariaDB contribution offer
by Colin Charles 09 Aug '11

09 Aug '11

His attachment didn't make it thru, but here are some links: Hello Colin, you can download it from my server, that's the easiest way: http://www.qa-navigation.com/Downloads/QANavBSmp.zip and http://www.qa-navigation.com/Downloads/QANavXDBTst.zip it's less than 400 KB, all in all. Begin forwarded message: > From: Ronald Grindle <ronald(a)grindle.de> > Date: 9 August 2011 17:07:25 GMT+08:00 > To: maria-developers(a)lists.launchpad.com, community(a)askmonty.org > Subject: MariaDB contribution offer > > Dear Sir and Madam, > > I am writing you to make you an contribution offer to your Open Source Project MariaDB. > > I have developed a driver, that turns MySQL into an XML Database. Following this mail I am sending you the Java implementation of what I called XMLDB. > > XMLDB is a set of classes, that provides an interface to the user, that has the appearance of an XML dataset object, while providing him the advantages of a database, i.e. search-ability of data by key values, extraction of subsets of data, insertion of subsets of data etc. I used it in my own programs to create a generic database, where the data base scheme does not need to be changed every time a new data entity is added to the data set and, at the same time, takes advantage of the simplified transportation and handling of data that XML objects provide. It has proven its usability under the circumstances of industrial use. > > The appendix contains the zipped package QANavBSmp.zip that contains all Java classes, the built jar, the Java-Docs and the SQL scripts for setting up the DB scheme. The XMLDB requires a UID server to run, a small server, that generates universally unique ids. The UID server is part of the jar, the zipped package contains scripts to start the server. > > Along with the package QANavBSmp.zip comes a another package QANavXDBTst.zip. It contains a short test program that demonstrates the usage of XMLDB and some of its functions. > > I am willing to make all of this public domain, I just have not added the according statements to the packages yet. If you are interested in the package I am willing to migrate it to use MaraiDB. > > As already said, the packages will follow this mail, in case the mail is rejected by your firewall or your spam filter due to its content. The mail is labeled “MariaDB contribution offer – packages”. > > If the packages do not reach you please contact me, so we can find out how to make the transfer work. > > I hope you find this offer interesting, I am looking to hearing from you what you think about it. > > Yours sincerely > > Ronald D. Grindle > > E.Mail: ronald(a)grindle.de > Tel: +49 (0)89-43573611 > Mobil: +49 (0)177-3775162 -- Colin Charles, http://bytebot.net/blog/ | twitter: @bytebot | skype: colincharles MariaDB: Community developed. Feature enhanced. Backward compatible. Download it at: http://www.mariadb.org/ Open MariaDB/MySQL documentation at the Knowledgebase: http://kb.askmonty.org/

1 0

[Maria-developers] Performance speed-up for DBT3 query set with MariaDB 5.3.0
by Igor Babaev 07 Aug '11

07 Aug '11

Hi, Here are the results for DBT3 query set I've got on my laptop with MariaDB 5.3.0 installed. [ Dell Latitude E6510: 4-cores with multithreading (8 threads in total) Memory: 8GB, SSD, SuSE 11.3 ] DBT3 factor 10 (60M rows in lineitem) built for InnoDB. Mysqld was launched with the following parameters: --innodb-buffer-pool-size=1G --innodb-additional-mem-pool-size=20M --innodb-log-file-size=256M --innodb-file-per-tab --innodb_file_format=barracuda Each query was executed 1. with default optimization options (as they are set in mysql-5.1/5.5) 2. with the best optimization options found for the query I would like to attract your attention to the fact that hash join can really compete with BKA join for some queries (see Q9, Q12, Q13). Also: EXPLAIN for Q7, Q8, Q9 now is instantaneous. Q18 practilly cannot be executed without optimizations. My estimation tells me that it would take ~50 years to execute it unless the in_to_exists transformation is optimized for group by queries (then it will take only ~80 hours). See how effectively optimize_join_buffer_size can be used for Q9. (similar join buffer optimization could be applied for other queries, I just did not do it). Enjoy the numbers below. Regards, Igor. ==================================================== time in seconds time in seconds ratio w/o optimizations with optimizations Q1 172 - [172] 1.0 Q2 112 31 3.61 Q3 189 197 0.96 Q4 417 103 4.05 Q5 416 137 3.04 Q6 119 - [119] 1.0 Q7 558 221 2.52 Q8 1023 338 3.02 Q9 2399 218 (265) 11.00 (9.05) Q10 542 139 3.90 Q11 33 25 1.32 Q12 308 247 1.25 Q13 446 133 3.35 Q14 302 92 3.28 Q15 1334 164 8.13 Q16 45 10 4.5 Q17 46 51 0.90 Q18 [*] 252 (218) - Q19 76 63 1.21 Q20 463 337 1.37 Q21 297 304 0.98 Q22 11.02 10.72 1.03 ==================================================== optimization settings used Q1 [none] Q2 set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*64; set join_cache_level=6; Q3 set join_buffer_space_limit=1024*1024*256; set join_buffer_size=1024*1024*32; set optimizer_switch='mrr=on'; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q4 set optimizer_switch='mrr=on'; set mrr_buffer_size=1024*1024*8; Q5 set optimizer_switch='mrr=on'; set mrr_buffer_size=1024*1024*8; set join_buffer_space_limit=1024*1024*1024; set join_buffer_size=1024*1024*32; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q6 [none] Q7 set optimizer_switch='derived_merge=on'; set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*1024; set join_buffer_size=1024*1024*64; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q8 set optimizer_switch='derived_merge=on'; set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*1024; set join_buffer_size=1024*1024*128; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q9 set optimizer_switch='derived_merge=on'; set optimizer_switch='optimize_join_buffer_size=on'; set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*128; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; ( set optimizer_switch='derived_merge=on'; set optimizer_switch='optimize_join_buffer_size=on'; set join_buffer_space_limit=1024*1024*32; set join_buffer_size=1024*1024*24; set join_cache_level=4; ) Q10 set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*128; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q11 set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*8; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q12 set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*512; set join_cache_level=4; Q13 set tmp_table_size=1024*1024*32; set max_heap_table_size=1924*1024*32; set optimizer_switch='outer_join_with_cache=on'; set join_buffer_space_limit=1024*1024*16; set join_buffer_size=1024*1024*8; set join_cache_level=4; Q14 set optimizer_switch='mrr=on'; set mrr_buffer_size=1024*1024*8; set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*32; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q15 set tmp_table_size=1024*1024*32; set max_heap_table_size=1924*1024*32; set optimizer_switch='derived_with_keys=on'; set optimizer_switch='mrr=on'; set mrr_buffer_size=1024*1024*32; set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*16; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q16 set optimizer_switch='materialization=on'; Q17 set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*16; set optimizer_switch='mrr=on'; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q18 set tmp_table_size=1024*1024*32; set max_heap_table_size=1024*1024*32; set optimizer_switch='materialization=on'; set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*128; set optimizer_switch='mrr=on'; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; ( set tmp_table_size=1024*1024*32; set max_heap_table_size=1024*1024*32; set optimizer_switch='materialization=on'; set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*128; set optimizer_switch='mrr=on'; set join_cache_level=4; ) Q19 set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*16; set optimizer_switch='mrr=on'; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q20 set optimizer_switch='semijoin=on'; set optimizer_switch='materialization=on'; set optimizer_switch='in_to_exists=off'; Q21 set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*64; set optimizer_switch='mrr=on'; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q22 set optimizer_switch='derived_merge=on';

1 0