List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:November 7 2009 11:10pm
Subject:bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3161)
Bug#47261
View as plain text  
#At file:///Users/mattiasj/clones/bzrroot/b47261-51-bugteam/ based on revid:mattias.jonsson@stripped

 3161 Mattias Jonsson	2009-11-08
      bug#47261 bad performance in 5.1 for heavily partitioned tables
      
      Further optimizations on optimizer statistics in
      the partitioning handler
     @ mysys/thr_lock.c
        bug#47261 bad performancd in 5.1 for heavily partitioned tables
        
        fix in comment
     @ sql/ha_partition.cc
        work in progress
        
        optimization of optimizer statistic calls

    modified:
      mysys/thr_lock.c
      sql/ha_partition.cc
      sql/ha_partition.h
=== modified file 'mysys/thr_lock.c'
--- a/mysys/thr_lock.c	2009-10-13 14:59:43 +0000
+++ b/mysys/thr_lock.c	2009-11-07 23:10:48 +0000
@@ -1006,7 +1006,7 @@ static int thr_lock_data_cmp(SORT_NODE *
 
 
 /**
-  Lock multiple locks in a consitent order
+  Lock multiple locks in a consistent order
 
   @param  data    pointer to an array of 2*count THR_LOCK_DATA elements,
                   where the first half is the locks to lock (read),

=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc	2009-10-13 14:59:43 +0000
+++ b/sql/ha_partition.cc	2009-11-07 23:10:48 +0000
@@ -5770,56 +5770,162 @@ const key_map *ha_partition::keys_to_use
   DBUG_RETURN(m_file[0]->keys_to_use_for_scanning());
 }
 
-
+#define MAX_CHECK_PARTS_FOR_OPTIMIZE 10
+struct st_records_in_range_args
+{
+  uint inx;
+  key_range *min_key;
+  key_range *max_key;
+  ha_rows rows;                            /* return value */
+};
+typedef st_records_in_range_args RECORDS_IN_RANGE_ARGS;
 /*
-  Return time for a scan of the table
+  Get a estimate of optimizer data for the table
 
   SYNOPSIS
-    scan_time()
+    get_optimizer_data()
 
   RETURN VALUE
-    time for scan
-*/
+    FALSE ok
+    TRUE  failure
 
