Detailed explanation of MySQL 8.0 dictionary table enhancement

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

Recommend

How to set Tomcat as an automatically started service? The quickest way

Set Tomcat to automatically start the service: I ...

Detailed example of deploying Nginx+Apache dynamic and static separation

Introduction to Nginx dynamic and static separati...

CSS to achieve pop-up window up and down center left and right and transparent background lock window effect

There is a simple CSS method to realize the pop-u...

An audio-visual Linux distribution that appeals to audiophiles

I recently stumbled upon the Audiovisual Linux Pr...

About the problem of offline installation of Docker package on CentOS 8.4

The virtual machine used is CentOS 8.4, which sim...

Summary of B-tree index knowledge points in MySQL optimization

Why do we need to optimize SQL? Obviously, when w...

JS deep and shallow copy details

Table of contents 1. What does shallow copy mean?...

Solve the problems encountered when installing MySQL 8.0 on Win10 system

The problems and solutions encountered when insta...

Installing Windows Server 2008 operating system on a virtual machine

This article introduces the installation of Windo...

MySQL quickly inserts 100 million test data

Table of contents 1. Create a table 1.1 Create te...

Vue implementation example using Google Recaptcha verification

In our recent project, we need to use Google robo...

Complete Tutorial on Deploying Java Web Project on Linux Server

Most of this article refers to other tutorials on...

Detailed explanation of replace into example in mysql

Detailed explanation of replace into example in m...

Let you understand how HTML and resources are loaded

All content in this blog is licensed under Creati...

Version numbers in css and js links in HTML (refresh cache)

background Search the keyword .htaccess cache in ...