List:Commits« Previous MessageNext Message »
From:Konstantin Osipov Date:May 11 2010 8:22pm
Subject:bzr commit into mysql-5.1-bugteam branch (kostja:3379) Bug#33278
View as plain text  
#At file:///opt/local/work/2_main/5.1-bugteam/ based on revid:martin.hansson@stripped

 3379 Konstantin Osipov	2010-05-12
      Committing on behalf of Kristofer Pettersson:
      A fix and a test case for Bug#33278 ANALYZE TABLE in InnoDB 
      holds exclusive locks on subsequent execution,
      with review fixes.
      
      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_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_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	2010-05-11 20:22:10 +0000
@@ -0,0 +1,49 @@
+#
+# Bug#33278 ANALYZE TABLE in InnoDB holds exclusive locks
+# on subsequent execution
+#
+create table t1 (c1 int) engine=innodb;
+insert into t1 values (1),(2),(3);
+select get_lock("Bug#33278", 0);
+get_lock("Bug#33278", 0)
+1
+# --> switching to connection 'con1'
+select get_lock("Bug#33278", 10000) FROM t1;
+# --> switching to connection 'default'
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+# Subsequent 'analyze' should not block.
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+# --> switching to connection 'con2'
+# Should not block.
+select * from t1;
+c1
+1
+2
+3
+# --> switching to connection 'default'
+select release_lock("Bug#33278");
+release_lock("Bug#33278")
+1
+# --> switching to connection 'con1'
+# --> Reaping the SELECT.
+get_lock("Bug#33278", 10000)
+1
+1
+1
+# --> switching to connection 'default'
+#
+# Cleanup
+# 
+select * from t1;
+c1
+1
+2
+3
+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	2010-05-11 20:22:10 +0000
@@ -0,0 +1,55 @@
+--source include/have_innodb.inc
+
+--echo #
+--echo # Bug#33278 ANALYZE TABLE in InnoDB holds exclusive locks
+--echo # on subsequent execution
+--echo #
+
+
+create table t1 (c1 int) engine=innodb;
+insert into t1 values (1),(2),(3);
+select get_lock("Bug#33278", 0);
+
+connect (con1,localhost,root,,test,,);
+--echo # --> switching to connection 'con1'
+connection con1;
+send select get_lock("Bug#33278", 10000) FROM t1;
+
+--echo # --> switching to connection 'default'
+connection default;
+
+analyze table t1;
+--echo # Subsequent 'analyze' should not block.
+analyze table t1;
+analyze table t1;
+
+connect (con2,localhost,root,,test,,);
+
+--echo # --> switching to connection 'con2'
+connection con2;
+--echo # Should not block.
+select * from t1;
+disconnect con2;
+--source include/wait_until_disconnected.inc
+
+--echo # --> switching to connection 'default'
+connection default;
+select release_lock("Bug#33278");
+
+--echo # --> switching to connection 'con1'
+connection con1;
+--echo # --> Reaping the SELECT.
+reap;
+disconnect con1;
+--source include/wait_until_disconnected.inc
+
+--echo # --> switching to connection 'default'
+connection default;
+
+--echo #
+--echo # Cleanup
+--echo # 
+
+select * from t1;
+drop table t1;
+

=== modified file 'sql/handler.h'
--- a/sql/handler.h	2010-04-14 09:53:59 +0000
+++ b/sql/handler.h	2010-05-11 20:22:10 +0000
@@ -726,6 +726,23 @@ 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
+/*
+  The server must remove the TABLE_SHARE from the
+  table definition cache at the end of ANALYZE
+  statement.
+  
+  Used in the engines where table statistics is stored not
+  only in the data dictionary cache, but also on disk.
+  Examples are MyISAM and HEAP.
+  ANALYZE for these engines updates the disk statistics,
+  and its results only become available when the respective
+  table definition is re-read from disk.
+  Other engines, such as InnoDB, keep its statistics only
+  in memory. InnoDB data dictionary cache (see dict/dict0dict.c)
+  is never shrunk, and thus there is no issue of losing any results
+  of ANALYZE.
+*/
+#define HTON_FLUSH_AFTER_ANALYZE     (1 << 9)
 
 class Ha_trx_info;
 

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2010-05-04 14:03:28 +0000
+++ b/sql/sql_table.cc	2010-05-11 20:22:10 +0000
@@ -4995,10 +4995,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	2010-05-11 20:22:10 +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	2010-03-02 09:45:50 +0000
+++ b/storage/myisam/ha_myisam.cc	2010-05-11 20:22:10 +0000
@@ -2107,7 +2107,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] bzr/kostja@sun.com-20100511202210-qx96n0201b1i7izs.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (kostja:3379) Bug#33278Konstantin Osipov11 May