mysql InnoDB Method of setting the initial size when a table is built
- 2020-06-07 05:25:27
- OfStack
When InnoDB is under heavy write pressure, the ES2en-ES3en extension also leads to data file extensions, however, InnoDB data file extensions require mutex to protect data files, which can lead to fluctuations. Dinky's blog illustrates the point:
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 as simple as knowing how big the data file is likely to grow and extending it up front. As you can see from the code, when InnoDB builds the table, the automatic initialization size is controlled by the constant FIL_IBD_FILE_INITIAL_SIZE, and the initialization data file is controlled by the fil_create_new_single_table_tablespace() function. So to change the data file initialization size, simply change the incoming value of fil_create_new_single_table_tablespace, which defaults to 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, I added the parameter datafile_initial_size to the syntax, such as:
CREATE TABLE test (
...
) ENGINE = InnoDB DATAFILE_INITIAL_SIZE=100000;
If the value set is smaller than FIL_IBD_FILE_INITIAL_SIZE, then FIL_IBD_FILE_INITIAL_SIZE to ES54en_ES55en_single_ES58en_size is passed in for initialization.
So, I add parameter for CREATE TABLE '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.
Therefore, the simple and safe patch have, we can see http: / / bugs mysql. com/bug php? Keep an eye on official progress:
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)},