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

数据运维 2023-04-23 向阳逐梦手机阅读

InnoDB在写密集的压力时，由于B-Tree扩展，因而也会带来数据文件的扩展，然而，InnoDB数据文件扩展需要使用mutex保护数据文件，这就会导致波动。丁奇的博客说明了这个问题： When Inn

                        <p>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:</p>

复制代码代码如下: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_NONINLIndex: 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 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_list) const; void reset_item_list(List *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 InnoDB建表时设定初始大小的方法

Mysql 乘法除法精度不一致问题(除法后四位小数

mysql服务性能优化—my.cnf_my.ini配置说明详解(16G内存)

SQLServer行转列实现思路记录

怎么运行mysql数据库

oracle 查询转换简单说明4