MySql 5.6: Ignores innodb_page_size setting in my.ini 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!
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


you must setup a new datadir with no data (but do not start mysqld)
set the innodb_page_size to 32768 in the my.ini for that new instance
start mysqld against that new my.ini and new datadir
mysqldump the data out of the old instance
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”.