首页 / Mysql / mysql InnoDB建表时设定初始大小的方法

mysql InnoDB建表时设定初始大小的方法

InnoDB在写密集的压力时,由于B-Tree扩展,因而也会带来数据文件的扩展,然而,InnoDB数据文件扩展需要使用mutex保护数据文件,这就会导致波动。 丁奇的博客说明了这个问题:
When InnoDB under heavy write workload, datafiles will extend quickly, because of B-Tree allocate new pages. But InnoDB need to use mutex to protect datafile, so it will cause performance jitter. Xiaobin Lin said this in his blog:
解决的方法也很简单,只要知道数据文件可能会增长到多大,预先扩展即可。阅读代码可以知道,InnoDB建表后自动初始化大小是FIL_IBD_FILE_INITIAL_SIZE这个常量控制的,而初始化数据文件是由fil_create_new_single_table_tablespace()函数控制的。所以要改变数据文件初始化大小,只要修改fil_create_new_single_table_tablespace的传入值即可,默认是FIL_IBD_FILE_INITIAL_SIZE。
How to solve it? That’s easy. If we know the datafile will extend to which size at most, we can pre-extend it. After reading source code, we can know InnoDB initial datafile size by FIL_IBD_FILE_INITIAL_SIZE, and fil_create_new_single_table_tablespace() function to do it. So if we want to change datafile initial size, we only need to change the initial size parameter in fil_create_new_single_table_tablespace(), the default value is FIL_IBD_FILE_INITIAL_SIZE.
因此,我在建表语法中加上了datafile_initial_size这个参数,例如:
CREATE TABLE test (

) ENGINE = InnoDB DATAFILE_INITIAL_SIZE=100000;
如果设定的值比FIL_IBD_FILE_INITIAL_SIZE还小,就依然传入FIL_IBD_FILE_INITIAL_SIZE给fil_create_new_single_table_tablespace,否则传入datafile_initial_size进行初始化。
So, I add a new parameter for CREATE TABLE, named ‘datafile_initial_size’. For example:
CREATE TABLE test (

) ENGINE = InnoDB DATAFILE_INITIAL_SIZE=100000;
If DATAFILE_INITIAL_SIZE value less than FIL_IBD_FILE_INITIAL_SIZE, I will still pass FIL_IBD_FILE_INITIAL_SIZE to fil_create_new_single_table_tablespace(), otherwise, I pass DATAFILE_INITIAL_SIZE value to fil_create_new_single_table_tablespace() function for initialization.
因此,这个简单安全的patch就有了,可以看 http://bugs.mysql.com/bug.php?id=67792 关注官方的进展:
So, I wrote this simple patch, see http://bugs.mysql.com/bug.php?id=67792:

InnoDB在写密集的压力时,由于B-Tree扩展,因而也会带来数据文件的扩展,然而,InnoDB数据文件扩展需要使用mutex保护数据文件,这就会导致波动。 丁奇的博客说明了这个问题:
When InnoDB under heavy write workload, datafiles will extend quickly, because of B-Tree allocate new pages. But InnoDB need to use mutex to protect datafile, so it will cause performance jitter. Xiaobin Lin said this in his blog:
解决的方法也很简单,只要知道数据文件可能会增长到多大,预先扩展即可。阅读代码可以知道,InnoDB建表后自动初始化大小是FIL_IBD_FILE_INITIAL_SIZE这个常量控制的,而初始化数据文件是由fil_create_new_single_table_tablespace()函数控制的。所以要改变数据文件初始化大小,只要修改fil_create_new_single_table_tablespace的传入值即可,默认是FIL_IBD_FILE_INITIAL_SIZE。
How to solve it? That’s easy. If we know the datafile will extend to which size at most, we can pre-extend it. After reading source code, we can know InnoDB initial datafile size by FIL_IBD_FILE_INITIAL_SIZE, and fil_create_new_single_table_tablespace() function to do it. So if we want to change datafile initial size, we only need to change the initial size parameter in fil_create_new_single_table_tablespace(), the default value is FIL_IBD_FILE_INITIAL_SIZE.
因此,我在建表语法中加上了datafile_initial_size这个参数,例如:
CREATE TABLE test (

) ENGINE = InnoDB DATAFILE_INITIAL_SIZE=100000;
如果设定的值比FIL_IBD_FILE_INITIAL_SIZE还小,就依然传入FIL_IBD_FILE_INITIAL_SIZE给fil_create_new_single_table_tablespace,否则传入datafile_initial_size进行初始化。
So, I add a new parameter for CREATE TABLE, named ‘datafile_initial_size’. For example:
CREATE TABLE test (

) ENGINE = InnoDB DATAFILE_INITIAL_SIZE=100000;
If DATAFILE_INITIAL_SIZE value less than FIL_IBD_FILE_INITIAL_SIZE, I will still pass FIL_IBD_FILE_INITIAL_SIZE to fil_create_new_single_table_tablespace(), otherwise, I pass DATAFILE_INITIAL_SIZE value to fil_create_new_single_table_tablespace() function for initialization.
因此,这个简单安全的patch就有了,可以看 http://bugs.mysql.com/bug.php?id=67792 关注官方的进展:
So, I wrote this simple patch, see http://bugs.mysql.com/bug.php?id=67792:

本文来自网络,不代表我是攻城狮立场,转载请注明出处:https://www.54gcs.cn/sql/mysql/1594.html

helwo作者

上一篇
下一篇

为您推荐

发表评论

联系我们

联系我们

在线咨询: QQ交谈

邮箱: web@5vo.cn

工作时间:周一至周五,9:00-17:30,节假日休息
关注微信
微信扫一扫关注我们

微信扫一扫关注我们

手机访问
手机扫一扫打开网站

手机扫一扫打开网站

返回顶部
跳至工具栏