《Mysql學習mysql InnoDB建表時設定初始大小的方法》要點：
本文介紹了Mysql學習mysql InnoDB建表時設定初始大小的方法，希望對您有用。如果有疑問，可以聯系我們。
MYSQL教程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)},
維易PHP培訓學院每天發布《Mysql學習mysql InnoDB建表時設定初始大小的方法》等實戰技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養人才。
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/11270.html
