Learn InnoDB tablespace

[[408831]]

This article is reprinted from the WeChat public account "SH's Full Stack Notes", author SH's Full Stack Notes. Please contact SH's Full Stack Notes public account for reprinting this article.

I thought about the title for a long time, but forget it, let’s just leave it like this.

This should be the most basic part of MySQL principle. What does the data stored in MySQL look like on disk? You may say, isn't the data stored in the clustered index? But unfortunately, you didn't answer my question. I will ask you again, what does the clustered index look like on disk?

Just like Redis's RDB file, the dump.rdb file is actually stored on the disk, but MySQL is a bit confusing. We only know how to get data through SQL, but we don't know how the data is stored. Of course, understanding its underlying storage logic is not just to satisfy curiosity.

The underlying storage method will affect the storage of data in the clustered index, and further affect the DML (Data Manipulation Language) performance of MySQL. Therefore, having a clear understanding of the underlying storage logic can help us optimize MySQL in certain scenarios where performance is extremely important.

What does a table look like on disk?

First, let's not talk about professional terms like tablespaces. Let's create a table and look at the disk structure. First, you need to find the MySQL data directory. If you use Docker to start, this directory will look like this:

 /data00/docker/volumes/ef876f70d5f5c95325c2a79689db79cc4d1cecb7d96e98901256bd49ca359287/_data

Then we create a new DB called test, and then there will be an additional test directory under the _data directory. Then we create a new student table under the test database, and there will be two more files under the test directory, namely student.frm and student.ibd.

It can be found that the final macro representation of the data on the disk is actually very simple. There are only a few files, and indexes and pages are ignored for now.

What is in the .frm file? It contains the metadata of each table, including the structure definition of the table. The .ibd file stores the data and index of the table.

I saw someone write .ibd as .idb in a blog... Although db looks more convenient, it is unfortunately not correct. If you remember the full name of ibd, innodb data, you will not make a mistake in the abbreviation.

The ibd file named after the table mentioned above actually has a professional term called tablespace.

As the name suggests, it can be understood as a space exclusive to my watch.

Understanding tablespaces

If I tell you right away that there is a concept called tablespace in InnoDB, you will probably find it difficult to understand.

As described above, each table has its own separate data storage file, which is called an exclusive tablespace; correspondingly, InnoDB also has its own system tablespace, in which the data of all tables are stored in the same file.

So when is data stored in the system tablespace and when is it stored in the exclusive tablespace?

This can be determined by the MySQL configuration item innodb_file_per_table. When this configuration item is turned on, each table will have its own separate tablespace; on the contrary, when this configuration item is turned off, the table data will be stored in the system tablespace.

This configuration item is enabled by default. You can view the status of this variable in MySQL by running the command SHOW VARIABLES LIKE 'innodb_file_per_table'.

In fact, since MySQL uses exclusive tablespace as the default setting, you should know that the performance of exclusive tablespace is definitely better than that of system tablespace.

Because for the system tablespace, there is usually only one file, and all table data is in this file. If we perform a TRUNCATE operation on a table, we need to delete the data scattered in various places in the file. First of all, this will not perform well. Secondly, the TRUNCATE operation will generate a lot of free fragmented space in the file and will not reduce the size of the shared tablespace file ibdata1.

If you don't understand, you can imagine the mark-sweep garbage collection algorithm in Java. This algorithm will cause a lot of memory fragmentation during cleaning, which is not conducive to improving memory utilization in the later stage.

For an exclusive tablespace, the data of the entire table is stored in only one file from beginning to end. It is obvious that it is easier to clean up and free up disk space than a shared tablespace. Therefore, the performance of TRUNCATE will be better for an exclusive tablespace.

In addition, exclusive tablespaces can increase the maximum capacity limit of a single table. This may not be easy to understand. Why does exclusive tablespaces have this effect? ​​Here you only need to remember this conclusion. When we talk about page-related things later, we will provide specific arguments.

After understanding the concept of tablespace, we can continue to understand in depth how data is stored in the tablespace.

Diving into tablespace files

In fact, I mentioned it a long time ago when I talked about the memory architecture of InnoDB that in InnoDB, pages are the smallest unit of data management. So it makes sense that we should start from the smallest part, but I have already written an article to discuss pages, so I will not repeat it here.

The table space consists of a bunch of **Pages**, and the size of each page is equal. The default page size is 16K, of course, this size can be adjusted.

The page size can be adjusted according to the actual business situation through the configuration item innodb_page_size. The available sizes are 4K, 8K, 16K, 32K and 64K.

A bunch of pages are grouped together to form extents.

The size of each extent is fixed. When we set different innodb_page_size, the number of pages contained in each extent (Extents) and the corresponding fixed extent size are different, as shown in the following figure.

When innodb_page_size is 4K, 8K, or 16K, the corresponding extent size is 1M; when the page size is 32K, the extent size is 2M; when the page size is 64K, the extent size is 4M.

