# InnoDB实现独立表空间多数据文件 (InnoDB multiple datafiles per single-tablespace)

As we know that Oracle can let one tablespace contains many datafiles, so file IO can distribute to multiple storage paths. Most of MySQL servers will not use multiple storage paths, but many old filesystems can’t support large files well, if datafiles too larger, performance will be lower, such as EXT3. So keeping MySQL/InnoDB datafiles size in a relatively small range is beneficial, too.

InnoDB在共享表空间模式下，是支持多文件的，用innodb_data_file_path选项可以配置：

InnoDB supports multiple datafiles in the shared tablespace mode, and we can configure it with innodb_data_file_path:

innodb_data_file_path = /disk1/ibdata1:2G;/disk2/ibdata2:2G:autoextend

Configuring innodb_data_file_path like this, InnoDB can distribute the two datafiles to disk1 & disk2. The first file is fiexed size, 2GB. And the second file is auto extened, initial size id 2GB.

But if innodb_file_per_table = TRUE, each tables will have their single tablespace datafile, and innodb_data_file_path only used for system-tablespace, single-tablespace can’t use mutiple datafiles. Even thought each tables will have one single datafile, file maybe become very large, too.

Of course, we can split databases / tables, or use partition, it can let datafiles become small. But most of small companies haven’t middleware to split these, and they also have many big tables. So it’s best for them to use mutile datafiles per single-tablespace feature.

How to implement multiple datafiles per single-tablespace feature with modifying source code as little as possible? I found something useful through research, InnoDB haven’t do many special judge for shared/single-tablesapce, and tablespace descriptor is the same for them (fil_space_t). And fil_space_t->chain (fil_node_t) is the list of the files belong to this tablespace.

Especially when I saw this comment:

  /* TODO: The following code must change when InnoDB supports multiple datafiles per tablespace. */

I think InnoDB team want to do it, too. And they are already do enough preparation when they code. So I’m sure I can implement this feature.

And then I try to modify code on MySQL 5.6 source code, I found a practical way, I’m coding with this design:

CREATE TABLE语法新增两个参数：DATAFILE_INITIAL_SIZE, DATAFILE_NUM，分别表示数据文件初始大小和数据文件数量。

I added two options in CREATE TABLE syntax: DATAFILE_INITIAL_SIZE & DATAFILE_NUM. They represent the initial size of the data files and the number of data files.

 CREATE TABLE table_name (...) ENGINE=InnoDB DATAFILE_INITIAL_SIZE=1000000, DATAFILE_NUM=100;

This SQL will let MySQL create a table with 100 datafiles, and each datafiles have 1000000 pages. The auto created datafiles named “table_name#num.ibd” in the default datadir. Allowed to contain up to 255 data files, each datafiles are fixed size. If you want to add datafile after created table, you need to use ALTER TABLESPACE command.

 ALTER TABLESPACE db_name/table_name ADD DATAFILE '/diskN/table_name#256' INITIAL_SIZE = 5000 AUTOEXTEND_SIZE=1000 ENGINE=InnoDB;

This SQL will add a datafile for db_name.tablename, datafile path is “/diskN/table_name#256.ibd” (suffix .ibd is added automatically), initial size is 5000 pages, each autoextend operation will extend 1000 pages.

1. 在I_S表中的tables表增加data_file_path字段，用于展示表的数据文件位置和大小，类似innodb_data_file_path中共享表空间的记录方式。

1. Adding a column named “data_file_path” on “I_S.tables” table to record the datafiles path and size, like “innodb_data_file_path” option.

2. 在数据目录下，增加table_name.dbf文件，为每张表持久化类似innodb_data_file_path字段的数据文件路径信息。

2. Adding a “table_name.dbf” file for recording datafiles information in datadir. The format like “innodb_data_file_path” option.

3. 在 fil_space_t 结构体中增加三个字段，跟InnoDB全局变量中定义的含义一样，分别用于记录属于表空间的数据文件数量，数据文件名，数据文件大小。

3. Adding 3 variables in fil_space_t, the meaning like InnoDB global variables “srv_n_data_files, srv_data_file_names, srv_data_file_sizes”, but they for each tablespace here.

  ulint n_data_files; /* The number of datafiles */ char** data_file_names; /* Every datafiles' name */ ulint* data_file_sizes; /* Every datafiles' size */

4. 增加 srv_ibd_file_initial_size 全局变量，默认等于 FIL_IBD_FILE_INITIAL_SIZE，建表时如果设置了DATAFILE_INITIAL_SIZE选项，并且这个选项 > FIL_IBD_FILE_INITIAL_SIZE，则创建表时用 srv_ibd_file_initial_size 作为初始化大小，这样某些已知会很大的表可以预先扩展，避免未来高速写入时出现扩展问题。

Adding “srv_ibd_file_initial_size” global variable. Its default value is FIL_IBD_FILE_INITIAL_SIZE. If you set DATAFILE_INITIAL_SIZE on “CREATE TABLE”, and the value > FIL_IBD_FILE_INITIAL_SIZE, then table datafile initial size will set to srv_ibd_file_initial_size. So if you know a table will be very large, you can set this option to pre-extend datafile size, it can avoid extend datafile operation when insert heavy workload.

5. 增加 fil_create_new_datafile_for_single_table_tablesapce() 函数，增加新的数据文件时调用这个函数，会用 os_file_create() 来创建新文件，并用 os_file_set_size() 设置大小，然后用 fil_node_create() 创建node加入 fil_space_t->chain，并更新 fil_space_t->n_data_files/data_file_names/data_file_sizes 三个变量。

Adding fil_create_new_datafile_for_single_table_tablesapce() function, it can add a new datafile for single-tablespace. It will call os_file_create() to create new file, and call os_file_set_size() to set size, and then call fil_node_create() to create a “node”, this “node” will add to fil_space_t->chain. fil_space_t->n_data_files/data_file_names/data_file_sizes will be updated in the same time.

6. InnoDB启动时在 open_or_create_data_files() 函数中增加检查步骤，查看是否有 table_name.dbf 文件，如果有则读取其中字串，复用共享表空间的处理代码，将解析结果存入表空间结构体 fil_space_t->n_data_files/data_file_names/data_file_sizes。

Adding some process in InnoDB startup function, open_or_create_data_files(). I will check if “table_name.dbf” file is existed, if it’s existed, I will read the string from it. I will use the code that parse “innodb_data_file_path” string, and storing the result to fil_space_t->n_data_files/data_file_names/data_file_sizes.

Code will be released & published in recently.

04-25 3万+
05-22 1万+
08-02 5689
09-25 1019