List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:May 30 2012 11:12am
Subject:bzr push into mysql-trunk branch (olav.sandstaa:3908 to 3909) WL#6082
View as plain text  
 3909 Olav Sandstaa	2012-05-30
      WL#6082 Improve the Disk-Sweep Multi-Range Read cost model
      
      Patch 3: Memory cost too large
      
      The cost of using memory for the sort buffer dominates the total cost
      for many queries. Since we do not consider cost of using buffer space
      in other places in the server we should not include it here either.
      
      This patch removes inclusion of memory cost for the sort buffer
      in the cost for doing ds-mrr scans.
      
      This change causes a few changes in plans for existing tests.
     @ mysql-test/r/join_cache_bka.result
        WL#6082: Change in plan after removing memory cost for sort buffer
        from DS-MRR cost model:
        
        Changing from using table scan to use range scan due to the new cost
        estimate for using DS-MRR becomes lower than the cost estimate for
        table scan.
     @ mysql-test/r/join_cache_bka_nixbnl.result
        WL#6082: Change in plan after removing memory cost for sort buffer
        from DS-MRR cost model:
        
        Changing from using table scan to use range scan due to the new cost
        estimate for using DS-MRR becomes lower than the cost estimate for
        table scan.
     @ mysql-test/r/join_cache_bkaunique.result
        WL#6082: Change in plan after removing memory cost for sort buffer
        from DS-MRR cost model:
        
        Changing from using table scan to use range scan due to the new cost
        estimate for using DS-MRR becomes lower than the cost estimate for
        table scan.
     @ mysql-test/r/join_cache_bnl.result
        WL#6082: Change in plan after removing memory cost for sort buffer
        from DS-MRR cost model:
        
        Changing from using table scan to use range scan due to the new cost
        estimate for using DS-MRR becomes lower than the cost estimate for
        table scan.
     @ mysql-test/r/join_cache_nojb.result
        WL#6082: Change in plan after removing memory cost for sort buffer
        from DS-MRR cost model:
        
        Changing from using table scan to use range scan due to the new cost
        estimate for using DS-MRR becomes lower than the cost estimate for
        table scan.
     @ mysql-test/r/order_by_all.result
        WL#6082: Change in plan after removing memory cost for sort buffer
        from DS-MRR cost model:
        
        Explanation for the change:
        
        Before this change the index to use was selected like this:
        1. best_access_path() selected wnid14 as the best index based on cost estimate.
        2. Later (likely in test_if_skip_sort_order()) we changes to use the wnid index
           to avoid sorting.
           (important note: the decision to use ICP or not is taken after step 1, 
           before step 2, thus ICP does not get used on the wnid index).
        
        With the fix:
        1. best_access_path() now selects to use the index wnid since due to this
           change this index now has a lower cost estimate.
        2. ICP is used on the wnid index.
     @ mysql-test/r/order_by_icp_mrr.result
        WL#6082: Change in plan after removing memory cost for sort buffer
        from DS-MRR cost model:
        
        Explanation for the change:
        
        Before this change the index to use was selected like this:
        1. best_access_path() selected wnid14 as the best index based on cost estimate.
        2. Later (likely in test_if_skip_sort_order()) we changes to use the wnid index
           to avoid sorting.
           (important note: the decision to use ICP or not is taken after step 1, 
           before step 2, thus ICP does not get used on the wnid index).
        
        With the fix:
        1. best_access_path() now selects to use the index wnid since due to this
           change this index now has a lower cost estimate.
        2. ICP is used on the wnid index.
     @ sql/handler.cc
        WL#6082: Remove the cost of using memory from cost calculations for
        DS-MRR.

    modified:
      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/order_by_all.result
      mysql-test/r/order_by_icp_mrr.result
      sql/handler.cc
 3908 Olav Sandstaa	2012-05-30
      WL#6082 Improve the Disk-Sweep Multi-Range Read cost model
                  
      Patch 6: The DS-MRR model assumes everything on disk
            
      DS-MRR can improve performance when most of the data for the query
      needs to be read from a hard disk. When all data is in memory the
      extra sorting will just add overhead. The relative overhead will be
      largest for small queries reading few records due to the need for
      setting up an extra handler to the storage engine, configure and
      allocate the sort buffer, and sort the records.
            
      With the current cost model we do not know whether data is in memory
      or on disk. So to avoid that DS-MRR is used when it is likely that the
      data is in memory the following heuristic is implemented:
            
      The cost based DS-MRR will not be used when:
      -the size of the table is smaller than the memory buffer of the
       storage engine (or smaller than 100 MB if we do not have information
       about the storage engine buffer).
      -the scan will retrieve less than 50 records.
     @ sql/handler.cc
        WL#6082: Avoid that the cost based DS-MRR is used for small tables.
     @ sql/handler.h
        WL#6082: Add a new handler::get_memory_buffer_size() to get 
        information about the size of the main memory buffer.
     @ storage/innobase/handler/ha_innodb.cc
        WL#6082: Implement handler::get_memory_buffer_size().
     @ storage/innobase/handler/ha_innodb.h
        WL#6082: Implement handler::get_memory_buffer_size().

    modified:
      sql/handler.cc
      sql/handler.h
      storage/innobase/handler/ha_innodb.cc
      storage/innobase/handler/ha_innodb.h
