which way to increase InnoDB Buffer after more resources given

Question

Sorry for re-treading the constant discussions of Buffer size and efficiently, we have a standalone server for a vendor system that has a database of 10GB on MySQL 8.0.28

the server itself is a bit meagre at just 8GB RAM so I have my buffer set to 2GB (16/128MB chunks) right now we are running at 87.5% usage (select statement gives us 1.7GB usage), no reads from disk over night and from what i can tell it seems to be working well... our hit rate is 1000 /1000

we are now getting a new server with 16GB RAM (use it, or loose it, in our workplace) so trying to figure out the best way of using this up... just increasing to 8GB (32/256MB) arbitrarily because we can or is it worth more increasing the instances then the size... i.e (64/128MB) to outnumber the connections, not sure i have seen anyone mention a benefit to that yet?

we cant use the data + 60% we don't quite have that much RAM, or even at +40% im not sure I will be getting any more RAM justified.

we have about 25 live connections from app servers, can go up to 50 at peak, we have a max set over 100 but it never gets there.

Answer 1

The variable innodb_buffer_pool_size is dynamic.

Since your database is 10GB, you can set the size as follows:

mysql> SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024 * 10;

This will dynamically expand the InnoDB Buffer Pool for you.

You can then add that value to the my.cnf under the [mysqld] group header like this:

[mysqld]
innodb_buffer_pool_size = 10G

This will leave 6GB of RAM free for the OS and DB Connections.

If you know for sure the working set will consistently be 1.7GB, you choose a lowere value for innodb_buffer_pool_size (perhaps 4GB ot 6GB).