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

各位用户为了找寻关于mysql InnoDB建表时设定初始大小的方法的资料费劲了很多周折。这里教程网为您整理了关于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:

 

代码如下: 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 <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)},