Question 12: What is the role of Table cache?

Posted May 29, 20203 min read

One question one experiment head picture.png

problem

We all know that MySQL's Table Cache is a table-defined cache, and various methods of tuning this parameter are circulating on the rivers and lakes.
In this issue, we use experiments to verify the function of Table Cache.

experiment

We first create a test database:

1.png

Build an empty table:

2.png

Establish a connection and check the initial state of the session:

3.png

In another window, enable strace to track the file operations of the MySQL server:

4.png

Select the newly created table in MySQL:

5.png

Check status:

6.png

See that the operation missed the table cache. Looking at strace, it is found that the table structure file(test \ _tbl.frm) is opened by the mysqld process. If we also capture the read event in strace(the parameter is changed to "-e file, read"), we can see that mysqld does Table structure file.

7.png

We again select in this session:

8.png

You can see that it starts to hit the table cache. In the output of strace, there is no new file operation.

It can be seen that the role of table cache is to save the overhead of reading table structure files.

If there is no hit on the table cache, will there be the overhead of reading the table structure file?

We start a new session, here we added a logo to distinguish the session:

9.png

Select in the new session and check the status:

10.png

It seems that the table cache is not hit, that is to say, the table cache is for threads, each thread has its own cache, and only caches the thread's table structure definition. However, we found that there is no open operation on the table structure file in strace(only stat operation to locate whether the table structure file exists), which means that the table cache does not hit, and it is not necessary to read the table structure file. This feeling seems to be:when the table cache is not hit, another table structure cache is hit. This cache is the table \ _definition \ _cache that we will introduce later.

?Operation and maintenance suggestions:
Let's read the MySQL documentation about the recommended value formula for table \ _open \ _cache:
Recommended value = maximum number of concurrent \ * the maximum number of tables involved in the join statement.

It is easy to understand through experiments:table \ _cache is for threads, so it needs a maximum number of concurrent caches. In addition, the table involved in a statement join needs to exist in the cache at the same time. Therefore, the minimum cache size is equal to the maximum number of tables involved in the statement join. Multiply these two numbers to get the recommended value formula of MySQL.


Do you want to know more about the technical content of MySQL? Hurry up and tell the editor!

黄炎 自 传媒 .png