Caches and Read Replicas Solve Different MySQL Bottlenecks

A Redis-style cache and a MySQL read replica can both reduce reads on the primary database, but they do different work. A cache avoids repeating selected work by serving a previously stored value. A read replica accepts SQL and performs the database work on another MySQL instance. That distinction decides which layer can help, what can become stale, and which new failure modes the application must own.

This article is for developers and database operators choosing a read-scaling design. It assumes familiarity with SQL, application requests and basic MySQL replication. The goal is not to pick a universal winner. It is to classify each read by reuse, query shape and correctness before adding infrastructure.

First Classify the Read, Not the Database

“The database is busy” is not a usable architecture requirement. Capture the endpoint or job creating load, the exact query family, calls per second, rows examined, result size, latency distribution, update frequency and the freshness the business action requires. Two endpoints that read the same tables can need different paths.

A public product page may request the same assembled result thousands of times while the underlying rows change only occasionally. That is a strong cache candidate: one computed answer can satisfy many requests. An internal report with different filters, joins and date ranges has little result reuse; a read replica can execute those varied queries without competing with primary reads. A checkout confirmation or permission check may require the newest committed state and should stay on the primary unless the system has a proven consistency mechanism.

The first measurement is therefore not overall read percentage. Measure reuse concentration: which small set of keys or result shapes accounts for a large share of calls? Then measure staleness tolerance: what is the permitted age after a write? Finally, identify query expressiveness: does the reader need MySQL to evaluate arbitrary predicates, joins, sorting or aggregation each time?

High reuse plus a bounded answer favors caching. Broad query diversity plus tolerance for replication delay favors a replica. Strong read-after-write or decision-grade correctness favors the primary. Real systems often use all three paths.

A Cache Avoids Work

Cache-aside starts with an application lookup. On a hit, the application returns the cached value without asking MySQL. On a miss, it reads MySQL, stores the result under a defined key and returns it. AWS’s current ElastiCache strategy documentation describes the same lazy-loading path and its two central costs: a miss penalty and the possibility of stale data.

That behavior explains the cache’s best workload. Repeated reads must map to stable, predictable keys; the stored value should be smaller or cheaper than recomputing the underlying query; and the hit ratio must remain high enough to justify another network hop and service. Highly variable filters can create a huge key space with few hits, turning the cache into memory spent on one-off answers.

Freshness is an application contract. A time to live (TTL) limits how long a key survives, but it does not prove that a value is current during that window. Event-driven invalidation can shorten the window, yet every write path must publish or perform the correct invalidation. Derived results are harder: changing one product may affect category pages, search results, counts and recommendation keys.

Read-after-write deserves an explicit route. After a user changes an address, inventory reservation or permission, reading the old cached object is not a harmless performance detail. The application can update or evict the relevant key after a successful commit, bypass cache for the confirming read, attach a version to the value, or route that action to the primary. Choose the correctness behavior before choosing the TTL.

A cache failure should normally degrade to MySQL, but a cold or recovering cache can send a sudden miss wave to the database. Request coalescing, randomized expirations, bounded concurrency and prewarming of genuinely hot keys reduce that stampede risk. The fallback capacity must be tested; “the database is the fallback” is not enough if it collapses when every cache node restarts.

The cache also becomes an operated data system. Observe hit and miss rates by endpoint, evictions, used memory, latency, errors, connection saturation and hot keys. Persistence work can affect latency too; when Redis background saving coincides with a tail spike, the Redis fork and copy-on-write investigation separates a cache-speed problem from MySQL load.

A Read Replica Moves Work

A read replica contains a copy of the database and runs the SQL it receives. It preserves relational operations: joins, filtering, sorting, grouping and access to the wider dataset. That makes it suitable for reports, dashboards, exports and varied application reads that would be expensive or ineffective to represent as cache keys.

Amazon RDS’s read-replica documentation defines the operating boundary clearly: applications route queries to the replica, updates from the primary are copied asynchronously, and the replica may be stale. It also distinguishes read replicas from synchronous standby instances. Read scaling and high-availability failover are related design concerns, not interchangeable labels.

Moving SQL does not make the SQL free. A poor index, large scan or expensive sort still consumes CPU, memory and I/O on every replica that runs it. Replication itself also uses network, logs and apply capacity. If a heavy report delays the apply thread, the system can become more stale exactly when the report load is highest.

Lag needs an application-facing policy, not only a dashboard. Define the maximum acceptable lag for each routed read and decide what happens beyond it: fall back to the primary, return a deliberately labeled stale view, pause the job or fail closed. The existing MySQL receiver-versus-applier lag diagnosis shows why one lag number cannot identify whether transport or replay is behind.

Connection routing is another responsibility. The application or proxy must separate write/authority traffic from replica-safe reads, and transactions cannot be split casually across endpoints. A write followed by a read on an asynchronous replica can return the older state even when both operations succeeded. Sticky-primary windows, version checks or primary reads for the affected workflow are common solutions, but the exact rule belongs in the product contract.

