List:Commits« Previous MessageNext Message »
From:igor Date:October 31 2006 4:14am
Subject:bk commit into 5.0 tree (igor:1.2307) BUG#21727
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-10-30 20:14:43-08:00, igor@stripped +10 -0
  Fixed bug #21727.
  This is a performance issue for queries with subqueries evaluation
  of which requires filesort.
  Allocation of memory for the sort buffer at each evaluation of a
  subquery may take a significant amount of time if the buffer is rather big.
  With the fix we allocate the buffer at the first evaluation of the
  subquery and reuse it at each subsequent evaluation.

  mysql-test/r/subselect.result@stripped, 2006-10-30 20:14:38-08:00, igor@stripped +16 -0
    Added a test case for bug #21727.

  mysql-test/t/subselect.test@stripped, 2006-10-30 20:14:38-08:00, igor@stripped +37 -0
    Added a test case for bug #21727.

  sql/filesort.cc@stripped, 2006-10-30 20:14:38-08:00, igor@stripped +34 -7
    Fixed bug #21727.
    This is a performance issue for queries with subqueries evaluation
    of which requires filesort.
    Allocation of memory for the sort buffer at each evaluation of a
    subquery may take a significant amount of time if the buffer is rather big.
    With the fix we allocate the buffer at the first evaluation of the
    subquery and reuse it at each subsequent evaluation.
      

  sql/item_subselect.h@stripped, 2006-10-30 20:14:38-08:00, igor@stripped +7 -0
    Fixed bug #21727.
    This is a performance issue for queries with subqueries evaluation
    of which requires filesort.
    Added an implementation for Item_subselect::is_uncacheable()
    returning TRUE if the engine if the subselect is uncacheable.

  sql/mysql_priv.h@stripped, 2006-10-30 20:14:38-08:00, igor@stripped +1 -1
    Fixed bug #21727.
    This is a performance issue for queries with subqueries evaluation
    of which requires filesort.
    Added a new boolean parameter to the filesort_free_buffers procedure.
    If the value of this parameter is TRUE the procedure frees the sort_keys
    buffpek buffers. 

  sql/records.cc@stripped, 2006-10-30 20:14:38-08:00, igor@stripped +1 -1
    Fixed bug #21727.
    This is a performance issue for queries with subqueries evaluation
    of which requires filesort.
    Added a new boolean parameter to the filesort_free_buffers procedure.
    If the value of this parameter is TRUE the procedure frees the sort_keys
    buffpek buffers.

  sql/sql_base.cc@stripped, 2006-10-30 20:14:39-08:00, igor@stripped +2 -0
    Fixed bug #21727.
    Made sure that st_table::pos_in_table_list would be always initialized.

  sql/sql_select.cc@stripped, 2006-10-30 20:14:39-08:00, igor@stripped +3 -3
    Fixed bug #21727.
    This is a performance issue for queries with subqueries evaluation
    of which requires filesort.
    Added a new boolean parameter to the filesort_free_buffers procedure.
    If the value of this parameter is TRUE the procedure frees the sort_keys
    buffpek buffers. 

  sql/sql_show.cc@stripped, 2006-10-30 20:14:39-08:00, igor@stripped +1 -1
    Fixed bug #21727.
    This is a performance issue for queries with subqueries evaluation
    of which requires filesort.
    Added a new boolean parameter to the filesort_free_buffers procedure.
    If the value of this parameter is TRUE the procedure frees the sort_keys
    buffpek buffers. 

  sql/table.cc@stripped, 2006-10-30 20:14:39-08:00, igor@stripped +17 -0
    Fixed bug #21727.
    This is a performance issue for queries with subqueries evaluation
    of which requires filesort.
    Added st_table_list::in_subselect() returning for a table the subselect that 
    contains the FROM list this table is taken from (if there is any).

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug21727

--- 1.106/sql/filesort.cc	2006-10-30 20:14:52 -08:00
+++ 1.107/sql/filesort.cc	2006-10-30 20:14:52 -08:00
@@ -109,6 +109,8 @@
   DBUG_PUSH("");		/* No DBUG here */
 #endif
   FILESORT_INFO table_sort;
