List:Commits« Previous MessageNext Message »
From:Kristofer Pettersson Date:December 15 2009 4:25pm
Subject:bzr commit into mysql-5.1-bugteam branch (kristofer.pettersson:3262)
Bug#33278
View as plain text  
#At file:///Users/thek/Development/51-bug33278/ based on revid:v.narayanan@stripped

 3262 Kristofer Pettersson	2009-12-15
      Bug#33278 ANALYZE TABLE in InnoDB holds exclusive locks on subsequent execution
      
      ANALYZE TABLE gathers table statistics for the optimizer. In
      order to aggregate the results the statement has to wait for
      the table to be flushed. This causes multiple calls to be
      queued and the effect is the same as if an exclusive lock was
      held by the ANALYZE TABLE operation.
      
      This patch adds an exception for the InnoDB storage engine
      since this storage engine only provide a rough estimate
      through ANALYZE TABLE anyway and doesn't benefit from
      the synchronization mechanism the same way as MyISAM does.
     @ mysql-test/r/analyze_innodb.result
        * Added test for bug 33278
     @ mysql-test/t/analyze_innodb.test
        * Added test for bug 33278
     @ sql/sql_class.cc
        * Add synchronization point
     @ sql/sql_parse.cc
        * Add synchronization point
     @ sql/sql_table.cc
        * Don't remove the table from the cache during
          a ANALYZE TABLE operation if the db_type is
          DB_TYPE_INNODB.

    added:
      mysql-test/r/analyze_innodb.result
      mysql-test/t/analyze_innodb.test
    modified:
      sql/sql_class.cc
      sql/sql_parse.cc
      sql/sql_table.cc
=== added file 'mysql-test/r/analyze_innodb.result'
--- a/mysql-test/r/analyze_innodb.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/analyze_innodb.result	2009-12-15 16:25:19 +0000
@@ -0,0 +1,31 @@
+*
+* Bug#33278 ANALYZE TABLE in InnoDB holds exclusive locks on subsequent execution
+*
+** Connection con1: Create a table and populate it.
+CREATE TABLE t1 (c1 INT) engine=innodb;
+INSERT INTO t1 VALUES (1),(2),(3);
+** Insert debug hook
+SET DEBUG_SYNC='select_send::send_data SIGNAL go_analyze WAIT_FOR go_select';
+SELECT count(*) FROM t1;;
+** Connection default 
+** Shouldn't block
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+** Shouldn't block
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+** Shouldn't block
+SELECT * FROM t1;
+c1
+1
+2
+3
+*' Shouldn't block
+SET DEBUG_SYNC='SQLCOM_ANALYZE SIGNAL go_select';
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+SET DEBUG_SYNC='RESET';
+DROP TABLE t1;

=== added file 'mysql-test/t/analyze_innodb.test'
--- a/mysql-test/t/analyze_innodb.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/analyze_innodb.test	2009-12-15 16:25:19 +0000
@@ -0,0 +1,35 @@
+--source include/have_innodb.inc
+
+--echo *
+--echo * Bug#33278 ANALYZE TABLE in InnoDB holds exclusive locks on subsequent execution
+--echo *
+
+connect (con1,localhost,root,,test,,);
+
+--echo ** Connection con1: Create a table and populate it.
+connection con1;
+CREATE TABLE t1 (c1 INT) engine=innodb;
+INSERT INTO t1 VALUES (1),(2),(3);
+
+--echo ** Insert debug hook
+SET DEBUG_SYNC='select_send::send_data SIGNAL go_analyze WAIT_FOR go_select';
+--send SELECT count(*) FROM t1;
+
+--echo ** Connection default 
+connection default;
+--echo ** Shouldn't block
+ANALYZE TABLE t1;
+
+--echo ** Shouldn't block
+ANALYZE TABLE t1;
+
+--echo ** Shouldn't block
+SELECT * FROM t1;
+
+--echo *' Shouldn't block
+SET DEBUG_SYNC='SQLCOM_ANALYZE SIGNAL go_select';
+ANALYZE TABLE t1;
+
+SET DEBUG_SYNC='RESET';
+DROP TABLE t1;
+disconnect con1;

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2009-12-01 10:38:40 +0000
+++ b/sql/sql_class.cc	2009-12-15 16:25:19 +0000
@@ -1641,7 +1641,7 @@ bool select_send::send_data(List<Item> &
   char buff[MAX_FIELD_WIDTH];
   String buffer(buff, sizeof(buff), &my_charset_bin);
   DBUG_ENTER("select_send::send_data");
-
+  DEBUG_SYNC(thd,"select_send::send_data");
   protocol->prepare_for_resend();
   Item *item;
   while ((item=li++))

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2009-11-24 18:22:22 +0000
+++ b/sql/sql_parse.cc	2009-12-15 16:25:19 +0000
@@ -15,6 +15,7 @@
 
 #define MYSQL_LEX 1
 #include "mysql_priv.h"
+#include "debug_sync.h"
 #include "sql_repl.h"
 #include "rpl_filter.h"
 #include "repl_failsafe.h"
@@ -3003,6 +3004,7 @@ end_with_restore_list:
   }
   case SQLCOM_ANALYZE:
   {
+    DEBUG_SYNC(thd,"SQLCOM_ANALYZE");
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
     if (check_table_access(thd, SELECT_ACL | INSERT_ACL, all_tables,
                            UINT_MAX, FALSE))

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2009-11-27 13:34:39 +0000
+++ b/sql/sql_table.cc	2009-12-15 16:25:19 +0000
@@ -4964,10 +4964,23 @@ send_result_message:
           table->table->file->info(HA_STATUS_CONST);
         else
         {
-          pthread_mutex_lock(&LOCK_open);
-          remove_table_from_cache(thd, table->table->s->db.str,
-                                  table->table->s->table_name.str, RTFC_NO_FLAG);
-          pthread_mutex_unlock(&LOCK_open);
+          /*
+            Subsequent execution of ANALYZE TABLE will block waiting on the
+            table to leave the table cache. This should be avoided for
+            InnoDB where the aggregated statistic isn't very accurate anyway.
+            Without a call to remove_table_from_cache() there is a risk that the
+            result of an ANALYZE TABLE won't be used by the optimizer for a very
+            long time.
+          */
+          if (!(table->table->file->ht->db_type == DB_TYPE_INNODB &&
+                 operator_func == &handler::ha_analyze))
+          {
+            pthread_mutex_lock(&LOCK_open);
+            remove_table_from_cache(thd, table->table->s->db.str,
+                                    table->table->s->table_name.str,
+                                    RTFC_NO_FLAG);
+            pthread_mutex_unlock(&LOCK_open);
+          }
         }
         /* May be something modified consequently we have to invalidate cache */
         query_cache_invalidate3(thd, table->table, 0);


Attachment: [text/bzr-bundle]
Thread
bzr commit into mysql-5.1-bugteam branch (kristofer.pettersson:3262)Bug#33278Kristofer Pettersson15 Dec