AWS Aurora cache metrics meanings

Question

Aurora has two query-cache related metrics :

• Buffer cache hit ratio : The percentage of requests that are served by the Buffer cache.
• Resultset cache hit ratio : The percentage of requests that are served by the Resultset cache.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Monitoring.html

But I can't find the documentation that explains the difference between "Buffer cache" and "Resultset cache".

What are they?

Answer 1

"Resultset Cache Hit Ratio" is related to the query cache, which is a feature that enables caching the read queries' results (that's why called result set cache hit). So,if the engine started to execute a new read query, it will check the cached results before executing the query itself and if it found that this same query has been executed before and that its result wasn't invalidated yet, then it will serve the result of the new query from the cache. This is generally useful & shows up high in number when the workload contains a lot of similar select queries that has the similar values and conditions.

On the other hand, "Buffer Cache Hit Ratio" is more related to the innodb page caching hit ratio (& not the query result cache), and this should increase with increasing all types of read queries, as this process is called by bufferpool warm up which will cause the engine to load all the needed pages from the storage to the memory for faster access to the data. However, with increased amount of writes to the writer, this will make the readers to invalidate there in memory pages then load these pages again from the storage when needed. The "ratio" here depends on the percentage of hitting the in memory pages which should be very high ex: more than 99%.

Query cache is generally considered with low connections, similar type of queries over & over again (based on few observations on mysql/aurora, query cache might be actually bad for performance if you have high no. of connections & lots of adhoc style, changing queries).