List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:June 6 2012 10:29am
Subject:bzr push into mysql-trunk branch (olav.sandstaa:3915 to 3916)
View as plain text  
 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 Sandstaa6 Jun