+  TABLE_LIST *tab= table->pos_in_table_list;
+  Item_subselect *subselect= tab ? tab->in_subselect() : 0;
   /* 
     Don't use table->sort in filesort as it is also used by 
     QUICK_INDEX_MERGE_SELECT. Work with a copy and put it back at the end 
@@ -121,7 +123,6 @@
   my_b_clear(&tempfile);
   my_b_clear(&buffpek_pointers);
   buffpek=0;
-  sort_keys= (uchar **) NULL;
   error= 1;
   bzero((char*) &param,sizeof(param));
   param.sort_length= sortlength(thd, sortorder, s_length, &multi_byte_charset);
@@ -202,13 +203,15 @@
     ulong old_memavl;
     ulong keys= memavl/(param.rec_length+sizeof(char*));
     param.keys=(uint) min(records+1, keys);
-    if ((sort_keys= (uchar **) make_char_array(param.keys, param.rec_length,
-					       MYF(0))))
+    if (table_sort.sort_keys ||
+        (table_sort.sort_keys= (uchar **) make_char_array(param.keys, param.rec_length,
+                                               MYF(0))))
       break;
     old_memavl=memavl;
     if ((memavl=memavl/4*3) < min_sort_memory && old_memavl > min_sort_memory)
       memavl= min_sort_memory;
   }
+  sort_keys= table_sort.sort_keys;
   if (memavl < min_sort_memory)
   {
     my_error(ER_OUTOFMEMORY,MYF(ME_ERROR+ME_WAITTANG),
@@ -235,8 +238,12 @@
   }
   else
   {
-    if (!(buffpek=read_buffpek_from_file(&buffpek_pointers, maxbuffer)))
+    if (!table_sort.buffpek && table_sort.buffpek_len < maxbuffer &&
+        !(table_sort.buffpek=
+          (byte *) read_buffpek_from_file(&buffpek_pointers, maxbuffer)))
       goto err;
+    buffpek= (BUFFPEK *) table_sort.buffpek;
+    table_sort.buffpek_len= maxbuffer;
     close_cached_file(&buffpek_pointers);
 	/* Open cached file if it isn't open */
     if (! my_b_inited(outfile) &&
@@ -269,8 +276,14 @@
  err:
   if (param.tmp_buffer)
     x_free(param.tmp_buffer);
-  x_free((gptr) sort_keys);
-  x_free((gptr) buffpek);
+  if (!subselect || !subselect->is_uncacheable())
+  {
+    x_free((gptr) sort_keys);
+    table_sort.sort_keys= 0;
+    x_free((gptr) buffpek);
+    table_sort.buffpek= 0;
+    table_sort.buffpek_len= 0;
+  }
   close_cached_file(&tempfile);
   close_cached_file(&buffpek_pointers);
   if (my_b_inited(outfile))
@@ -301,12 +314,26 @@
 } /* filesort */
 
 
-void filesort_free_buffers(TABLE *table)
+void filesort_free_buffers(TABLE *table, bool full)
 {
   if (table->sort.record_pointers)
   {
     my_free((gptr) table->sort.record_pointers,MYF(0));
     table->sort.record_pointers=0;
+  }
+  if (full)
+  {
+    if (table->sort.sort_keys )
+    {
+      x_free((gptr) table->sort.sort_keys);
+      table->sort.sort_keys= 0;
+    }
+    if (table->sort.buffpek)
+    {
+      x_free((gptr) table->sort.buffpek);
+      table->sort.buffpek= 0;
+      table->sort.buffpek_len= 0;
+    }
   }
   if (table->sort.addon_buf)
   {

--- 1.414/sql/mysql_priv.h	2006-10-30 20:14:52 -08:00
+++ 1.415/sql/mysql_priv.h	2006-10-30 20:14:52 -08:00
@@ -1465,7 +1465,7 @@
 ha_rows filesort(THD *thd, TABLE *form,struct st_sort_field *sortorder,
 		 uint s_length, SQL_SELECT *select,
 		 ha_rows max_rows, ha_rows *examined_rows);
-void filesort_free_buffers(TABLE *table);
+void filesort_free_buffers(TABLE *table, bool full);
 void change_double_for_sort(double nr,byte *to);
 double my_double_round(double value, int dec, bool truncate);
 int get_quick_record(SQL_SELECT *select);

--- 1.42/sql/records.cc	2006-10-30 20:14:52 -08:00
+++ 1.43/sql/records.cc	2006-10-30 20:14:52 -08:00
@@ -193,7 +193,7 @@
   }
   if (info->table)
   {
-    filesort_free_buffers(info->table);
+    filesort_free_buffers(info->table,0);
     (void) info->file->extra(HA_EXTRA_NO_CACHE);
     if (info->read_record != rr_quick) // otherwise quick_range does it
       (void) info->file->ha_index_or_rnd_end();

--- 1.355/sql/sql_base.cc	2006-10-30 20:14:52 -08:00
+++ 1.356/sql/sql_base.cc	2006-10-30 20:14:52 -08:00
@@ -1478,6 +1478,7 @@
   table->file->ft_handler= 0;
   if (table->timestamp_field)
     table->timestamp_field_type= table->timestamp_field->get_auto_set_type();
+  table->pos_in_table_list= table_list;
   table_list->updatable= 1; // It is not derived table nor non-updatable VIEW
   DBUG_ASSERT(table->key_read == 0);
   DBUG_RETURN(table);
@@ -2762,6 +2763,7 @@
     if (thd->slave_thread)
       slave_open_temp_tables++;
   }
+  tmp_table->pos_in_table_list= 0;
   DBUG_RETURN(tmp_table);
 }
 

--- 1.463/sql/sql_select.cc	2006-10-30 20:14:52 -08:00
+++ 1.464/sql/sql_select.cc	2006-10-30 20:14:52 -08:00
@@ -1260,14 +1260,14 @@
     exec_tmp_table1->file->extra(HA_EXTRA_RESET_STATE);
     exec_tmp_table1->file->delete_all_rows();
     free_io_cache(exec_tmp_table1);
