How MySQL tables are opened? table_definition_cache, table_open_cache, .frm files and Data Dictionary: How are they related?

MySQL & MariaDB Other MySQL® Questions
, , , ,
1

MySQL version: 5.7 Machine: Ubuntu 20.04

Recently, we were analysing the performance issues that can occur when increasing the number of tables in a schema. And during this, we came across 4 system variables that have impact on the performance: table_open_cache, open_files_limit, table_definition_cache and the Dictionary memory allocated from SHOW ENGINE INNODB STATUS

Here’s what we know:

  1. table_open_cache: The maximum number of tables that can be open concurrently across all sessions.
  2. open_files_limit: Maximum number of files that can be open in the mysqld process.
  3. table_definition_cache: The maximum number of tables whose metadata can be stored in memory, without having to use file descriptors to read the .frm files.
  4. Dictionary Memory Allocated: The amount of memory that is allocated in the innodb data dictionary. There is no limit and it keeps increasing.

With these information in mind, we decided to create 100,000 tables in a schema and run a loop to read these tables using the SELECT * FROM tablename command.

Here is the code and the stats we arrived at:

int num = 1;                
long currTime= System.currentTimeMillis();
for(int i=1;i<=100000;i++)
{
    String tableName = table + i;
    st.executeQuery("select * from " + tableName + "");
    if(i%5000==0)
    {
        printDictMemoryAndTime(st,num++);
    }               
}

The printDictMemoryAndTime() method runs the SHOW ENGINE INNODB STATUS, SELECT NOW(), SHOW GLOBAL STATUS LIKE 'open%_table%' commands and prints the output

Global VARIABLES and STATUS initially:

innodb_open_files	        2000
open_files_limit	        5000
table_definition_cache	    1400
table_open_cache           	2000
table_open_cache_instances	1600
Opened_table_definitions	502560
Opened_tables               553
Open_table_definitions	    1400
Open_tables                 546

Stats:

enter image description here

The file descriptors count during the process did not go beyond 2030.

So, here are the questions that we need answers for:

  1. Why is the Dictionary memory not constantly increasing?
  2. What is happening when the Dictionary memory reduces in size?
  3. I took stats by setting the table_open_cache=3000. But still, the file descriptors count stayed at 2020-2030. Why are the file descriptors not increasing in number?
  4. We know that, in a File-per-Table tablespace, .frm files contain the table_definition and the .ibd files contain the table data. In the /proc/PID/fd is where we took the File descriptor count from. Here, even while reading the table for the first time, only .ibd files were read. No .frm files were listed. Why?
  5. Finally, Here’s what we think happens when we open a MySQL table:

The server first checks if the number of tables opened is less than the value of the table_open_cache variable. If it’s not, MySQL uses an LRU algorithm to remove the least recently used table. Now, when a table is read, the InnoDB engine checks the Data Dictionary if the table definition is present or not. If not, it checks the table_definition_cache in the server layer. If it’s not present in the table_defintion_cache, MySQL uses File Descriptors to read the .frm and then the .ibd files. It then adds the table definition to the table_definition_cache in the server layer and also to the Data Dictionary in the InnoDB storage engine layer. So, next time when the table needs to be read, InnoDB can simply get the table definition from the Data Dictionary cache.

Is the above flow correct? If not, How does MySQL open the tables? Please share your knowledge on the same.

Thank you for the patience. Would definitely appreciate your answers.

2 Likes