MySQL Bugs: #50595: funcs_1.myisam_views takes longer time on 6.0 branch than 5.1 branch

Bug #50595	funcs_1.myisam_views takes longer time on 6.0 branch than 5.1 branch
Submitted:	25 Jan 2010 16:28	Modified:	8 Jan 2011 16:32
Reporter:	Alexander Nozdrin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Solaris (Solaris 10 sparc32 debug, sparc64)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any
Tags:	experimental, pb2, regression, test failure

Description:
funcs_1.myisam_views started to fail with the following symptoms:

funcs_1.myisam_views [ fail ] timeout after 900 seconds
        Test ended at 2010-01-22 22:03:45

Test case timeout after 900 seconds

....

current thread: t@496
=>[1] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 1574912ULL, idx = 18U, record_count = 3814697265625.0, read_time = 17068896858.158, search_depth = 44U, prune_level = 1U), line 7571 in "sql_select.cc"
  [2] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 1705984ULL, idx = 17U, record_count = 762939453125.0, read_time = 3223153106.363, search_depth = 45U, prune_level = 1U), line 7627 in "sql_select.cc"
  [3] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 1722368ULL, idx = 16U, record_count = 152587890625.0, read_time = 596321233.71045, search_depth = 46U, prune_level = 1U), line 7627 in "sql_select.cc"
  [4] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 1984512ULL, idx = 15U, record_count = 30517578125.0, read_time = 110838644.86768, search_depth = 47U, prune_level = 1U), line 7627 in "sql_select.cc"
  [5] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2050048ULL, idx = 14U, record_count = 6103515625.0, read_time = 20273845.285645, search_depth = 48U, prune_level = 1U), line 7627 in "sql_select.cc"
  [6] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2058240ULL, idx = 13U, record_count = 1220703125.0, read_time = 3729271.1420898, search_depth = 49U, prune_level = 1U), line 7627 in "sql_select.cc"
  [7] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2091008ULL, idx = 12U, record_count = 244140625.0, read_time = 690875.39013672, search_depth = 50U, prune_level = 1U), line 7627 in "sql_select.cc"
  [8] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2095104ULL, idx = 11U, record_count = 48828125.0, read_time = 128487.57275391, search_depth = 51U, prune_level = 1U), line 7627 in "sql_select.cc"
  [9] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2095136ULL, idx = 10U, record_count = 9765625.0, read_time = 22584.482666016, search_depth = 52U, prune_level = 1U), line 7627 in "sql_select.cc"
  [10] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2096160ULL, idx = 9U, record_count = 1953125.0, read_time = 4099.0305175781, search_depth = 53U, prune_level = 1U), line 7627 in "sql_select.cc"
  [11] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2096416ULL, idx = 8U, record_count = 390625.0, read_time = 731.99560546875, search_depth = 54U, prune_level = 1U), line 7627 in "sql_select.cc"
  [12] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2096420ULL, idx = 7U, record_count = 78125.0, read_time = 131.109375, search_depth = 55U, prune_level = 1U), line 7627 in "sql_select.cc"
  [13] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2096548ULL, idx = 6U, record_count = 15625.0, read_time = 30.4775390625, search_depth = 56U, prune_level = 1U), line 7627 in "sql_select.cc"
  [14] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2097060ULL, idx = 5U, record_count = 3125.0, read_time = 12.240234375, search_depth = 57U, prune_level = 1U), line 7627 in "sql_select.cc"
  [15] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2097076ULL, idx = 4U, record_count = 625.0, read_time = 8.1328125, search_depth = 58U, prune_level = 1U), line 7627 in "sql_select.cc"
  [16] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2097140ULL, idx = 3U, record_count = 125.0, read_time = 6.1064453125, search_depth = 59U, prune_level = 1U), line 7627 in "sql_select.cc"
  [17] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2097148ULL, idx = 2U, record_count = 25.0, read_time = 4.080078125, search_depth = 60U, prune_level = 1U), line 7627 in "sql_select.cc"
  [18] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2097150ULL, idx = 1U, record_count = 5.0, read_time = 2.0263671875, search_depth = 61U, prune_level = 1U), line 7627 in "sql_select.cc"
  [19] best_extension_by_limited_search(join = 0x7c80de8, remaining_tables = 2097151ULL, idx = 0, record_count = 1.0, read_time = 0.0, search_depth = 62U, prune_level = 1U), line 7627 in "sql_select.cc"
  [20] greedy_search(join = 0x7c80de8, remaining_tables = 2097151ULL, search_depth = 62U, prune_level = 1U), line 7299 in "sql_select.cc"
  [21] choose_plan(join = 0x7c80de8, join_tables = 2097151ULL), line 6910 in "sql_select.cc"
  [22] make_join_statistics(join = 0x7c80de8, tables_arg = 0x7d7ae10, conds = 0x7c80448, keyuse_array = 0x7c864d4), line 4527 in "sql_select.cc"
  [23] JOIN::optimize(this = 0x7c80de8), line 1621 in "sql_select.cc"
  [24] mysql_select(thd = 0x52b2be8, rref_pointer_array = 0x52b4100, tables = 0x63f8b48, wild_num = 0, fields = CLASS, conds = (nil), og_num = 0, order = (nil), group = (nil), having = (nil), proc_param = (nil), select_options = 2147748608ULL, result = 0x7c7c570, unit = 0x52b3b28, select_lex = 0x52b3ff8), line 3123 in "sql_select.cc"
  [25] handle_select(thd = 0x52b2be8, lex = 0x52b3ac8, result = 0x7c7c570, setup_tables_done_option = 0), line 292 in "sql_select.cc"
  [26] execute_sqlcom_select(thd = 0x52b2be8, all_tables = 0x63f8b48), line 4940 in "sql_parse.cc"
  [27] mysql_execute_command(thd = 0x52b2be8), line 2165 in "sql_parse.cc"
  [28] mysql_parse(thd = 0x52b2be8, inBuf = 0x63f8790 "SELECT CAST(f1 AS SIGNED INTEGER) AS f1,\nCAST(f2 AS CHAR) AS f2 FROM test1.v20", length = 78U, found_semicolon = 0xfebb19b8), line 5973 in "sql_parse.cc"
  [29] dispatch_command(command = COM_QUERY, thd = 0x52b2be8, packet = 0x5753e39 "SELECT CAST(f1 AS SIGNED INTEGER) AS f1,\nCAST(f2 AS CHAR) AS f2 FROM test1.v20", packet_length = 78U), line 1090 in "sql_parse.cc"
  [30] do_command(thd = 0x52b2be8), line 774 in "sql_parse.cc"
  [31] do_handle_one_connection(thd_arg = 0x52b2be8), line 1173 in "sql_connect.cc"
  [32] handle_one_connection(arg = 0x52b2be8), line 1113 in "sql_connect.cc"
  [33] pfs_spawn_thread(arg = 0x171a718), line 1011 in "pfs.cc"

How to repeat:
Check out PB2 for mysql-6.0-codebase, mysql-6.0-codebase-bugfixing.

Log: http://pb2.norway.sun.com/web.py?action=archive_download&archive_id=1226848&pretty=please

Thank you for the report.

Verified as described.

triage: set tag to SRFEATURE (and added regression tag)

Here is the amount of time it takes to run this test on a similar machine running Solaris 10 and using 32 bit debug builds for the following four code bases (all results are in seconds with two or three runs on each code base):

 mysql-6.0-codebase-bugfixing: 1307, 1307, 1309
 mysql-next-mr-bugfixing: 959, 959
 mysql-trunk-bugfixing: 960, 959
 mysql-5.1-bugteam: 874, 875

Here is the amount of time it takes to run this test on a similar machine running Solaris 10 and using 32 bit debug builds for the following four code bases (all results are in seconds with two or three runs on each code base):

 mysql-6.0-codebase-bugfixing: 1307, 1307, 1309
 mysql-next-mr-bugfixing: 959, 959
 mysql-trunk-bugfixing: 960, 959
 mysql-5.1-bugteam: 874, 875

On my Linux 64-bit, non-debug builds (BUILD/compile-pentium64), comparing
5.1 revision-id:build@mysql.com-20100115170348-qyaxw83mnms3gx8y and
6.0-codebase-bugfixing revision-id:jon.hauglid@sun.com-20100125081549-aoow4ause17c44or
5.1: myisam_views runs in 16 secs 
6.0: 21 secs. That's 30% more which is similar to what Olav sees on Solaris.
With or without --mem or Performance Schema is insignificant.
Changes in mysqltest and mtr are not the culprit (I verified by using mysqltest&mtr of 5.1 for both measures; to tell mysqltest&mtr to connect to a 6.0 mysqld, I passed --manual-gdb and opened a 6.0 mysqld in gdb).
Using ./mtr --mark-progress I see it's 10 queries which each take 0.5 or 0.6 seconds more in 6.0 than in 5.1, accounting for 6 seconds in total.

