#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#33278 | Kristofer Pettersson | 17 Dec |
