Detailed example of how to modify the data page size of Innodb to optimize MySQL

This article mainly introduces the method of modifying the data page size of Innodb to optimize MySQL. Innodb is the next popular data engine of MySQL. Friends who need it can refer to it.

We know that the data page of Innodb is 16K, and it is a rigid rule. There is no way to change it in the system. We hope that MySQL can also support multiple data page sizes like Oracle in the future.
But in actual applications, sometimes 16K is a bit too big, especially when many businesses migrate to MySQL when Oracle or SQL SERVER are running very well and find that the IO growth is too obvious,
will Thought of changing the data page size.
In fact, the data page size of innodb can also be changed. You just need to change it at the source code layer and then rebuild MySQL.
Change method:
(Take MySQL-5.1.38 source code as an example)
The location is in storage/innobase/include/univ.i, search 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 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 data page size, the default is 16K. In the following remarks Indicates that the value can be set to a power of 2. This value can be set to 4k, 8k, 16k, 32K, 64K, which is meaningless in large applications.
After changing UNIV_PAGE_SIZE at the same time, you need to change UNIV_PAGE_SIZE_SHIFT. The value is UNIV_PAGE_SIZE to the power of 2, so the settings of the data pages are 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 8K, The corresponding modification is:

/*
  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, then test, test, and test again. Good luck!

The above is the detailed content of Detailed example of how to modify the data page size of Innodb to optimize MySQL. For more information, please follow other related articles on the PHP Chinese website!