Explain this memory consumption pattern in Amazon RDS/Mysql?

Question

Folks,

Can someone explain this memory consumption pattern on Amazon RDS running Mysql? In this graph, I upgraded to a db.m2.2xlarge, with 34GB of available memory, at 03:30. You can see the switchover very clearly. As clients start connecting and hitting that instance, the Freeable memory drops steeply to 5GB, where it is now hovering. On my previous upgrade between DB instance sizes, I saw the same pattern, until the freeable memory dropped to just under 1GB and hovered there indefinitely.

What is this instance doing between 03:30 and 07:30? Why isn't it freeing unused memory as it becomes available? I guess I would expect this graph to be a wave shape, corresponding to usage and traffic patterns, vs and exponential decay shape, which suggests that it's a super lazy and/or broken garbage collection algorithm.

Also note that about 2/3rds of DB operations are writes and 1/3 are reads, and there is about 2GB of memcache in front of the DB.

Answer 1

MySQL maintains a cache of recently used tables, queries and results in memory, in order to return faster results. For example, if you query "select * from company where id = 1" from a client 1 million times, only the first query needs to go to disk, the next 999,999 will come straight from the in RAM cache. There is no reason for MySQL to expire this cache until it knows it needs more memory, so it keeps things saved until it needs to free up RAM for other work or more frequently used results.

I don't claim to be an expert - I understand DB query cache optimization to be a very complex and deep science. Programmers at Oracle, Microsoft and others have spent years and years working out the best way for the cache space to be managed, so it's hard to predict from the outside.