-double ha_partition::scan_time()
-{
-  double scan_time= 0;
-  handler **file;
-  DBUG_ENTER("ha_partition::scan_time");
+  NOTE: probably OK to change order for RANGE to look at first, last,
+        last - 1, last - 2 etc.
+        An alternative way would be to do ::info() first and then call the ones
+        with most rows...
+*/
+bool ha_partition::get_optimizer_data(enum enum_partition_optimizer_func func,
+                                      void *data)
+{
+  double total_time= 0, *ret_time;
+  ha_rows rows, total_rows= 0, *ret_rows;
+  RECORDS_IN_RANGE_ARGS *recs_arg;
+  uint first, last, part_id, num_used_parts, partitions_called, check_min_num;
+  //bool force_open= FALSE;
+  DBUG_ENTER("ha_partition::get_optimizer_data");
+  partitions_called= 0;
+  num_used_parts= bitmap_bits_set(&(m_part_info->used_partitions));
+
+  /* This might even be optimized also when not having open_files */
+  //check_min_num= m_tot_parts;                /* lower when forced open */
+  check_min_num= (num_used_parts + 2) / 3;
+  check_min_num= min(MAX_CHECK_PARTS_FOR_OPTIMIZE, check_min_num);
+  if (func == func_records_in_range)
+    recs_arg= (RECORDS_IN_RANGE_ARGS*) data;
 
-  for (file= m_file; *file; file++)
-    if (bitmap_is_set(&(m_part_info->used_partitions), (file - m_file)))
-      scan_time+= (*file)->scan_time();
-  DBUG_RETURN(scan_time);
+  /*
+    First check if any of the already opened partitions can be used,
+    otherwise open and check a third of the used partitions, up to 10
+    and the result is greater than 0.
+  */
+  first= bitmap_get_first_set(&(m_part_info->used_partitions));
+  //last= bitmap_get_last_set(&(m_part_info->used_partitions));
+  last = m_tot_parts - 1;
+  for (part_id= first; part_id <= last ; part_id++)
+  {
+    if (bitmap_is_set(&(m_part_info->used_partitions), part_id))
+    {
+      /*
+        For the first round, only use opened partitions,
+        on the other round, only use non opened partitions.
+      */
+      /*
+      if (!(force_open ^ partition_is_open(part_id)))
+        continue;
+      */
+      switch (func) {
+      case func_scan_time:
+        total_time+= m_file[part_id]->scan_time();
+        break;
+      case func_records_in_range:
+        rows= m_file[part_id]->records_in_range(recs_arg->inx,
+                                                recs_arg->min_key,
+                                                recs_arg->max_key);
+        if (rows == HA_POS_ERROR)
+        {
+          recs_arg->rows= HA_POS_ERROR;
+          DBUG_RETURN(TRUE);
+        }
+        total_rows+= rows;
+        break;
+      case func_estimate_rows_upper_bound:
+        rows= m_file[part_id]->estimate_rows_upper_bound();
+        if (rows == HA_POS_ERROR)
+        {
+          ha_rows *ret= (ha_rows*) data;
+          *ret= HA_POS_ERROR;
+          DBUG_RETURN(TRUE);
+        }
+        total_rows+= rows;
+        break;
+      default:
+        DBUG_ASSERT(0);
+      }
+      partitions_called++;
+      if (partitions_called >= check_min_num)
+      {
+        /* 0 for result has a special meaning (i.e. empty), so assure this */
+        bool can_leave= TRUE;
+        switch (func) {
+          case func_scan_time:
+            if (total_time == 0)
+              can_leave= FALSE;
+            break;
+          case func_records_in_range:
+          case func_estimate_rows_upper_bound:
+            if (total_rows == 0)
+              can_leave= FALSE;
+            break;
+          default:
+            DBUG_ASSERT(0);
+        }
+        if (can_leave)
+          break;
+      }
+    }
+  }
+  switch (func) {
+    case func_scan_time:
+      ret_time= (double*) data;
+      *ret_time= total_time *
+                      (double) num_used_parts / (double) partitions_called;
+      break;
+    case func_records_in_range:
+      recs_arg->rows= total_rows * num_used_parts / partitions_called;
+      break;
+    case func_estimate_rows_upper_bound:
+      ret_rows= (ha_rows*) data;
+      *ret_rows= total_rows * num_used_parts / partitions_called;
+      break;
+    default:
+      DBUG_ASSERT(0);
+      /* no return value */
+  }
+
+  DBUG_RETURN(FALSE);
 }
 
 
 /*
-  Get time to read
+  Return time for a scan of the table
 
   SYNOPSIS
-    read_time()
-    index                Index number used
-    ranges               Number of ranges
-    rows                 Number of rows
+    scan_time()
 
   RETURN VALUE
-    time for read
-
-  DESCRIPTION
-    This will be optimised later to include whether or not the index can
-    be used with partitioning. To achieve we need to add another parameter
-    that specifies how many of the index fields that are bound in the ranges.
-    Possibly added as a new call to handlers.
+    time for scan
 */
 
-double ha_partition::read_time(uint index, uint ranges, ha_rows rows)
+double ha_partition::scan_time()
 {
-  DBUG_ENTER("ha_partition::read_time");
+  double scan_time= 0;
+  DBUG_ENTER("ha_partition::scan_time");
 
-  DBUG_RETURN(m_file[0]->read_time(index, ranges, rows));
+  get_optimizer_data(func_scan_time, (void*) &scan_time);
+  DBUG_RETURN(scan_time);
 }
 
+
 /*
   Find number of records in a range
 
@@ -5847,22 +5953,17 @@ double ha_partition::read_time(uint inde
 ha_rows ha_partition::records_in_range(uint inx, key_range *min_key,
 				       key_range *max_key)
 {
-  handler **file;
-  ha_rows in_range= 0;
+  struct st_records_in_range_args func_arg;
   DBUG_ENTER("ha_partition::records_in_range");
 
-  file= m_file;
-  do
-  {
-    if (bitmap_is_set(&(m_part_info->used_partitions), (file - m_file)))
-    {
-      ha_rows tmp_in_range= (*file)->records_in_range(inx, min_key, max_key);
-      if (tmp_in_range == HA_POS_ERROR)
-        DBUG_RETURN(tmp_in_range);
-      in_range+= tmp_in_range;
-    }
-  } while (*(++file));
-  DBUG_RETURN(in_range);
+  func_arg.inx= inx;
+  func_arg.min_key= min_key;
+  func_arg.max_key= max_key;
+  func_arg.rows= 0;
+
+  if (get_optimizer_data(func_records_in_range, (void*) &func_arg))
+    DBUG_RETURN(HA_POS_ERROR);
+  DBUG_RETURN(func_arg.rows);
 }
 
 
@@ -5878,25 +5979,42 @@ ha_rows ha_partition::records_in_range(u
 
 ha_rows ha_partition::estimate_rows_upper_bound()
 {
-  ha_rows rows, tot_rows= 0;
-  handler **file;
+  ha_rows tot_rows= 0;
   DBUG_ENTER("ha_partition::estimate_rows_upper_bound");
 
-  file= m_file;
-  do
-  {
-    if (bitmap_is_set(&(m_part_info->used_partitions), (file - m_file)))
-    {
-      rows= (*file)->estimate_rows_upper_bound();
-      if (rows == HA_POS_ERROR)
-        DBUG_RETURN(HA_POS_ERROR);
-      tot_rows+= rows;
-    }
-  } while (*(++file));
+  if (get_optimizer_data(func_estimate_rows_upper_bound, (void*) &tot_rows))
+    DBUG_RETURN(HA_POS_ERROR);
   DBUG_RETURN(tot_rows);
 }
 
 
+/*
+  Get time to read
+
+  SYNOPSIS
+    read_time()
+    index                Index number used
+    ranges               Number of ranges
+    rows                 Number of rows
+
+  RETURN VALUE
+    time for read
+
+  DESCRIPTION
+    This will be optimised later to include whether or not the index can
+    be used with partitioning. To achieve we need to add another parameter
+    that specifies how many of the index fields that are bound in the ranges.
+    Possibly added as a new call to handlers.
+*/
+
+double ha_partition::read_time(uint index, uint ranges, ha_rows rows)
+{
+  DBUG_ENTER("ha_partition::read_time");
+
+  DBUG_RETURN(m_file[0]->read_time(index, ranges, rows));
+}
+
+
 /**
   Number of rows in table. see handler.h
 

=== modified file 'sql/ha_partition.h'
--- a/sql/ha_partition.h	2009-09-23 13:21:29 +0000
+++ b/sql/ha_partition.h	2009-11-07 23:10:48 +0000
@@ -547,6 +547,21 @@ public:
      -------------------------------------------------------------------------
   */
 
+private:
+  enum enum_partition_optimizer_func
+  {
+    func_scan_time,
+    func_records_in_range,
+    func_estimate_rows_upper_bound,
+    func_records
+  };
+  /*
+    Helper function since the partitioning code is very similar for every
+    the optimizer hints/cost calls
+  */
+  bool get_optimizer_data(enum_partition_optimizer_func func, void *data);
+public:
+
   /*
     keys_to_use_for_scanning can probably be implemented as the
     intersection of all underlying handlers if mixed handlers are used.


Attachment: [text/bzr-bundle]
Thread
bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3161)Bug#47261Mattias Jonsson8 Nov