List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:June 7 2011 11:34am
Subject:Re: bzr commit into mysql-trunk branch (roy.lyseng:3371) Bug#12316645
View as plain text  
On 26.05.11 10.55, Øystein Grøvlen wrote:
> Hi Roy,
>
> See inline for some comments on the changes in test results:
>
> On 12/05/2011 17:26, Roy Lyseng wrote:
>  > #At file:///home/rl136806/mysql/repo/mysql-work0/ based on
> revid:roy.lyseng@stripped
>  >
>  > 3371 Roy Lyseng 2011-05-12
>  > Bug#12316645: Wrong cost calculation with optimizer_join_cache_level settings
>  >
>  > best_access_path() did not take properly into account the setting of
>  > optimizer_join_cache_level when calculating the access path that gives
>  > the smallest cost to access a given table.
>  >
>  > There are three changes applied in this bug fix:
>  > - best_access_path() properly accounts for join cache level 0, ie
>  > do not use join caching.
>  > - best_access_path() does account for join cache level greater than 3
>  > when a table is outer-joined to the current join prefix.
>  > - best_access_path() does account for join cache level greater than 3
>  > when a table is semi-joined to the current join prefix.
>  >
>  > Static properties for setting join buffering has been centralized to
>  > a new function set_join_buffer_properties().
>  >
>  > Further on, setting of join cache level is changed.
>  > check_join_cache_usage() is renamed to setup_join_cache().
>  >
>  > Cost calculation for joins with join buffering is better than it used
>  > to be, but it is still not fair. Sometimes, cost is calculated using
>  > join buffering, but the decision has to be reverted later.
>  > Cost calculation for BKA join strategy is also not considered properly
>  > (but this patch changes nothing in this area).
>  >
>
> ...
>
>  > === modified file 'mysql-test/r/join_cache_jcl4.result'
>  > --- a/mysql-test/r/join_cache_jcl4.result 2011-04-28 11:55:16 +0000
>  > +++ b/mysql-test/r/join_cache_jcl4.result 2011-05-12 15:25:46 +0000
>  > @@ -1449,8 +1449,8 @@ explain
>  > select t1.a, count(t2.p) as count
>  > from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
>  > id select_type table type possible_keys key key_len ref rows Extra
>  > -1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
>  > -1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where
>  > +1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using
> filesort
>  > +1 SIMPLE t2 ALL i_a NULL NULL NULL 10 Using where; Using join buffer (BNL,
> incremental buffers)
>
> Hm. The text in the result file just above this diff is
> The following must not show "using join cache":
>
> Looking at the original bug report (Bug#40136) and fix, it seems the
> intention is to prevent BKA in this case. So it is probably the
> comment that is wrong. I also notice your patch changes the for this
> query from BKA to BNL for higher values of join cache level. Join
> caching appeared with the fix for Bug#42955, so maybe it is not longer
> true that BKA cannot be used?

It looks so, yes. I will delete that line from the test case.
>
> I also looked at handler request for the above query. The change does
> not look like an improvement in this case. On the contrary, both
> random and sequential accesses increases:
>
> show status like 'Handler_read%';
> Variable_name Value
> Handler_read_first 1
> -Handler_read_key 8
> +Handler_read_key 10
> Handler_read_last 0
> -Handler_read_next 18
> +Handler_read_next 8
> Handler_read_prev 0
> -Handler_read_rnd 0
> -Handler_read_rnd_next 0
> +Handler_read_rnd 8
> +Handler_read_rnd_next 20

The cost model is far from perfect...

Without join buffering, the cost for table t2 is 16.0 for the lookups (8 lookups 
multiplied by 2 for each?) and 16 x 0.2 for the compare cost, giving a total 
cost of 22.8175 before the sort stage. The row count estimate is 16 and a sort 
cost of 16 is added to get a total cost of 38.81. I assume that the sort stage 
is removed later in optimization.

With join buffering, the cost for table t2 is 2.422 for the table scan and 64 x 
0.2 for the compare cost, giving a total cost of 18.84. The row count estimate 
is 64, which means that after sorting the total cost estimate is 82.84.

So, the reason for the discrepancy here is:
  - Sorting is not considered when comparing costs after reading table t2.
  - If it were considered, it would still have been wrong because the sort stage
    is later deleted when not using join buffering.
  - Row count estimates are wrong, giving a huge sort cost for BNL (which however
    is not considered in the total cost estimate).

I do not think there is anything I can do about this, except noting the above 
points for the cost model refactoring project...
>
> ...
>
>
>  > === modified file 'mysql-test/r/join_nested.result'
>  > --- a/mysql-test/r/join_nested.result 2010-12-06 13:12:51 +0000
>  > +++ b/mysql-test/r/join_nested.result 2011-05-12 15:25:46 +0000
>
> ...
>
>  > @@ -1729,11 +1729,11 @@ LEFT JOIN
>  > (t5 JOIN t4 ON t5.carrier_id = t4.id)
>  > ON t4.carrier = t1.carrier;
>  > id select_type table type possible_keys key key_len ref rows Extra
>  > -1 SIMPLE t2 index package_id package_id 5 NULL 45 Using where; Using index
>  > -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1
>  > +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 36
>  > +1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where; Using index
>  > +1 SIMPLE t2 ref package_id package_id 5 test.t3.package_id 5 Using where;
> Using index
>  > 1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1
>  > 1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index
>  > -1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where; Using index
>
> The handler statistics indicates that the old plan was probably better:
>
> show status like 'Handler_read%';
> Variable_name Value
> -Handler_read_first 1
> -Handler_read_key 56
> +Handler_read_first 0
> +Handler_read_key 74
> Handler_read_last 0
> -Handler_read_next 51
> +Handler_read_next 38
> Handler_read_prev 0
> Handler_read_rnd 0
> -Handler_read_rnd_next 0
> +Handler_read_rnd_next 37

Current join order: t2 - t1 - t4 - t5 - t3
New join order:     t1 - t3 - t2 - t4 - t5

The reason why the current join order is no longer select has to do with t4. The 
access for this table used to be an eq_ref, but now a table scan and join 
buffering appears to be cheaper. The problem is that the fanout increases from 1 
to 5, hence the subsequent table accesses become very expensive and the plan is 
eventually pruned.

It is probably correct that join buffering is cheaper than eq_ref for this 
table, but the problem is the wrong fanout estimate. best_access_path() should 
have used the fanout estimate given by the eq_ref access method (ie 1) when 
calculating the cost. Nevertheless, setup_join_buffering() will later cancel 
join buffering for t4, because the second table in the outer join nest (t5) does 
not allow join buffering.
>
> ...
>
>  > === modified file 'mysql-test/r/select_all.result'
>  > --- a/mysql-test/r/select_all.result 2011-01-13 10:48:28 +0000
>  > +++ b/mysql-test/r/select_all.result 2011-05-12 15:25:46 +0000
>  > @@ -2398,7 +2398,7 @@ INSERT INTO t2 VALUES ('one'),('two'),('
>  > EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;
>  > id select_type table type possible_keys key key_len ref rows Extra
>  > 1 SIMPLE t1 ALL NULL NULL NULL NULL 5
>  > -1 SIMPLE t2 ref a a 23 test.t1.a 2 Using where
>  > +1 SIMPLE t2 ALL a NULL NULL NULL 5 Using where; Using join buffer (BNL,
> incremental buffers)
>
> The above query is probably no longer testing what it was intended
> to test. ("Test case for bug 7520: a wrong cost of the index for a
> BLOB field").

We should probably have more tests with join_cache_level=0 for improved coverage.

Thanks,
Roy
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3371) Bug#12316645Roy Lyseng12 May
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3371) Bug#12316645Øystein Grøvlen23 May
    • Re: bzr commit into mysql-trunk branch (roy.lyseng:3371) Bug#12316645Roy Lyseng23 May
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3371) Bug#12316645Øystein Grøvlen26 May
    • Re: bzr commit into mysql-trunk branch (roy.lyseng:3371) Bug#12316645Roy Lyseng7 Jun