From: Dmitry Lenev
Date: May 12 2012 2:54pm
Subject: bzr push into mysql-trunk branch (Dmitry.Lenev:3892 to 3893) WL#5772
List-Archive: http://lists.mysql.com/commits/143827
Message-Id: <20120512145458.64680420781@jubjub>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 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).