List:Commits« Previous MessageNext Message »
From:Kristofer Pettersson Date:December 17 2009 12:22am
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-17
      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 a handlerton flag which can indicate if a 
      storage engine (InnoDB or MyISAM) needs to invalidate the
      table cache after an ANALYZE TABLE operation. Since InnoDB
      has its own way of keeping relevant statistics it wont carry
      this flag and thus avoid the blocking issue.
     @ mysql-test/r/analyze_innodb.result
        * Added test for bug 33278
     @ mysql-test/t/analyze_innodb.test
        * Added test for bug 33278
     @ sql/handler.h
        * Introduced new handlerton flag for a cache
          invalidation option after an ANALYZE TABLE
          operation.
     @ 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.
     @ storage/heap/ha_heap.cc
        * Added handleton flag HTON_FLUSH_AFTER_ANALYZE
     @ storage/myisam/ha_myisam.cc
        * Added handleton flag HTON_FLUSH_AFTER_ANALYZE

    added:
      mysql-test/r/analyze_innodb.result
      mysql-test/t/analyze_innodb.test
    modified:
      sql/handler.h
      sql/sql_class.cc
      sql/sql_parse.cc
      sql/sql_table.cc
      storage/heap/ha_heap.cc
      storage/myisam/ha_myisam.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-17 00:22:46 +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-17 00:22:46 +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/handler.h'
--- a/sql/handler.h	2009-10-12 12:46:00 +0000
+++ b/sql/handler.h	2009-12-17 00:22:46 +0000
@@ -726,6 +726,7 @@ struct handlerton
 #define HTON_TEMPORARY_NOT_SUPPORTED (1 << 6) //Having temporary tables not supported
 #define HTON_SUPPORT_LOG_TABLES      (1 << 7) //Engine supports log tables
 #define HTON_NO_PARTITION            (1 << 8) //You can not partition these tables
+#define HTON_FLUSH_AFTER_ANALYZE     (1 << 9) // ANALYZE TABLE require a table flush
 
 class Ha_trx_info;
 

=== 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-17 00:22:46 +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-17 00:22:46 +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-17 00:22:46 +0000
@@ -4964,10 +4964,21 @@ 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);
+          /*
+            Some storage engines (like MyISAM) might need an extra
+            cache flush to be able to aggregate the statistics from
+            an ANALYZE TABLE operation.
+          */
+          if (operator_func == &handler::ha_analyze &&
+              ha_check_storage_engine_flag(table->table->file->ht,
+                                           HTON_FLUSH_AFTER_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);

=== modified file 'storage/heap/ha_heap.cc'
--- a/storage/heap/ha_heap.cc	2009-07-08 12:11:34 +0000
+++ b/storage/heap/ha_heap.cc	2009-12-17 00:22:46 +0000
@@ -43,7 +43,7 @@ int heap_init(void *p)
   heap_hton->db_type=    DB_TYPE_HEAP;
   heap_hton->create=     heap_create_handler;
   heap_hton->panic=      heap_panic;
-  heap_hton->flags=      HTON_CAN_RECREATE;
+  heap_hton->flags=      HTON_CAN_RECREATE | HTON_FLUSH_AFTER_ANALYZE;
 
   return 0;
 }

=== modified file 'storage/myisam/ha_myisam.cc'
--- a/storage/myisam/ha_myisam.cc	2009-10-27 14:27:27 +0000
+++ b/storage/myisam/ha_myisam.cc	2009-12-17 00:22:46 +0000
@@ -2099,7 +2099,8 @@ static int myisam_init(void *p)
   myisam_hton->db_type= DB_TYPE_MYISAM;
   myisam_hton->create= myisam_create_handler;
   myisam_hton->panic= myisam_panic;
-  myisam_hton->flags= HTON_CAN_RECREATE | HTON_SUPPORT_LOG_TABLES;
+  myisam_hton->flags= HTON_CAN_RECREATE | HTON_SUPPORT_LOG_TABLES |
+                      HTON_FLUSH_AFTER_ANALYZE;
   return 0;
 }
 


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