Background:

The ability to have innodb_buffer_pool_instances > 1 means you can reduce some locking under heavy load and that should be good. However it's hard to actually see if the load is that high that you might want to change this, or if you set it to some arbitrary "n" if that value is too high or too low. This value is currently static and thus requires a server restart, so changing it is very intrusive, hence the decision to use a specific value and figure out if that value is the right one seems quite difficult, which makes the knob a difficult knob to change and tune correctly.

So the request here is to have some way to figure out if the current setting is appropriate or perhaps might be worth changing to something else, or to give some feedback in the documentation on the best way to determine this.

I suggest following existing method to measure this. Compile server with performance_schema with

cmake . -DWITH_PERFSCHEMA_STORAGE_ENGINE=1

Then modfy my.cnf to contain:

• performance_schema=ON
• performance_schema_instrument='%synch%=on'

Run your application and then on a regular time interval executing the following SQL query, and getting a diff from the current values vs the previous ones and divide them by your time interval :

 select EVENT_NAME, max(SUM_TIMER_WAIT)/1000000000000 as WaitTM 

from events_waits_summary_by_instance group by 1 order by 2 desc limit 25;

If the buffer pool mutex is near the top, you might consider to increase the number of buffer pool instances.