Since 5.7 its possible to set "dynamic" Buffer Pool Sizes with:
innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
How exactly does that work?
I have a fairly small server with 4 Cores and 16GB RAM with a PHP business application on it.
I now have located 8GB as static Pool Buffer(around 70-80% Key Efficiency & 99% Buffer usage).
On the server is also a Java MassMailer that runs on average once per day, so i keep ~8GB free for the mailer. I have programmed the MassMailer in a way that he will just load as many emails as the memory allows.
Can i use that new dynamic Buffer Pool feature to allocate during the normal workload to around 12GB and take some while sending mails? Can i ajust it manually or does the MySQL server that automatically? Will the server give some memory back if the mailer wants more or will it throw a "out of memory" exception?
Best Answer
The chunk_size setting is for dynamically changing
innodb_buffer_pool_size
. I see no use for changing the buffer_pool_size except for experimenting.Memory:
5.6G
.If you dynamically give more room to MySQL, you need to simultaneously take RAM away from Java. This seems too clumsy and error prone to attempt.
I would expect the Mailing program to be network-bound, hence trying to optimize RAM is probably not worth doing.
(MySQL DBA questions beong in dba.stackexchange.com ,)