Replicas can also serve a recovery role after deliberate promotion, yet promotion changes the topology and does not erase missing transactions. Do not count a read replica as a tested failover system until promotion, client reconnection, write authority, data-loss boundary and re-seeding have been rehearsed.

Correctness Decides Which Reads May Move

Display reads and decision reads should not share a default merely because they use SELECT. A cached blog page can tolerate a short TTL. A catalog browse may tolerate replica lag. A stock decrement, authorization check, payment state or post-update confirmation can cause harm if it observes old data.

Write-path pressure remains on the primary. Neither a cache nor a read replica removes inserts, updates, deletes, index maintenance or transaction conflicts. When competing transactions deadlock, use the MySQL lock-order and retry analysis rather than expecting another read node to change the write cycle.

The two read layers also expose different stale states. A replica is generally an ordered but delayed database view under its engine’s replication guarantees. A cache may contain only selected objects, each with its own fill time, TTL and invalidation history. One page can therefore combine values from different moments unless the application carries versions or composes the result behind one consistency rule.

Security and privacy follow the copied data. A full replica expands the locations holding the database and the credentials able to query it. A cache should store only the fields needed for its read contract, with a TTL and access policy appropriate to the data. Masking secrets in the application response does not remove them from a broadly cached object or replica.

Measure the Bottleneck Before Adding a Layer

Start with query evidence. Slow logs, execution plans, rows examined, buffer-pool behavior, lock waits, CPU, storage latency and connection concurrency may reveal that the primary needs an index, a query rewrite or safer admission control. A cache with a low hit ratio and a replica executing the same inefficient plan simply distribute the original mistake.

Connection exhaustion is a different bottleneck again. The PgBouncer connection-admission guide targets PostgreSQL rather than MySQL, but its boundary is instructive: limiting concurrent database work is distinct from caching results or replicating reads. For MySQL, apply the same diagnostic separation with MySQL-compatible pooling and connection metrics rather than copying PostgreSQL configuration.

Application queues can dominate perceived database latency. If PHP requests wait before they even issue SQL, the PHP-FPM worker saturation path helps prove whether the delay belongs to the application pool. Add a data layer only after tracing where time and contention accumulate.

Run one representative experiment at a time:

• For a cache, record baseline MySQL calls and latency, then measure hit ratio, miss penalty, database offload, stale-read incidents and cold-cache behavior.
• For a replica, record primary CPU/I/O and query latency, then measure routed read volume, replica query cost, apply lag and the product behavior when lag crosses its limit.
• For both, test dependency failure and recovery. Confirm whether the application fails open, fails closed, falls back or overloads another layer.

Compare designs against the same traffic sample and correctness rules. An AWS benchmark can demonstrate a cache’s potential for one dataset and query—its RDS for MySQL and ElastiCache study includes a specific MySQL version, instance size, dataset and cacheable query—but its cost and throughput numbers are not portable defaults.

Reproduce One Read Path Before Buying Infrastructure

The following lab turns the architecture decision into evidence. It was reproduced in a disposable MariaDB 11.8.6 compatibility instance with Performance Schema enabled and Redis 8.0.2 over local Unix sockets. The SQL shown here is shared with MySQL 8, but MariaDB is not a substitute for a final test on the exact MySQL release and replica topology used in production. Use a read-only MySQL account for the first four inspections, keep credentials in an option file rather than the command line, and substitute the real schema and query family.

First find repeated query shapes instead of guessing from total CPU. COUNT_STAR shows frequency, while SUM_ROWS_EXAMINED versus SUM_ROWS_SENT exposes work that discards many rows. Performance Schema summaries reset after restart or an explicit truncate, so record the observation window with the result.

SELECT
  LEFT(DIGEST_TEXT, 120) AS query_shape,
  COUNT_STAR AS executions,
  SUM_ROWS_EXAMINED AS rows_examined,
  SUM_ROWS_SENT AS rows_sent,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_seconds
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'application_db'
  AND DIGEST_TEXT LIKE 'SELECT%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Take one dominant digest and inspect its real predicate values with EXPLAIN FORMAT=JSON. Do not run EXPLAIN ANALYZE blindly on a production query: it executes the statement. This read-only plan check proves whether MySQL can use the intended composite index before a cache or replica hides an inefficient access path.

EXPLAIN FORMAT=JSON
SELECT id, name, price_cents
FROM products
WHERE category_id = 10
  AND active = 1
ORDER BY price_cents
LIMIT 20;

Now test one namespaced cache key with a short TTL. The two hits and one deliberate miss below are a plumbing check, not a production hit-ratio benchmark. A real acceptance run must replay representative endpoint traffic and compare database calls, latency and stale-read behavior before and after caching.

