Understanding InnoDB physical files is crucial for optimizing your MySQL 8.0 database performance. This comprehensive guide explores each file's function, from ibdata1 to temporary tablespace.
MySQL 8.0.10 GPL came out with more changes and advanced features. We have changes on InnoDB physical file layout ( MySQL data directory ) too. This blog will provide the information about the MySQL 8 InnoDB physical files.
MySQL system tables are completely InnoDB now ?
Yes, Previously, we don’t have too many InnoDB tables on MySQL system tables. We have the innodb_index_stats, innodb_table_stats, slave_master_info, slave_relay_log_info and slave_worker_info in MySQL 5.7 as InnoDB tables. But, now all the MySQL System tables were converted to InnoDB from MySQL 8.0 . It helps in the transactional DDL’s .
Below are the list of InnoDB physical files on MySQL 8.0 .
ibdata1
.ibd
.SDI
undo_001 & undo_002
iblogfile0
iblogfile1
ib_buffer_pool
ibtmp1
MySQL 8.0 InnoDB Disk Layout Architecture
MySQL 8.0 InnoDB Disk Layout Architecture
ibdata1 (Shared Tablespace)
ibdata1 is the shared tablespace ( tablespace for all the available tables). It contains the change buffer, system tables (MySQL) , double write buffer. The undo was also a part of ibdata1 prior to MySQL 8.0, but they are moved out now (undo_001 & undo_002).Without innodb_file_per_table all the tables data and index pages will be stored in the ibdata1 . It is hard to shrink the ibdata1 with individual table space ( .ibd ), we need to perform the complete rebuilt with mysqldump / mydumper /mysqlpump (logical rebuild).
ibd ( Individual tablespace)
Each table has its own table space file under the name table_name.ibd . It was introduced in MySQL 5.1 . We need to manually enable the innodb_file_per_table variable for individual tablespace till MySQL 5.6. From MySQL 5.6, it was enabled as default. The file contains the tables data and index pages.
The individual table space adds more benefit to the database while comparing to the disadvantages. In case of partition each partition is distinguished by a ibd file and SDI files take care of the partition details.
SDI (Serialized Dictionary Information )
The SDI file provides the additional information about table metadata and table partitions details . The file is in JSON format. It replaces the older FRM files and PAR files and TRG, TRN files .
.par (file for partition structure) -frm -(file for table structure) .trg & .trn (files for triggers)
Below are the some important details we can get from SDI files .
Table schema
Table name
Partitions
Collation
Foreign key
SDI file format for the table general_log
Table Structure
mysql> show create table general_log\G
*************************** 1. row ***************************
Table: general_log
Create Table: CREATE TABLE `general_log` (
`event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumblob NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)
From MySQL 8.0, By default undo log files are separated from system tablespace (ibdata1) and located on data directory . Before MySQL 8.0 we need to manually enable the variable innodb_undo_tablespaces (introduced in MySQL 5.7) to separate the undo tablespace .
undo_001, undo_002 – Undo files are used to undo the transactions, if the transaction needs a ROLL BACK.
iblogfile (Redo log files )
MySQL maintains the redo log files for crash recovery . We can resize the redo log file by using innodb_log_file_size. Also, we can define the number of files using the variable innodb_log_files_in_group. By default we will be have 2 redo log files in the group . The log files will be sequentially writes the data . Log buffer will feed the data to redo log files.
MySQL 8.0 InnoDB Disk Layout Architecture
iblogfile0, iblogfile1 – redo log buffer will catch up the data from innodb buffer pool ( flushed data ) and written into the redo log files (disk). Those data will be helpful incase of MySQL crash happened .
With innodb_flush_log_at_trx_commit we can define , how the redo log files needs to be written. Setting innodb_flush_log_at_trx_commit = 1 is transaction safe and be more ACID complaint .
ib_buffer_pool (buffer pool dump )
MySQL will maintain the frequently access data and index pages on buffer pool. While shutting down the MySQL, all the data available on memory will be lost as it is volatile . So, there is a performance degradation post restart until the buffer pool is warmed up.
the ib_buffer_pool contains the tablespace id and the page id ( tablespace ID, page ID ) .
ib_buffer_pool Structure –
cat ib_buffer_pool | head -n5
432,3
432,1
430,3
430,1
428,3
These pages in the dump file are loaded back from disk to buffer pool while starting the MySQL again.
ibtmp1 (Innodb temporary table space )
MySQL community introduced the new file ibtmp1 ( from MySQL 5.7 ) for storing the innodb internal temporary tables. This is a shared table space for temporary tables created on disk.
By this blog I am just giving the information about the each InnoDB files located in MySQL 8.0 physical data system.
Optimize performance, enhance reliability, and ensure data security with Mydbops' MySQL InnoDB Cluster Consulting and Support Services. Our team can help you fine-tune temporary tablespace configuration, troubleshoot performance issues, and implement best practices. Contact us today to schedule a free consultation.
.png)
.png)
.svg)