Those queries are marked with "#ZZ" in the views_master.inc which I'm attaching (which apart from that is identical to what is in suite/funcs_1/views/views_master.inc and which is included by myisam_views.test).
They are all of the form:
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
            CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
            CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
Each of them takes 1.5 sec on 5.1 and 2.0 sec on 6.0 and there are 5 pairs like above. The results are the same on both servers though (both for SELECT and EXPLAIN).
Note this comment by the test writer, above one such EXPLAIN:
let $message= The output of following EXPLAIN is deactivated, because the result
              differs on some platforms
              FIXME Is this a bug ? ;
and thus the EXPLAIN is run but not printed; note also that this is true only for 2 of the 5 guilty EXPLAIN, so may be irrelevant.
I will also attach a reduced testcase, bug.test, which contains only the first pair of slow queries. Run it with --mark-progress and look at var/log/bug.progress to see the timings (1.5 sec vs 2.0 sec).

Note also that next-mr-bugfixing (revision-id:guilhem@mysql.com-20100125221934-ei1bo7a69bmwg30j) is 2% slower than 5.1:
(1.548 sec vs 1.519 sec for the first SELECT of bug.test).

Note also that the debug binary (compile-pentium64-valgrind-max) is much slower than the non-debug one (the first SELECT of bug.test takes 5.5 sec vs 1.5 sec for a non-debug binary). That is an impressive difference which might be worth investigating.

same as views_master.inc but with #ZZ marks marking the 10 slow queries

Attachment: views_master.inc.with_ZZ (application/octet-stream, text), 140.74 KiB.

reduced testcase, SELECT and EXPLAIN each take 0.5 secs more in 6.0

Attachment: bug.test (application/octet-stream, text), 3.18 KiB.

Note that the funcs_1.myisam_views test run by 5.1 and 6.0 is the same: it contains the same queries, the sub-tests which it includes via included files, are also identical. So it's not about changes in the tests.

I tried this. Took bug.test, made the query run 100 times. This should make a 50-second gap between good and bad. Added timing (using unix_timestamp()) inside the test, so that test fails if test takes too much time.
Then tried bzrfind with this test, it narrowed to 18 revisions, all from SergeyP in Jul-Aug-Sep 2008. But after that, the next revision fails to build. And the next. And the next.
So I cannot pursue this automated path. I can look into why it doesn't build, but that would be quite time-consuming, so I'm not going to do it, unless assigned the bug.

I insisted a bit and this revision:
sergefp@mysql.com-20080712192605-8ctivu0kz6ih6owu
increases the query's time from 1.5 secs to 1.9 i.e. 35%.
It is a huge patch, the same as what introduced BUG#42620:
      WL#3985: Subquery optimization: smart choice between semi-join and materialization

Nowadays, 6.0-codebase-bugfixing (jon.hauglid@sun.com-20100125081549-aoow4ause17c44or) takes 2.1 secs. So while this patch introduced most of the degradation (1.9-1.5 = +0.4s), some other later patch has introduced another +0.2s later.
It would be interesting to identify that other patch, as +0.2s is still a 13% degradation. I'll search for it with bzrfind later.

"the other patch" adding +0.2s will likely not be identified with bzrfind, because it seems to have happened in multiple steps: between the sergefp revision above and 6.0.10 I see +0.1s, and between 6.0.10 and 6.0-codebase-bugfixing I see another +0.1s.
So we should probably just concentrate on the +0.4s of the sergefp revision.

