Understanding MySQL Query Cache, InnoDB Buffer Pool, and ReadySet Middleware for Performance Optimization

Introduction: The product version upgraded from 2.X to 3.X, requiring testing and performance testing in a clean environment. After half a month of operation, query performance degraded, prompting an investigation of database caching mechanisms.

Query Cache: Describes MySQL query cache (querycahce) historically considered of limited use, disabled by default in MySQL 5.6 and removed in MySQL 8. It caches result sets keyed by the full SQL text, offering large speed gains but has drawbacks: strict byte‑level matching, suitable only for read‑heavy workloads, and incurs overhead for non‑cached queries. Therefore it is generally not recommended in production.

InnoDB Buffer Pool: Explains the innodb_buffer_pool_size parameter, which caches disk pages in memory to reduce I/O. Describes how InnoDB reads data in pages using B+‑tree indexes, loads whole pages into the buffer pool, and subsequent queries can reuse cached pages. Mentions change_buffer tracking and LRU eviction. Provides a command to view the current setting and suggests setting it to roughly a quarter of system memory.

show variables like '%innodb_buffer_pool_size%';

ReadySet Middleware: Introduces ReadySet, a high‑performance lightweight MySQL/PG caching middleware discovered on GitHub. It implements a data‑flow graph that snapshots query results in memory, similar to MySQL query cache and buffer pool, and can be managed with CREATE CACHE FROMSQL and SHOW CACHES commands. It listens to binlog for updates, provides eventual consistency, but may consume significant memory, making it suitable for BI‑type workloads.

Conclusion: While MySQL’s built‑in caching mechanisms have limitations, tuning the InnoDB buffer pool and exploring external middleware like ReadySet can substantially improve query latency in upgraded product environments.