InnoDB Storage Engine tablespace file, redo log file

Storage Engine files: Because of the relationship of the MySQL table storage engine, each storage engine will have its own files to hold various data. These storage engines really store data such as data and indexes .

Table Space Files

The InnoDB storage engine mimics Oracle on the storage design, storing the stored data as a tablespace . In the default configuration, there is a file that is initialized with a size of 10MB and is named Ibdata1. The file is the default tablespace file (tablespace files). You can set it by using the parameter innodb_data_file_path . The format is as follows:

innodb_data_file_path=datafile_spec1[;d ATAFILE_SPEC2] ...

You can also make a table space with multiple files, and also make the properties of the file, such as:

innodb_data_file_path=/db/ibdata1:2000m;/dr2/db/ibdata2:2000m:autoextend

Here,/db/ibdata1 and/dr2/db/ibdata2 two files are used to form a tablespace. If these two files are on different disks, you can improve performance to a certain extent. The file name of the two files followed by the property, indicating that the file idbdata1 size is 2000MB, the file ibdata2 size is 2000MB, but if the 2000MB is full, the file can automatically grow (autoextend).

After you set the Innodb_data_file_path parameter, the data for all tables that are based on the InnoDB storage engine are recorded in the file. By setting the parameter innodb_file_per_table, we can create a single table space for each table based on the InnoDB storage engine, with the file name table name. IBD, so you don't have to store all your data in the default tablespace. Below this server set up innodb_file_per_table, you can see:

Show variables like ' innodb_file_per_table ';

System LS-LH /usr/local/var/mysql/*

Table User,zcy_config and so on are all InnoDB storage engines, resulting in a separate. IBD tablespace file due to the setup parameters innodb_file_per_table=on. It is important to note that these individual tablespace files store only information such as data, indexes, and insert buffers for the table, and the rest of the information is stored in the default table space.

InnoDB storage engine for how files are stored:

Redo log Files

By default, there are two files, named Ib_logfile0 and Ib_logfile1, respectively. The MySQL official manual is called the log file for the InnoDB storage engine, but a more accurate definition should be redo log files (redo log file). Why is the Redo log file emphasized? Because redo log files are critical to the InnoDB storage engine, they record the transaction log for the InnoDB storage engine .

The primary purpose of the Redo log file is to make the redo log file useful in the event of an instance or media failure (media failure). If the database fails due to a host power-down, the InnoDB storage engine uses redo logs to restore the data to the point in time before the power-down is ensured.

Each InnoDB storage engine has at least 1 redo log file groups (group), with at least 2 redo log files under each filegroup, such as the default IB_LOGFILE0, Ib_logfile1. For greater reliability, you can set up multiple mirror log groups (mirrored log groups) to place different filegroups on different disks. Each redo log file in the log group is the same size and is used in a circular fashion. The InnoDB storage engine first writes redo log file 1, when the end of the file is reached, it switches to redo log file 2, and when the redo log file 2 is also full, it is switched to redo log file 1.

A redo log filegroup with 3 redo log files:

Parameters innodb_log_file_size,innodb_log_files_in_group,innodb_mirrored_log_groups, The Innodb_log_group_home_dir effect focuses on the properties of the log file.

Parameter innodb_log_file_size Specifies the size of the redo log file;

Innodb_log_files_in_group Specifies the number of redo log files in the log file group, which defaults to 2;

INNODB_MIRRORED_LOG_GROUPS Specifies the number of log mirror filegroups, which defaults to 1, representing only one log file group, without mirroring;

INNODB_LOG_GROUP_HOME_DIR specifies the path of the log file group, which is by default in the database path. The following shows a configuration for the Redo log group:

Show variables like ' innodb%log% ';

The Redo log file size setting has an impact on all aspects of the MySQL database. On the one hand cannot be set too large, if set very large, may take a long time to recover, on the other hand cannot be too small, otherwise may cause a transaction log to need to switch redo log file multiple times.

You may see the following warning in the error log:

090924  One： the： -InnoDB:ERROR:the Age ofThe lastCheckpoint  is 9433712, Innodb:which exceeds theLog GroupCapacity9433498. InnoDB:IfYou are using the big BLOBor TEXTRows,you mustSettheinnodb:combined Size of LogFiles at leastTenTimes bigger than theinnodb:largest such row.090924  One： +：xxInnoDB:ERROR:the Age ofThe lastCheckpoint  is 9433823, Innodb:which exceeds theLog GroupCapacity9433498. InnoDB:IfYou are using the big BLOBor TEXTRows,you mustSettheinnodb:combined Size of LogFiles at leastTenTimes bigger than theinnodb:largest such row.090924  One： +： -InnoDB:ERROR:the Age ofThe lastCheckpoint  is 9433645, Innodb:which exceeds theLog GroupCapacity9433498. InnoDB:IfYou are using the big BLOBor TEXTRows,you mustSettheinnodb:combined Size of LogFiles at leastTenTimes bigger than theinnodb:largest such row.

The above errors focus on InnoDB:ERROR:the age of the last checkpoint is 9433645,innodb:which exceeds the log group capacity 9433498. This is because the redo log has a capacity variable that indicates that the last checkpoint cannot exceed this threshold , and if it is exceeded, a portion of the dirty data page in the Refresh list (InnoDB buffer pool) must be written back to disk.

Since the transaction log is also logged, what is the difference between our previous binary logs? First, the binary log records all mysql-related logging, including InnoDB, MyISAM, heap, and other storage engine logs. The redo log of the InnoDB storage engine only records the transaction log about itself. Second, the content of the record is different, regardless of whether you set the binary log file format to statement or row, or mixed, it records the specific operation of a transaction. The InnoDB storage engine's redo log files record the physical condition of the changes on each page (page) , as shown in table 3-2. In addition, the write time is different, the binary log file is recorded before the transaction commits, and during the process of the transaction, there are constantly redo log entries (redo entry) are written to the redo log file.

Instead of writing the redo log file directly, write a redo log buffer (redo log buffer), and then write the log file according to certain criteria.

Figure 3-3 is a good indication of this process:

As mentioned above, the redo log files written from the log buffer to disk are conditionally, what are these conditions?

The redo log buffers are written to the disk's redo log file every second in the main thread, regardless of whether the transaction has been committed.

Another trigger this process is controlled by the parameter innodb_flush_log_at_trx_commit , which indicates how the redo log is handled when the commit operation is committed.

The parameter innodb_flush_log_at_trx_commit can be set to a value of 0, 1, 2.

0 means that when a transaction is committed, the redo log of the transaction is not written to the log file on disk, but instead waits for the main thread to refresh every second.

1 is to write the redo log buffer synchronously to disk when commit;

2 is the Redo log writes asynchronously to the disk, that is, the commit will not be fully guaranteed to write redo log files, only this action.

InnoDB Storage Engine tablespace file, redo log file