3360 Dmitry Lenev 2011-08-11 [merge]
Merged fix for #12828477 "MDL SUBSYSTEM CREATES BIG
OVERHEAD FOR CERTAIN QUERIES TO INFORMATION_SCHEMA"
into mysql-trunk.
modified:
mysql-test/r/information_schema.result
mysql-test/t/information_schema.test
sql/sql_show.cc
3359 Tatjana Azundris Nuernberg 2011-08-11 [merge]
merge
added:
client/mysql_plugin.c
mysql-test/include/daemon_example_bad_format.ini
mysql-test/include/daemon_example_bad_soname.ini
mysql-test/r/mysql_plugin.result
mysql-test/t/mysql_plugin-master.opt
mysql-test/t/mysql_plugin.test
plugin/daemon_example/daemon_example.ini
modified:
client/CMakeLists.txt
include/my_global.h
mysql-test/include/plugin.defs
mysql-test/mysql-test-run.pl
plugin/daemon_example/CMakeLists.txt
support-files/mysql.spec.sh
=== modified file 'mysql-test/r/information_schema.result'
--- a/mysql-test/r/information_schema.result 2011-08-10 07:06:02 +0000
+++ b/mysql-test/r/information_schema.result 2011-08-11 18:53:46 +0000
@@ -1902,5 +1902,119 @@ unlock tables;
drop table t1;
drop view v1;
#
+# Test for bug #12828477 - "MDL SUBSYSTEM CREATES BIG OVERHEAD FOR
+# CERTAIN QUERIES TO INFORMATION_SCHEMA".
+#
+# Check that metadata locks which are acquired during the process
+# of opening tables/.FRMs/.TRG files while filling I_S table are
+# not kept to the end of statement. Keeping the locks has caused
+# performance problems in cases when big number of tables (.FRMs
+# or .TRG files) were scanned as cost of new lock acquisition has
+# increased linearly.
+drop database if exists mysqltest;
+create database mysqltest;
+use mysqltest;
+create table t0 (i int);
+create table t1 (j int);
+create table t2 (k int);
+#
+# Test that we don't keep locks in case when we to fill
+# I_S table we perform full-blown table open.
+#
+# Acquire lock on 't2' so upcoming RENAME is
+# blocked.
+lock tables t2 read;
+#
+# Switching to connection 'con12828477_1'.
+#
+# The below RENAME should wait on 't2' while
+# keeping X lock on 't1'.
+rename table t1 to t3, t2 to t1, t3 to t2;
+#
+# Switching to connection 'con12828477_2'.
+#
+# Wait while the above RENAME is blocked.
+# Issue query to I_S which will open 't0' and get
+# blocked on 't1' because of RENAME.
+select table_name, auto_increment from information_schema.tables where table_schema='mysqltest';
+#
+# Switching to connection 'con12828477_3'.
+#
+# Wait while the above SELECT is blocked.
+#
+# Check that it holds no lock on 't0' so it can be renamed.
+rename table t0 to t4;
+#
+# Switching to connection 'default'.
+#
+#
+# Unblock the first RENAME.
+unlock tables;
+#
+# Switching to connection 'con12828477_1'.
+#
+# Reap the first RENAME
+#
+# Switching to connection 'con12828477_2'.
+#
+# Reap SELECT to I_S.
+table_name auto_increment
+t0 NULL
+t1 NULL
+t2 NULL
+#
+# Switching to connection 'default'.
+#
+#
+# Now test that we don't keep locks in case when we to fill
+# I_S table we read .FRM or .TRG file only (this was the case
+# for which problem existed).
+#
+rename table t4 to t0;
+# Acquire lock on 't2' so upcoming RENAME is
+# blocked.
+lock tables t2 read;
+#
+# Switching to connection 'con12828477_1'.
+#
+# The below RENAME should wait on 't2' while
+# keeping X lock on 't1'.
+rename table t1 to t3, t2 to t1, t3 to t2;
+#
+# Switching to connection 'con12828477_2'.
+#
+# Wait while the above RENAME is blocked.
+# Issue query to I_S which will open 't0' and get
+# blocked on 't1' because of RENAME.
+select event_object_table, trigger_name from information_schema.triggers where event_object_schema='mysqltest';
+#
+# Switching to connection 'con12828477_3'.
+#
+# Wait while the above SELECT is blocked.
+#
+# Check that it holds no lock on 't0' so it can be renamed.
+rename table t0 to t4;
+#
+# Switching to connection 'default'.
+#
+#
+# Unblock the first RENAME.
+unlock tables;
+#
+# Switching to connection 'con12828477_1'.
+#
+# Reap the first RENAME
+#
+# Switching to connection 'con12828477_2'.
+#
+# Reap SELECT to I_S.
+event_object_table trigger_name
+#
+# Switching to connection 'default'.
+#
+#
+# Clean-up.
+drop database mysqltest;
+#
# End of 5.5 tests
#
=== modified file 'mysql-test/t/information_schema.test'
--- a/mysql-test/t/information_schema.test 2011-07-18 09:49:22 +0000
+++ b/mysql-test/t/information_schema.test 2011-08-11 18:53:46 +0000
@@ -1581,11 +1581,6 @@ drop function f1;
disconnect con7;
-
-
-# Wait till all disconnects are completed
---source include/wait_until_count_sessions.inc
-
#
# Bug #43834 Assertion in Natural_join_column::db_name() on an I_S query
#
@@ -1649,5 +1644,185 @@ drop view v1;
--echo #
+--echo # Test for bug #12828477 - "MDL SUBSYSTEM CREATES BIG OVERHEAD FOR
+--echo # CERTAIN QUERIES TO INFORMATION_SCHEMA".
+--echo #
+--echo # Check that metadata locks which are acquired during the process
+--echo # of opening tables/.FRMs/.TRG files while filling I_S table are
+--echo # not kept to the end of statement. Keeping the locks has caused
+--echo # performance problems in cases when big number of tables (.FRMs
+--echo # or .TRG files) were scanned as cost of new lock acquisition has
+--echo # increased linearly.
+--disable_warnings
+drop database if exists mysqltest;
+--enable_warnings
+create database mysqltest;
+use mysqltest;
+create table t0 (i int);
+create table t1 (j int);
+create table t2 (k int);
+
+--echo #
+--echo # Test that we don't keep locks in case when we to fill
+--echo # I_S table we perform full-blown table open.
+--echo #
+
+--echo # Acquire lock on 't2' so upcoming RENAME is
+--echo # blocked.
+lock tables t2 read;
+
+--echo #
+--echo # Switching to connection 'con12828477_1'.
+--echo #
+connect (con12828477_1, localhost, root,,mysqltest);
+--echo # The below RENAME should wait on 't2' while
+--echo # keeping X lock on 't1'.
+--send rename table t1 to t3, t2 to t1, t3 to t2
+
+--echo #
+--echo # Switching to connection 'con12828477_2'.
+--echo #
+connect (con12828477_2, localhost, root,,mysqltest);
+--echo # Wait while the above RENAME is blocked.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = "Waiting for table metadata lock" and
+ info = "rename table t1 to t3, t2 to t1, t3 to t2";
+--source include/wait_condition.inc
+
+--echo # Issue query to I_S which will open 't0' and get
+--echo # blocked on 't1' because of RENAME.
+--send select table_name, auto_increment from information_schema.tables where table_schema='mysqltest'
+
+--echo #
+--echo # Switching to connection 'con12828477_3'.
+--echo #
+connect (con12828477_3, localhost, root,,mysqltest);
+--echo # Wait while the above SELECT is blocked.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = "Waiting for table metadata lock" and
+ info = "select table_name, auto_increment from information_schema.tables where table_schema='mysqltest'";
+--source include/wait_condition.inc
+
+--echo #
+--echo # Check that it holds no lock on 't0' so it can be renamed.
+rename table t0 to t4;
+
+--echo #
+--echo # Switching to connection 'default'.
+--echo #
+connection default;
+--echo #
+--echo # Unblock the first RENAME.
+unlock tables;
+
+--echo #
+--echo # Switching to connection 'con12828477_1'.
+--echo #
+connection con12828477_1;
+--echo # Reap the first RENAME
+--reap
+
+--echo #
+--echo # Switching to connection 'con12828477_2'.
+--echo #
+connection con12828477_2;
+--echo # Reap SELECT to I_S.
+--reap
+
+--echo #
+--echo # Switching to connection 'default'.
+--echo #
+connection default;
+
+--echo #
+--echo # Now test that we don't keep locks in case when we to fill
+--echo # I_S table we read .FRM or .TRG file only (this was the case
+--echo # for which problem existed).
+--echo #
+
+rename table t4 to t0;
+--echo # Acquire lock on 't2' so upcoming RENAME is
+--echo # blocked.
+lock tables t2 read;
+
+--echo #
+--echo # Switching to connection 'con12828477_1'.
+--echo #
+connection con12828477_1;
+--echo # The below RENAME should wait on 't2' while
+--echo # keeping X lock on 't1'.
+--send rename table t1 to t3, t2 to t1, t3 to t2
+
+--echo #
+--echo # Switching to connection 'con12828477_2'.
+--echo #
+connection con12828477_2;
+--echo # Wait while the above RENAME is blocked.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = "Waiting for table metadata lock" and
+ info = "rename table t1 to t3, t2 to t1, t3 to t2";
+--source include/wait_condition.inc
+
+--echo # Issue query to I_S which will open 't0' and get
+--echo # blocked on 't1' because of RENAME.
+--send select event_object_table, trigger_name from information_schema.triggers where event_object_schema='mysqltest'
+
+--echo #
+--echo # Switching to connection 'con12828477_3'.
+--echo #
+connection con12828477_3;
+--echo # Wait while the above SELECT is blocked.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = "Waiting for table metadata lock" and
+ info = "select event_object_table, trigger_name from information_schema.triggers where event_object_schema='mysqltest'";
+--source include/wait_condition.inc
+
+--echo #
+--echo # Check that it holds no lock on 't0' so it can be renamed.
+rename table t0 to t4;
+
+--echo #
+--echo # Switching to connection 'default'.
+--echo #
+connection default;
+--echo #
+--echo # Unblock the first RENAME.
+unlock tables;
+
+--echo #
+--echo # Switching to connection 'con12828477_1'.
+--echo #
+connection con12828477_1;
+--echo # Reap the first RENAME
+--reap
+
+--echo #
+--echo # Switching to connection 'con12828477_2'.
+--echo #
+connection con12828477_2;
+--echo # Reap SELECT to I_S.
+--reap
+
+--echo #
+--echo # Switching to connection 'default'.
+--echo #
+connection default;
+disconnect con12828477_1;
+disconnect con12828477_2;
+disconnect con12828477_3;
+
+--echo #
+--echo # Clean-up.
+drop database mysqltest;
+
+
+--echo #
--echo # End of 5.5 tests
--echo #
+
+# Wait till all disconnects are completed
+--source include/wait_until_count_sessions.inc
=== modified file 'sql/sql_show.cc'
--- a/sql/sql_show.cc 2011-08-02 08:34:30 +0000
+++ b/sql/sql_show.cc 2011-08-11 18:53:46 +0000
@@ -3168,6 +3168,10 @@ end:
*/
thd->temporary_tables= NULL;
close_thread_tables(thd);
+ /*
+ Release metadata lock we might have acquired.
+ See comment in fill_schema_table_from_frm() for details.
+ */
thd->mdl_context.rollback_to_savepoint(open_tables_state_backup->mdl_system_tables_svp);
thd->lex= old_lex;
@@ -3350,6 +3354,9 @@ try_acquire_high_prio_shared_mdl_lock(TH
@param[in] db_name database name
@param[in] table_name table name
@param[in] schema_table_idx I_S table index
+ @param[in] open_tables_state_backup Open_tables_state object which is used
+ to save/restore original state of metadata
+ locks.
@param[in] can_deadlock Indicates that deadlocks are possible
due to metadata locks, so to avoid
them we should not wait in case if
@@ -3367,6 +3374,7 @@ static int fill_schema_table_from_frm(TH
LEX_STRING *db_name,
LEX_STRING *table_name,
enum enum_schema_tables schema_table_idx,
+ Open_tables_backup *open_tables_state_backup,
bool can_deadlock)
{
TABLE *table= tables->table;
@@ -3512,13 +3520,27 @@ end_share:
end_unlock:
mysql_mutex_unlock(&LOCK_open);
- /*
- Don't release the MDL lock, it can be part of a transaction.
- If it is not, it will be released by the call to
- MDL_context::rollback_to_savepoint() in the caller.
- */
end:
+ /*
+ Release metadata lock we might have acquired.
+
+ Without this step metadata locks acquired for each table processed
+ will be accumulated. In situation when a lot of tables are processed
+ by I_S query this will result in transaction with too many metadata
+ locks. As result performance of acquisition of new lock will suffer.
+
+ Of course, the fact that we don't hold metadata lock on tables which
+ were processed till the end of I_S query makes execution less isolated
+ from concurrent DDL. Consequently one might get 'dirty' results from
+ such a query. But we have never promised serializability of I_S queries
+ anyway.
+
+ We don't have any tables open since we took backup, so rolling back to
+ savepoint is safe.
+ */
+ DBUG_ASSERT(thd->open_tables == NULL);
+ thd->mdl_context.rollback_to_savepoint(open_tables_state_backup->mdl_system_tables_svp);
thd->clear_error();
return res;
}
@@ -3769,6 +3791,7 @@ int get_all_tables(THD *thd, TABLE_LIST
int res= fill_schema_table_from_frm(thd, tables, schema_table,
db_name, table_name,
schema_table_idx,
+ &open_tables_state_backup,
can_deadlock);
thd->pop_internal_handler();
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (Dmitry.Lenev:3359 to 3360) | Dmitry Lenev | 16 Aug |
