Explain this model of memory consumption in Amazon RDS / Mysql?

Question

People,

Can someone explain this picture of memory consumption on Amazon RDS working with Mysql? In this graph, I upgraded to db.m2.2xlarge, with 34 GB of available memory, at 03:30. You see the switch very clearly. When clients start connecting and fall into this instance, Freeable memory drops steeply to 5 GB, where it now hovers. In my previous update, between the dimensions of the DB instance, I saw the same pattern until free memory dropped to less than 1 GB and hung there indefinitely.

What does this example do from 03:30 to 07:30? Why doesn't he free up unused memory when it becomes available? I think I would expect this graph to be a waveform corresponding to usage patterns and traffic vs vs an exponential decay form, which suggests that this is a super lazy and / or broken garbage collection algorithm.

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

Answer 1

MySQL maintains a cache of recently used tables, queries, and results in memory to return faster results. For example, if you request "select * from company, where id = 1" from the client 1 million times, only the first request should go to disk, the next 999,999 will come directly from the RAM cache. MySQL has no reason to expire this cache until it finds out that it needs more memory, so it saves everything until it needs to free up RAM for other work or more frequently used results.

I do not pretend to be an expert - I understand that optimizing the database query cache is a very complex and deep science. Programmers from Oracle, Microsoft, and other companies have spent years and years developing a better way to manage the cache, so it's hard to predict from the outside.