MySql 5.6: Ignores innodb_page_size setting in my.ini

Question

So I tried to increase the innodb_page_size to 32k, by setting this value in my.ini:

innodb_page_size=32k

I then stopped the MySql service, and restarted it. The service in Win looks like this:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

And I edited the correct file, and its saved:

but when I look at the variable in HeidiSql, I see the standard value of 16k:

Im not sure what I am doing wrong... Any help is appreciated!

Answer 1

According to the MySQL Documentation on innodb_page_size, Paragraph 2

innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default page size. See Section 14.6.1, “InnoDB Startup Configuration”.

You would had to have initialized that value in my.ini the day you first created the database and started mysqld for the first time. No restart of mysqld afterwards can ever change that.

If you want to create a MySQL Instance with a new size for innodb_page_size

1. you must setup a new datadir with no data (but do not start mysqld)
2. set the innodb_page_size to 32768 in the my.ini for that new instance
3. start mysqld against that new my.ini and new datadir
4. mysqldump the data out of the old instance
5. load the mysqldump into the new instance

Give it a try and let us know !!!

CAVEAT

Please be aware of disk I/O changes. Paragraph 3 of the same document says:

The default 16k page size is appropriate for a wide range of workloads, particularly for queries involving table scans and DML operations involving bulk updates. Smaller page sizes might be more efficient for OLTP workloads involving many small writes, where contention can be an issue when single pages contain many rows. Smaller pages might also be efficient with SSD storage devices, which typically use small block sizes. Keeping the InnoDB page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.

Answer 2

If you have a particular need for a non-standard page size, let's hear it. I have review thousands of I can't remember a single case of someone changing that setting. Furthermore, I find it hard to think of a use case for the change. Some proposed use cases are unlikely to benefit enough to bother deviating from the norm.

To discuss this further, include disk characteristics (SSD vs HDD, RAID, hardware block size, etc), index characteristics (UUID, range scans, point queries, etc), Record size (and why it can't be a different size).

UUID+SSD may benefit from a smaller page_size.

A large record size can usually be split up by vertical partitioning rather than resort to a larger page size.