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

This article will give you a detailed explanation on how to set up Table cache in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Because there is suddenly a duty duty at 5 o'clock in the morning today, I open my colleagues' books at random and see the table object cache, which is slightly different from my usual understanding. So I sorted out table_definition_cache,table_open_cache and table_open_cache_instances.

Let's take a look at what the official website says:

1.table_definition_cache

The number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.

To understand, it is to control the number of total frm files, or a hash table, internal maintenance. If the number of open table instances exceeds the table_definition_cache setting

The LRU mechanism starts tagging table instances for cleanup and eventually removes them from the data dictionary cache.

Simple and popular point frm file how many, set how many.

2.table_open_cache

The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable

The number of tables opened by all threads. Increasing this value increases the number of file descriptors required by mysqld. You can check whether the table cache needs to be added by checking the Opened_tables state variable.

Can not be understood as ibd/MYI/MYD files, open the number of. But when mysql needs to operate on the table, the first thing to do is to find the handle information of the top file, which can be provided by table_open_cache_instances, and then look for the corresponding ibd,MYI,MYD file. The official website does not specify this part.

3.table_open_cache_instances

The number of open tables cache instances. To improve scalability by reducing contention among sessions, the open tables cache can be partitioned into several smaller cache instances of size table_open_cache / table_open_cache_instances. A session needs to lock only one instance to access it for DML statements. This segments cache access among instances, permitting higher performance for operations that use the cache when there are many sessions accessing tables.

The number of open table cache instances. To improve scalability by reducing contention between sessions, the open table cache can be divided into several smaller cache instances of table_open_cache / table_open_cache_instances size. A session can access DML statements by locking only one instance. At this point, we have a general understanding of the relationship as follows:

Table_definition_cache > table_open_cache_instances > table_open_cache

What are the limitations related to 4.table?

Mysql is multithreaded, for the concurrency of the same file, different data, multiple files will be opened, what are the restrictions? Here is what the logic looks like in the source code

1) table_definition_cache

In fact, the maximum value of .frm file can only reach 2000, which has nothing to do with the maximum value given by the official website.

The Max value conflicts with the description, and it is actually confirmed to be 2000.

2) open_files_limit

Limit_1= 10 + max_connections + table_cache_size * 2

Limit_2= max_connections * 5

Limit_3= open_files_limit? Open_files_limit: 5000

It can be seen that max_connections is relevant, and we need to rely on the information of table open file.

3) when max_connections exceeds the threshold of the number of files opened, it is also related to table_open_cache.

4) table_cache_size calculation method

Remarks: TABLE_OPEN_CACHE_MIN=table_open_cache

5. Check the open table situation regularly.

Confirm whether this parameter is tuned through show global status like'% Open%_table%';

6. Common failure response:

For example, in the running database, you can see a large number of Opening tables and closing tables status through show processlist, resulting in application-side access operations.

You need to confirm the maximum number of concurrent tables in table_open_cache= (2 tables may be used in join) to meet the current configuration

For example, if the number of concurrent threads reaches 1000, assuming that 40% of these concurrent connections access 2 tables and the others are single tables, then cache size will reach (100040% 2 "100060% 1) = 1400

It is recommended that the values be monitored regularly:

Open_tables / Opened_tables > = 0.85 table reuse rate

Open_tables / table_open_cache

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.