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 <[e-mail]>
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)},