diff options
| -rw-r--r-- | mysql-test/r/status.result | 23 | ||||
| -rw-r--r-- | mysql-test/t/status.test | 33 | ||||
| -rw-r--r-- | sql/mysqld.cc | 4 | ||||
| -rw-r--r-- | sql/sql_class.cc | 9 | ||||
| -rw-r--r-- | sql/sql_class.h | 3 | ||||
| -rw-r--r-- | sql/table_cache.cc | 35 | ||||
| -rw-r--r-- | sql/table_cache.h | 1 |
7 files changed, 99 insertions, 9 deletions
diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index 9a8a5bd8f93..9b82c7896cb 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -386,6 +386,29 @@ 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_active_instances 1 +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_active_instances 1 +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/t/status.test b/mysql-test/t/status.test index 7ab32241bcb..bff45fca583 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 ad27c58f67f..f02ded12254 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -8616,6 +8616,10 @@ 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_active_instances", (char*) &tc_active_instances, SHOW_UINT}, + {"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 889acc57b76..02f49b6d645 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -1728,6 +1728,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 @@ -1779,6 +1782,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 fda56c8cc3a..740e9911838 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -823,6 +823,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/table_cache.cc b/sql/table_cache.cc index 6067ecb059d..f55e24f3b04 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 @@ retry: 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 @@ retry: 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 2e5bb3428dc..b41665258c9 100644 --- a/sql/table_cache.h +++ b/sql/table_cache.h @@ -71,6 +71,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); |