Bug #61239 InnoDB silently switches to 1 buffer pool instance if size is < 1GB
Submitted: 20 May 2011 5:01 Modified: 10 Apr 2013 15:26
Reporter: Vasil Dimov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: Vasil Dimov

[20 May 2011 5:01] Vasil Dimov
Description:
For example with these settings:

innodb-buffer-pool-size=200M
innodb-buffer-pool-instances=4

InnoDB silently switches to using 1 buffer pool instance. Two things should be fixed:
1. A notice should be emitted to the user so he knows that his choice has been overriden.
2. The value of innodb_buffer_pool_instances should be set back to 1, it currently shows 4, which is very confusing.

The code that sets back to 1 buffer pool is srv0start.c:

1257         /* Set the maximum number of threads which can wait for a semaphore
1258         inside InnoDB: this is the 'sync wait array' size, as well as the
1259         maximum number of threads that can wait in the 'srv_conc array' for
1260         their time to enter InnoDB. */
1261 
1262         if (srv_buf_pool_size >= 1000 * 1024 * 1024) {
1263                 /* If buffer pool is less than 1000 MB,
1264                 assume fewer threads. Also use only one
1265                 buffer pool instance */
1266                 srv_max_n_threads = 50000;
1267 
1268         } else if (srv_buf_pool_size >= 8 * 1024 * 1024) {
1269 
1270                 srv_buf_pool_instances = 1;
1271                 srv_max_n_threads = 10000;
1272         } else {
1273                 srv_buf_pool_instances = 1;
1274                 srv_max_n_threads = 1000;       /* saves several MB of memory,
1275                                                 especially in 64-bit
1276                                                 computers */
1277         }

How to repeat:
see above
[10 Apr 2013 15:25] Bugs System
Added a changelog entry for 5.7.1:

Without warning, InnoDB would silently set "innodb-buffer-pool-instances"
to 1 if the buffer pool size is less than 1GB. For example, if
"innodb-buffer-pool-size" is set to 200M and
"innodb-buffer-pool-instances" is set to 4, InnoDB would silently set
"innodb-buffer-pool-instances" to 1. This fix implements a warning message
and new logic for "innodb-buffer-pool-size" and
"innodb-buffer-pool-instances".