MySQL InnoDB Buffer Pool VS MySQL Query Cache

[premiumrush]

Both buffer pool and query cache act like in-memory database. I am leaning towards InnoDB buffer pool because of its ability to support multiple pool instances and tweaking of read/write threads. Also, I read articles recommending that Query Cache be turned off on production systems because any updates to tables will cause it to purge itself and that can be a performance bottleneck.

If InnoDB buffer pool is sufficient for the system's caching needs, is Query Cache needed? What scenarios can justify its use? (Putting aside the opinion that Query Cache may be a performance bottleneck)

 

[davidktw]

Both buffer pool and query cache act like in-memory database. I am leaning towards InnoDB buffer pool because of its ability to support multiple pool instances and tweaking of read/write threads. Also, I read articles recommending that Query Cache be turned off on production systems because any updates to tables will cause it to purge itself and that can be a performance bottleneck.

If InnoDB buffer pool is sufficient for the system's caching needs, is Query Cache needed? What scenarios can justify its use? (Putting aside the opinion that Query Cache may be a performance bottleneck)

Buffer Pool and Query Cache are read cache. When you write, you don't just store in-memory, the write get persist into the secondary storage system. So we don't compare any of them similar to in-memory database like Oracle TimesTen


Query Cache is not just an InnoDB feature. It is also used across other database engines especially for MyISAM. Issue with query cache is locking, not just eviction of cache if something that updated. Eviction of cache when data get updated is an issue with all sorts of caching mechanism, even if it is a simple app caching. The database does not have the luxury of understanding your data domain, hence it cannot perform stale caching. All updates to the data must evict the cache regardless if it is implemented in Query cache or Innodb buffer pool.

Query cache scalability issue in large number of running cores database is mainly due to lock contention. Do read up articles like the following
https://www.percona.com/blog/2012/09/05/write-contentions-on-the-query-cache/

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. This pretty much answer your doubts

 

[premiumrush]

Hey davidktw,

Apologies for the late reply. Thank you for sharing the article. It has given me a better insight into query cache. Looks like it was bad design to start with.

>> "All updates to the data must evict the cache regardless if it is implemented in Query cache or Innodb buffer pool."

https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-adaptive_flushing.html

Based on what I read on innodb adaptive flushing, innodb keeps track of dirty pages and flush them to disk when the number reaches a certain percentage as defined by innodb_max_dirty_pages_pct. So, in that respect, innodb doesn't really evict the cache on update. Am I right to say this or am I understanding adaptive flushing wrongly?

 

[davidktw]

Hey davidktw,

Apologies for the late reply. Thank you for sharing the article. It has given me a better insight into query cache. Looks like it was bad design to start with.

>> "All updates to the data must evict the cache regardless if it is implemented in Query cache or Innodb buffer pool."

https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-adaptive_flushing.html

Based on what I read on innodb adaptive flushing, innodb keeps track of dirty pages and flush them to disk when the number reaches a certain percentage as defined by innodb_max_dirty_pages_pct. So, in that respect, innodb doesn't really evict the cache on update. Am I right to say this or am I understanding adaptive flushing wrongly?

You have mistaken about the dirty pages flushing. This has nothing to do with cache. RDBMS by default are configured to exhibit ACID properties. As such, all updates from the clients to the database must persist into the secondary storage once the server responded successful.

How database do is to first journal the updates into the redo log, then data files are updated. Here the dirty pages are updates that are only persisted in the memory and the redo logs, but not the data files.

As for the Query Cache design, it was design at the early stage of MySQL. Back then, multiple cores are not a trend and hence the locking contention is not major issue. Just that now due to high number of cores that the contention becomes a problem. Also back then MySQL ISAM tables are not transactional. Most yesterdays designs always looks inadequate in today and tomorrow.