Question 47: What is the function of Table definition cache

Ask

We introduced the role of table cache in Question 12: In the same thread, it reduces the cost of repeatedly reading table definitions, including the IO cost of reading table definition files, and the CPU cost of constructing memory structures. (Note that the table cache is thread-level)

At the same time, we found a problem, even if it does not hit the table cache, MySQL may not necessarily read from the table definition file.

This is because it hits the table definition cache (hereafter referred to as TDC), which is a global level table definition cache

In this issue, we will introduce the role of table definition cache

experiment

Our experimental method is the same as Question 12

Construct a database:

We set the TDC to a minimum value of 400:

Now use sysbench to construct 500 tables:

Now use strace to monitor MySQL IO operations:

Next, we will visit the 500 tables created just now one by one. We first need to generate a script:

We read the table name from the information_schema and spell out the relevant SQL

Now the script can be executed. We will import the SQL generated just now into the MySQL client for execution through a pipe character:

Observe the output of strace:

You will find that MySQL is constantly opening the frm file to read the definition of each table

We now increase the size of the TDC and perform another experiment:

Redo the experiment, we will find that strace has no new output: MySQL no longer reads frm files repeatedly

This is the role of TDC: a cache is established at the global level to reduce the cost of reading table definitions

Questions

Here we leave a question:

We set the TDC to 400. In the experiment, we accessed 500 tables through scripts. This experiment can be repeated.

But every time strace will show that MySQL has accessed 500 frm files, as if TDC did not work.

Please think about: What is the reason for this phenomenon?

If we repeat the above experiment in MySQL 8.0, we will find that MySQL no longer reads the frm file.

This is because MySQL 8.0 introduces the Data Dictionary, which places the table definition in the table and no longer needs to access the frm file.

Operation and maintenance recommendations

If the TDC overflows, it will not be like table_cache overflow, there will be related state quantities to display (table_open_cache_overflows).

In order to prevent the benefits of TDC from disappearing, we recommend to always keep the state Opened_table_definitions smaller than the parameter table_definition_cache, so as to ensure that TDC always hits.

And what we pay is only a little memory price, the advantages outweigh the disadvantages.

---

What else do you want to know about the technical content of MySQL? Hurry up and leave a message to tell the editor!