#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#47261 | Mattias Jonsson | 8 Nov |
