我们知道,在Oracle中,每个表空间都可以由很多文件组成,这样文件的IO就可以分散在很多存储路径上。虽然MySQL的服务器一般来说不会配置多路径存储,但是,很多老式文件系统(例如EXT3)对大文件的IO操作支持不好,性能很差,所以对MySQL/InnoDB来说,把数据文件大小控制在比较小的范围,也是有好处的。
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
这样配置就把数据文件分散在了disk1和disk2两个路径下,第一个文件固定2G大小,第二个文件初始化2G,可以自增长。
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.
但是如果设置了 innodb_file_per_table 选项,每张表都会有一个独立的表空间文件,就不能再对每个独立表空间使用多数据文件了。但是,即使每张表分配一个独立的文件,还是可能有某些表变得非常大,例如我们就有几百GB的表,在XFS文件系统上这还没什么问题,有些系统为了“安全”依然使用EXT3,大文件的操作性能就堪忧了。
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.
如何通过尽可能少的改动,来为InnoDB独立表空间也增加多数据文件的功能呢,经过一段时间调研,可以发现,大多数地方,InnoDB并没有用特别的方式来判断是独立表空间还是共享表空间,并且表空间描述符并没有因共享表空间还是独立表空间而有差异,都是使用 fil_space_t,并且其中 fil_space_t->chain 就是记录从属于表空间的所有文件,用 fil_node_t 描述。
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. */
我觉得InnoDB团队在开发时,也已经考虑到了未来需要增加表空间多文件的支持,更让我确信这是可以实现的。
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.
因此基于5.6的源码树修改代码测试,我觉得如下思路是靠谱的,正按着这个方案重新整理代码:
And then I try to modify code on MySQL 5.6 source code, I found a practical way, I’m coding with this design:
用户接口 (User Interface):
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;
这样就会建100个包含1000000个页面的文件,命名方式采用 “table_name#num.ibd”,都建在默认数据目录下,最多允许初始化255个文件,每个文件都是固定大小,如果还需要增加文件,需要使用ALTER TABLESPACE命令。
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;
这个命令会为db_name下的table_name表增加一个数据文件,位置在”/diskN/table_name#256.ibd”(后缀自动加),初始大小为5000个页面,每次自动扩展1000个页面。
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.
阅读全文…