How to set the initial size when creating a table in mysql InnoDB

When InnoDB is writing intensive, B-Tree extension also brings about data file extension. However, InnoDB data file extension requires mutex to protect data files, this will lead to fluctuations. Ding Qi's blog illustrates this problem:
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:
The solution is also very simple, as long as you know how large the data file may grow, you can do it in advance. Read the code to understand that the automatic initialization size of InnoDB after table creation is controlled by the constant FIL_IBD_FILE_INITIAL_SIZE, and the initial data file is controlled by the fil_create_new_single_table_tablespace () function. To change the data file initialization size, you only need to modify the input value of fil_create_new_single_table_tablespace. The default value is 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.
Therefore, the datafile_initial_size parameter is added to the table syntax. For example:
Create table test (
...
) ENGINE = InnoDB DATAFILE_INITIAL_SIZE = 100000;
If the value is smaller than FIL_IBD_FILE_INITIAL_SIZE, FIL_IBD_FILE_INITIAL_SIZE is still input to fil_create_new_single_table_tablespace; otherwise, datafile_initial_size is input for initialization.
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 partition (), otherwise, I pass DATAFILE_INITIAL_SIZE value to partition () function for initialization.
Therefore, this simple and secure patch has, can you look at the http://bugs.mysql.com/bug.php? Id = 67792 follow the official progress:
So, I wrote this simple patch, see http://bugs.mysql.com/bug.php? Id = 67792:

Copy codeThe Code is as follows:
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,201 1, % {mysql_vendor}. All rights reserved. Under % {license_type} license as shown in the Description field.
+ License: Copyright (c) 2000,201 2, % {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;
@-Random + random @@
# 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 )},