In MySQL 5.6 (mysqld Ver 5.6.19 for Win64 on x86_64 (MySQL Community Server (GPL))), if for some reason there is a need to change the page size using
innodb_page_size
in the config file (my.ini or my.cnf) it is better to give attention to the following points:
Scenario:
In this scenario, a changed page size causes a problem and MySQL 5.6 InnoDB is unable to recover (even when there is nothing to recover) the .idb's with the old page size. The server simply will not start and the user log file given under
innodb_page_size
in the config file (my.ini or my.cnf) it is better to give attention to the following points:
Scenario:
- Assume there are DBs already created and running with a different page size.
- Changing the page size in my.ini and restarting usually works fine for InnoDB (well as far as I have checked, which is kind of minimal)
- But assume that there happened to be a crash, like the computer shut down without bringing down the MySQL
- On restart, MySQL will attempt a recovery since the entry numbers in the log file and .idb files may not match.
In this scenario, a changed page size causes a problem and MySQL 5.6 InnoDB is unable to recover (even when there is nothing to recover) the .idb's with the old page size. The server simply will not start and the user log file given under
log-output=FILE
general-log=0
general_log_file="xxx.log"
will have various options (just as restoring from backup, deleting the .idb, etc., etc.) and none of it works. Changing the page size back will have problems with the newly created DBs that has a different (new) page size.
In my case it was an experimental scenario with only dummy data and the schema was in the model, so I can delete all the new .idb's and recover. But situation would have been bad if the tables had been populated with data, as there was no indications that things might go wrong - which it doesn't till there is a crash...!
And if there is a need to know (Obsessive-Compulsive Disorder - OCD) why the page size was changed in the first place from default 16k to 4k - it was done in an attempt to align InnoDB's IO with the computer's OS/Disk characteristics for an optimal performance.
general-log=0
general_log_file="xxx.log"
will have various options (just as restoring from backup, deleting the .idb, etc., etc.) and none of it works. Changing the page size back will have problems with the newly created DBs that has a different (new) page size.
In my case it was an experimental scenario with only dummy data and the schema was in the model, so I can delete all the new .idb's and recover. But situation would have been bad if the tables had been populated with data, as there was no indications that things might go wrong - which it doesn't till there is a crash...!
And if there is a need to know (Obsessive-Compulsive Disorder - OCD) why the page size was changed in the first place from default 16k to 4k - it was done in an attempt to align InnoDB's IO with the computer's OS/Disk characteristics for an optimal performance.