Change InnoDB data page size optimization MySQL

Change InnoDB data page size optimization MySQL

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

Change InnoDB data page size optimization MySQL2009 December 13 posted by WUBX

Wu Bingxi Source: http://www.mysqlsupport.cn/Contact: wubingxi#gmail.com Reprint Please specify AS/translator and source, and can not be used for commercial purposes, offenders must investigate.
We know that InnoDB data page is 16K, and is a hard rule, the system does not change the way, I hope that in the future, MySQL can also support the size of a variety of data pages Oracle.
However, in practical applications sometimes 16K is a little big, especially when many of the business in Oracle or SQL Server is very good to move to MySQL found that IO growth is too obvious,
You'll think about changing the data page size.
In fact, INNODB data page size can also be changed, just need to change at the source layer, and then re-rebuild MySQL.
Change method:
(Take MySQL-5.1.38 source code as an example)
Location in Storage/innobase/include/univ.i, find in UNIV.I: univ_page_size

/* Database VERSION CONTROL ========================*//* The Universal page size of the database */#define Univ_page_ SIZE (2 * 8192)/* note! Currently, this have to be a power of 2 *//* the 2-logarithm of univ_page_size: */#define UNIV_PAGE_SIZE_SHIFT/* Ma Ximum number of parallel threads in a parallelized operation */#define Univ_max_parallelism 32

Univ_page_size is the data page size, the default is 16K. The following notes indicate that the value can be set to 2 times. For this value can be set to 4k,8k,16k,32k,64k, in large also meaningless.
After you change the univ_page_size, you need to change univ_page_size_shift the value is 2 of the number of univ_page_size, so the data page is set as follows:

#define UNIV_PAGE_SIZE_SHIFT If Univ_page_siz=4k#define univ_page_size_shift if Univ_page_siz=8k#define UNIV_PAGE _size_shift if univ_page_siz=32k

Example:
Change the data page for InnoDB to 8K, and modify it accordingly:

/* Database VERSION CONTROL ========================*//* The Universal page size of the database */#define Univ_page_ SIZE 8192/ * note! Currently, this have to be a power of 2 *//* the 2-logarithm of univ_page_size: */#define UNIV_PAGE_SIZE_SHIFT/* Ma Ximum number of parallel threads in a parallelized operation */#define Univ_max_parallelism 32

Recompile, then test the test, and then test. Good luck!

updated:2009 December 13 at 14:27 Tags: innodb page size?InnoDB How to use memoryInnodb File Table Spatial structure ?About the Authorwubxcomments

1. Rj03houJuly 23, 2010 20:43

   InnoDB plugin can already set the page size by setting the page size to implement the block compression function.
   By the end, did you have a test? Can the test results be published?

   Reply
2. RuochenApril 8, 2010 14:41

   XTRADB is ready to support multiple page size, which can be specified when creating a database, many other features