CACHE_KEY='voxfor:readlab:catalog:v1:category:10'
redis-cli SET "$CACHE_KEY" \
  '[{"id":1,"price_cents":1200},{"id":2,"price_cents":2400}]' EX 300
redis-cli GET "$CACHE_KEY"
redis-cli GET "$CACHE_KEY" >/dev/null
redis-cli GET 'voxfor:readlab:catalog:v1:category:999' >/dev/null
redis-cli INFO stats | grep -E '^(keyspace_hits|keyspace_misses):'

A replica needs a different proof. Run the status query on the actual MySQL 8 replica and capture at least the receiver thread, applier thread, delay and last SQL error. Seconds_Behind_Source can be NULL when replication is not running and is not a complete latency measurement, so the application still needs a freshness limit and a fallback rule.

mysql --batch --raw -e 'SHOW REPLICA STATUS\G' |
  grep -E 'Replica_(IO|SQL)_Running:|Seconds_Behind_Source:|Last_SQL_Error:'

This is the representative output observed in the disposable compatibility lab. The digest executed twice and examined four rows to return four rows. The plan used the intended composite index and estimated two qualifying rows. Redis then recorded two hits and one controlled miss. Those values prove the fixture behaved as designed; they are not targets to copy into a production SLO.

query_shape: SELECT ID, NAME, price_cents FROM products WHERE category_id = ? AND active = ? ...
executions: 2
rows_examined: 4
rows_sent: 4

access_type: ref
key: idx_category_active_price
used_key_parts: [category_id, active]
rows: 2

keyspace_hits:2
keyspace_misses:1

Write acceptance criteria before changing routing. For this example, the query plan must use the expected index without a full scan, the cache experiment must reduce MySQL executions for the selected endpoint under representative traffic, and the replica path must be enabled only while both replication threads are healthy and measured lag remains below that endpoint’s declared freshness limit. The commands below fail closed for an absent plan key or an unhealthy replica; adjust the index name and maximum lag to the contract you recorded.

EXPECTED_INDEX='idx_category_active_price'
MAX_LAG_SECONDS=5

mysql --batch --raw -e "EXPLAIN FORMAT=JSON
SELECT id,name,price_cents FROM products
WHERE category_id=10 AND active=1 ORDER BY price_cents LIMIT 20" |
  grep -Fq "\"key\": \"$EXPECTED_INDEX\"" || exit 1

replica_status=$(mysql --batch --raw -e 'SHOW REPLICA STATUS\G')
grep -q 'Replica_IO_Running: Yes' <<<"$replica_status" || exit 1
grep -q 'Replica_SQL_Running: Yes' <<<"$replica_status" || exit 1
lag=$(awk -F': ' '/Seconds_Behind_Source:/ {print $2}' <<<"$replica_status")
test "$lag" != NULL && test "$lag" -le "$MAX_LAG_SECONDS"

Rollback must remove only the experiment’s route and namespaced data. Disable the application feature flag or routing rule first, let in-flight requests drain, then remove the exact disposable cache key and lab schema. Never use FLUSHALL, wildcard deletion against an unscoped namespace or DROP DATABASE against an application schema.

# First disable the application's cache/replica feature flag and drain traffic.
redis-cli UNLINK 'voxfor:readlab:catalog:v1:category:10'
mysql -e 'DROP DATABASE IF EXISTS voxfor_readlab;'

If the primary query remains expensive after indexing, caching can avoid repeated execution for a reusable result while a replica moves varied SQL to separate compute. The measurement above tells you which effect you are buying and provides a reversible boundary for the rollout.

Use Cache, Replica or Both Deliberately

Choose a cache when a limited set of answers dominates traffic, the application can define cache keys and invalidation, and a database bypass materially reduces work. Choose a read replica when reads need relational flexibility across a wide dataset, can tolerate a defined replication delay, and benefit from separate database compute or I/O.

Use both when the read portfolio contains both shapes. Hot product or configuration objects can be cached; reports and variable searches can run on replicas; authority-sensitive actions can stay on the primary. Momento’s Aurora scaling analysis frames caching as a way to absorb eventually consistent reads, while Pearson’s read-replica comparison notes that replicas retain the whole dataset and full relational query capability. Their product contexts differ, but that architectural split is useful.

Do not add both on the same day without separate acceptance criteria. Otherwise a latency improvement cannot be attributed, a stale read cannot be traced, and capacity may be overbuilt. The smallest design that passes the workload and correctness test is easier to operate and reverse.

Record the Read Contract

For every moved read, preserve six facts: endpoint or job, query family, freshness limit, selected path, fallback behavior and measured proof. Add cache hit ratio or replica lag as appropriate, plus the owner who responds when the boundary fails.

That record turns “we need Redis” or “add a replica” into a testable design. When traffic, query diversity or correctness rules change, reclassify the read before scaling the layer that served yesterday’s workload.