InnoDB在寫密集的壓力時(shí)，由于B-Tree擴(kuò)展，因而也會帶來數(shù)據(jù)文件的擴(kuò)展，然而，InnoDB數(shù)據(jù)文件擴(kuò)展需要使用mutex保護(hù)數(shù)據(jù)文件，這就會導(dǎo)致波動(dòng)。 丁奇的博客說明了這個(gè)問題：
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:
解決的方法也很簡單，只要知道數(shù)據(jù)文件可能會增長到多大，預(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.
因此，我在建表語法中加上了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è)簡單安全的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:
