Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to understand MySQL Table open cache hits/Table open cache misses/Table open cache overflows. Many people may not know much about it. In order to let everyone know more, Xiaobian summarizes the following contents for everyone. I hope you can gain something according to this article.

These three values are all related to the function open_table, and they are all related to table cache and table share. Here is a rough explanation. Refer to other books for details:

table cache: session instantiation, instantiated by table share definition, holds file descriptors of files.

table share: Definition of a table in memory.

Table_open_cache_hits Main logic:

The approximate logic is as follows

retry_share: { Table_cache *tc= table_cache_manager.get_cache(thd); tc->lock(); /* Try to get unused TABLE object or at least pointer to TABLE_SHARE from the table cache. */ table= tc->get_table(thd, hash_value, key, key_length, &share); if (table) {... thd->status_var.table_open_cache_hits++; goto table_found;

tc->get_table This call, you can roughly see that an instance (table cache) pops up in the free list of table share, that is, without actual instantiation, then it hits, as follows:

Function Table_cache::get_tableel->free_tables.front()

Table_open_cache_hits +1.

Table_open_cache_misses main logic

We said comprehensively that if we find an idle instance (table cache), we can reuse it. If we don't find it, we still need to deal with table share, and then establish an instance (table cache) through table share. Let's see how to establish an instance through table share:

error= open_table_from_share(thd, share, alias, (uint) (HA_OPEN_KEYFILE | HA_OPEN_RNDFILE | HA_GET_INDEX | HA_TRY_READ_ONLY), EXTRA_RECORD, thd->open_options, table, FALSE); if (error) {... thd->status_var.table_open_cache_misses++;

The table cache is instantiated from table share to table cache by the function open_table_from_share, and then marked as Table_open_cache_misses +1.

Table_open_cache_overflows main logic

As mentioned above, if there is no hint, you need to create an instance (table cache), but you need to pay attention to the creation of an instance (table cache). If the table_open_cache setting is exceeded, it will be eliminated (note that the code in 5.7.26 can be divided into multiple instances, the default is 16). Then these eliminated values are recorded in Table_open_cache_overflows, and the logic is as follows:

Function: Table_cache::free_unused_tables_if_necessary is called open_table ->Table_cache::add_used_table->Table_cache::free_unused_tables_if_necessaryif (m_table_count > table_cache_size_per_instance && m_unused_tables) { mysql_mutex_lock(&LOCK_open); while (m_table_count > table_cache_size_per_instance && m_unused_tables) { TABLE *table_to_free= m_unused_tables; remove_table(table_to_free); intern_close_table(table_to_free); thd->status_var.table_open_cache_overflows++; } mysql_mutex_unlock(&LOCK_open); }

Note that the condition m_table_count > table_cache_size_per_instance here is the elimination condition, which is directly related to table_open_cache.

Open_tables/Open_tables

There is also a value Open_tables, which is similar to Table_open_cache_misses, and its call is self-adding in open_table_from_share.（thd->status_var.opened_tables++;）

Open_tables is the total number of instances (table cache) as follows:

static int show_open_tables(THD *thd, SHOW_VAR *var, char *buff){ var->type= SHOW_LONG; var->value= buff; *((long *)buff)= (long)table_cache_manager.cached_tables(); return 0;}/** Get total number of used and unused TABLE objects in all table caches. @note Doesn't require acquisition of table cache locks if inexact number of tables is acceptable.*/ uint Table_cache_manager::cached_tables(){ uint result= 0; for (uint i= 0; i < table_cache_instances; i++) result+= m_table_cache[i].cached_tables(); return result;}

Table_open_cache_hits: If an instance (table cache) can be found from the free list of table share, it is regarded as a hit, with a value of +1.

Table_open_cache_misses: Table_open_cache_hits In contrast, if it is not found, the value +1 needs to be reinstantiated, which usually occurs when the table is initialized for the first time, or after it has been retired due to a setting exceeding the parameter table_open_cache.

Table_open_cache_overflows: This is the number of instances (table cache) eliminated above, each elimination value +1.

Open_tables: Similar to Table_open_cache_misses.

Open_tables: Total number of instances (table cache).

We can usually judge whether the table_open_cache parameter setting is reasonable by them.

After reading the above, do you have any further understanding of how to understand Table open cache hits/Table open cache misses/Table open cache overflows in MySQL? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.