This article mainly introduces how to modify the data page size of Innodb to optimize MySQL. Innodb is MySQL's next popular data engine, if you need it, you can refer to the following: we know that the Innodb data page is 16 KB, which is a hard rule and there is no change in the system, we hope that MySQL will support multiple data page sizes like 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!