test and result which have been used for finding the regression source

Attachment: bzrfind_test.tar.gz (application/x-gzip, text), 1.01 KiB.

"bzrfind --lower tag:mysql-6.0.5 --upper tag:mysql-6.0.10" was used.

optimizer_switch and optimizer_join_cache_level don't influence this.
Between 5.1 and 6.0 there is no difference in the output of EXPLAIN:
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 |                                |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using join buffer              |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using join buffer              |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using join buffer              |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using join buffer              |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using join buffer              |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using join buffer              |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
Regarding the rewritten query given by EXPLAIN EXTENDED, which is a long multi-join query, no difference except that the 5.1 one starts like this:
 select cast(`tab1`.`f1` as signed) AS
 `f1`,cast(`tab1`.`f2` as char charset latin1) etc
whereas the 6.0 one has "utf8" in place of "latin1".
But that doesn't seem interesting: the speed regression is seen in EXPLAIN too, where character sets shouldn't matter; it's likely rather too much time spent in optimizing the query; profiling could tell.

in 6.0, setting optimizer_search_depth to [2..8] gives instant EXPLAIN; to 16 gives 1.5 seconds, to 62 (the default) takes 2 seconds, to 17 takes 2.3 seconds (17 takes more than 62 which is expected to be more exhaustive...??).

For later reference, here is how I used bzrfind to find the guilty revision.

- find a good base, a revision X which doesn't have the slowdown.
- build two trees (you can use your Linux machine): latest and revision X
- check that the test takes significantly longer on latest than on X (don't use --mem); if it's not the case, you may have to build on the machine which experienced the timeout
- the rest here assumes that it takes longer.
- run the testcase with "./mtr --mark-progress", it will put in var/log a file (the_test.log I think) which contains timings of each query in microseconds; locate the query which slowed down
- once you have it, create a slowdown testcase with just the needed queries.
- add a while() loop around the slow query in this testcase, so that the query is repeated a fixed number of times, so that the slowdown is great (>30 seconds)
- add something like this around the while():

SELECT (@start:=unix_timestamp())*0;
while()...
SELECT (unix_timestamp()-@start) < 15;

So if the revision which bzrfind is testing has the slowdown, test will fail.
Don't load your machine with other jobs while bzrfind is running, because it may influence the timings;

From time to time it fails in next-mr too.
The timings are different though:

  - Debug build:

    - 5.1: 86158 ms, 83677 ms, 81680 ms
    - 5.5: 91672 ms, 89653 ms, 87790 ms

    The difference is about 6-7 %

  - Non-debug build:

    - 5.1: 25691 ms, 25141 ms, 25864 ms
    - 5.5: 25924 ms, 24908 ms, 25804 ms

    There is almost no difference. 

Marking as experimental on Solaris in mysql-next-mr.

Tried to optimize advance_sj_state() by exiting immediately when there are no semijoin nests in the join plan:

  if (join->select_lex->sj_nests.is_empty())
    return;

This reduces CPU time when running funcs_1.myisam_views by 9% in optimized build.

Eliminating the calls to advance_sj_state() and restore_prev_sj_state() altogether reduces CPU time by 11%.

If someone walks this path, make sure that join->positions[].sj_strategy are initialized to SJ_OPT_NONE, because there are other functions that rely on this value.

However, this does not cover the whole 30% degradation, so more analysis is still needed to make up for the performance degradation.

What Roy suggests seems good to do anyway. Gaining 9% with such little coding effort...

http://lists.mysql.com/commits/105932

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/106736

3853 Guilhem Bichot	2010-04-27
      Fix for BUG#50595 "funcs_1.myisam_views takes longer time on 6.0 branch than 5.1 branch".
      EXPLAIN SELECT for a 20-table query took 28% less time in next-mr than in 6.0-codebase
      (1427 ms vs 1979 ms, release builds on Linux 64-bit, gcc 4.4.1-4ubuntu9). This patch
      makes 6.0-codebase almost as fast as next-mr in the "index" test (time decreased by 26%
      compared to pre-patch 6.0-codebase), and faster than next-mr in the "no index" test
      (time decreased by 35%).
      Details of the test query and observed timings are in the bug report.
     @ mysql-test/r/greedy_optimizer.result
        see comment of subselect_mat.result
     @ mysql-test/r/status.result
        consequence of removing floor() from best_access_path()
     @ mysql-test/r/subselect_mat.result
        removing floor() from best_access_path() slightly increases cost of table scan, which changes plan's cost.
        When floor() was present, plan (t2_16,t2) had cost 4.05 and (t2,t2_16) too, so first was chosen.
        When floor() is removed, costs become 4.0528 and 4.0514, so second is chosen.
        In short, when several plans for a query have almost equal costs, a small cost change
        for one plan (due to floor()'s removal) easily promotes or demotes it.
     @ mysql-test/t/subselect3.test
        Removing floor() made this test piece crash; it's just that the change led to another
        incarnation of BUG 50052. As find_best() will soon be removed to fix that bug,
        the test portion is simply removed now.
     @ sql/sql_select.cc
        Several changes:
        1) restore_prev_nj_state() and restore_prev_sj_state(), as they are always called in sequence,
        are merged into a single function backout_nj_sj_state(). This doesn't notably affect speed.
        2) Loose_scan_opt's constructor had some UNINIT_VAR(x), which translate to x=0. Those
        useless initializations degraded speed, they are removed, at the cost of more compiler warnings.
        3) Our logic is that cases which are not covered by a 6.0-codebase-specific new feature 
        should not be made slower in 6.0-codebase than in next-mr (so we should
        try to not add code to the execution path in this case); and other cases should be made
        faster (more code added to the execution path, but to enable new strategies, summing up
        to a net gain). Thus, Loose_scan_opt::init() is now optimized for the case where
        there is no semijoin: the conditions in test "//(1)" are swapped so that when there is no
        semijoin, a single expression in the big if() is evaluated instead of two. This improves
        speed.
        4) Some variables are made more local (scope reduction). Doesn't affect speed but looks
        nicer.
        5) In best_access_path(), the block under "if(s->keyuse") is marked as unlikely,
        this improves speed when there is no index, and doesn't degrade it when there is an
        index.
        6) In best_access_path(), the call to floor() was very costly, it is removed
        7) In best_extension_by_limited_search(), advance_sj_state() is very costly
        even when there is no semijoin; it is thus put under an if().