-    filesort_free_buffers(exec_tmp_table1);
+    filesort_free_buffers(exec_tmp_table1,0);
   }
   if (exec_tmp_table2)
   {
     exec_tmp_table2->file->extra(HA_EXTRA_RESET_STATE);
     exec_tmp_table2->file->delete_all_rows();
     free_io_cache(exec_tmp_table2);
-    filesort_free_buffers(exec_tmp_table2);
+    filesort_free_buffers(exec_tmp_table2,0);
   }
   if (items0)
     set_items_ref_array(items0);
@@ -6066,7 +6066,7 @@
     if (tables > const_tables) // Test for not-const tables
     {
       free_io_cache(table[const_tables]);
-      filesort_free_buffers(table[const_tables]);
+      filesort_free_buffers(table[const_tables],full);
     }
 
     if (full)

--- 1.328/sql/sql_show.cc	2006-10-30 20:14:52 -08:00
+++ 1.329/sql/sql_show.cc	2006-10-30 20:14:52 -08:00
@@ -3977,7 +3977,7 @@
         table_list->table->file->extra(HA_EXTRA_RESET_STATE);
         table_list->table->file->delete_all_rows();
         free_io_cache(table_list->table);
-        filesort_free_buffers(table_list->table);
+        filesort_free_buffers(table_list->table,1);
         table_list->table->null_row= 0;
       }
       else

--- 1.234/sql/table.cc	2006-10-30 20:14:52 -08:00
+++ 1.235/sql/table.cc	2006-10-30 20:14:52 -08:00
@@ -3018,6 +3018,23 @@
          embedding->nested_join->join_list.head() == embedded);
 }
 
+/*
+  Return subselect that contains the FROM list this table is taken from
+
+  SYNOPSIS
+    st_table_list::in_subselect()
+ 
+  RETURN
+    Subselect item for the subquery that contains the FROM list
+    this table is taken from if there is any
+    0 - otherwise
+
+*/
+
+Item_subselect *st_table_list::in_subselect()
+{    
+  return (select_lex ? select_lex->master_unit()->item : 0);
+}
 
 /*****************************************************************************
 ** Instansiate templates

--- 1.163/mysql-test/r/subselect.result	2006-10-30 20:14:52 -08:00
+++ 1.164/mysql-test/r/subselect.result	2006-10-30 20:14:52 -08:00
@@ -3545,3 +3545,19 @@
 2
 2
 DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
+CREATE TABLE t2 (x int auto_increment, y int, z int,
+PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
+SET SESSION sort_buffer_size = 32 * 1024;
+SELECT SQL_NO_CACHE COUNT(*) 
+FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+FROM t1) t;
+COUNT(*)
+3000
+SET SESSION sort_buffer_size = 8 * 1024 * 1024;
+SELECT SQL_NO_CACHE COUNT(*) 
+FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+FROM t1) t;
+COUNT(*)
+3000
+DROP TABLE t1,t2;

--- 1.130/mysql-test/t/subselect.test	2006-10-30 20:14:52 -08:00
+++ 1.131/mysql-test/t/subselect.test	2006-10-30 20:14:52 -08:00
@@ -2426,3 +2426,40 @@
 FROM t1 t2
 GROUP BY t2.a;
 DROP TABLE t1,t2;  
+
+#
+# Bug #21727: Correlated subquery that requires filesort:
+#             slow with big sort_buffer_size 
+#
+
+CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
+CREATE TABLE t2 (x int auto_increment, y int, z int,
+                 PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
+
+disable_query_log;
+let $1=3000;
+while ($1)
+{
+  eval INSERT INTO t1(a) VALUES(RAND()*1000);
+  eval SELECT MAX(b) FROM t1 INTO @id;
+  let $2=10;
+  while ($2)
+  {
+    eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000);
+    dec $2;
+  } 
+  dec $1;
+}
+enable_query_log;
+
+SET SESSION sort_buffer_size = 32 * 1024;
+SELECT SQL_NO_CACHE COUNT(*) 
+  FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+          FROM t1) t;
+
+SET SESSION sort_buffer_size = 8 * 1024 * 1024;
+SELECT SQL_NO_CACHE COUNT(*) 
+  FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+          FROM t1) t;
+
+DROP TABLE t1,t2;

--- 1.78/sql/item_subselect.h	2006-10-30 20:14:52 -08:00
+++ 1.79/sql/item_subselect.h	2006-10-30 20:14:52 -08:00
@@ -114,6 +114,7 @@
     single select and union subqueries only.
   */
   bool is_evaluated() const;
+  bool is_uncacheable() const;
 
   /*
     Used by max/min subquery to initialize value presence registration
@@ -427,4 +428,10 @@
 {
   return engine->is_executed();
 }
+
+inline bool Item_subselect::is_uncacheable() const
+{
+  return engine->uncacheable();
+}
+
 
Thread
bk commit into 5.0 tree (igor:1.2307) BUG#21727igor31 Oct