Optimization: Increase Innodb_pool_size useful?

Question

We run an AWS RDS instance with 8vCPUs and 64GB memory. All tables are using InnoDB engine.

I have kept the default setup of innodb_pool_size to be half the memory.

What I see is that almost 30Gb of memory is used and no read I/O operation (except for some spikes).

Freeable memory:

Read IO:

I've seen some great blog post, answers (like this one How large should be mysql innodb_buffer_pool_size?) about tuning MySQL.

Would it be beneficial to increase the innodb_pool_size to something like 3/4 of the memory? As I don't see read IO.

Also, but I don't know if that is related: what can explain this huge storage drop in a couple of minutes? We don't store large objects, BLOBs, etc.

Free Storage:

Answer 1

It's good you don't see a lot of reads, ideally it should be zero if available memory permits.

When InnoDB needs a page it checks the buffer pool. If the page is in the buffer pool - done. If not - InnoDB will read it from the disk and cache it in the buffer pool. So, if the data set fits into the buffer pool there will be eventually no reads from disk.

If you already get no reads further buffer pool increase won't increase performance.

The storage drop - I don't know RDS is a black box, who know what's going on there.

Answer 2

There is no advantage in having the buffer_pool_size much more than the total amount of data and indexes you have. (Cf, SHOW TABLE STATUS)

The buffer_pool_size should not be so large that it leads to swapping. Swapping is terrible for performance. Note: If you are running other apps on the same box, you need to take their memory usage into account.

The buffer_pool is a "cache", so if you have more data+index than fit in RAM, blocks (16KB each) will be bumped out of the way as needed. No problem, except for being a little slower.

The buffer_pool is filled as needed, hence the decline if 'free' memory you are seeing.

http://mysql.rjweb.org/doc.php/memory