Optimize MySQL by changing the InnoDB data page size

Original link http://www.mysqlsupport.cn/change_innodb_page_size/

 

 

Author: Wu Bingxi Source: http://www.mysqlsupport.cn/contact information: wubingxi # gmail.com reprint please indicate/translator and source, and cannot be used for commercial purposes, offenders must investigate.
We know that the data page of InnoDB is 16 KB, which is a rigid rule. There is no change in the system. We hope MySQL will support multiple data pages in the same way as Oracle in the future.
However, in actual applications, sometimes 16 K is a little big, especially when many businesses are migrated to MySQL when oracle or SQL server runs well and I/O increases too much,
You can change the data page size.
In fact, the InnoDB data page size can also be changed, but it needs to be changed at the source layer, and then re-build MySQL.
Change method:
(Take MySQL-5.1.38 source code as an example)
The location is stored in storage/Innobase/include/Univ. I. Search for univ_page_size in Univ. I.

/*   DATABASE VERSION CONTROL   ========================*/ /* The universal page size of the database */#define UNIV_PAGE_SIZE          (2 * 8192) /* NOTE! Currently, this has to be a     power of 2 *//* The 2-logarithm of UNIV_PAGE_SIZE: */#define UNIV_PAGE_SIZE_SHIFT 14 /* Maximum number of parallel threads in a parallelized operation */#define UNIV_MAX_PARALLELISM 32

Univ_page_size is the size of the data page. The default value is 16 KB. The value can be set to the power of 2. You can set this value to 4 K, 8 K, 16 K, 32 K, and 64 K, which is meaningless.
After changing the univ_page_size, you must change the univ_page_size_shift value to the univ_page_size value of 2. Therefore, set the data page as follows:

#define UNIV_PAGE_SIZE_SHIFT 12  if UNIV_PAGE_SIZ=4K#define UNIV_PAGE_SIZE_SHIFT 13  if UNIV_PAGE_SIZ=8K#define UNIV_PAGE_SIZE_SHIFT 15  if UNIV_PAGE_SIZ=32K

Example:
Change the data page of InnoDB to 8 KB and change it:

/*   DATABASE VERSION CONTROL   ========================*/ /* The universal page size of the database */#define UNIV_PAGE_SIZE          8192   /* NOTE! Currently, this has to be a     power of 2 *//* The 2-logarithm of UNIV_PAGE_SIZE: */#define UNIV_PAGE_SIZE_SHIFT 13 /* Maximum number of parallel threads in a parallelized operation */#define UNIV_MAX_PARALLELISM 32

Recompile, test, and test. Good luck!