queued to 6.0-codebase-bugfixing.
Some detailed notes about the pushed patch:
- benchmarking was done with tree at revision-id alik@sun.com-20100324081645-mty1jwojamhbljy1 , BUILD/compile-pentium64 (release build, non-debug) on a Linux 64-bit 4 core Xeon 2.27 GHz, Ubuntu, kernel 2.6.31-20-generic .
- the pushed patch breaks down in 6 small patches; they are explained (what change, why this change, what performance measures) in http://lists.mysql.com/commits/105934 . The pushed patch is a combination of the patches mentioned in that mailing list post, with the exception that additionally, restore_prev_[nj|sj]_state() have been unified in one function (but that doesn't change speed).
- The benchmark tests described in the mailing list post are the following:
- - mysqld is started with no special option.
- - tables are created with the soon-attached script
- - timing code is inserted in greedy_search, with the soon-attached patch
- - the "no index" case is to run
     set optimizer_search_depth=25;
     EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,CAST(f2 AS CHAR) AS f2 FROM test1.v20;
     and then the time taken by greedy_search() can be seen in mysqld's stderr
- - the "index" case is to run
     set optimizer_search_depth=5;
     EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,CAST(f2 AS CHAR) AS f2 FROM test1i.v20; # notice the "i" : not same database as "no index"
- - I run the test 20 times, eliminate two first runs, take average, verify that standard deviation is below 5 milliseconds.

mysqldump-made script to create tables of benchmark

Attachment: benchmark_tables_dump.sql.txt.gz (application/x-gzip, text), 2.57 KiB.

patch for sql_select.cc to have timings

Attachment: timing_patch.txt (text/plain), 730 bytes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/106907

