Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about the disk file and disk drop mechanism of InnoDB in MySQL. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

Any   technology has its underlying key basic technologies, these key technologies are likely to be the key technologies of other technologies, learning these low-level technologies, you can understand everything, so that you can quickly master other technologies. How to store data on disk, how to use log files to ensure that the data is not lost and how to drop the disk is not only the key technology of database such as MySQL, but also one of the key technologies of MQ message queue or other middleware.

The overall architecture of InnoDB

The figure above   shows in detail the architecture of the InnoDB storage engine. As can be seen from the figure, the InnoDB storage engine consists of memory pools, background threads and disk files. Next, let's take a brief look at the concepts and principles related to disk files. The main disk files of InnoDB are mainly divided into three blocks: the first is the system table space, the second is the user table space, and the third is the redo log file and archive file. Files such as binaries (binlog) are maintained by the MySQL Server layer, so they are not included in the disk files of InnoDB. System and user tablespaces

The   InnoDB system tablespace contains the InnoDB data dictionary (metadata and related objects) and the storage area of the doublewrite buffer,change buffer,undo logs. The system tablespace also contains table and index data created by any user in the system tablespace by default. A system tablespace is a shared tablespace because it is shared by multiple tables

  system tablespaces consist of one or more data files. By default, a system data file named ibdata1 with an initial size of 10MB is created in the data directory of MySQL. Users can use innodb_data_file_path to configure the size and number of data files.

The format of   innodb_data_file_path is as follows:

Innodb_data_file_path=datafile1 [, datafile2]...

  users can form a tablespace from multiple files and define the attributes of the file at the same time:

Innodb_data_file_path = / db/ibdata1:1000M;/dr2/db/ibdata2:1000M:autoextend

  here / db/ibdata1 and / dr2/db/ibdata2 make up the system tablespace. If the two files are on different disks, the load on the disk may be evenly distributed, thus improving the overall performance of the database. The file names of both files are followed by attributes, indicating that the size of the file ibdata1 is 1000MB, the size of the file ibdata2 is 1000MB, and it can automatically grow (autoextend) after running out of space.

After   sets the innodb_data_file_path parameter, the data of tables based on InnoDB storage engine will be recorded in the system tablespace. If the parameter innodb_file_per_table is set, users can generate an independent user tablespace for each table based on InnoDB storage engine. The naming convention for user tablespaces is: table name .ibd.

  in this way, users do not have to store all the data in the default system table space, but the user table empty only stores the table's data, index, insert buffer BITMAP and other information, and the rest of the information is still stored in the default table space.

InnoDB Table Storage engine File

The figure above   shows how the InnoDB storage engine stores files, where frm files are table structure definition files that record table structure definitions for each table.

Redo log files and archive files

  by default, there are two files named ib_logfile0 and ib_logfile1 under the data directory of the InnoDB storage engine, which is InnoDB's redo log file (redo log fiel), which records the transaction log for the InnoDB storage engine.

  redo log files come in handy when there is an error in InnoDB's data storage file. The InnoDB storage engine can use redo log files to restore the data to the correct state to ensure the correctness and integrity of the data.

  each InnoDB storage engine has at least 1 redo log filegroup (group), and at least 2 redo log files under each filegroup, such as the default ib_logfile0 and ib_logfile1.

In order to achieve higher reliability,   users can set up multiple mirror log groups and put different filegroups on different disks, so as to improve the high availability of redo logs.

  has the same size for each redo log file in the log group and runs as a circular write. The InnoDB storage engine first writes to redo log file 1, then switches to redo log file 2 when the file is full, and then switches to redo log file 1 when redo log file 2 is also full.

  users can use innodb_log_file_size to set the size of the redo log file, which has a great impact on the performance of the InnoDB storage engine.

  if the setting of the redo log file is too large, it may take a long time to recover when the data is lost; on the other hand, if the setting is too small, the redo log file is too small, which will cause dirty pages to be refreshed to disk frequently according to checkpoint check, resulting in performance jitter.

The mechanisms related to   redo logs and Checkpoint can be read in the corresponding chapters of my previous article. Exploring the Secrets of MySQL (3): memory structure and characteristics of InnoDB

The downloading mechanism of redo log

  InnoDB follows WAL (Write ahead redo log) and Force-log-at-commit rules for flushing data files and log files, both of which ensure the persistence of transactions. WAL requires that before data changes are written to disk, the log in memory must be written to disk first; Force-log-at-commit requires that when a transaction commits, all generated logs must be flushed to disk. If the database downtime occurs before the data in the buffer pool is refreshed to disk after the log refresh is successful, then the database can recover data from the log when it is restarted.

Writing mechanism

  as shown in the figure above, when InnoDB changes data in the buffer pool, it will first write the relevant changes to the redo log buffer, and then write to disk on time or when the transaction commits, which is in line with the Force-log-at-commit principle. When the redo log is written to disk, the change data in the buffer pool will be written to disk according to the checkpoint mechanism, which is in line with the WAL principle.

  has the judgment that the redo log file is full in the checkpoint timing mechanism, so, as mentioned earlier, if the redo log file is too small and often full, it will frequently cause checkpoint to write the changed data to disk, resulting in performance jitter.

The file system of the   operating system is cached, and when InnoDB writes data to disk, it may just write to the cache of the file system.

The innodb_flush_log_at_trx_commit property of   InnoDB controls the behavior of InnoDB each time a transaction commits. When the attribute value is 0, when the transaction commits, it does not write to the redo log, but waits for the main thread to write on time; when the attribute value is 1, the transaction commits, the redo log is written to the file system cache, and the fsync of the file system is called to actually write the data in the file system buffer to disk storage to ensure that there is no data loss. When the property value is 2, when the transaction commits, the log file is also written to the file system cache, but instead of calling fsync, the file system is left to determine when the cache is written to disk. The flushing mechanism of the log is shown in the following figure.

Log flushing mechanism

  innodb_flush_log_at_commit is a basic parameter of InnoDB performance tuning, which concerns the write efficiency and data security of InnoDB. When the parameter value is 0, the write efficiency is the highest, but the data security is the lowest; when the parameter value is 1, the write efficiency is the lowest, but the data security is the highest; when the parameter value is 2, both are medium level. It is generally recommended that the attribute value be set to 1 for higher data security, and only if it is set to 1 to ensure the persistence of the transaction.

This is what the disk file and disk drop mechanism of InnoDB in MySQL shared by Xiaobian is. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.