InnoDB建表时设定初始大小(SettingInnoDBtabledatafileini_懂视

InnoDB建表时设定初始大小(SettingInnoDBtabledatafileini

2020-11-09 13:14:12 责编:小采

InnoDB在写密集的压力时，由于B-Tree扩展，因而也会带来数据文件的扩展，然而，InnoDB数据文件扩展需要使用mutex保护数据文件，这就会导致波动。丁奇的博客说明了这个问题：http://dinglin.iteye.com/blog/1317874

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: http://dinglin.iteye.com/blog/1317874

解决的方法也很简单，只要知道数据文件可能会增长到多大，预先扩展即可。阅读代码可以知道，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:

Index: storage/innobase/dict/dict0crea.c
===================================================================
--- storage/innobase/dict/dict0crea.c	(revision 3063)
+++ storage/innobase/dict/dict0crea.c	(working copy)
@@ -294,7 +294,8 @@
 	error = fil_create_new_single_table_tablespace(
 	space, path_or_name, is_path,
 	flags == DICT_TF_COMPACT ? 0 : flags,
-	FIL_IBD_FILE_INITIAL_SIZE);
+	table->datafile_initial_size < FIL_IBD_FILE_INITIAL_SIZE ? 
+ FIL_IBD_FILE_INITIAL_SIZE : table->datafile_initial_size);
 	table->space = (unsigned int) space;
?
 	if (error != DB_SUCCESS) {
Index: storage/innobase/handler/ha_innodb.cc
===================================================================
--- storage/innobase/handler/ha_innodb.cc	(revision 3063)
+++ storage/innobase/handler/ha_innodb.cc	(working copy)
@@ -7155,6 +7155,7 @@
 	col_len);
 	}
?
+ table->datafile_initial_size= form->datafile_initial_size;
 	error = row_create_table_for_mysql(table, trx);
?
 	if (error == DB_DUPLICATE_KEY) {
@@ -7760,6 +7761,7 @@
?
 	row_mysql_lock_data_dictionary(trx);
?
+ form->datafile_initial_size= create_info->datafile_initial_size;
 	error = create_table_def(trx, form, norm_name,
 	create_info->options & HA_LEX_CREATE_TMP_TABLE ? name2 : NULL,
 	flags);
Index: storage/innobase/include/dict0mem.h
===================================================================
--- storage/innobase/include/dict0mem.h	(revision 3063)
+++ storage/innobase/include/dict0mem.h	(working copy)
@@ -678,6 +678,7 @@
 /** Value of dict_table_struct::magic_n */
 # define DICT_TABLE_MAGIC_N	76333786
 #endif /* UNIV_DEBUG */
+ uint datafile_initial_size; /* the initial size of the datafile */
 };
?
 #ifndef UNIV_NONINL
Index: support-files/mysql.5.5.18.spec
===================================================================
--- support-files/mysql.5.5.18.spec	(revision 3063)
+++ support-files/mysql.5.5.18.spec	(working copy)
@@ -244,7 +244,7 @@
 Version: 5.5.18
 Release: %{release}%{?distro_releasetag:.%{distro_releasetag}}
 Distribution: %{distro_description}
-License: Copyright (c) 2000, 2011, %{mysql_vendor}. All rights reserved. Under %{license_type} license as shown in the Description field.
+License: Copyright (c) 2000, 2012, %{mysql_vendor}. All rights reserved. Under %{license_type} license as shown in the Description field.
 Source: http://www.mysql.com/Downloads/MySQL-5.5/%{src_dir}.tar.gz
 URL: http://www.mysql.com/
 Packager: MySQL Release Engineering 
Index: sql/table.h
===================================================================
--- sql/table.h	(revision 3063)
+++ sql/table.h	(working copy)
@@ -596,6 +596,7 @@
 */
 key_map keys_in_use;
 key_map keys_for_keyread;
+ uint datafile_initial_size; /* the initial size of the datafile */
 ha_rows min_rows, max_rows;	/* create information */
 ulong avg_row_length;	/* create information */
 ulong version, mysql_version;
@@ -1094,6 +1095,8 @@
 #endif
 MDL_ticket *mdl_ticket;
?
+ uint datafile_initial_size;
+
 void init(THD *thd, TABLE_LIST *tl);
 bool fill_item_list(List *item_list) const;
 void reset_item_list(List *item_list) const;
Index: sql/sql_yacc.yy
===================================================================
--- sql/sql_yacc.yy	(revision 3063)
+++ sql/sql_yacc.yy	(working copy)
@@ -906,6 +906,7 @@
 %token DATABASE
 %token DATABASES
 %token DATAFILE_SYM
+%token DATAFILE_INITIAL_SIZE_SYM
 %token DATA_SYM /* SQL-2003-N */
 %token DATETIME
 %token DATE_ADD_INTERVAL /* MYSQL-FUNC */
@@ -5046,6 +5047,18 @@
 Lex->create_info.db_type= $3;
 Lex->create_info.used_fields|= HA_CREATE_USED_ENGINE;
 }
+ | DATAFILE_INITIAL_SIZE_SYM opt_equal ulonglong_num
+ {
+ if ($3 > UINT_MAX32)
+ {
+ Lex->create_info.datafile_initial_size= UINT_MAX32;
+ }
+ else
+ {
+ Lex->create_info.datafile_initial_size= $3;
+ }
+ Lex->create_info.used_fields|= HA_CREATE_USED_DATAFILE_INITIAL_SIZE;
+ }
 | MAX_ROWS opt_equal ulonglong_num
 {
 Lex->create_info.max_rows= $3;
@@ -12585,6 +12598,7 @@
 | CURSOR_NAME_SYM {}
 | DATA_SYM {}
 | DATAFILE_SYM {}
+ | DATAFILE_INITIAL_SIZE_SYM{}
 | DATETIME {}
 | DATE_SYM {}
 | DAY_SYM {}
Index: sql/handler.h
===================================================================
--- sql/handler.h	(revision 3063)
+++ sql/handler.h	(working copy)
@@ -387,6 +387,8 @@
 #define HA_CREATE_USED_TRANSACTIONAL (1L << 20)
 /** Unused. Reserved for future versions. */
 #define HA_CREATE_USED_PAGE_CHECKSUM (1L << 21)
+/** Used for InnoDB initial table size. */
+#define HA_CREATE_USED_DATAFILE_INITIAL_SIZE (1L << 22)
?
 typedef ulonglong my_xid; // this line is the same as in log_event.h
 #define MYSQL_XID_PREFIX "MySQLXid"
@@ -1053,6 +1055,7 @@
 LEX_STRING comment;
 const char *data_file_name, *index_file_name;
 const char *alias;
+ uint datafile_initial_size; /* the initial size of the datafile */
 ulonglong max_rows,min_rows;
 ulonglong auto_increment_value;
 ulong table_options;
Index: sql/lex.h
===================================================================
--- sql/lex.h	(revision 3063)
+++ sql/lex.h	(working copy)
@@ -153,6 +153,7 @@
 { "DATABASE",	SYM(DATABASE)},
 { "DATABASES",	SYM(DATABASES)},
 { "DATAFILE", 	SYM(DATAFILE_SYM)},
+ { "DATAFILE_INITIAL_SIZE", SYM(DATAFILE_INITIAL_SIZE_SYM)},
 { "DATE",	SYM(DATE_SYM)},
 { "DATETIME",	SYM(DATETIME)},
 { "DAY",	SYM(DAY_SYM)},

下载本文

显示全文

全部频道