3893 Dmitry Lenev 2012-05-12
WL#5772 "Add partitioned Table Definition Cache to avoid
using LOCK_open and its derivatives in DML queries".
Review changes #5:
- Made status variables for table cache visible in SHOW STATUS.
- Added test coverage for them.
- Fixed setting of @@table_open_cache size at runtime.
modified:
mysql-test/r/status.result
mysql-test/t/status.test
sql/mysqld.cc
sql/sys_vars.cc
3892 Dmitry Lenev 2012-05-11
WL#5772 "Add partitioned Table Definition Cache to avoid
using LOCK_open and its derivatives in DML queries".
Review changes #4:
- Make Table_cache API object-oriented.
- Get rid of hash look-ups when iterating over
all used TABLE instances for the TABLE_SHARE.
- Improve comments.
modified:
sql/mysqld.cc
sql/sql_base.cc
sql/sql_base.h
sql/sql_const.h
sql/sql_handler.cc
sql/sql_parse.cc
sql/sql_table.cc
sql/sql_test.cc
sql/sys_vars.cc
sql/table.cc
sql/table.h
=== modified file 'mysql-test/r/status.result'
--- a/mysql-test/r/status.result 2012-03-06 14:29:42 +0000
+++ b/mysql-test/r/status.result 2012-05-12 14:54:12 +0000
@@ -238,5 +238,104 @@ SELECT 9;
9
DROP PROCEDURE p1;
DROP FUNCTION f1;
+#
+# Test coverage for status variables which were introduced by
+# WL#5772 "Add partitioned Table Definition Cache to avoid
+# using LOCK_open and its derivatives in DML queries".
+#
+create table t1 (i int);
+create table t2 (j int);
+create table t3 (k int);
+# Flush table cache to ensure that it is empty and reset status
+# variables. Since to test cache overflow we will need to reduce
+# its size, also save original table cache size.
+flush tables;
+flush status;
+set @old_table_open_cache= @@table_open_cache;
+# Check that after reset all status variables are zero.
+show status like 'table_open_cache_%';
+Variable_name Value
+Table_open_cache_hits 0
+Table_open_cache_misses 0
+Table_open_cache_overflows 0
+# The first statement accessing t1 after flush should result
+# in table cache miss.
+select * from t1;
+i
+show status like 'table_open_cache_%';
+Variable_name Value
+Table_open_cache_hits 0
+Table_open_cache_misses 1
+Table_open_cache_overflows 0
+# The second statement accessing the same table should
+# result in table cache hit.
+select * from t1;
+i
+show status like 'table_open_cache_%';
+Variable_name Value
+Table_open_cache_hits 1
+Table_open_cache_misses 1
+Table_open_cache_overflows 0
+# Again table cache miss if accessing different table.
+select * from t2;
+j
+show status like 'table_open_cache_%';
+Variable_name Value
+Table_open_cache_hits 1
+Table_open_cache_misses 2
+Table_open_cache_overflows 0
+# And cache hit then accessing it second time.
+select * from t2;
+j
+show status like 'table_open_cache_%';
+Variable_name Value
+Table_open_cache_hits 2
+Table_open_cache_misses 2
+Table_open_cache_overflows 0
+# The below statement should result in 2 cache hits and
+# 4 cache misses since it needs 6 table instances in total.
+select * from t1 as a, t2 as b, t1 as c, t2 as d, t1 as e, t2 as f;
+i j i j i j
+show status like 'table_open_cache_%';
+Variable_name Value
+Table_open_cache_hits 4
+Table_open_cache_misses 6
+Table_open_cache_overflows 0
+# Reduce size of table cache to check that status
+# variable tracking cache overflows works.
+set @@global.table_open_cache= 4;
+# The below statement should result in table cache hit, but
+# as a side effect it should result in trimming of table
+# cache by 2 TABLE instances, meaning that overflow counter
+# will get increased by 2.
+select * from t1;
+i
+show status like 'table_open_cache_%';
+Variable_name Value
+Table_open_cache_hits 5
+Table_open_cache_misses 6
+Table_open_cache_overflows 2
+# This statement should result in 4 cache hits, 2 cache misses/
+# overflows.
+select * from t1 as a, t2 as b, t1 as c, t2 as d, t1 as e, t2 as f;
+i j i j i j
+show status like 'table_open_cache_%';
+Variable_name Value
+Table_open_cache_hits 9
+Table_open_cache_misses 8
+Table_open_cache_overflows 4
+# Finally, the below statement should result in 1 cache miss
+# and 1 overflow since it accesses table which is not yet in
+# cache and table cache is full.
+select * from t3;
+k
+show status like 'table_open_cache_%';
+Variable_name Value
+Table_open_cache_hits 9
+Table_open_cache_misses 9
+Table_open_cache_overflows 5
+# Cleanup
+set @@global.table_open_cache= @old_table_open_cache;
+drop tables t1, t2, t3;
set @@global.concurrent_insert= @old_concurrent_insert;
SET GLOBAL log_output = @old_log_output;
=== modified file 'mysql-test/t/status.test'
--- a/mysql-test/t/status.test 2011-09-07 10:08:09 +0000
+++ b/mysql-test/t/status.test 2012-05-12 14:54:12 +0000
@@ -354,6 +354,75 @@ DROP FUNCTION f1;
# End of 5.1 tests
+
+--echo #
+--echo # Test coverage for status variables which were introduced by
+--echo # WL#5772 "Add partitioned Table Definition Cache to avoid
+--echo # using LOCK_open and its derivatives in DML queries".
+--echo #
+create table t1 (i int);
+create table t2 (j int);
+create table t3 (k int);
+--echo # Flush table cache to ensure that it is empty and reset status
+--echo # variables. Since to test cache overflow we will need to reduce
+--echo # its size, also save original table cache size.
+flush tables;
+flush status;
+set @old_table_open_cache= @@table_open_cache;
+
+--echo # Check that after reset all status variables are zero.
+show status like 'table_open_cache_%';
+
+--echo # The first statement accessing t1 after flush should result
+--echo # in table cache miss.
+select * from t1;
+show status like 'table_open_cache_%';
+
+--echo # The second statement accessing the same table should
+--echo # result in table cache hit.
+select * from t1;
+show status like 'table_open_cache_%';
+
+--echo # Again table cache miss if accessing different table.
+select * from t2;
+show status like 'table_open_cache_%';
+
+--echo # And cache hit then accessing it second time.
+select * from t2;
+show status like 'table_open_cache_%';
+
+--echo # The below statement should result in 2 cache hits and
+--echo # 4 cache misses since it needs 6 table instances in total.
+select * from t1 as a, t2 as b, t1 as c, t2 as d, t1 as e, t2 as f;
+show status like 'table_open_cache_%';
+
+--echo # Reduce size of table cache to check that status
+--echo # variable tracking cache overflows works.
+set @@global.table_open_cache= 4;
+
+--echo # The below statement should result in table cache hit, but
+--echo # as a side effect it should result in trimming of table
+--echo # cache by 2 TABLE instances, meaning that overflow counter
+--echo # will get increased by 2.
+select * from t1;
+show status like 'table_open_cache_%';
+
+--echo # This statement should result in 4 cache hits, 2 cache misses/
+--echo # overflows.
+select * from t1 as a, t2 as b, t1 as c, t2 as d, t1 as e, t2 as f;
+show status like 'table_open_cache_%';
+
+--echo # Finally, the below statement should result in 1 cache miss
+--echo # and 1 overflow since it accesses table which is not yet in
+--echo # cache and table cache is full.
+select * from t3;
+show status like 'table_open_cache_%';
+
+--echo # Cleanup
+set @@global.table_open_cache= @old_table_open_cache;
+drop tables t1, t2, t3;
+
+
# Restore global concurrent_insert value. Keep in the end of the test file.
--connection default
set @@global.concurrent_insert= @old_concurrent_insert;
=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc 2012-05-11 16:05:27 +0000
+++ b/sql/mysqld.cc 2012-05-12 14:54:12 +0000
@@ -7433,6 +7433,9 @@ SHOW_VAR status_vars[]= {
#endif /* HAVE_OPENSSL */
{"Table_locks_immediate", (char*) &locks_immediate, SHOW_LONG},
{"Table_locks_waited", (char*) &locks_waited, SHOW_LONG},
+ {"Table_open_cache_hits", (char*) offsetof(STATUS_VAR, table_open_cache_hits), SHOW_LONGLONG_STATUS},
+ {"Table_open_cache_misses", (char*) offsetof(STATUS_VAR, table_open_cache_misses), SHOW_LONGLONG_STATUS},
+ {"Table_open_cache_overflows",(char*) offsetof(STATUS_VAR, table_open_cache_overflows), SHOW_LONGLONG_STATUS},
#ifdef HAVE_MMAP
{"Tc_log_max_pages_used", (char*) &tc_log_max_pages_used, SHOW_LONG},
{"Tc_log_page_size", (char*) &tc_log_page_size, SHOW_LONG},
=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc 2012-05-11 16:05:27 +0000
+++ b/sql/sys_vars.cc 2012-05-12 14:54:12 +0000
@@ -2713,16 +2713,18 @@ static Sys_var_ulong Sys_table_def_size(
VALID_RANGE(TABLE_DEF_CACHE_MIN, 512*1024),
DEFAULT(TABLE_DEF_CACHE_DEFAULT), BLOCK_SIZE(1));
-/*
- TODO/FIXME: Ensure that table_cache_size_per_instance is properly
- updated when table_cache_size is changed dynamically.
-*/
+static bool fix_table_cache_size(sys_var *self, THD *thd, enum_var_type type)
+{
+ table_cache_size_per_instance= table_cache_size / table_cache_instances;
+ return false;
+}
static Sys_var_ulong Sys_table_cache_size(
"table_open_cache", "The number of cached open tables",
GLOBAL_VAR(table_cache_size), CMD_LINE(REQUIRED_ARG),
VALID_RANGE(1, 512*1024), DEFAULT(TABLE_OPEN_CACHE_DEFAULT),
- BLOCK_SIZE(1));
+ BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(NULL),
+ ON_UPDATE(fix_table_cache_size));
static Sys_var_ulong Sys_table_cache_instances(
"table_open_cache_instances", "The number of table cache instances",
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (Dmitry.Lenev:3892 to 3893) WL#5772 | Dmitry Lenev | 16 May |
