0

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.

1 Answer 1

0

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 * 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).

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.