List:Commits« Previous MessageNext Message »
From:Alexey Kopytov Date:December 22 2006 11:52am
Subject:bk commit into 5.0 tree (igor:1.2312) BUG#21727
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of kaa. When kaa 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-12-22 14:52:20+03:00, igor@stripped +12 -0
  [PATCH] bk commit - 5.0 tree (igor:1.2307) BUG#21727
  
  ChangeSet@stripped, 2006-10-31 17:31:56-08:00, igor@stripped +12 -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-31 17:31:48-08:00, igor@stripped +16 -0
      Added a test case for bug #21727.
  
    mysql-test/t/subselect.test@stripped, 2006-10-31 17:31:48-08:00, igor@stripped +37 -0
      Added a test case for bug #21727.
  
    sql/filesort.cc@stripped, 2006-10-31 17:31:49-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-31 17:31:49-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-31 17:31:49-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-31 17:31:49-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-31 17:31:49-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-31 17:31:49-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-31 17:31:49-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_table.cc@stripped, 2006-10-31 17:31:49-08:00, igor@stripped +0 -3
      Fixed bug #21727.
      This is a performance issue for queries with subqueries evaluation
      of which requires filesort.
      Cleanup.
  
    sql/table.cc@stripped, 2006-10-31 17:31:49-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).
  
    sql/table.h@stripped, 2006-10-31 17:31:49-08:00, igor@stripped +5 -0
      Fixed bug #21727.
      This is a performance issue for queries with subqueries evaluation
      of which requires filesort.
      Added fields for sort_keys and buffpek buffers to the FILESORT_INFO structure.
  
  # 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

  mysql-test/r/subselect.result@stripped, 2006-11-01 04:32:03+03:00, igor@stripped +16 -0
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

  mysql-test/t/subselect.test@stripped, 2006-11-01 04:32:03+03:00, igor@stripped +37 -0
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

  sql/filesort.cc@stripped, 2006-11-01 04:32:02+03:00, igor@stripped +34 -7
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

  sql/item_subselect.h@stripped, 2006-11-01 04:32:03+03:00, igor@stripped +7 -0
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

  sql/mysql_priv.h@stripped, 2006-11-01 04:32:02+03:00, igor@stripped +1 -1
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

  sql/records.cc@stripped, 2006-11-01 04:32:02+03:00, igor@stripped +1 -1
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

  sql/sql_base.cc@stripped, 2006-11-01 04:32:02+03:00, igor@stripped +2 -0
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

  sql/sql_select.cc@stripped, 2006-11-01 04:32:02+03:00, igor@stripped +3 -3
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

  sql/sql_show.cc@stripped, 2006-11-01 04:32:02+03:00, igor@stripped +1 -1
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

  sql/sql_table.cc@stripped, 2006-11-01 04:32:03+03:00, igor@stripped +0 -3
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

  sql/table.cc@stripped, 2006-11-01 04:32:03+03:00, igor@stripped +17 -0
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

  sql/table.h@stripped, 2006-11-01 04:32:03+03:00, igor@stripped +5 -0
    bk commit - 5.0 tree (igor:1.2307) BUG#21727

# 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:	polly.local
# Root:	/home/kaa/src/maint/mysql-5.0.30-SP1

