MySQL Data Dictionary in MySQL 8.0

In previous chapters, you learned how to use indexes and optimize complex queries. Optimization will not be complete if we do not examine the MySQL server settings and the data dictionary. This chapter explains how to get optimal MySQL server 8.0 settings, and you will learn how to work with the MySQL data dictionary.

We will cover the following topics in this chapter:

• The MySQL data dictionary structure in MySQL 8.0
• The dictionary object cache
• Transactional storage of the data dictionary
• Different uses of the data dictionary
• Removal of file-based metadata storage
• Serialized Dictionary Information (SDI)
• Limitations of the data dictionary

MySQL data dictionary structure in MySQL 8.0

We have already discussed in Chapter 2, MySQL 8's New Features, the new features of the data dictionary. Now, we'll explore its structure and limitations.

Let's summarize the problems of the MySQL data dictionary before MySQL 8.0:

• The INFORMATION_SCHEMA is slow when questioned
• Because of the storage of non-transactional metadata, there are inconsistencies
• There are inconsistencies between metadata, InnoDB, and MySQL metadata

The big challenges in MySQL replication are these:

• It's almost impossible to extend
• The API is not uniform

The following diagram shows the structure of the data dictionary before MySQL 8.0:

Let's look at the new concept of the transactional data dictionary in MySQL 8.0:

The benefits of the new MySQL 8.0 data dictionary structure include the following:

• It is based on standard SQL
• It...

Dictionary object cache

As stated in the official documentation of MySQL, the dictionary object cache is a global cache shared by MySQL 8.0 that stores previously accessed data dictionary objects in memory to enable object reuse and minimize disk I/O.

It should be mentioned that the concept is similar to other caching mechanisms used by MySQL. The cache of dictionary objects uses a control strategy based on LRU to handle the least recently used memory objects.

It also includes cache partitions that store different types of objects. Some of the limits in the cache partition size are configurable, while others are integrated.

The following is the list of the tables from information_schema:

Tablespaces

Tablespace cache partition	Schema cache partition	table cache partition
Stores table definition of objects	Stores schema definition items	Stores table definition...

Transactional storage of the data dictionary

In the past, the MySQL data dictionary was stored in the file metadata and non-transactional tables, as already indicated in previous sections, but in MySQL 8.0 we now have a transactional data dictionary to store the information on the database objects.

The main difference between the tables MySQL system and data dictionary tables is that the system tables contain auxiliary data, such as time zone and help information, while the data dictionary tables contain the data required to perform SQL queries.

That said, the transactional storage of the data dictionary stores dictionary data in tables such as InnoDB, and not MyISAM as before. These tables of data dictionaries are located in the mysql schema with the other system tables.

The dictionary data is now protected by the same validation, restoration, and recovery after an incident that...

Applications of the data dictionary

In this section, we will explore and summarize the different uses of the data dictionary.

Previously, to prevent the creation and destruction of tables or databases:

• It was necessary to enable the innodb_read_only variable for protection, which impacted only the InnoDB engine. Now, with MySQL version 8.0, all storage engines are affected.
• One of the functionalities in MySQL 8.0 has been improved, the data dictionary tables are protected and not visible, and the INFORMATION_SCHEMA is more stable.
• The tables of INFORMATION_SCHEMA are now connected directly to the data dictionary. This allows the optimizer to use indexes continuously live, resulting in better performance.
• Before MySQL 8.0, we could use mysqldump and do a MySQL export scheme. This will now not be possible and the export action will be only on schemas and tables, and not systems...

Removal of file-based storage metadata

In previous versions of MySQL, the dictionary data was partially stored in the metadata file type. Frequently encountered problems of storage related to these files, based on the type metadata, included scans of very expensive files. Thus, vulnerability to bugs related to the filesystem, complex code to handle failures for data replication and disaster recovery, and a lack of scalability made it difficult to add metadata to new features and relational objects.

Remember, in our previous section on the MySQL data dictionary structure in MySQL 8.0, we saw a graph of the data dictionary before MySQL 8.0, where the data dictionary was surrounded by auxiliary files. Now, with MySQL 8.0, the following list of files will be removed and replaced with tables:

• .frm
• .par
• .trn
• .trg
• .isl
• db.opt

It is better to have metadata managed from tables than files...

Serialized Dictionary Information (SDI)

In this chapter, we'll look at the SDI and discuss its advantages and changes.

Many DBAs and developers prefer to copy table data and .frm files from the data dictionary and schedule some batch jobs that automatically recover these tables. This ability has also been used for disaster recovery, where people who are really familiar with .frm are able to rebuild the metadata in the .frm file when they want to.

In MySQL 8.0, the information is provided in the dictionary serialized objects for the dictionary. For InnoDB tablespaces, this information is added to the tablespace, so that the metadata and data are combined, again with performance in mind. For storage engines that do not support this feature, a .sdi file is written.

This is illustrated in the following diagram:

For MySQL tablespaces, InnoDB has an API type of tool to read the...

Limitations of the data dictionary

In this section, we will look at the limitations of the data dictionary.

As we saw in the previous sections, the data dictionary of previous versions of MySQL 8.0 had many performance problems because it was all managed from files instead of tables, as Oracle has built-in MySQL 8.0.

One limitation is the need to create manual database directories in the data directory (for example, with mkdir Linux). This is not supported by MySQL. Manually created database directories are not recognized by the MySQL server.

Another limitation is that DDL type operations take longer because of the need to write to storage, the cancellation of logs, and restoring logs instead of .frm.

Tips and techniques

As already mentioned, in MySQL 8.0, Oracle introduced a new concept called the transactional data dictionary, which changed the classic view files by passing the InnoDB table types.

MySQL 8.0 is very promising regarding performance, with the new concept of the transactional data dictionary.

Here is an example:

Summary

The new transactional data dictionary in MySQL 8.0 is an incredible addition that opens the door to many future improvements in how MySQL handles DDLs, for example.

In this chapter, you have learned about the new structure of the data dictionary, with its improvements and its different uses and limitations. We must not forget that a new feature has been incorporated, called SDI, allowing for more openings toward new flexible and powerful options.

In the next chapter, we will cover all the following points by getting started with the most significant variables. We will then look at MySQL server optimization. We will discuss two important variables and introduce two case studies.