Database Performance Tuning for Magento

Magento's database performance is unusually sensitive to MySQL configuration because of the EAV (Entity-Attribute-Value) data model that backs catalog and customer data. EAV is highly normalized — a single product attribute lookup can hit 4–6 different tables — which means MySQL needs to keep more of its working set in memory than a typical CMS workload would require.

The single most important MySQL setting

innodb_buffer_pool_size determines how much of the database can stay in RAM instead of hitting disk. For Magento, this should be the largest value the host can sustain — typically 50–75% of available system memory on a database-only server, or 30–50% on a combined application+database server.

Stores with the buffer pool sized below the catalog working set produce a characteristic slowness pattern: fast on the first hit, then progressively slower as the database starts evicting cached pages back to disk. The fix is almost always buying more RAM rather than a clever query rewrite.

Indexer state

Magento maintains nine core indexers — the URL rewrites indexer, product price indexer, stock indexer, search indexer, and so on. When indexers are 'invalid' (out of sync with their source data), Magento falls back to non-indexed queries that are typically 10–100× slower.

php bin/magento indexer:status

# If any are 'Reindex required':
php bin/magento indexer:reindex

# Set indexers to 'Update by Schedule' rather than 'Update on Save'
# so that index updates batch through cron rather than happening
# inline on every catalog edit:
php bin/magento indexer:set-mode schedule

Slow query log

Enable MySQL's slow query log with a threshold of 1 second and analyze the output regularly:

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

On a healthy Magento store the slow query log should be quiet — fewer than 10 entries per hour during normal operation. A noisy slow query log almost always points at one of three patterns:

• Catalog search queries on un-indexed product attributes. Layered-navigation filters on attributes that aren't marked 'Use in Layered Navigation' produce full-table scans on the catalog_product_entity_int table.
• Sales report queries during peak traffic. The Magento sales reports (Top Sellers, Most Viewed) run aggregation queries that should be moved to the report indexer rather than running them inline.
• SELECT * patterns in custom modules. Custom module developers occasionally use Magento's collection API in a way that produces SELECT * queries against the EAV tables. These look fine on a development store with a small catalog and devastating on a 50,000-SKU production store.

MariaDB or MySQL?

Magento 2 officially supports both MySQL 8.0+ and MariaDB 10.4+. In production benchmarks the performance difference between the two is small — typically within 5–10% on identical workloads, with MySQL slightly ahead on heavy write workloads and MariaDB slightly ahead on read-heavy workloads. The choice is more often dictated by hosting-provider defaults than by performance considerations.