Mysql – (AWS) Mysql memory alert threshold(for monitoring) and innodb_buffer_pool_size

awsmemoryMySQL

I recently upgraded my AWS RDS(mysql) from 5.6.23 to 5.7.11.

innodb_buffer_pool_size is automatically set as 75% of RAM by AWS. (I think it's maybe a recommendation)

The problem is the available RAM(FreeableMemory in AWS) slowly but steadily decreasing. It's never going up.. now at 66%..2 days after rebooting.

First I thought it's kind of bug at 5.7.11(it never happened before) and memory leaking but maybe it's not.

What if.. mysql is storing query to cache for the performance.. much more aggressively than 5.6.x..?

So my question is, what is the proper number of memory threshold for the monitoring?

If innodb_buffer_pool_size is set as 75% of RAM, memory threshold is set as 25% of RAM?

Please notice me for any further information needed.

Best Answer

With the buffer_pool set at 75% of RAM, it will grow until it consumes that much of RAM. But there is a lot of other things in RAM -- the OS, MySQL code, other caches, buffers, etc. The real goal (for a server running only MySQL) is to consume 100% of RAM, but no more.

The buffer_pool is the biggest item that can be tuned. 75% is a good number (assuming you have at least 4GB of RAM). Only in extreme cases (or you fiddle with other tunables), will you blow out 100%.

Using more memory than you have is really bad because it leads to "swapping". But MySQL is not coded to understand that. It assumes, for example, that the entire buffer_pool can be accessed without any disk I/O. Disk I/O is a performance killer.

I don't know what AWS means by FreeableMemory, but 66% seems fine. Even 25% seems fine. 5% would seem dangerously close to swapping, and I would worry. None of the buffer_pool should be "freeable"; it sounds like the buffer_pool has not yet grown to its stated max.

Related Question