I am trying to optimize the DB server of one of our production environments (MySQL DB, centos7), and I've noticed that there is a very high memory consumption of the DB.

After further investigation I noticed that we should resize the DB from 8 GB RAM to 16 GB RAM due to increased usage. But when looking at the my.cnf file, I found the following settings for the buffer pool parameters:

#Pool size configuration

Now after checking Configuring Multiple Buffer Pool Instances and a few other websites, it seems to point out that innodb_buffer_pool_instances should be set to 1 per CPU core.

For some reason ours is 48, and I couldn't find anyone in charge who could give me the reason for this.

I'd like to know if this setting could remain the same, which means it's probably there, because of a certain usage pattern?

This is a relatively small DB.

I will be resizing to 16 GB and adjusting the pool size to 12 GB, so it makes sense that innodb_buffer_pool_instances should be also set to 12, from my very basic understanding. Am I correct with my assumption?

  • Welcome to DBA.SE. I've taken the liberty of slightly rephrasing your quesiton. I hope you don't have any objections? Where did you read that it should be 1 per CPU core? And what will you be resizing to 16 GB and what parameter to 12 GB? It's not quite clear. Could you hit edit to modify your question and be a bit more specific in explaining what exactly that you will be modifying? Thanks.
    – John K. N.
    Jul 20, 2020 at 11:18

1 Answer 1


The most important change would be

innodb_buffer_pool_size=6G --> 12G

The number of buffer_pool_instances should be 1 per GB, not one per core. So, 12 in your new machine. However, there is a limit of 16. As for why...

  • I suspect it makes very little difference -- perhaps single-digit-percentage when the server is extremely busy. (If a server is that busy, it is about to keel over; you have worse problems.)

Essentially nothing else requires changing.

After you have been running normally for at least a day, grab SHOW GLOBAL STATUS and SHOW VARIABLES; then get an analysis here: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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