Question 12: What is the role of Table cache?
Posted May 29, 2020 • 3 min read
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.
We first create a test database:
Build an empty table:
Establish a connection and check the initial state of the session:
In another window, enable strace to track the file operations of the MySQL server:
Select the newly created table in MySQL:
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.
We again select in this session:
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:
Select in the new session and check the status:
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!