0

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.

2
  • 2
    What is the value of innodb_buffer_pool_size ? Sep 29 '19 at 14:48
  • 2
    Please post results of SELECT @@innodb_buffer_pool_size; and how much RAM is available to your instance? Sep 30 '19 at 17:45
1

The default values for innodb_buffer_pool_instances is explained in the official MySQL Documentation:

Default Value (Other) : 8 (or 1 if innodb_buffer_pool_size < 1GB)

Default Value (Windows, 32-bit platforms) : (autosized)

So, if your innodb_buffer_pool_size is less than 1 GB, innodb_buffer_pool_instance will 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:

  • If innodb_buffer_pool_size is greater than 1.3GB, the default for innodb_buffer_pool_instances is innodb_buffer_pool_size/128MB, with individual memory allocation requests for each chunk. 1.3GB was chosen as the boundary at which there is significant risk for 32-bit Windows to be unable to allocate the contiguous address space needed for a single buffer pool.

  • Otherwise, the default is 1.

4
  • Your answer is potentially correct, but for the wrong reason. You are citing only information about the default value, which only applies when innodb_buffer_pool_instances is not explicitly configured. But none of this is relevant, since the default value is not used. The probable correct answer is that this setting is ignored for small buffer pools, mentioned in the same section of the docs: "This option only takes effect when setting innodb_buffer_pool_size to 1GB or more." Sep 29 '19 at 20:05
  • @Michael-sqlbot I think the same is being said in the first few lines of my answer. Sep 29 '19 at 22:09
  • Not exactly. You were correct when you said "So, if your innodb_buffer_pool_size is less than 1 GB, innodb_buffer_pool_instance will always be reset to 1" but the justification you have given for this is wrong, because you have quoted the documentation about the default value. The default value is only used when innodb_buffer_pool_instances has not been configured at all. The default value has no meaning when the admin configures an explicit value. The single sentence at the end of my prior comment is the complete and correct answer. The rest does not apply. Sep 30 '19 at 2:06
  • @Michael-sqlbot Am unable to spot any difference. Pls feel free to edit my answer, or post a new one Sep 30 '19 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.