# How MySQL tables are opened? table\_definition\_cache, table\_open\_cache, .frm files and Data Dictionary: How are they related?

**URL:** <https://forums.percona.com/t/how-mysql-tables-are-opened-table-definition-cache-table-open-cache-frm-files-and-data-dictionary-how-are-they-related/16142>\
**Category:** Other MySQL® Questions\
**Tags:** community, mysql, percona, mysql-test, closed-no-reply\
**Created:** [June 15, 2022, 7:22am UTC](https://forums.percona.com/t/how-mysql-tables-are-opened-table-definition-cache-table-open-cache-frm-files-and-data-dictionary-how-are-they-related/16142 "2022-06-15T07:22:24Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![vasanths20](https://avatars.discourse-cdn.com/v4/letter/v/a5b964/32.png) [@vasanths20](https://forums.percona.com/u/vasanths20)\
**Post date:** [June 15, 2022, 7:22am UTC](https://forums.percona.com/t/how-mysql-tables-are-opened-table-definition-cache-table-open-cache-frm-files-and-data-dictionary-how-are-they-related/16142/1 "2022-06-15T07:22:24Z")

</div>

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:

```auto
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:**

```auto
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](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/3/368403475fedfa1ca13dc600fc850a7a0a3d9748.png)](https://i.stack.imgur.com/PAjoO.png)

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.