Mysql dynamic InnoDB Buffer Pool Size

Question

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?

Answer 1

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:

• 16GB of RAM
• 8GB for Java -- does this include Java uses other than the MassMailer?
• That leaves 8GB for MySQL
• Set the buffer_pool to about 70% of that, namely 5.6G.
• If you see swapping then the buffer_pool should be shrunk further.
• Dynamically changing the buffer_pool_size will block MySQL activity (to some extent).

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 ,)