3844 Guilhem Bichot	2010-04-28
      Fix for BUG#50595 "funcs_1.myisam_views takes longer time on 6.0 branch than 5.1 branch".
      EXPLAIN SELECT for a 20-table query took 28% less time in next-mr than in 6.0-codebase
      (1427 ms vs 1979 ms, release builds on Linux 64-bit, gcc 4.4.1-4ubuntu9). This patch
      makes 6.0-codebase almost as fast as next-mr in the "index" test (time decreased by 26%
      compared to pre-patch 6.0-codebase), and faster than next-mr in the "no index" test
      (time decreased by 35%).
      Details of the test query and observed timings are in the bug report.
     @ mysql-test/r/greedy_optimizer.result
        see comment of subselect_mat.result.
        Regarding the first query, when floor() was present, all 7-table plans had exactly the same cost,
        so t1...t7 was chosen because:
        1) it is the first tested, because join_tab_cmp sorts tables by increasing record count
        2) a next plan evicts a previous plan only if its cost is strictly lower (not equal).
        When floor() is removed, plans get slightly different costs (all 44.3xx or 44.4xx)
        so the winner is different.
     @ mysql-test/r/status.result
        consequence of removing floor() from best_access_path()
     @ mysql-test/r/subselect_mat.result
        removing floor() from best_access_path() slightly increases cost of table scan, which changes plan's cost.
        When floor() was present, plan (t2_16,t2) had cost 4.05 and (t2,t2_16) too, so first was chosen.
        When floor() is removed, costs become 4.0528 and 4.0514, so second is chosen.
        In short, when several plans for a query have almost equal costs, a small cost change
        for one plan (due to floor()'s removal) easily promotes or demotes it.
     @ mysql-test/t/subselect3.test
        Removing floor() made this test piece crash; it's just that the change led to another
        incarnation of BUG 50052. As find_best() will soon be removed to fix that bug,
        the test portion is simply removed now.
     @ sql/sql_select.cc
        Several changes:
        1) restore_prev_nj_state() and restore_prev_sj_state(), as they are always called in sequence,
        are merged into a single function backout_nj_sj_state(). This doesn't notably affect speed.
        2) Loose_scan_opt's constructor had some UNINIT_VAR(x), which translate to x=0. Those
        useless initializations degraded speed, they are removed, at the cost of more compiler warnings.
        3) Our logic is that cases which are not covered by a 6.0-codebase-specific new feature 
        should not be made slower in 6.0-codebase than in next-mr (so we should
        try to not add code to the execution path in this case); and other cases should be made
        faster (more code added to the execution path, but to enable new strategies, summing up
        to a net gain). Thus, Loose_scan_opt::init() is now optimized for the case where
        there is no semijoin: the conditions in test "//(1)" are swapped so that when there is no
        semijoin, a single expression in the big if() is evaluated instead of two. This improves
        speed.
        4) Some variables are made more local (scope reduction). Doesn't affect speed but looks
        nicer.
        5) In best_access_path(), the block under "if(s->keyuse") is marked as unlikely,
        this improves speed when there is no index, and doesn't degrade it when there is an
        index.
        6) In best_access_path(), the call to floor() was very costly, it is removed
        7) In best_extension_by_limited_search(), advance_sj_state() is very costly
        even when there is no semijoin; it is thus put under an if().

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100507091908-vqyhpwf2km0aokno) (version source revid:alik@sun.com-20100507091737-12vceffs11elb25g) (merge vers: 6.0.14-alpha) (pib:16)

Hello Paul. The bug was that for queries with lots of tables (20 in my case), where the optimizer spends a lot of time finding the best way to access them (1.5 seconds in my case), 6.0 was significantly slower than next-mr to find this best way (2 secs vs 1.5 secs).

So there is something to document only for users of 6.0.

Noted in 6.0.14 changelog.

For some queries with many tables, the optimizer spent too much time
seeking the execution plan.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107836

