diff options
| -rw-r--r-- | mysql-test/r/mysqld--help.result | 4 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_embedded.result | 6 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result | 6 | ||||
| -rw-r--r-- | sql/sys_vars.cc | 4 | ||||
| -rw-r--r-- | sql/table.h | 1 | ||||
| -rw-r--r-- | sql/table_cache.cc | 79 |
6 files changed, 85 insertions, 15 deletions
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 9557049711f..25aa8990998 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1092,7 +1092,7 @@ The following options may be given as the first argument: --table-open-cache=# The number of cached open tables --table-open-cache-instances=# - The number of table cache instances + Maximum number of table cache instances --tc-heuristic-recover=name Decision to use in heuristic recover process. One of: OFF, COMMIT, ROLLBACK @@ -1459,7 +1459,7 @@ sysdate-is-now FALSE table-cache 431 table-definition-cache 400 table-open-cache 431 -table-open-cache-instances 1 +table-open-cache-instances 8 tc-heuristic-recover OFF thread-cache-size 151 thread-pool-idle-timeout 60 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 a089bac8412..80146d0b224 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -3889,12 +3889,12 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TABLE_OPEN_CACHE_INSTANCES SESSION_VALUE NULL -GLOBAL_VALUE 1 +GLOBAL_VALUE 8 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 1 +DEFAULT_VALUE 8 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT The number of table cache instances +VARIABLE_COMMENT Maximum number of table cache instances NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 64 NUMERIC_BLOCK_SIZE 1 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 35749a8de12..b38f9f0ac72 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -4659,12 +4659,12 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TABLE_OPEN_CACHE_INSTANCES SESSION_VALUE NULL -GLOBAL_VALUE 1 +GLOBAL_VALUE 8 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 1 +DEFAULT_VALUE 8 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT The number of table cache instances +VARIABLE_COMMENT Maximum number of table cache instances NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 64 NUMERIC_BLOCK_SIZE 1 diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 91568dd9dd2..59c1d1d9e12 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3228,9 +3228,9 @@ static Sys_var_ulong Sys_table_cache_size( ON_UPDATE(fix_table_open_cache)); static Sys_var_ulong Sys_table_cache_instances( - "table_open_cache_instances", "The number of table cache instances", + "table_open_cache_instances", "Maximum number of table cache instances", READ_ONLY GLOBAL_VAR(tc_instances), CMD_LINE(REQUIRED_ARG), - VALID_RANGE(1, 64), DEFAULT(1), BLOCK_SIZE(1)); + VALID_RANGE(1, 64), DEFAULT(8), BLOCK_SIZE(1)); static Sys_var_ulong Sys_thread_cache_size( "thread_cache_size", diff --git a/sql/table.h b/sql/table.h index b3b54362538..b313ee5de8c 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1027,6 +1027,7 @@ private: public: + uint32 instance; /** Table cache instance this TABLE is belonging to */ THD *in_use; /* Which thread uses this */ Field **field; /* Pointer to fields */ diff --git a/sql/table_cache.cc b/sql/table_cache.cc index a8fff7e0410..26a4ad8f904 100644 --- a/sql/table_cache.cc +++ b/sql/table_cache.cc @@ -56,6 +56,8 @@ ulong tdc_size; /**< Table definition cache threshold for LRU eviction. */ ulong tc_size; /**< Table cache threshold for LRU eviction. */ ulong tc_instances; +static uint32 tc_active_instances= 1; +static uint32 tc_contention_warning_reported; /** Data collections. */ static LF_HASH tdc_hash; /**< Collection of TABLE_SHARE objects. */ @@ -127,10 +129,12 @@ struct Table_cache_instance I_P_List_null_counter, I_P_List_fast_push_back<TABLE> > free_tables; ulong records; + uint mutex_waits; + uint mutex_nowaits; /** Avoid false sharing between instances */ char pad[CPU_LEVEL1_DCACHE_LINESIZE]; - Table_cache_instance(): records(0) + Table_cache_instance(): records(0), mutex_waits(0), mutex_nowaits(0) { mysql_mutex_init(key_LOCK_table_cache, &LOCK_table_cache, MY_MUTEX_INIT_FAST); @@ -142,6 +146,68 @@ struct Table_cache_instance DBUG_ASSERT(free_tables.is_empty()); DBUG_ASSERT(records == 0); } + + /** + Lock table cache mutex and check contention. + + Instance is considered contested if more than 20% of mutex acquisiotions + can't be served immediately. Up to 100 000 probes may be performed to avoid + instance activation on short sporadic peaks. 100 000 is estimated maximum + number of queries one instance can serve in one second. + + These numbers work well on a 2 socket / 20 core / 40 threads Intel Broadwell + system, that is expected number of instances is activated within reasonable + warmup time. It may have to be adjusted for other systems. + + Only TABLE object acquistion is instrumented. We intentionally avoid this + overhead on TABLE object release. All other table cache mutex acquistions + are considered out of hot path and are not instrumented either. + */ + void lock_and_check_contention(uint32 n_instances, uint32 instance) + { + if (mysql_mutex_trylock(&LOCK_table_cache)) + { + mysql_mutex_lock(&LOCK_table_cache); + if (++mutex_waits == 20000) + { + if (n_instances < tc_instances) + { + if (my_atomic_cas32_weak_explicit(&tc_active_instances, &n_instances, + n_instances + 1, + MY_MEMORY_ORDER_RELAXED, + MY_MEMORY_ORDER_RELAXED)) + { + sql_print_information("Detected table cache mutex contention at instance %d: " + "%d%% waits. Additional table cache instance " + "activated. Number of instances after " + "activation: %d.", + instance + 1, + mutex_waits * 100 / (mutex_nowaits + mutex_waits), + n_instances + 1); + } + } + else if (!my_atomic_fas32_explicit(&tc_contention_warning_reported, 1, + MY_MEMORY_ORDER_RELAXED)) + { + sql_print_warning("Detected table cache mutex contention at instance %d: " + "%d%% waits. Additional table cache instance " + "cannot be activated: consider raising " + "table_open_cache_instances. Number of active " + "instances: %d.", + instance + 1, + mutex_waits * 100 / (mutex_nowaits + mutex_waits), + n_instances); + } + mutex_waits= 0; + mutex_nowaits= 0; + } + } + else if (++mutex_nowaits == 80000) + { + mutex_waits= 0; + mutex_nowaits= 0; + } + } }; @@ -287,11 +353,12 @@ void tc_purge(bool mark_flushed) void tc_add_table(THD *thd, TABLE *table) { - ulong i= thd->thread_id % tc_instances; + uint32 i= thd->thread_id % my_atomic_load32_explicit(&tc_active_instances, MY_MEMORY_ORDER_RELAXED); TABLE *LRU_table= 0; TDC_element *element= table->s->tdc; DBUG_ASSERT(table->in_use == thd); + table->instance= i; mysql_mutex_lock(&element->LOCK_table_share); /* Wait for MDL deadlock detector to complete traversing tdc.all_tables. */ while (element->all_tables_refs) @@ -327,10 +394,12 @@ void tc_add_table(THD *thd, TABLE *table) static TABLE *tc_acquire_table(THD *thd, TDC_element *element) { - ulong i= thd->thread_id % tc_instances; + uint32 n_instances= + my_atomic_load32_explicit(&tc_active_instances, MY_MEMORY_ORDER_RELAXED); + uint32 i= thd->thread_id % n_instances; TABLE *table; - mysql_mutex_lock(&tc[i].LOCK_table_cache); + tc[i].lock_and_check_contention(n_instances, i); table= element->free_tables[i].list.pop_front(); if (table) { @@ -375,7 +444,7 @@ static TABLE *tc_acquire_table(THD *thd, TDC_element *element) void tc_release_table(TABLE *table) { - ulong i= table->in_use->thread_id % tc_instances; + uint32 i= table->instance; DBUG_ASSERT(table->in_use); DBUG_ASSERT(table->file); |