MySQL 5.6 actually only supports 4K, 8K and 16K. As for the 32K and 64K mentioned above, they were added after MySQL 5.7.6.

As the page and extent sizes change, the number of pages that can be accommodated in each extent will also change. For example, when the value of innodb_page_size is 16K, each extent contains 64 pages; when it is 8K, each extent contains 128 pages; when it is 4K, each extent contains 256 pages.

As mentioned above, pages of data make up zones, and zones make up segments.

Logically, the InnoDB tablespace is composed of such segments one by one. When new space is needed as the amount of data continues to grow, InnoDB will first request 32 pages, and then directly allocate an entire extent. Even in a large segment, 4 extents will be allocated at once.

By default, InnoDB allocates two segments to each index, one for storing non-leaf nodes in the index and the other for storing leaf nodes.

Classification of tablespaces

The above roughly introduces two types of tablespaces, namely system tablespace and exclusive tablespace. Next, you need to understand the details of each tablespace classification in detail.

System tablespace

When we enable the innodb_file_per_table configuration item (which is enabled by default), the system tablespace is only used to store data related to the Change Buffer. When we disable it, the system tablespace stores data related to tables and indexes. Of course, before MySQL 8.0, the exclusive tablespace also contained the Double Write Buffer, but it was moved out after MySQL 8.0.20 and stored in a separate file.

By default, the system tablespace will only have one data file named ibdata1. Of course, it is allowed to have multiple files. All the properties including file name and file size are set by the configuration item innodb_data_file_path. For example:

 innodb_data_file_path=ibdata1:10M:autoextend

Here, the file name of the system tablespace is ibdata1, and the initial size is 10M. Do you know what autoextend is?

As mentioned earlier, the initial size is 10M. As MySQL runs, the amount of data will slowly grow, and the data file must apply for more space to store data. If autoextend is defined, InnoDB will automatically expand the data file for us, applying for 8M of space each time. Of course, this 8M can also be configured, and we can configure it through the configuration item innodb_autoextend_increment.

Exclusive tablespace

This has been introduced above, so I will just briefly summarize it here. When the configuration item innodb_file_per_table is turned on (now it is turned on by default), the data of each table will be stored in its own separate data file.

General tablespace

You don't need to understand this for now. Just know that the regular tablespace is similar to the system tablespace and is also a shared storage space.

Undo Tablespace

Undo Logs are mainly stored here. With Undo Logs, we can quickly roll back changes after a transaction error. InnoDB will create two data files for the Undo tablespace by default. If not specified, the file names are undo_001 and undo_002 by default.

As for the specific storage path of these two data files, you can specify it through the configuration item innodb_undo_directory. Of course, if it is not specified, the data files of the Undo tablespace will be placed in the default data directory of InnoDB, usually /usr/local/mysql.

The initial sizes of the two Undo tablespace data files were determined by the InnoDB page size before MySQL 8.0.23, as shown in the following figure:

After MySQL 8.0.23, the initial size of the Undo tablespace is 16M. As for the expansion of the Undo tablespace, different versions have different processing methods.

Prior to MySQL 8.0.23, each expansion required applying for 4 extensions. According to the previous discussion, if the page size is 16K, then the corresponding extension is 1M. In other words, each expansion required applying for 4M of space. Of course, the specific size will vary depending on the page size. This has been mentioned above and will not be repeated here.

After MySQL 8.0.23, the space must be expanded by at least 16 MB each time. In order to prevent the explosive growth of data volume, InnoDB will make a dynamic adjustment to the expanded capacity.

If the time difference between this expansion and the last expansion is less than 0.1 seconds, the expanded space will be doubled, that is, it will become 32M. If the time difference between multiple expansions is less than 0.1 seconds, this doubling operation will accumulate until the upper limit of 256M is reached; then you may ask, what if the request volume is large during a period of time, so that the expanded capacity reaches the maximum of 256M, and the subsequent request volume decreases? Should we still apply for 256M? This is not reasonable. Therefore, if InnoDB determines that the interval between two expansions is greater than 0.1 seconds, it will halve the expanded capacity until it is reduced to the minimum limit of 16M.

Temporary tablespace

The data in the temporary tablespace is temporary, as the name implies.

You are talking bullshit...

It is divided into two parts:

• Session temporary tablespace
• Global temporary tablespace

For the Session temporary tablespace, temporary tables created by users or optimizers are stored in it. For each Session, InnoDB will allocate up to two data files (tablespaces), which are used to store temporary tables created by users and internal temporary tables created by the optimizer. When the Session expires, these allocated data files will be truncated and placed in a data file pool.

This operation is actually no different from other pooling technologies. It is worth noting that the size of these files will not change after being truncated. This data file pool will be created when the MySQL server is started, and 10 files will be thrown into it by default, and the default size of each file is 5 pages.

As for the global temporary tablespace, it will store the rollback segment (Rollback Segment) that has made changes to the temporary table. Its initial size is about 12M and it will also be created when the MySQL server is started.