On a well-cached Magento store, MySQL should be nearly idle - full page cache absorbs catalog traffic, Redis absorbs sessions and cache. When MySQL is busy anyway, either caching is leaking or the catalog has outgrown default tuning. Here is how to tune the database layer for Magento’s actual workload.
innodb_buffer_pool_size: The One Setting
InnoDB’s buffer pool caches table and index pages in memory. The rule: fit your working set in RAM. For Magento, the working set is roughly the catalog, index, and quote tables - measure real table sizes:
SELECT ROUND(SUM(data_length+index_length)/1024/1024/1024,1) AS gb
FROM information_schema.tables WHERE table_schema='magento';
Set innodb_buffer_pool_size to the working set size with headroom (a 20GB database wants a 24-28GB pool if the server allows). A pool smaller than the working set means every uncached query hits disk - the single most common cause of slow Magento databases.
Supporting settings:
innodb_log_file_size=2G
innodb_flush_log_at_trx_commit=2 # slight durability trade for big write gains; use 1 for strict
innodb_flush_method=O_DIRECT
max_connections=300 # sized to FPM workers * nodes + cron + headroom
Magento’s Query Patterns
Know what you are tuning for: EAV joins (many-table reads), layered navigation aggregations, price index lookups, and heavy write bursts from imports and reindexing. Practical implications:
- Slow query log on, threshold 0.5s, reviewed weekly - Magento’s worst queries are usually layered navigation on huge attribute sets or missing indexes on custom tables
- Index hygiene on custom tables: every custom module table queried by a non-indexed column is a full table scan waiting for traffic
- Watch
url_rewriteandcatalogsearchtables on large catalogs - they grow into query-time problems before anyone notices their size
Connections and Concurrency
max_connections exhaustion is a classic peak-day failure: each FPM worker can hold a connection, plus cron, consumers, and imports. Size it from your actual max concurrency, monitor Threads_connected vs limit, and prefer fixing connection leaks (persistent connections gone wrong, forgotten imports) over endlessly raising the limit.
Scaling Reads (When You Actually Need To)
Read replicas are a real option: Magento can route SELECT traffic to replicas via database connection configuration. The complexity cost is real (replication lag means a customer can change a product and not see it for seconds) - worth it at genuinely high read volume, overkill below it. Exhaust buffer-pool tuning and cache hit ratios first; most “we need read replicas” conversations end with a bigger buffer pool and a fixed cache leak.
The database layer rewards fundamentals: RAM for the working set, indexes for the queries you actually run, connections sized for your real concurrency, and a slow log habit. Do those four and MySQL stops being the bottleneck people assume it is.