mysql InnoDB建表时设定初始大小的方法
mysql InnoDB建表时设定初始大小的方法
发布时间:2016-12-29 来源:查字典编辑
摘要:InnoDB在写密集的压力时,由于B-Tree扩展,因而也会带来数据文件的扩展,然而,InnoDB数据文件扩展需要使用mutex保护数据文件...

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:

复制代码 代码如下:

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_N76333786

#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 <mysql-build@oss.oracle.com>

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> *item_list) const;

void reset_item_list(List<Item> *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)},

推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
相关阅读
网友关注
最新mysql数据库学习
热门mysql数据库学习
编程开发子分类