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

JS cross-domain solution react configuration reverse proxy

Cross-domain solutions jsonp (simulate get) CORS ...

The process of installing and configuring nginx in win10

1. Introduction Nginx is a free, open source, hig...

How to analyze SQL execution plan in MySQL through EXPLAIN

Preface In MySQL, we can use the EXPLAIN command ...

Vue+element+springboot to implement file download progress bar display function example

Table of contents 1. Demand Background 2. Optimiz...

Analysis of MySQL latency issues and data flushing strategy process

Table of contents 1. MySQL replication process 2....

MySQL primary key naming strategy related

Recently, when I was sorting out the details of d...

Markup Language - Phrase Elements

Click here to return to the 123WORDPRESS.COM HTML ...

Analyze the sql statement efficiency optimization issues of Mysql table reading, writing, indexing and other operations

Last time we talked about some SQL query optimiza...

The implementation process of Linux process network traffic statistics

Preface Linux has corresponding open source tools...

Vue3.0 implements the magnifying glass effect case study

The effect to be achieved is: fixed zoom in twice...

Analysis of the implementation process of three modes of VMWare network adapter

Three modes Bridged (bridge mode), NAT (network a...

MySQL backup and recovery design ideas

background First, let me explain the background. ...

Detailed explanation of how to use the Vue license plate search component

A simple license plate input component (vue) for ...

Alibaba Cloud domain name and IP binding steps and methods

1 Enter the Alibaba Cloud console, find the domai...

Binary installation of mysql 5.7.23 under CentOS7

The installation information on the Internet is u...