=== modified file 'mysql-test/r/join_cache_bka.result'

=== modified file 'mysql-test/r/join_cache_bka.result'
--- a/mysql-test/r/join_cache_bka.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/join_cache_bka.result	2012-05-30 11:11:53 +0000
@@ -475,7 +475,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	188	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -782,7 +782,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	188	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language

=== modified file 'mysql-test/r/join_cache_bka_nixbnl.result'
--- a/mysql-test/r/join_cache_bka_nixbnl.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/join_cache_bka_nixbnl.result	2012-05-30 11:11:53 +0000
@@ -475,7 +475,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	188	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -782,7 +782,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	188	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language

=== modified file 'mysql-test/r/join_cache_bkaunique.result'
--- a/mysql-test/r/join_cache_bkaunique.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/join_cache_bkaunique.result	2012-05-30 11:11:53 +0000
@@ -476,7 +476,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	188	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access (unique))
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access (unique))
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -783,7 +783,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	188	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access (unique))
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access (unique))
 SELECT City.Name, Country.Name, CountryLanguage.Language

=== modified file 'mysql-test/r/join_cache_bnl.result'
--- a/mysql-test/r/join_cache_bnl.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/join_cache_bnl.result	2012-05-30 11:11:53 +0000
@@ -476,7 +476,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	188	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -783,7 +783,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	188	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language

=== modified file 'mysql-test/r/join_cache_nojb.result'
--- a/mysql-test/r/join_cache_nojb.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/join_cache_nojb.result	2012-05-30 11:11:53 +0000
@@ -476,7 +476,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	188	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
@@ -783,7 +783,7 @@
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	188	Using index condition; Using MRR
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
 1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language

=== modified file 'mysql-test/r/order_by_all.result'
--- a/mysql-test/r/order_by_all.result	2012-04-30 10:06:23 +0000
+++ b/mysql-test/r/order_by_all.result	2012-05-30 11:11:53 +0000
@@ -785,7 +785,7 @@
 ('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
 explain select * from t1 where wnid like '0101%' order by wnid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	wnid14,wnid	wnid	13	NULL	10	Using where
+1	SIMPLE	t1	range	wnid14,wnid	wnid	13	NULL	10	Using index condition
 select * from t1 where wnid like '0101%' order by wnid;
 sid	wnid
 10100	01019000000

=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result	2012-04-30 10:06:23 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result	2012-05-30 11:11:53 +0000
@@ -785,7 +785,7 @@
 ('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
 explain select * from t1 where wnid like '0101%' order by wnid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	wnid14,wnid	wnid	13	NULL	10	Using where
+1	SIMPLE	t1	range	wnid14,wnid	wnid	13	NULL	10	Using index condition
 select * from t1 where wnid like '0101%' order by wnid;
 sid	wnid
 10100	01019000000

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2012-05-30 09:33:26 +0000
+++ b/sql/handler.cc	2012-05-30 11:11:53 +0000
@@ -6335,22 +6335,12 @@
   (*cost)+= last_step_cost;
 
   /*
-    With the old COST_VECT, memory cost was part of total_cost() but
-    that's not the case with Cost_estimate. Introducing Cost_estimate
-    shall not change any costs, hence the memory cost is added as if
-    it was CPU cost below. To be reconsidered when DsMRR costs are
-    refactored.
+    Cost of memory is not included in the total_cost() function and
+    thus will not be considered when comparing costs. Still, we
+    record it in the cost estimate object for future use.
   */
-  if (n_full_steps != 0)
-  {
-    cost->add_mem(*buffer_size);
-    cost->add_cpu(*buffer_size);
-  }
-  else
-  {
-    cost->add_mem(rows_in_last_step * elem_size);
-    cost->add_cpu(rows_in_last_step * elem_size);
-  }  
+  cost->add_mem(*buffer_size);
+
   /* Total cost of all index accesses */
   index_read_cost= h->index_only_read_time(keynr, rows);
   cost->add_io(index_read_cost * Cost_estimate::IO_BLOCK_READ_COST());

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (olav.sandstaa:3908 to 3909) WL#6082Olav Sandstaa30 May