[Commits] 9145b7d: MDEV-11153 - Introduce status variables for table cache monitoring and tuning

Sergey Vojtovich svoj at mariadb.org
Thu Aug 10 14:46:42 EEST 2017


revision-id: 9145b7dd11a0e95016304e53acf306daac323580 (mariadb-10.3.0-49-g9145b7d)
parent(s): 63ad4fe5bbe77fe17ab441fcce0106bdee9b618d
committer: Sergey Vojtovich
timestamp: 2017-08-10 15:45:03 +0400
message:

MDEV-11153 - Introduce status variables for table cache monitoring and tuning

Status variables added: Table_open_cache_hits, Table_open_cache_misses,
Table_open_cache_overflows.

System variables added: table_open_cache_active_instances (read-only).

---
 mysql-test/r/status.result                         | 21 +++++++++++++
 .../sys_vars/r/sysvars_server_embedded.result      | 14 +++++++++
 .../sys_vars/r/sysvars_server_notembedded.result   | 14 +++++++++
 mysql-test/t/status.test                           | 33 ++++++++++++++++++++
 sql/mysqld.cc                                      |  3 ++
 sql/sql_class.cc                                   |  9 ++++++
 sql/sql_class.h                                    |  3 ++
 sql/sys_vars.cc                                    |  6 ++++
 sql/table_cache.cc                                 | 35 ++++++++++++++++------
 sql/table_cache.h                                  |  1 +
 10 files changed, 130 insertions(+), 9 deletions(-)

diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result
index 9a8a5bd..3c46f0e 100644
--- a/mysql-test/r/status.result
+++ b/mysql-test/r/status.result
@@ -386,6 +386,27 @@ Handler_tmp_write	2
 Handler_update	0
 Handler_write	0
 drop table t1;
+#
+# MDEV-11153 - Introduce status variables for table cache monitoring and
+#              tuning
+#
+SET @old_table_open_cache= @@table_open_cache;
+SET @@global.table_open_cache=10;
+FLUSH TABLES;
+FLUSH STATUS;
+SHOW STATUS LIKE 'Table_open_cache%';
+Variable_name	Value
+Table_open_cache_hits	0
+Table_open_cache_misses	0
+Table_open_cache_overflows	0
+SHOW STATUS LIKE 'Table_open_cache%';
+Variable_name	Value
+Table_open_cache_hits	30
+Table_open_cache_misses	15
+Table_open_cache_overflows	5
+FLUSH TABLES;
+FLUSH STATUS;
+SET @@global.table_open_cache= @old_table_open_cache;
 connection default;
 set @@global.concurrent_insert= @old_concurrent_insert;
 SET GLOBAL log_output = @old_log_output;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index 0a98a09..d79cfb2 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -3971,6 +3971,20 @@ NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	TABLE_OPEN_CACHE_ACTIVE_INSTANCES
+SESSION_VALUE	NULL
+GLOBAL_VALUE	1
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	NULL
+VARIABLE_SCOPE	GLOBAL
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	Number of active table cache instances
+NUMERIC_MIN_VALUE	1
+NUMERIC_MAX_VALUE	64
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	YES
+COMMAND_LINE_ARGUMENT	NULL
 VARIABLE_NAME	TABLE_OPEN_CACHE_INSTANCES
 SESSION_VALUE	NULL
 GLOBAL_VALUE	8
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index 6c8817c..672e9a5 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -4769,6 +4769,20 @@ NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	TABLE_OPEN_CACHE_ACTIVE_INSTANCES
+SESSION_VALUE	NULL
+GLOBAL_VALUE	1
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	NULL
+VARIABLE_SCOPE	GLOBAL
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	Number of active table cache instances
+NUMERIC_MIN_VALUE	1
+NUMERIC_MAX_VALUE	64
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	YES
+COMMAND_LINE_ARGUMENT	NULL
 VARIABLE_NAME	TABLE_OPEN_CACHE_INSTANCES
 SESSION_VALUE	NULL
 GLOBAL_VALUE	8
diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test
index 7ab3224..bff45fc 100644
--- a/mysql-test/t/status.test
+++ b/mysql-test/t/status.test
@@ -387,6 +387,39 @@ drop table t1;
 
 # End of 5.3 tests
 
+--echo #
+--echo # MDEV-11153 - Introduce status variables for table cache monitoring and
+--echo #              tuning
+--echo #
+SET @old_table_open_cache= @@table_open_cache;
+SET @@global.table_open_cache=10;
+FLUSH TABLES;
+FLUSH STATUS;
+SHOW STATUS LIKE 'Table_open_cache%';
+disable_query_log;
+let $i= `SELECT @@table_open_cache + 5`;
+while ($i)
+{
+  eval CREATE TABLE t$i(a INT);
+  eval DELETE FROM t$i;
+  eval DELETE FROM t$i;
+  eval DELETE FROM t$i;
+  dec $i;
+}
+enable_query_log;
+SHOW STATUS LIKE 'Table_open_cache%';
+FLUSH TABLES;
+FLUSH STATUS;
+disable_query_log;
+let $i= `SELECT @@table_open_cache + 5`;
+while ($i)
+{
+  eval DROP TABLE t$i;
+  dec $i;
+}
+enable_query_log;
+SET @@global.table_open_cache= @old_table_open_cache;
+
 # Restore global concurrent_insert value. Keep in the end of the test file.
 --connection default
 set @@global.concurrent_insert= @old_concurrent_insert;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index dad15c9..dff728a 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -8573,6 +8573,9 @@ SHOW_VAR status_vars[]= {
   {"Subquery_cache_miss",      (char*) &subquery_cache_miss,    SHOW_LONG},
   {"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_NOFLUSH},
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 3851a25..b6b57ab 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -1716,6 +1716,9 @@ void add_to_status(STATUS_VAR *to_var, STATUS_VAR *from_var)
   to_var->binlog_bytes_written+= from_var->binlog_bytes_written;
   to_var->cpu_time+=            from_var->cpu_time;
   to_var->busy_time+=           from_var->busy_time;
+  to_var->table_open_cache_hits+= from_var->table_open_cache_hits;
+  to_var->table_open_cache_misses+= from_var->table_open_cache_misses;
+  to_var->table_open_cache_overflows+= from_var->table_open_cache_overflows;
 
   /*
     Update global_memory_used. We have to do this with atomic_add as the
@@ -1767,6 +1770,12 @@ void add_diff_to_status(STATUS_VAR *to_var, STATUS_VAR *from_var,
                                  dec_var->binlog_bytes_written;
   to_var->cpu_time+=             from_var->cpu_time - dec_var->cpu_time;
   to_var->busy_time+=            from_var->busy_time - dec_var->busy_time;
+  to_var->table_open_cache_hits+= from_var->table_open_cache_hits -
+                                  dec_var->table_open_cache_hits;
+  to_var->table_open_cache_misses+= from_var->table_open_cache_misses -
+                                    dec_var->table_open_cache_misses;
+  to_var->table_open_cache_overflows+= from_var->table_open_cache_overflows -
+                                       dec_var->table_open_cache_overflows;
 
   /*
     We don't need to accumulate memory_used as these are not reset or used by
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 4b39cad..041e0f8 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -810,6 +810,9 @@ typedef struct system_status_var
   ulonglong rows_sent;
   ulonglong rows_tmp_read;
   ulonglong binlog_bytes_written;
+  ulonglong table_open_cache_hits;
+  ulonglong table_open_cache_misses;
+  ulonglong table_open_cache_overflows;
   double last_query_cost;
   double cpu_time, busy_time;
   /* Don't initialize */
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index e89dd47..92721b6 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -3219,6 +3219,12 @@ static Sys_var_ulong Sys_table_cache_size(
        BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
        ON_UPDATE(fix_table_open_cache));
 
+static Sys_var_uint Sys_table_cache_active_instances(
+       "table_open_cache_active_instances",
+       "Number of active table cache instances",
+       READ_ONLY GLOBAL_VAR(tc_active_instances), NO_CMD_LINE,
+       VALID_RANGE(1, 64), DEFAULT(1), BLOCK_SIZE(1));
+
 static Sys_var_uint Sys_table_cache_instances(
        "table_open_cache_instances", "Maximum number of table cache instances",
        READ_ONLY GLOBAL_VAR(tc_instances), CMD_LINE(REQUIRED_ARG),
diff --git a/sql/table_cache.cc b/sql/table_cache.cc
index 2feace3..212b2f4 100644
--- a/sql/table_cache.cc
+++ b/sql/table_cache.cc
@@ -56,7 +56,7 @@
 ulong tdc_size; /**< Table definition cache threshold for LRU eviction. */
 ulong tc_size; /**< Table cache threshold for LRU eviction. */
 uint32 tc_instances;
-static uint32 tc_active_instances= 1;
+uint32 tc_active_instances= 1;
 static uint32 tc_contention_warning_reported;
 
 /** Data collections. */
@@ -369,18 +369,30 @@ void tc_add_table(THD *thd, TABLE *table)
   mysql_mutex_unlock(&element->LOCK_table_share);
 
   mysql_mutex_lock(&tc[i].LOCK_table_cache);
-  if (tc[i].records == tc_size && (LRU_table= tc[i].free_tables.pop_front()))
+  if (tc[i].records == tc_size)
   {
-    LRU_table->s->tdc->free_tables[i].list.remove(LRU_table);
-    /* Needed if MDL deadlock detector chimes in before tc_remove_table() */
-    LRU_table->in_use= thd;
+    if ((LRU_table= tc[i].free_tables.pop_front()))
+    {
+      LRU_table->s->tdc->free_tables[i].list.remove(LRU_table);
+      /* Needed if MDL deadlock detector chimes in before tc_remove_table() */
+      LRU_table->in_use= thd;
+      mysql_mutex_unlock(&tc[i].LOCK_table_cache);
+      /* Keep out of locked LOCK_table_cache */
+      tc_remove_table(LRU_table);
+    }
+    else
+    {
+      tc[i].records++;
+      mysql_mutex_unlock(&tc[i].LOCK_table_cache);
+    }
+    /* Keep out of locked LOCK_table_cache */
+    status_var_increment(thd->status_var.table_open_cache_overflows);
   }
   else
+  {
     tc[i].records++;
-  mysql_mutex_unlock(&tc[i].LOCK_table_cache);
-
-  if (LRU_table)
-    tc_remove_table(LRU_table);
+    mysql_mutex_unlock(&tc[i].LOCK_table_cache);
+  }
 }
 
 
@@ -841,7 +853,10 @@ TABLE_SHARE *tdc_acquire_share(THD *thd, TABLE_LIST *tl, uint flags,
 
     tdc_purge(false);
     if (out_table)
+    {
+      status_var_increment(thd->status_var.table_open_cache_misses);
       *out_table= 0;
+    }
     share->m_psi= PSI_CALL_get_table_share(false, share);
     goto end;
   }
@@ -858,8 +873,10 @@ TABLE_SHARE *tdc_acquire_share(THD *thd, TABLE_LIST *tl, uint flags,
       DBUG_ASSERT(element->share);
       DBUG_ASSERT(!element->share->error);
       DBUG_ASSERT(!element->share->is_view);
+      status_var_increment(thd->status_var.table_open_cache_hits);
       DBUG_RETURN(element->share);
     }
+    status_var_increment(thd->status_var.table_open_cache_misses);
   }
 
   mysql_mutex_lock(&element->LOCK_table_share);
diff --git a/sql/table_cache.h b/sql/table_cache.h
index cac6929..387859a 100644
--- a/sql/table_cache.h
+++ b/sql/table_cache.h
@@ -69,6 +69,7 @@ enum enum_tdc_remove_table_type
 extern ulong tdc_size;
 extern ulong tc_size;
 extern uint32 tc_instances;
+extern uint32 tc_active_instances;
 
 extern bool tdc_init(void);
 extern void tdc_start_shutdown(void);


More information about the commits mailing list