--- 1.107/sql/filesort.cc	2006-12-22 14:52:27 +03:00
+++ 1.108/sql/filesort.cc	2006-12-22 14:52:27 +03:00
@@ -109,6 +109,8 @@ ha_rows filesort(THD *thd, TABLE *table,
   DBUG_PUSH("");		/* No DBUG here */
 #endif
   FILESORT_INFO table_sort;
+  TABLE_LIST *tab= table->pos_in_table_list;
+  Item_subselect *subselect= tab ? tab->containing_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 @@ ha_rows filesort(THD *thd, TABLE *table,
   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 @@ ha_rows filesort(THD *thd, TABLE *table,
     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 @@ ha_rows filesort(THD *thd, TABLE *table,
   }
   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 @@ ha_rows filesort(THD *thd, TABLE *table,
  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 @@ ha_rows filesort(THD *thd, TABLE *table,
 } /* 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.421/sql/mysql_priv.h	2006-12-22 14:52:27 +03:00
+++ 1.422/sql/mysql_priv.h	2006-12-22 14:52:27 +03:00
@@ -1471,7 +1471,7 @@ void end_read_record(READ_RECORD *info);
 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-12-22 14:52:27 +03:00
+++ 1.43/sql/records.cc	2006-12-22 14:52:27 +03:00
@@ -193,7 +193,7 @@ void end_read_record(READ_RECORD *info)
   }
   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-12-22 14:52:27 +03:00
+++ 1.356/sql/sql_base.cc	2006-12-22 14:52:27 +03:00
@@ -1483,6 +1483,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
   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);
@@ -2767,6 +2768,7 @@ TABLE *open_temporary_table(THD *thd, co
     if (thd->slave_thread)
       slave_open_temp_tables++;
   }
+  tmp_table->pos_in_table_list= 0;
   DBUG_RETURN(tmp_table);
 }
 

--- 1.472/sql/sql_select.cc	2006-12-22 14:52:27 +03:00
+++ 1.473/sql/sql_select.cc	2006-12-22 14:52:27 +03:00
@@ -1273,14 +1273,14 @@ JOIN::reinit()
     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);
@@ -6094,7 +6094,7 @@ void JOIN::cleanup(bool full)
     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.331/sql/sql_show.cc	2006-12-22 14:52:27 +03:00
+++ 1.332/sql/sql_show.cc	2006-12-22 14:52:27 +03:00
@@ -3979,7 +3979,7 @@ bool get_schema_tables_result(JOIN *join
         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.325/sql/sql_table.cc	2006-12-22 14:52:27 +03:00
+++ 1.326/sql/sql_table.cc	2006-12-22 14:52:27 +03:00
@@ -2267,7 +2267,6 @@ static bool mysql_admin_table(THD* thd, 
       goto send_result;
     }
 
-    table->table->pos_in_table_list= table;
     if ((table->table->db_stat & HA_READ_ONLY) && open_for_modify)
     {
       char buff[FN_REFLEN + MYSQL_ERRMSG_SIZE];
@@ -4056,8 +4055,6 @@ bool mysql_checksum_table(THD *thd, TABL
     }
     else
     {
-      t->pos_in_table_list= table;
-
       if (t->file->table_flags() & HA_HAS_CHECKSUM &&
 	  !(check_opt->flags & T_EXTEND))
 	protocol->store((ulonglong)t->file->checksum());

--- 1.236/sql/table.cc	2006-12-22 14:52:27 +03:00
+++ 1.237/sql/table.cc	2006-12-22 14:52:27 +03:00
@@ -3032,6 +3032,23 @@ void st_table_list::reinit_before_use(TH
          embedding->nested_join->join_list.head() == embedded);
 }
 
+/*
+  Return subselect that contains the FROM list this table is taken from
+
+  SYNOPSIS
+    st_table_list::containing_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::containing_subselect()
+{    
+  return (select_lex ? select_lex->master_unit()->item : 0);
+}
 
 /*****************************************************************************
 ** Instansiate templates

--- 1.134/sql/table.h	2006-12-22 14:52:27 +03:00
+++ 1.135/sql/table.h	2006-12-22 14:52:27 +03:00
@@ -18,6 +18,7 @@
 /* Structs that defines the TABLE */
 
 class Item;				/* Needed by ORDER */
+class Item_subselect;
 class GRANT_TABLE;
 class st_select_lex_unit;
 class st_select_lex;
@@ -68,6 +69,9 @@ enum frm_type_enum
 typedef struct st_filesort_info
 {
   IO_CACHE *io_cache;           /* If sorted through filebyte                */
+  uchar   **sort_keys;          /* Buffer for sorting keys                   */
+  byte     *buffpek;            /* Buffer for buffpek structures             */
+  uint      buffpek_len;        /* Max number of buffpeks in the buffer      */
   byte     *addon_buf;          /* Pointer to a buffer if sorted with fields */
   uint      addon_length;       /* Length of the buffer                      */
   struct st_sort_addon_field *addon_field;     /* Pointer to the fields info */
@@ -678,6 +682,7 @@ typedef struct st_table_list
     procedure.
   */
   void reinit_before_use(THD *thd);
+  Item_subselect *containing_subselect();
 
 private:
   bool prep_check_option(THD *thd, uint8 check_opt_type);

--- 1.164/mysql-test/r/subselect.result	2006-12-22 14:52:27 +03:00
+++ 1.165/mysql-test/r/subselect.result	2006-12-22 14:52:27 +03:00
@@ -3544,3 +3544,19 @@ FROM t1 GROUP BY t1.a LIMIT 1)
 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.132/mysql-test/t/subselect.test	2006-12-22 14:52:27 +03:00
+++ 1.133/mysql-test/t/subselect.test	2006-12-22 14:52:27 +03:00
@@ -2426,3 +2426,40 @@ SELECT (
 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-12-22 14:52:27 +03:00
+++ 1.79/sql/item_subselect.h	2006-12-22 14:52:27 +03:00
@@ -114,6 +114,7 @@ public:
     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 @@ inline bool Item_subselect::is_evaluated
 {
   return engine->is_executed();
 }
+
+inline bool Item_subselect::is_uncacheable() const
+{
+  return engine->uncacheable();
+}
+
 
Thread
bk commit into 5.0 tree (igor:1.2312) BUG#21727Alexey Kopytov22 Dec