InnoDB physical files on MySQL 8.0

Introduction –

              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 file system 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)
SDI Structure –
 cat general_log_195.sdi {"mysqld_version_id":80011,"dd_version":80011,"sdi_version":1,"dd_object_type":"Table","dd_object":{"name":"general_log","mysql_version_id":80011,"created":20180723063147,"last_altered":20180723063147,"hidden":1,"options":"avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;","columns":[{"name":"event_time","type":18,"is_nullable":false,"is_zerofill":false,"is_unsigned":false,"is_auto_increment":false,"is_virtual":false,"hidden":1,"ordinal_position":1,"char_length":26,"numeric_precision":0,"numeric_scale":0,"numeric_scale_null":true,"datetime_precision":6,"datetime_precision_null":0,"has_no_default":false,"default_value_null":false,"srs_id_null":true,"srs_id":0,"default_value":"AAAAAAAAAA==","default_value_utf8_null":false,"default_value_utf8":"CURRENT_TIMESTAMP(6)","default_option":"CURRENT_TIMESTAMP(6)","update_option":"CURRENT_TIMESTAMP(6)","comment":"","generation_expression":"","generation_expression_utf8":"","options":"interval_count=0;","se_private_data":"","column_key":1,"column_type_utf8":"timestamp(6)","elements":[],"collation_id":8,"is_explicit_collation":false},{"name":"user_host","type":25,"is_nullable":false,"is_zerofill":false,"is_unsigned":false,"is_auto_increment":false,"is_virtual":false,"hidden":1,"ordinal_position":2,"char_length":8,"numeric_precision":0,"numeric_scale":0,"numeric_scale_null":true,"datetime_precision":0,"datetime_precision_null":1,"has_no_default":true,"default_value_null":false,"srs_id_null":true,"srs_id":0,"default_value":"AAAAAAAAAAAAAAA=","default_value_utf8_null":true,"default_value_utf8":"","default_option":"","update_option":"","comment":"","generation_expression":"","generation_expression_utf8":"","options":"interval_count=0;","se_private_data":"","column_key":1,"column_type_utf8":"mediumtext","elements":[],"collation_id":33,"is_explicit_collation":false},{"name":"thread_id","type":9,"is_nullable":false,"is_zerofill":false,"is_unsigned":true,"is_auto_increment":false,"is_virtual":false,"hidden":1,"ordinal_position":3,"char_length":21,"numeric_precision":20,"numeric_scale":0,"numeric_scale_null":false,"datetime_precision":0,"datetime_precision_null":1,"has_no_default":true,"default_value_null":false,"srs_id_null":true,"srs_id":0,"default_value":"AAAAAAAAAAA=","default_value_utf8_null":true,"default_value_utf8":"","default_option":"","update_option":"","comment":"","generation_expression":"","generation_expression_utf8":"","options":"interval_count=0;","se_private_data":"","column_key":1,"column_type_utf8":"bigint(21) unsigned","elements":[],"collation_id":33,"is_explicit_collation":false},{"name":"server_id","type":4,"is_nullable":false,"is_zerofill":false,"is_unsigned":true,"is_auto_increment":false,"is_virtual":false,"hidden":1,"ordinal_position":4,"char_length":10,"numeric_precision":10,"numeric_scale":0,"numeric_scale_null":false,"datetime_precision":0,"datetime_precision_null":1,"has_no_default":true,"default_value_null":false,"srs_id_null":true,"srs_id":0,"default_value":"AAAAAA==","default_value_utf8_null":true,"default_value_utf8":"","default_option":"","update_option":"","comment":"","generation_expression":"","generation_expression_utf8":"","options":"interval_count=0;","se_private_data":"","column_key":1,"column_type_utf8":"int(10) unsigned","elements":[],"collation_id":33,"is_explicit_collation":false},{"name":"command_type","type":16,"is_nullable":false,"is_zerofill":false,"is_unsigned":false,"is_auto_increment":false,"is_virtual":false,"hidden":1,"ordinal_position":5,"char_length":192,"numeric_precision":0,"numeric_scale":0,"numeric_scale_null":true,"datetime_precision":0,"datetime_precision_null":1,"has_no_default":true,"default_value_null":false,"srs_id_null":true,"srs_id":0,"default_value":"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==","default_value_utf8_null":true,"default_value_utf8":"","default_option":"","update_option":"","comment":"","generation_expression":"","generation_expression_utf8":"","options":"interval_count=0;","se_private_data":"","column_key":1,"column_type_utf8":"varchar(64)","elements":[],"collation_id":33,"is_explicit_collation":false},{"name":"argument","type":25,"is_nullable":false,"is_zerofill":false,"is_unsigned":false,"is_auto_increment":false,"is_virtual":false,"hidden":1,"ordinal_position":6,"char_length":8,"numeric_precision":0,"numeric_scale":0,"numeric_scale_null":true,"datetime_precision":0,"datetime_precision_null":1,"has_no_default":true,"default_value_null":false,"srs_id_null":true,"srs_id":0,"default_value":"AAAAAAAAAAAAAAA=","default_value_utf8_null":true,"default_value_utf8":"","default_option":"","update_option":"","comment":"","generation_expression":"","generation_expression_utf8":"","options":"interval_count=0;","se_private_data":"","column_key":1,"column_type_utf8":"mediumblob","elements":[],"collation_id":63,"is_explicit_collation":true}],"schema_ref":"mysql","se_private_id":18446744073709551615,"engine":"CSV","comment":"General log","se_private_data":"","row_format":2,"partition_type":0,"partition_expression":"","partition_expression_utf8":"","default_partitioning":0,"subpartition_type":0,"subpartition_expression":"","subpartition_expression_utf8":"","default_subpartitioning":0,"indexes":[],"foreign_keys":[],"partitions":[],"collation_id":33}}

undo_ ( UNDO log files )

                   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.

Redo log - MySQLiblogfile0, 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.

From MySQL 5.6, we have the variables  innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup to dump and load the memory pages while restart 

Also, innodb_buffer_pool_dump_pct will define the percentage of memory pages needs to be dump and load . We can also dump the memory pages at any time by using the variable innodb_buffer_pool_dump_now.

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.

We can define the file path and size by using the variable innodb_temp_data_file_path and innodb_tmpdir .

ibtmp1 – Innodb temporary table file for SELECT’s

Conclusion –

By this blog I am just giving the information about the each InnoDB files located in MySQL 8.0 physical data system. 

Image Courtesy : Photo by chuttersnap on Unsplash

Advertisements

One thought on “InnoDB physical files on MySQL 8.0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s