Adjusting the my.cnf file for DB servers after an instance resize

Question

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
innodb_buffer_pool_size=6G
innodb_buffer_pool_chunk_size=128M
innodb_buffer_pool_instances=48

Now after checking 15.8.3.2 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?

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