Detailed explanation of MySQL 8.0 dictionary table enhancement

The data dictionary in MySQL is one of the important components of the database. INFORMATION_SCHEMA was first introduced in MySQL 5.0 as a standard-compliant way to retrieve metadata from a running MySQL server. Used to store data metadata, statistical information, and access information about the MySQL server (for example, database name or table name, field data type and access permissions, etc.).

Prior to 8.0:

1. Metadata comes from files

2. Use the MEMORY table engine

3. The frm file stores table structure information

4. The opt file records some basic information of each library, including the character set of the library, etc.

5. .TRN and .TRG files are used to store trigger information

5.6> SELECT TABLE_SCHEMA ,ENGINE ,COUNT(*) from information_schema.tables where table_schema in ('information_schema' ,'mysql','performance_schema', 'sys') group by TABLE_SCHEMA ,ENGINE;
+--------------------+--------------------+----------+
| TABLE_SCHEMA | ENGINE | COUNT(*) |
+--------------------+--------------------+----------+
| information_schema | MEMORY | 49 |
| information_schema | MyISAM | 10 |
| mysql | CSV | 2 |
| mysql | InnoDB | 6 |
| mysql | MyISAM | 21 |
| performance_schema | PERFORMANCE_SCHEMA | 52 |
+--------------------+--------------------+----------+

5.7> SELECT TABLE_SCHEMA ,ENGINE ,COUNT(*) from information_schema.tables where table_schema in ('information_schema' ,'mysql','performance_schema', 'sys') group by TABLE_SCHEMA ,ENGINE;
+--------------------+--------------------+----------+
| TABLE_SCHEMA | ENGINE | COUNT(*) |
+--------------------+--------------------+----------+
| information_schema | InnoDB | 10 |
| information_schema | MEMORY | 51 |
| mysql | CSV | 2 |
| mysql | InnoDB | 19 |
| mysql | MyISAM | 10 |
| performance_schema | PERFORMANCE_SCHEMA | 87 |
| sys | NULL | 100 |
| sys | InnoDB | 1 |
+--------------------+--------------------+----------+

After 8.0:

1. Metadata exists in the table

2. All are moved to MySQL database, changed to innodb table engine, and hidden

3. Information_schema can only be viewed through view

4. All NULLs are views

5. Stored in a separate tablespace mysql.ibd

8.0> select TABLE_SCHEMA,ENGINE,count(*) from tables where TABLE_SCHEMA in ('information_schema','mysql','performance_schema','sys') group by TABLE_SCHEMA,ENGINE;
+--------------------+--------------------+----------+
| TABLE_SCHEMA | ENGINE | count(*) |
+--------------------+--------------------+----------+
| information_schema | NULL | 65 |
| mysql | InnoDB | 31 |
| mysql | CSV | 2 |
| performance_schema | PERFORMANCE_SCHEMA | 102 |
| sys | NULL | 100 |
| sys | InnoDB | 1 |
+--------------------+--------------------+----------+

Despite some improvements in 5.7, INFORMATION_SCHEMA performance continues to be a major pain point for many of our users. The key reason behind the performance issues in the current INFORMATION_SCHEMA implementation is that queries against INFORMATION_SCHEMA tables are implemented in a way that temporary tables are created during query execution.

As follows, when we query the table fragments:

5.7> explain select round(DATA_FREE/1024/1024) as DATA_FREE from information_schema.TABLES where DATA_FREE/1024/1024 > 1024 and TABLE_SCHEMA not in ('information_schema', 'mysql', 'performance_schema', 'sys');
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | TABLES | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Open_full_table; Scanned all databases |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------------------------------+

Extra information will include Open_full_table; Scanned all databases.
Skip_open_table, Open_frm_only, Open_full_table These values indicate optimization of file opening for INFORMATION_SCHEMA table queries;

Skip_open_table: The table file does not need to be opened. The information has been made available in the query by scanning the database catalog.
Open_frm_only: Only the .frm file of the table needs to be opened.
Open_full_table: Unoptimized information lookup. .frm, .MYD, and .MYI files must be opened.
Scanned N databases: refers to how many directories need to be scanned when processing information_schema queries.

If a MySQL instance has hundreds of databases, each with hundreds of tables, the INFORMATION_SCHEMA query will eventually read each individual frm file from the file system, causing a lot of I/O reads. And it also ends up consuming more CPU to open the table and prepare the associated in-memory data structures. It does try to use the MySQL server-level table cache (the table_definition_cache system variable), but in large instances there is rarely a table cache large enough to hold all the tables. Therefore, memory usage will increase dramatically, and even OOM may occur.

Usually we use the following methods to solve this problem:

1. Split the library table to reduce the number of files opened by a single instance

2. Adjust the number of table_definition_cache and table_open_cache

3. Add physical memory

After the release of MySQL 8.0, another option was provided. Since the dictionary table uses the InnoDB engine, the dictionary table can use indexes.

The following diagram explains the design differences between MySQL 5.7 and 8.0:

8.0> explain select table_name,table_rows,concat(round(DATA_LENGTH/1024/1024, 2), 'MB') as size,concat(round(INDEX_LENGTH/1024/1024, 2), 'MB') as index_size,DATA_FREE/1024/1024 AS data_free_MB from information_schema.TABLES where table_schema not in ('information_schema','performance_schema','test') order by data_free_MB desc limit 10;
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | sch | NULL | ref | PRIMARY,catalog_id | catalog_id | 8 | mysql.cat.id | 6 | 50.00 | Using where; Using index |
| 1 | SIMPLE | tbl | NULL | ref | schema_id | schema_id | 8 | mysql.sch.id | 52 | 100.00 | Using where |
| 1 | SIMPLE | ts | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.tablespace_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | stat | NULL | eq_ref | PRIMARY | PRIMARY | 388 | mysql.sch.name,mysql.tbl.name | 1 | 100.00 | NULL |
| 1 | SIMPLE | col | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.collation_id | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------------+------+----------+----------------------------------------------+

The above is a detailed explanation of the MySQL 8.0 dictionary table enhancement. For more information about MySQL 8.0 dictionary table enhancement, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

How to deal with the problem of MySQL users forgetting their passwords or password expiration
Detailed explanation of MySQL user password expiration function
MySQL password expired, resulting in failure to connect to MySQL
Descending Index in MySQL 8.0
Example of how to deploy MySQL 8.0 using Docker
In-depth explanation of binlog in MySQL 8.0
How to quickly add columns in MySQL 8.0
MySQL 8.0.21 installation tutorial with pictures and text
MySQL 8.0.21 installation tutorial under Windows system (illustration and text)
MySQL 8.0.21.0 Community Edition Installation Tutorial (Detailed Illustrations)
Detailed explanation of MySQL 8.0 password expiration policy

<<: How to add Vite support to old Vue projects

>>: Nginx configures the same domain name to support both http and https access