3159 Guilhem Bichot	2010-05-10
      Fix for BUG#50595 "funcs_1.myisam_views takes longer time on 6.0 branch than 5.1 branch".
      (Backporting of guilhem@mysql.com-20100428194200-tqlpkfre7tzjye0e ).
      EXPLAIN SELECT for a 20-table query took 28% less time in next-mr than in 6.0-codebase
      (1427 ms vs 1979 ms, release builds on Linux 64-bit, gcc 4.4.1-4ubuntu9). This patch
      makes 6.0-codebase almost as fast as next-mr in the "index" test (time decreased by 26%
      compared to pre-patch 6.0-codebase), and faster than next-mr in the "no index" test
      (time decreased by 35%).
      Details of the test query and observed timings are in the bug report.
     @ mysql-test/r/greedy_optimizer.result
        see comment of subselect_mat.result.
        Regarding the first query, when floor() was present, all 7-table plans had exactly the same cost,
        so t1...t7 was chosen because:
        1) it is the first tested, because join_tab_cmp sorts tables by increasing record count
        2) a next plan evicts a previous plan only if its cost is strictly lower (not equal).
        When floor() is removed, plans get slightly different costs (all 44.3xx or 44.4xx)
        so the winner is different.
     @ mysql-test/r/status.result
        consequence of removing floor() from best_access_path()
     @ mysql-test/r/subselect_mat.result
        removing floor() from best_access_path() slightly increases cost of table scan, which changes plan's cost.
        When floor() was present, plan (t2_16,t2) had cost 4.05 and (t2,t2_16) too, so first was chosen.
        When floor() is removed, costs become 4.0528 and 4.0514, so second is chosen.
        In short, when several plans for a query have almost equal costs, a small cost change
        for one plan (due to floor()'s removal) easily promotes or demotes it.
     @ mysql-test/t/subselect3.test
        Removing floor() made this test piece crash; it's just that the change led to another
        incarnation of BUG 50052. As find_best() will soon be removed to fix that bug,
        the test portion is simply removed now.
     @ sql/sql_select.cc
        Several changes:
        1) restore_prev_nj_state() and restore_prev_sj_state(), as they are always called in sequence,
        are merged into a single function backout_nj_sj_state(). This doesn't notably affect speed.
        2) Loose_scan_opt's constructor had some UNINIT_VAR(x), which translate to x=0. Those
        useless initializations degraded speed, they are removed, at the cost of more compiler warnings.
        3) Our logic is that cases which are not covered by a 6.0-codebase-specific new feature 
        should not be made slower in 6.0-codebase than in next-mr (so we should
        try to not add code to the execution path in this case); and other cases should be made
        faster (more code added to the execution path, but to enable new strategies, summing up
        to a net gain). Thus, Loose_scan_opt::init() is now optimized for the case where
        there is no semijoin: the conditions in test "//(1)" are swapped so that when there is no
        semijoin, a single expression in the big if() is evaluated instead of two. This improves
        speed.
        4) Some variables are made more local (scope reduction). Doesn't affect speed but looks
        nicer.
        5) In best_access_path(), the block under "if(s->keyuse") is marked as unlikely,
        this improves speed when there is no index, and doesn't degrade it when there is an
        index.
        6) In best_access_path(), the call to floor() was very costly, it is removed
        7) In best_extension_by_limited_search(), advance_sj_state() is very costly
        even when there is no semijoin; it is thus put under an if().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/108310

