3916 Olav Sandstaa 2012-06-06 [merge]
Merge from mysql-trunk to mysql-trunk-wl6082
modified:
mysql-test/include/index_merge1.inc
mysql-test/include/index_merge2.inc
mysql-test/include/index_merge_ror.inc
mysql-test/include/index_merge_ror_cpk.inc
mysql-test/include/mrr_tests.inc
mysql-test/suite/engines/funcs/r/se_join_default.result
mysql-test/suite/engines/funcs/t/se_join_cross.test
mysql-test/suite/engines/funcs/t/se_join_default.test
mysql-test/suite/engines/funcs/t/se_join_inner.test
mysql-test/suite/engines/funcs/t/se_join_left.test
mysql-test/suite/engines/funcs/t/se_join_left_outer.test
mysql-test/suite/engines/funcs/t/se_join_natural_left.test
mysql-test/suite/engines/funcs/t/se_join_natural_left_outer.test
mysql-test/suite/engines/funcs/t/se_join_natural_right.test
mysql-test/suite/engines/funcs/t/se_join_natural_right_outer.test
mysql-test/suite/engines/funcs/t/se_join_right.test
mysql-test/suite/engines/funcs/t/se_join_right_outer.test
mysql-test/suite/engines/funcs/t/se_join_straight.test
mysql-test/t/index_merge_innodb.test
sql/log_event.h
storage/innobase/dict/dict0dict.cc
storage/innobase/dict/dict0load.cc
storage/innobase/dict/dict0stats.cc
storage/innobase/dict/dict0stats_background.cc
storage/innobase/handler/ha_innodb.cc
storage/innobase/include/dict0dict.h
storage/innobase/include/dict0load.h
storage/innobase/include/dict0stats.h
storage/innobase/include/dict0stats.ic
storage/innobase/row/row0mysql.cc
strings/ctype-uca.c
3915 Olav Sandstaa 2012-06-06
WL#6082 Improve the Disk-Sweep Multi-Range Read cost model
Patch 4: CPU cost is too high
The cost estimate for sorting the keys in the sort buffer for a large
scan is too high. It can be higher than the IO cost estimate for
reading the data from the database and in can be several times higher
than the CPU cost estimate for the same scan using standard MRR. In
addition it does not include the normal row processing cost that is
done in a standard MRR scan. So for scan that read just a few records,
the CPU cost estimate for DS-MRR can actually be lower than the CPU
estimate for standard MRR.
This patch fixes the following issues:
-a bug in the formula for calculating the CPU estimate for the sort
based on quick sort.
-reduces the cost for doing the sort operation by replacing the use of
ROWID_COMPARE_COST constant (value 0.1) with a new constant
ROWID_COMPARE_SORT_COST that has the value =0.01.
-add the cost for doing row processing in the same way as it is
calculated for stand MRR scans.
This patch changes execution plan for several test cases that are run
with mrr_cost_based off. The cause for these changes is that the cost
estimate for doing DS-MRR has changed (either increased or decreased).
@ mysql-test/include/range.inc
WL#6082: Change in comment for test due to this query will now be
executed using ref access when MRR is not used and as a range
scan when MRR is used (by setting mrr_cost_based=off). The
cause for this change is that a DS-MRR scan now is less costly than
a table scan for this query.
@ mysql-test/r/join_cache_bka.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
The change for the query plan for the 11 way join is caused by changes
to the cost estimate for accessing table "t3". Before this fix it had
the following costs:
-table_scan: 4.53 (rows=2)
-range scan using t3_formatid: cost 6.7 (where IO-cost is 1.9 and cpu-cost
is 4.8 and row estimate is 3)
After the fix it has the following costs:
-table_scan: 4.53 (rows=2)
-range scan using t3_formatid: 2.6 (where IO-cost is 1.9 and cpu-cost is 0.6)
Thus, this fix has changed preferred access strategy for t3 from using
a table scan to use a range scan (which probably is questionable given
the table contains 2 records - which also indicates that the table
scan cost is too high here?). This change causes the t3 table to have
a quick select object that is considered used in best_access_path()
and using this causes changes to cost for alternative plans and make
the join optimizer produce a different plan.
The second query plan changes access method from table scan to range
access due to lower cost estimate for reading the t1 table using
DS-MRR. Before this fix the cost estimate for using DS-MRR was 6.7
(IO-cost was 2.0 and CPU-cost was 4.8). With this change the new cost
estimate is 2.9 (IO-cost 2.0 and CPU-cost 0.9).
@ mysql-test/r/join_cache_bka_nixbnl.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
The change for the query plan for the 11 way join is caused by changes
to the cost estimate for accessing table "t3". Before this fix it had
the following costs:
-table_scan: 4.53 (rows=2)
-range scan using t3_formatid: cost 6.7 (where IO-cost is 1.9 and cpu-cost
is 4.8 and row estimate is 3)
After the fix it has the following costs:
-table_scan: 4.53 (rows=2)
-range scan using t3_formatid: 2.6 (where IO-cost is 1.9 and cpu-cost is 0.6)
Thus, this fix has changed preferred access strategy for t3 from using
a table scan to use a range scan (which probably is questionable given
the table contains 2 records - which also indicates that the table
scan cost is too high here?). This change causes the t3 table to have
a quick select object that is considered used in best_access_path()
and using this causes changes to cost for alternative plans and make
the join optimizer produce a different plan.
The second query plan changes access method from table scan to range
access since due to lower cost estimate for reading the t1 table using
DS-MRR. Before this fix the cost estimate for using DS-MRR was 6.7
(IO-cost was 2.0 and CPU-cost was 4.8). With this change the new cost
estimate is 2.9 (IO-cost 2.0 and CPU-cost 0.9).
@ mysql-test/r/join_cache_bkaunique.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
The change for the query plan for the 11 way join is caused by changes
to the cost estimate for accessing table "t3". Before this fix it had
the following costs:
-table_scan: 4.53 (rows=2)
-range scan using t3_formatid: cost 6.7 (where IO-cost is 1.9 and cpu-cost
is 4.8 and row estimate is 3)
After the fix it has the following costs:
-table_scan: 4.53 (rows=2)
-range scan using t3_formatid: 2.6 (where IO-cost is 1.9 and cpu-cost is 0.6)
Thus, this fix has changed preferred access strategy for t3 from using
a table scan to use a range scan (which probably is questionable given
the table contains 2 records - which also indicates that the table
scan cost is too high here?). This change causes the t3 table to have
a quick select object that is considered used in best_access_path()
and using this causes changes to cost for alternative plans and make
the join optimizer produce a different plan.
The second query plan changes access method from table scan to range
access due to lower cost estimate for reading the t1 table using
DS-MRR. Before this fix the cost estimate for using DS-MRR was 6.7
(IO-cost was 2.0 and CPU-cost was 4.8). With this change the new cost
estimate is 2.9 (IO-cost 2.0 and CPU-cost 0.9).
@ mysql-test/r/join_cache_bnl.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
The change for the query plan for the 11 way join is caused by changes
to the cost estimate for accessing table "t3". Before this fix it had
the following costs:
-table_scan: 4.53 (rows=2)
-range scan using t3_formatid: cost 6.7 (where IO-cost is 1.9 and cpu-cost
is 4.8 and row estimate is 3)
After the fix it has the following costs:
-table_scan: 4.53 (rows=2)
-range scan using t3_formatid: 2.6 (where IO-cost is 1.9 and cpu-cost is 0.6)
Thus, this fix has changed preferred access strategy for t3 from using
a table scan to use a range scan (which probably is questionable given
the table contains 2 records - which also indicates that the table
scan cost is too high here?). This change causes the t3 table to have
a quick select object that is considered used in best_access_path()
and using this causes changes to cost for alternative plans and make
the join optimizer produce a different plan.
The second query plan changes access method from table scan to range
access due to lower cost estimate for reading the t1 table using
DS-MRR. Before this fix the cost estimate for using DS-MRR was 6.7
(IO-cost was 2.0 and CPU-cost was 4.8). With this change the new cost
estimate is 2.9 (IO-cost 2.0 and CPU-cost 0.9).
@ mysql-test/r/join_cache_nojb.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
The change for the query plan for the 11 way join is caused by changes
to the cost estimate for accessing table "t3". Before this fix it had
the following costs:
-table_scan: 4.53 (rows=2)
-range scan using t3_formatid: cost 6.7 (where IO-cost is 1.9 and cpu-cost
is 4.8 and row estimate is 3)
After the fix it has the following costs:
-table_scan: 4.53 (rows=2)
-range scan using t3_formatid: 2.6 (where IO-cost is 1.9 and cpu-cost is 0.6)
Thus, this fix has changed preferred access strategy for t3 from using
a table scan to use a range scan (which probably is questionable given
the table contains 2 records - which also indicates that the table
scan cost is too high here?). This change causes the t3 table to have
a quick select object that is considered used in best_access_path()
and using this causes changes to cost for alternative plans and make
the join optimizer produce a different plan.
The second query plan changes access method from table scan to range
access since due to lower cost estimate for reading the t1 table using
DS-MRR. Before this fix the cost estimate for using DS-MRR was 6.7
(IO-cost was 2.0 and CPU-cost was 4.8). With this change the new cost
estimate is 2.9 (IO-cost 2.0 and CPU-cost 0.9).
@ mysql-test/r/range_all.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
The query plan for two queries changes to use range access. This is
a result of that the cost of doing a DS-MRR scan is now lower than
the cost of doing a table scan and a quick object will be created for
this table.
@ mysql-test/r/range_icp.result
WL#6082: Change in comment for test due to this query will now be
executed using ref access when MRR is not used and as a range
scan when MRR is used (by setting mrr_cost_based=off). The
cause for this change is that a DS-MRR scan now is less costly than
a table scan for this query.
@ mysql-test/r/range_icp_mrr.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
The query plan for two queries changes to use range access. This is
a result of that the cost of doing a DS-MRR scan is now lower than
the cost of doing a table scan and a quick object will be created for
this table.
@ mysql-test/r/range_mrr.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
The query plan for two queries changes to use range access. This is
a result of that the cost of doing a DS-MRR scan is now lower than
the cost of doing a table scan and a quick object will be created for
this table.
@ mysql-test/r/range_mrr_cost.result
WL#6082: Change in comment for test due to this query will now be
executed using ref access when MRR is not used and as a range
scan when MRR is used (by setting mrr_cost_based=off). The
cause for this change is that a DS-MRR scan now is less costly than
a table scan for this query.
@ mysql-test/r/range_none.result
WL#6082: Change in comment for test due to this query will now be
executed using ref access when MRR is not used and as a range
scan when MRR is used (by setting mrr_cost_based=off). The
cause for this change is that a DS-MRR scan now is less costly than
a table scan for this query.
@ mysql-test/r/select_all.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
@ mysql-test/r/select_all_bka.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
@ mysql-test/r/select_all_bka_nixbnl.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
@ mysql-test/r/select_icp_mrr.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
@ mysql-test/r/select_icp_mrr_bka.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
@ mysql-test/r/select_icp_mrr_bka_nixbnl.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
@ mysql-test/r/subquery_sj_all.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
Several plans switches from using Materialization to FirstMatch: This
is caused by that FirstMatch uses a range search for look-up
into the inner table. As a result, the cost of FirstMatch is reduced more
than the cost of Materialization when cost of the DS-MRR range search is
reduced.
@ mysql-test/r/subquery_sj_all_bka.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
Several plans switches from using Materialization to FirstMatch: This
is caused by that FirstMatch uses a range search for look-up
into the inner table. As a result, the cost of FirstMatch is reduced more
than the cost of Materialization when cost of the DS-MRR range search is
reduced.
@ mysql-test/r/subquery_sj_all_bka_nixbnl.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
Several plans switches from using Materialization to FirstMatch: This
is caused by that FirstMatch uses a range search for look-up
into the inner table. As a result, the cost of FirstMatch is reduced more
than the cost of Materialization when cost of the DS-MRR range search is
reduced.
@ mysql-test/r/subquery_sj_all_bkaunique.result
WL#6082: Change in plan due to changes in CPU cost estimate for DS-MRR.
Several plans switches from using Materialization to FirstMatch: This
is caused by that FirstMatch uses a range search for look-up
into the inner table. As a result, the cost of FirstMatch is reduced more
than the cost of Materialization when cost of the DS-MRR range search is
reduced.
@ mysql-test/suite/opt_trace/r/bugs_no_prot_all.result
WL#6082: Change cost estimate for range scan due to changes in
how cpu cost is estimated in the DS-MRR cost model.
@ mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result
WL#6082: Change cost estimate for range scan due to changes in
how cpu cost is estimated in the DS-MRR cost model.
@ sql/handler.cc
WL#6082: Improve the estimate for CPU cost in DS-MRR.
modified:
mysql-test/include/range.inc
mysql-test/r/join_cache_bka.result
mysql-test/r/join_cache_bka_nixbnl.result
mysql-test/r/join_cache_bkaunique.result
mysql-test/r/join_cache_bnl.result
mysql-test/r/join_cache_nojb.result
mysql-test/r/range_all.result
mysql-test/r/range_icp.result
mysql-test/r/range_icp_mrr.result
mysql-test/r/range_mrr.result
mysql-test/r/range_mrr_cost.result
mysql-test/r/range_none.result
mysql-test/r/select_all.result
mysql-test/r/select_all_bka.result
mysql-test/r/select_all_bka_nixbnl.result
mysql-test/r/select_icp_mrr.result
mysql-test/r/select_icp_mrr_bka.result
mysql-test/r/select_icp_mrr_bka_nixbnl.result
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/suite/opt_trace/r/bugs_no_prot_all.result
mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result
sql/handler.cc
Diff too large for email (31614 lines, the limit is 10000).
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (olav.sandstaa:3915 to 3916) | Olav Sandstaa | 6 Jun |
