Modern MySQL Scalability, Caching and Workload Suitability

Introduction

MySQL is one of the most popular relational databases. Early versions offered a query cache that stored the results of select statements in shared memory. Many critiques of MySQL’s scalability compare this early cache to modern, horizontally‑scaled caches such as Redis Cluster or Memcached, leading to the impression that MySQL cannot scale and that application‑level caches have always been superior. However, MySQL has evolved significantly since version 4, and its built‑in cache was removed altogether in 8.0 because it could not scale on multi‑core machines. This article revisits MySQL’s caching and replication mechanisms in light of recent developments and discusses when MySQL is appropriate for different workloads.

Evolution of MySQL Caching and Replication

Query cache limitations and deprecation

The query cache introduced in early MySQL versions stored the result of each query in a global cache. On any modification to a table, the entire cache was invalidated, and queries needed to match exactly to hit the cache. These design choices meant the cache did not scale on multi‑core or high‑throughput workloads, and performance benefits were unpredictable because only queries that hit the cache benefited. Due to these limitations, MySQL 5.6 disabled the cache by default and, starting with MySQL 8.0, Oracle removed it entirely.

Modern read scalability through replication

While the built‑in cache has been removed, MySQL’s replication mechanisms have greatly improved. Versions 5.6 and later introduced Global Transaction IDs (GTIDs), event checksums, multi‑threaded slaves and crash‑safe replication. Replication can be asynchronous or semi‑synchronous; asynchronous replication is the default because it allows the primary to process writes without waiting for replicas, minimizing write latency. With replication, multiple read‑only replicas can serve queries in parallel, distributing read load across servers. Cloud providers like Azure recommend using read replicas to isolate read workloads, with proxies or microservices directing writes to the primary and reads to replicas. Typical scenarios include scaling read‑heavy applications, offloading analytics/reporting queries and ingesting IoT data to multiple read nodes. Thus, modern MySQL does scale horizontally for reads when configured with replication and appropriate proxies, challenging the assertion that it cannot leverage additional CPU cores.

Modern Caching Strategies

Application‑level caches

Because MySQL’s query cache is gone, caching responsibilities have shifted closer to the application. Redis Cluster, Memcached and similar caches shard or hash data across multiple nodes, enabling horizontal scaling. For example, Redis Cluster divides the keyspace into hash slots; each node manages a subset of slots and has replica nodes for redundancy. The cluster can continue operating when some nodes fail, though replication is asynchronous and a master failure before replication can cause loss of recent writes. Application code must integrate these caches, storing frequently accessed data outside the database to reduce load and latency.

Proxy‑based query caching

Developers or DBAs who cannot modify application code can deploy caching inside a database proxy. ProxySQL offers a “query cache” that intercepts queries between the application and MySQL, caches result sets and serves repeated queries without hitting the database. DBAs define which queries to cache and the time‑to‑live for each cache entry. Cloud architectures may place multiple ProxySQL servers behind a load balancer, routing read traffic to MySQL reader endpoints while writes go to the primary, providing high availability and cost‑effective scalingbuilder.aws.com. Benchmarks from AWS show that ProxySQL with MySQL 8.0 can equal or exceed the performance of MySQL 5.7’s obsolete query cachebuilder.aws.com.

Drop‑in SQL caching engines (Readyset)

Readyset is a newer technology that acts as a query‑result cache for MySQL and PostgreSQL. It registers itself as a replica on the upstream database and maintains cached result sets using a dataflow graph. When the source database receives writes, Readyset updates its caches automatically, ensuring that cached query results remain consistent without developer intervention. Applications connect through the MySQL protocol; if a query is not in the cache, Readyset forwards the query to the upstream database and stores the result for subsequent requests. Because Readyset is built as a read replica, it scales horizontally by adding more Readyset nodes, and it integrates with ProxySQL rules to determine which queries should be cached.

Suitability of MySQL for Different Workloads

OLTP vs. OLAP

MySQL is designed for online transaction processing (OLTP). OLTP systems handle many concurrent transactions, focusing on short operations, ACID compliance and millisecond response times. Workloads such as user accounts, e‑commerce and operational systems benefit from the strong consistency guarantees and indexing capabilities of MySQL. In contrast, online analytical processing (OLAP) systems are optimized for complex queries over large datasets and are commonly used for dashboards, reporting and historical analysis. OLAP databases use multidimensional data models to support aggregations and predictive analytics. Using MySQL for heavy analytical dashboards may force the database to sacrifice performance for consistency, so organizations often extract data into columnar stores (e.g., ClickHouse, BigQuery, Snowflake) or real‑time analytics engines.

When to use MySQL and when to look elsewhere

• Transactional workloads with high concurrency: MySQL remains a strong choice. Combining row‑level replication and ACID semantics ensures data integrity and low write latency. Read replicas can scale reads for web applications or microservices.
• Read‑heavy services with predictable query patterns: Use an external cache (Redis or Memcached) or a proxy cache (ProxySQL). Modern caching layers address the deficiencies of the old query cache and allow horizontal scaling through sharding.
• Real‑time analytics or dashboards: Consider using a dedicated OLAP system or caching engine. Readyset can cache query results from the OLTP database and update them automatically when data changes, providing a middle ground. For more complex analytical workloads, extract data into a columnar warehouse built for large scans and aggregations.

Conclusion

Comparisons of MySQL’s early query cache with modern caching technologies are misleading because the query cache was removed due to scalability issues. Today, MySQL can scale reads horizontally using improved replication mechanisms and cloud‑managed read replicas. Application‑level caches such as Redis Cluster and Memcached, proxy‑level caches like ProxySQL, and drop‑in engines like Readyset provide powerful alternatives to the obsolete query cache, enabling caching at the network or replica layerr. Recognizing MySQL’s role as an OLTP database and choosing the right data store for each workload—relational for transactions, caches for hot reads and specialized OLAP engines for analytics—ensures both scalability and efficiency.