3160 Guilhem Bichot	2010-05-10
      Fix for BUG#50595 "funcs_1.myisam_views takes longer time on 6.0 branch than 5.1 branch".
      (Backporting of guilhem@mysql.com-20100428194200-tqlpkfre7tzjye0e ).
      EXPLAIN SELECT for a 20-table query took 28% less time in next-mr than in 6.0-codebase
      (1427 ms vs 1979 ms, release builds on Linux 64-bit, gcc 4.4.1-4ubuntu9). This patch
      makes 6.0-codebase almost as fast as next-mr in the "index" test (time decreased by 26%
      compared to pre-patch 6.0-codebase), and faster than next-mr in the "no index" test
      (time decreased by 35%).
      Details of the test query and observed timings are in the bug report.
     @ mysql-test/r/greedy_optimizer.result
        see comment of subselect_mat.result.
        Regarding the first query, when floor() was present, all 7-table plans had exactly the same cost,
        so t1...t7 was chosen because:
        1) it is the first tested, because join_tab_cmp sorts tables by increasing record count
        2) a next plan evicts a previous plan only if its cost is strictly lower (not equal).
        When floor() is removed, plans get slightly different costs (all 44.3xx or 44.4xx)
        so the winner is different.
     @ mysql-test/r/status.result
        consequence of removing floor() from best_access_path()
     @ mysql-test/r/subselect_mat.result
        removing floor() from best_access_path() slightly increases cost of table scan, which changes plan's cost.
        When floor() was present, plan (t2_16,t2) had cost 4.05 and (t2,t2_16) too, so first was chosen.
        When floor() is removed, costs become 4.0528 and 4.0514, so second is chosen.
        In short, when several plans for a query have almost equal costs, a small cost change
        for one plan (due to floor()'s removal) easily promotes or demotes it.
     @ mysql-test/t/subselect3.test
        Removing floor() made this test piece crash; it's just that the change led to another
        incarnation of BUG 50052. As find_best() will soon be removed to fix that bug,
        the test portion is simply removed now.
     @ sql/sql_select.cc
        Several changes:
        1) restore_prev_nj_state() and restore_prev_sj_state(), as they are always called in sequence,
        are merged into a single function backout_nj_sj_state(). This doesn't notably affect speed.
        2) Loose_scan_opt's constructor had some UNINIT_VAR(x), which translate to x=0. Those
        useless initializations degraded speed, they are removed, at the cost of more compiler warnings.
        3) Our logic is that cases which are not covered by a 6.0-codebase-specific new feature 
        should not be made slower in 6.0-codebase than in next-mr (so we should
        try to not add code to the execution path in this case); and other cases should be made
        faster (more code added to the execution path, but to enable new strategies, summing up
        to a net gain). Thus, Loose_scan_opt::init() is now optimized for the case where
        there is no semijoin: the conditions in test "//(1)" are swapped so that when there is no
        semijoin, a single expression in the big if() is evaluated instead of two. This improves
        speed.
        4) Some variables are made more local (scope reduction). Doesn't affect speed but looks
        nicer.
        5) In best_access_path(), the block under "if(s->keyuse") is marked as unlikely,
        this improves speed when there is no index, and doesn't degrade it when there is an
        index.
        6) In best_access_path(), the call to floor() was very costly, it is removed
        7) In best_extension_by_limited_search(), advance_sj_state() is very costly
        even when there is no semijoin; it is thus put under an if().

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Re opening the bug since the test is still in experimental group. Please enable the test if it is passing now

http://trollheim.norway.sun.com/archive/2348637.disabled_tests.html

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Guilheim,
The big tests are run in the weekly PB2 run. See mysql-trunk/mysql-test/collections/mysql-trunk.weekly. So if the test is expected to take a long time you can mark it as big and add to the weekly run.alter-table.test fails due to Bug#37248 and hence disabled .

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127732

3531 Guilhem Bichot	2010-12-31
      Test which runs slowly on some machines, is marked as big
      so will be run only weekly; this closes BUG#50595.

Pushed into mysql-5.1 5.1.55 (revid:guilhem.bichot@oracle.com-20101231110734-mf0jvafrh6kbwdek) (version source revid:guilhem.bichot@oracle.com-20101231110734-mf0jvafrh6kbwdek) (merge vers: 5.1.55) (pib:24)

Pushed into mysql-trunk 5.6.2 (revid:guilhem.bichot@oracle.com-20101231130432-75izpjdrb7hrvzg7) (version source revid:guilhem.bichot@oracle.com-20101231130432-75izpjdrb7hrvzg7) (merge vers: 5.6.2) (pib:24)

Pushed into mysql-5.5 5.5.9 (revid:guilhem.bichot@oracle.com-20101231111448-marh583un7wuszsh) (version source revid:guilhem.bichot@oracle.com-20101231111448-marh583un7wuszsh) (merge vers: 5.5.9) (pib:24)

In 5.1, 5.5 and trunk:
- test has been marked as "big", and added to the weekly run of pb2 (which uses a timeout of 60 minutes instead of 15)
- test is not experimental anymore.
This closes the bug.

Changes to test suite. No changelog entry needed.