3730 Alexander Nozdrin 2011-03-09
Cherry-picking merge from mysql-trunk-innodb.
Original revision:
######################################################
revno: 3528
revision-id: sunny.bains@stripped
parent: vasil.dimov@stripped
committer: Sunny Bains <Sunny.Bains@stripped>
branch nick: trunk
timestamp: Thu 2011-03-03 10:09:09 +1100
message:
Bug# 11765850 - 58854: HIGH CONCURRENCY SYSTEM TEST SHOWS SIGNIFICANT PERFORMANCE DROP
The bug is that the InnoDB pre-fetch cache was not being used in
row_search_for_mysql(). Secondly the changeset that planted the
bug also introduced some inefficient code. It would read an extra
row, convert it to MySQL row format (for ICP==off), copy the row
to the pre-fetch cache row buffer, then check for cache overflow
and dequeue the row that was pushed if there was a possibility of
a cache overflow.
No rb entry, approved via email by Marko.
######################################################
modified:
storage/innobase/row/row0sel.c
3729 Alexander Nozdrin 2011-03-09
Cherry-picking merge from mysql-5.5.10-release.
Original revision:
###############################################
revno: 3167
revision-id: jon.hauglid@stripped
parent: kent.boortz@stripped
committer: Jon Olav Hauglid <jon.hauglid@stripped>
branch nick: mysql-5.5.10-release
timestamp: Wed 2011-03-09 16:06:13 +0100
message:
Bug#11815600 [ERROR] INNODB COULD NOT FIND INDEX PRIMARY
KEY NO 0 FOR TABLE IN ERROR LOG
With the changes made by the patches for Bug#11751388 and
Bug#11784056, concurrent reads are allowed while secondary
indexes are created in InnoDB. This means that the metadata
lock on the affected table is not upgraded to exclusive
until the .FRM is updated at the end of ALTER TABLE processing.
The problem was that if this lock upgrade failed for some
reason (e.g. timeout), the index information in the server
and inside InnoDB would be out of sync. This would happen
since the add index operation already was committed inside
InnoDB but the table metadata inside the server had not been
updated yet.
This patch fixes the problem by (for now) reverting the
effects of the patches for Bug#11751388 and Bug#11784056.
Concurrent reads will now again be blocked during creation
of secondary indexes in InnoDB.
Test case added to innodb_mysql_lock.test.
###############################################
modified:
mysql-test/r/innodb_mysql_lock.result
mysql-test/r/innodb_mysql_sync.result
mysql-test/t/innodb_mysql_lock.test
mysql-test/t/innodb_mysql_sync.test
storage/innobase/handler/ha_innodb.cc
3728 Alexander Barkov 2011-03-04 {clone-5.6.2-m5-build} [merge]
Merging from mysql-5.5
modified:
mysql-test/r/ctype_latin1.result
mysql-test/r/ctype_utf8.result
mysql-test/t/ctype_latin1.test
mysql-test/t/ctype_utf8.test
sql/item.cc
sql/mysqld.h
sql/sql_parse.cc
sql/sql_view.cc
=== modified file 'mysql-test/r/innodb_mysql_lock.result'
--- a/mysql-test/r/innodb_mysql_lock.result 2010-06-26 20:23:28 +0000
+++ b/mysql-test/r/innodb_mysql_lock.result 2011-03-09 18:31:30 +0000
@@ -148,3 +148,25 @@ COMMIT;
# Connection default
DROP TABLE t1, t2;
DROP VIEW v1;
+#
+# Bug#11815600 [ERROR] INNODB COULD NOT FIND INDEX PRIMARY
+# KEY NO 0 FOR TABLE IN ERROR LOG
+#
+DROP TABLE IF EXISTS t1;
+# Connection default
+CREATE TABLE t1 (id INT PRIMARY KEY, value INT) ENGINE = InnoDB;
+INSERT INTO t1 VALUES (1, 12345);
+START TRANSACTION;
+SELECT * FROM t1;
+id value
+1 12345
+# Connection con1
+SET lock_wait_timeout=1;
+ALTER TABLE t1 ADD INDEX idx(value);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+# Connection default
+SELECT * FROM t1;
+id value
+1 12345
+COMMIT;
+DROP TABLE t1;
=== modified file 'mysql-test/r/innodb_mysql_sync.result'
--- a/mysql-test/r/innodb_mysql_sync.result 2011-01-26 13:23:29 +0000
+++ b/mysql-test/r/innodb_mysql_sync.result 2011-03-09 18:31:30 +0000
@@ -94,64 +94,6 @@ SET DEBUG_SYNC= 'RESET';
# Bug#42230 during add index, cannot do queries on storage engines
# that implement add_index
#
-DROP DATABASE IF EXISTS db1;
-DROP TABLE IF EXISTS t1;
-# Test 1: Secondary index, should not block reads (original test case).
-# Connection default
-CREATE DATABASE db1;
-CREATE TABLE db1.t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT) engine=innodb;
-INSERT INTO db1.t1(value) VALUES (1), (2);
-SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query";
-# Sending:
-ALTER TABLE db1.t1 ADD INDEX(value);
-# Connection con1
-SET DEBUG_SYNC= "now WAIT_FOR manage";
-USE db1;
-SELECT * FROM t1;
-id value
-1 1
-2 2
-SET DEBUG_SYNC= "now SIGNAL query";
-# Connection default
-# Reaping: ALTER TABLE db1.t1 ADD INDEX(value)
-DROP DATABASE db1;
-# Test 2: Primary index (implicit), should block reads.
-CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb;
-SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query";
-# Sending:
-ALTER TABLE t1 ADD UNIQUE INDEX(a);
-# Connection con1
-SET DEBUG_SYNC= "now WAIT_FOR manage";
-USE test;
-# Sending:
-SELECT * FROM t1;
-# Connection con2
-# Waiting for SELECT to be blocked by the metadata lock on t1
-SET DEBUG_SYNC= "now SIGNAL query";
-# Connection default
-# Reaping: ALTER TABLE t1 ADD UNIQUE INDEX(a)
-# Connection con1
-# Reaping: SELECT * FROM t1
-a b
-# Test 3: Primary index (explicit), should block reads.
-# Connection default
-ALTER TABLE t1 DROP INDEX a;
-SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query";
-# Sending:
-ALTER TABLE t1 ADD PRIMARY KEY (a);
-# Connection con1
-SET DEBUG_SYNC= "now WAIT_FOR manage";
-# Sending:
-SELECT * FROM t1;
-# Connection con2
-# Waiting for SELECT to be blocked by the metadata lock on t1
-SET DEBUG_SYNC= "now SIGNAL query";
-# Connection default
-# Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a)
-# Connection con1
-# Reaping: SELECT * FROM t1
-a b
-# Test 4: Secondary unique index, should not block reads.
-# Connection default
-SET DEBUG_SYNC= "RESET";
-DROP TABLE t1;
+#
+# DISABLED due to Bug#11815600
+#
=== modified file 'mysql-test/t/innodb_mysql_lock.test'
--- a/mysql-test/t/innodb_mysql_lock.test 2010-08-06 11:29:37 +0000
+++ b/mysql-test/t/innodb_mysql_lock.test 2011-03-09 18:31:30 +0000
@@ -279,6 +279,38 @@ disconnect con2;
disconnect con3;
+--echo #
+--echo # Bug#11815600 [ERROR] INNODB COULD NOT FIND INDEX PRIMARY
+--echo # KEY NO 0 FOR TABLE IN ERROR LOG
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+--connect (con1,localhost,root)
+
+--echo # Connection default
+connection default;
+CREATE TABLE t1 (id INT PRIMARY KEY, value INT) ENGINE = InnoDB;
+INSERT INTO t1 VALUES (1, 12345);
+START TRANSACTION;
+SELECT * FROM t1;
+
+--echo # Connection con1
+--connection con1
+SET lock_wait_timeout=1;
+--error ER_LOCK_WAIT_TIMEOUT
+ALTER TABLE t1 ADD INDEX idx(value);
+
+--echo # Connection default
+--connection default
+SELECT * FROM t1;
+COMMIT;
+DROP TABLE t1;
+disconnect con1;
+
+
# Check that all connections opened by test cases in this file are really
# gone so execution of other tests won't be affected by their presence.
--source include/wait_until_count_sessions.inc
=== modified file 'mysql-test/t/innodb_mysql_sync.test'
--- a/mysql-test/t/innodb_mysql_sync.test 2011-01-26 13:23:29 +0000
+++ b/mysql-test/t/innodb_mysql_sync.test 2011-03-09 18:31:30 +0000
@@ -152,132 +152,133 @@ disconnect con1;
--echo # that implement add_index
--echo #
---disable_warnings
-DROP DATABASE IF EXISTS db1;
-DROP TABLE IF EXISTS t1;
---enable_warnings
-
-connect(con1,localhost,root);
-connect(con2,localhost,root);
-
---echo # Test 1: Secondary index, should not block reads (original test case).
-
---echo # Connection default
-connection default;
-CREATE DATABASE db1;
-CREATE TABLE db1.t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT) engine=innodb;
-INSERT INTO db1.t1(value) VALUES (1), (2);
-SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query";
---echo # Sending:
---send ALTER TABLE db1.t1 ADD INDEX(value)
-
---echo # Connection con1
-connection con1;
-SET DEBUG_SYNC= "now WAIT_FOR manage";
-# Neither of these two statements should be blocked
-USE db1;
-SELECT * FROM t1;
-SET DEBUG_SYNC= "now SIGNAL query";
-
---echo # Connection default
-connection default;
---echo # Reaping: ALTER TABLE db1.t1 ADD INDEX(value)
---reap
-DROP DATABASE db1;
-
---echo # Test 2: Primary index (implicit), should block reads.
-
-CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb;
-SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query";
---echo # Sending:
---send ALTER TABLE t1 ADD UNIQUE INDEX(a)
-
---echo # Connection con1
-connection con1;
-SET DEBUG_SYNC= "now WAIT_FOR manage";
-USE test;
---echo # Sending:
---send SELECT * FROM t1
-
---echo # Connection con2
-connection con2;
---echo # Waiting for SELECT to be blocked by the metadata lock on t1
-let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
- WHERE state= 'Waiting for table metadata lock'
- AND info='SELECT * FROM t1';
---source include/wait_condition.inc
-SET DEBUG_SYNC= "now SIGNAL query";
-
---echo # Connection default
-connection default;
---echo # Reaping: ALTER TABLE t1 ADD UNIQUE INDEX(a)
---reap
-
---echo # Connection con1
-connection con1;
---echo # Reaping: SELECT * FROM t1
---reap
-
---echo # Test 3: Primary index (explicit), should block reads.
-
---echo # Connection default
-connection default;
-ALTER TABLE t1 DROP INDEX a;
-SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query";
---echo # Sending:
---send ALTER TABLE t1 ADD PRIMARY KEY (a)
-
---echo # Connection con1
-connection con1;
-SET DEBUG_SYNC= "now WAIT_FOR manage";
---echo # Sending:
---send SELECT * FROM t1
-
---echo # Connection con2
-connection con2;
---echo # Waiting for SELECT to be blocked by the metadata lock on t1
-let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
- WHERE state= 'Waiting for table metadata lock'
- AND info='SELECT * FROM t1';
---source include/wait_condition.inc
-SET DEBUG_SYNC= "now SIGNAL query";
-
---echo # Connection default
-connection default;
---echo # Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a)
---reap
-
---echo # Connection con1
-connection con1;
---echo # Reaping: SELECT * FROM t1
---reap
-
---echo # Test 4: Secondary unique index, should not block reads.
-# This requires HA_INPLACE_ADD_UNIQUE_INDEX_NO_WRITE to be supported
-# by InnoDB. Adding this flag currently introduces a regression so
-# this test is disabled until the regression has been fixed.
+--echo #
+--echo # DISABLED due to Bug#11815600
+--echo #
---echo # Connection default
-connection default;
+#--disable_warnings
+#DROP DATABASE IF EXISTS db1;
+#DROP TABLE IF EXISTS t1;
+#--enable_warnings
+#
+#connect(con1,localhost,root);
+#connect(con2,localhost,root);
+#
+#--echo # Test 1: Secondary index, should not block reads (original test case).
+#
+#--echo # Connection default
+#connection default;
+#CREATE DATABASE db1;
+#CREATE TABLE db1.t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT) engine=innodb;
+#INSERT INTO db1.t1(value) VALUES (1), (2);
+#SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query";
+#--echo # Sending:
+#--send ALTER TABLE db1.t1 ADD INDEX(value)
+#
+#--echo # Connection con1
+#connection con1;
+#SET DEBUG_SYNC= "now WAIT_FOR manage";
+# # Neither of these two statements should be blocked
+#USE db1;
+#SELECT * FROM t1;
+#SET DEBUG_SYNC= "now SIGNAL query";
+#
+#--echo # Connection default
+#connection default;
+#--echo # Reaping: ALTER TABLE db1.t1 ADD INDEX(value)
+#--reap
+#DROP DATABASE db1;
+#
+#--echo # Test 2: Primary index (implicit), should block reads.
+#
+#CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb;
+#SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query";
+#--echo # Sending:
+#--send ALTER TABLE t1 ADD UNIQUE INDEX(a)
+#
+#--echo # Connection con1
+#connection con1;
+#SET DEBUG_SYNC= "now WAIT_FOR manage";
+#USE test;
+#--echo # Sending:
+#--send SELECT * FROM t1
+#
+#--echo # Connection con2
+#connection con2;
+#--echo # Waiting for SELECT to be blocked by the metadata lock on t1
+#let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
+# WHERE state= 'Waiting for table metadata lock'
+# AND info='SELECT * FROM t1';
+#--source include/wait_condition.inc
+#SET DEBUG_SYNC= "now SIGNAL query";
+#
+#--echo # Connection default
+#connection default;
+#--echo # Reaping: ALTER TABLE t1 ADD UNIQUE INDEX(a)
+#--reap
+#
+#--echo # Connection con1
+#connection con1;
+#--echo # Reaping: SELECT * FROM t1
+#--reap
+#
+#--echo # Test 3: Primary index (explicit), should block reads.
+#
+#--echo # Connection default
+#connection default;
+#ALTER TABLE t1 DROP INDEX a;
+#SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query";
+#--echo # Sending:
+#--send ALTER TABLE t1 ADD PRIMARY KEY (a)
+#
+#--echo # Connection con1
+#connection con1;
+#SET DEBUG_SYNC= "now WAIT_FOR manage";
+#--echo # Sending:
+#--send SELECT * FROM t1
+#
+#--echo # Connection con2
+#connection con2;
+#--echo # Waiting for SELECT to be blocked by the metadata lock on t1
+#let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
+# WHERE state= 'Waiting for table metadata lock'
+# AND info='SELECT * FROM t1';
+#--source include/wait_condition.inc
+#SET DEBUG_SYNC= "now SIGNAL query";
+#
+#--echo # Connection default
+#connection default;
+#--echo # Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a)
+#--reap
+#
+#--echo # Connection con1
+#connection con1;
+#--echo # Reaping: SELECT * FROM t1
+#--reap
+#
+#--echo # Test 4: Secondary unique index, should not block reads.
+#
+#--echo # Connection default
+#connection default;
#SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query";
#--echo # Sending:
#--send ALTER TABLE t1 ADD UNIQUE (b)
-
+#
#--echo # Connection con1
#connection con1;
#SET DEBUG_SYNC= "now WAIT_FOR manage";
#SELECT * FROM t1;
#SET DEBUG_SYNC= "now SIGNAL query";
-
+#
#--echo # Connection default
#connection default;
#--echo # Reaping: ALTER TABLE t1 ADD UNIQUE (b)
#--reap
-
-disconnect con1;
-disconnect con2;
-SET DEBUG_SYNC= "RESET";
-DROP TABLE t1;
+#
+#disconnect con1;
+#disconnect con2;
+#SET DEBUG_SYNC= "RESET";
+#DROP TABLE t1;
# Check that all connections opened by test cases in this file are really
=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc 2011-03-01 14:47:01 +0000
+++ b/storage/innobase/handler/ha_innodb.cc 2011-03-09 18:31:30 +0000
@@ -2827,7 +2827,6 @@ innobase_alter_table_flags(
uint flags)
{
return(HA_INPLACE_ADD_INDEX_NO_READ_WRITE
- | HA_INPLACE_ADD_INDEX_NO_WRITE
| HA_INPLACE_DROP_INDEX_NO_READ_WRITE
| HA_INPLACE_ADD_UNIQUE_INDEX_NO_READ_WRITE
| HA_INPLACE_DROP_UNIQUE_INDEX_NO_READ_WRITE
=== modified file 'storage/innobase/row/row0sel.c'
--- a/storage/innobase/row/row0sel.c 2011-02-07 11:23:17 +0000
+++ b/storage/innobase/row/row0sel.c 2011-03-09 19:16:29 +0000
@@ -3276,16 +3276,15 @@ row_sel_pop_cached_row_for_mysql(
}
/********************************************************************//**
-Pushes a row for MySQL to the fetch cache.
-@return TRUE on success, FALSE if the record contains incomplete BLOBs */
-UNIV_INLINE __attribute__((warn_unused_result))
-ibool
+Pushes a row for MySQL to the fetch cache. */
+UNIV_INLINE
+void
row_sel_push_cache_row_for_mysql(
/*=============================*/
byte* mysql_rec, /*!< in/out: MySQL record */
row_prebuilt_t* prebuilt) /*!< in/out: prebuilt struct */
{
- ut_ad(prebuilt->n_fetch_cached < MYSQL_FETCH_CACHE_SIZE);
+ ut_a(prebuilt->n_fetch_cached < MYSQL_FETCH_CACHE_SIZE);
ut_a(!prebuilt->templ_contains_blob);
if (UNIV_UNLIKELY(prebuilt->fetch_cache[0] == NULL)) {
@@ -3317,12 +3316,7 @@ row_sel_push_cache_row_for_mysql(
memcpy(prebuilt->fetch_cache[prebuilt->n_fetch_cached],
mysql_rec, prebuilt->mysql_row_len);
- if (++prebuilt->n_fetch_cached < MYSQL_FETCH_CACHE_SIZE) {
- return(FALSE);
- }
-
- row_sel_pop_cached_row_for_mysql(mysql_rec, prebuilt);
- return(TRUE);
+ ++prebuilt->n_fetch_cached;
}
/*********************************************************************//**
@@ -4650,12 +4644,18 @@ requires_clust_rec:
not cache rows because there the cursor is a scrollable
cursor. */
+ ut_a(prebuilt->n_fetch_cached < MYSQL_FETCH_CACHE_SIZE);
+
+ /* We only convert from InnoDB row format to MySQL row
+ format when ICP is disabled. */
+
if (!prebuilt->idx_cond
&& !row_sel_store_mysql_rec(
buf, prebuilt, result_rec,
result_rec != rec,
result_rec != rec ? clust_index : index,
offsets)) {
+
/* Only fresh inserts may contain incomplete
externally stored columns. Pretend that such
records do not exist. Such records may only be
@@ -4664,7 +4664,11 @@ requires_clust_rec:
transaction. Rollback happens at a lower
level, not here. */
goto next_rec;
- } else if (row_sel_push_cache_row_for_mysql(buf, prebuilt)) {
+ }
+
+ row_sel_push_cache_row_for_mysql(buf, prebuilt);
+
+ if (prebuilt->n_fetch_cached < MYSQL_FETCH_CACHE_SIZE) {
goto next_rec;
}
} else {
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (alexander.nozdrin:3728 to 3730)Bug#11765850 | Alexander Nozdrin | 9 Mar |
