MySQL
I set in my.ini
innodb_buffer_pool_instances=8
However, no matter how I change the innodb buffer pool size. I check the
innodb_buffer_pool_instances in mysql workbench, it always equal to 1. I cannot find a way to change the instances number. What is wrong? I am using Mysql 5.7.12.
Sounds like an issue with MySQL Workbench.
MySQL 5.5 has a default storage engine of InnoDB.
In fact, the option default_storage_engine is predefined as follows:
There is another option called storage-engine, which was deprecated as for MySQL 5.5.3. Keep in mind that MySQL 5.5.8 was the very first GA release.
I would strongly suspect MySQL Workbench if it is coded to check storage-engine instead of default-storage-engine. This would allow MySQL Workbench to monitor older release of MySQL.
If you expect your InnoDB data to grow, you should prepare for it by having a larger InnoDB Buffer Pool.
Where does the idea of having additional 10% extra space for the Buffer Pool come from ???
Please note the following diagram of the InnoDB Architecture
Look in the upper left hand corner where you see the buffer pool. There is a section of it called the "Insert Buffer Part". The purpose of the Insert Buffer is to populate changes to non-unique indexes into the Insert Buffer inside the system tablespace file (a.k.a. ibdata1).
From the diagram, up to 50% of the Buffer Pool can be used to manage the Insert Buffer. That being the case, it would be in your best interests to assign a lot more memory to the Buffer Pool. With a data set of 60M, 256M would be a great place to start,
Best Answer
The default values for
innodb_buffer_pool_instancesis explained in the official MySQL Documentation:So, if your
innodb_buffer_pool_sizeis less than 1 GB,innodb_buffer_pool_instancewill always be reset to 1.Moreover, since you mentioned my.ini file, it indicates that you are using Windows. If your Windows is 32-bit, then MySQL auto-determines this value, and thus changing it in my.ini won't affect anything. The rules are: