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