mysql InnoDB建表時(shí)設(shè)定初始大小的方法
InnoDB在寫(xiě)密集的壓力時(shí)，由于B-Tree擴(kuò)展，因而也會(huì)帶來(lái)數(shù)據(jù)文件的擴(kuò)展，然而，InnoDB數(shù)據(jù)文件擴(kuò)展需要使用mutex保護(hù)數(shù)據(jù)文件，這就會(huì)導(dǎo)致波動(dòng)。 丁奇的博客說(shuō)明了這個(gè)問(wèn)題：
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:
解決的方法也很簡(jiǎn)單，只要知道數(shù)據(jù)文件可能會(huì)增長(zhǎng)到多大，預(yù)先擴(kuò)展即可。閱讀代碼可以知道，InnoDB建表后自動(dòng)初始化大小是FIL_IBD_FILE_INITIAL_SIZE這個(gè)常量控制的，而初始化數(shù)據(jù)文件是由fil_create_new_single_table_tablespace()函數(shù)控制的。所以要改變數(shù)據(jù)文件初始化大小，只要修改fil_create_new_single_table_tablespace的傳入值即可，默認(rèn)是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.
因此，我在建表語(yǔ)法中加上了datafile_initial_size這個(gè)參數(shù)，例如：
CREATE TABLE test (
…
) ENGINE = InnoDB DATAFILE_INITIAL_SIZE=100000;
如果設(shè)定的值比FIL_IBD_FILE_INITIAL_SIZE還小，就依然傳入FIL_IBD_FILE_INITIAL_SIZE給fil_create_new_single_table_tablespace，否則傳入datafile_initial_size進(jìn)行初始化。
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.
因此，這個(gè)簡(jiǎn)單安全的patch就有了，可以看 http://bugs.mysql.com/bug.php?id=67792 關(guān)注官方的進(jìn)展：
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)},
相關(guān)文章
這篇文章主要介紹了如何優(yōu)化sql中的orderBy語(yǔ)句，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-09-09
這篇文章主要介紹了一鍵清空本地MySQL8.0密碼腳本，再也不用擔(dān)心MySQL密碼忘記了,很容易的解決了忘記mysql密碼的煩惱，操作方法也非常簡(jiǎn)單，需要的朋友可以參考下2023-01-01
MySQL是中小型網(wǎng)站普遍使用的數(shù)據(jù)庫(kù)之一，然而，很多人并不清楚MySQL到底能支持多大的數(shù)據(jù)量，再加上某些國(guó)內(nèi)CMS廠(chǎng)商把數(shù)據(jù)承載量的責(zé)任推給它，導(dǎo)致很多不了解MySQL的站長(zhǎng)對(duì)它產(chǎn)生了很多誤解2008-12-12
這篇文章主要介紹了SQLServer注釋快捷鍵操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
mysql性能監(jiān)控工具Innotop簡(jiǎn)介及配置
INNOTOP是一個(gè)通過(guò)文本模式顯示MySQL和InnoDB的監(jiān)測(cè)工具。INNOTOP是用PERL語(yǔ)言寫(xiě)成的，這使它能更加靈活的使用在各種操作平臺(tái)之上，它能詳細(xì)的的監(jiān)控出當(dāng)前MYSQL和INNODB運(yùn)行的狀態(tài)，以DBA根據(jù)結(jié)果，可以合理的優(yōu)化MYSQL，讓MYSQL更穩(wěn)定更高效的運(yùn)行。2014-08-08
MySQL 5.7之關(guān)于SQL_MODE的設(shè)置
這篇文章主要介紹了MySQL 5.7之關(guān)于SQL_MODE的設(shè)置方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-08-08
MySQL問(wèn)答系列之如何避免ibdata1文件大小暴漲
MySql innodb如果是共享表空間，ibdata1文件會(huì)越來(lái)越大，所以下面這篇文章主要給大家介紹了關(guān)于MySQL問(wèn)答系列之如何避免ibdata1文件大小暴漲的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，需要的朋友可以參考下2018-09-09
MySQL中TEXT與BLOB字段類(lèi)型的區(qū)別
這篇文章主要介紹了MySQL中TEXT與BLOB字段類(lèi)型的區(qū)別,本文總結(jié)了6大區(qū)別,需要的朋友可以參考下2014-08-08
在存儲(chǔ)字符串時(shí)可以使用char、varchar或者text類(lèi)型,下面這篇文章主要給大家介紹了關(guān)于mysql中varchar和text的區(qū)別和比較的相關(guān)資料,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-10-10
我相信很多人都遭遇過(guò)MySQL主從復(fù)制延遲的問(wèn)題，處理方案基本上就是你能搜索到的那些，那么我今天卻要說(shuō)說(shuō)如何讓MySQL延遲復(fù)制。對(duì)于剛組建的團(tuán)隊(duì)來(lái)說(shuō)，這可能會(huì)救了你的項(xiàng)目2020-02-02