Magento 2 MySQL Tuning: Settings and Slow Queries

Most Magento 2 performance work starts at the application, and that is the right place to start. Still, a store with a badly configured database will stay slow no matter how good the code is. Indexers take hours, the admin grid hangs, checkout waits on locks. This article covers the MySQL and MariaDB settings that matter for Magento Open Source and Adobe Commerce 2.4.8, how to find the queries that actually hurt, and a few Magento specific requirements that are easy to miss.

Supported versions first

Before tuning anything, check that the database version is one Magento 2.4.8 supports. The 2.4.8 system requirements list MySQL 8.4 and MariaDB 11.4. Older 2.4 releases supported MySQL 8.0 and earlier MariaDB versions, so after an upgrade the database is often still on the old version. Running an unsupported version can work for a while and then fail in odd ways during setup:upgrade or under load. Check the official system requirements page for the exact list on the release you run.

Also make sure the database server is not sharing memory with everything else. On small setups MySQL, PHP-FPM, Redis, OpenSearch and Varnish often run on one machine, and each one is configured as if it had the whole box to itself. Tuning MySQL in that situation means first deciding how much RAM it really gets.

The InnoDB buffer pool

The single most important setting is innodb_buffer_pool_size. InnoDB keeps data and index pages in this memory area. If the working set of the store fits in it, most reads never touch the disk.

On a dedicated database server the usual starting point is around 70 percent of RAM. On a shared server, size it from the actual data. This query shows how big the data and indexes are:

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_gb DESC;

If your Magento schema is 6 GB, a buffer pool of 8 GB leaves room for growth. A buffer pool of 1 GB means InnoDB reads from disk all day. Remember that a big part of a typical Magento database is log and history tables that are rarely read, so the working set is usually smaller than the total. Cleaning those tables, covered below, also shrinks what the buffer pool has to hold.

You can watch how well the buffer pool works with:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_reads counts reads that had to go to disk. Innodb_buffer_pool_read_requests counts all logical reads. The first number should be a tiny fraction of the second.

A starting configuration

This is a reasonable starting point for a dedicated MySQL 8.4 server with 16 GB of RAM running one Magento store. Treat every number as a starting value to measure against, not a recommendation for your server.

[mysqld]
innodb_buffer_pool_size = 11G
innodb_redo_log_capacity = 2G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_file_per_table = 1

max_connections = 300
table_open_cache = 4000
table_definition_cache = 4000
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_slow_admin_statements = 1

log_bin_trust_function_creators = 1
binlog_expire_logs_seconds = 259200

Some notes on the less obvious lines.

innodb_redo_log_capacity

In MySQL 8.0.30 and later, innodb_redo_log_capacity replaces the old innodb_log_file_size and innodb_log_files_in_group pair. A redo log that is too small forces frequent checkpoints during big writes, which you feel during imports and full reindexes. On MariaDB the setting is still innodb_log_file_size.

innodb_flush_log_at_trx_commit

The default value of 1 flushes the redo log to disk on every commit. Setting it to 2 writes on commit but flushes about once per second. You can lose up to a second of transactions if the operating system crashes, not just MySQL. Many stores accept that trade for the write speed, especially on slow disks. If you cannot accept any loss, keep it at 1 and buy faster storage.

tmp_table_size and max_heap_table_size

Magento queries with GROUP BY, sorting on joined EAV tables and layered navigation aggregations create internal temporary tables. When they grow past these limits they go to disk. Check Created_tmp_disk_tables against Created_tmp_tables in global status. Raise both values together, since the smaller one wins, but do not set them huge. They are per temporary table, and many connections can use them at once.

max_connections

Each PHP-FPM worker, cron process and queue consumer can hold a connection. Add them up. If pm.max_children across all web nodes is 200 and you run consumers and cron, 300 is a sane ceiling. Setting it to several thousand does not help. It only lets the server accept more work than it can handle.

No query cache

The query cache was removed in MySQL 8.0, so old configs with query_cache_size will fail to start on MySQL 8.4. On MariaDB it still exists, but keep it off. Magento writes constantly, and every write to a table invalidates its cached queries, so the cache mostly adds lock contention.

Binary logs and triggers

When binary logging is on, which is the default in MySQL 8, creating triggers requires either the SUPER privilege or log_bin_trust_function_creators = 1. Magento creates triggers for every indexer that runs in Update by Schedule mode. Without the setting, switching indexers to schedule mode or running setup:upgrade fails with a privilege error on hosted databases where you do not get SUPER.

Binary logs also grow. If you do not use replication or point in time recovery, you can turn them off. If you do, set binlog_expire_logs_seconds so they do not fill the disk. A full disk on the database server is one of the most common reasons a Magento store goes down without any code change.

Find the slow queries

Server settings move the baseline. Slow queries are where the real gains are. With the slow query log on, use pt-query-digest from Percona Toolkit to group the log by query pattern:

pt-query-digest /var/log/mysql/slow.log --limit 20 > /tmp/slow-report.txt

The report ranks query patterns by total time, which is what you want. A query that takes 200 ms and runs 50,000 times a day matters more than a 30 second report query that runs once.

For each query at the top, run EXPLAIN and look for full table scans and filesorts on large tables. In Magento, the usual suspects are:

• Custom code filtering a collection on an attribute or column with no index.
• Third party extensions that query sales_order or quote with conditions on unindexed columns.
• Collections loaded without a page size, pulling thousands of rows into PHP.
• Queries against LIKE '%term%' on large text columns.

When a missing index is the answer, add it through db_schema.xml in your module, not by hand on the server. A manual index disappears from your other environments and can conflict with the declarative schema later. See the declarative schema guide for the syntax.

Keep the tables lean

A lot of Magento database weight is data nobody reads. Common growth points:

• quote and quote_item: old guest and customer carts. Magento's cron job for expired quotes cleans them based on the quote lifetime setting under Sales, Checkout, Shopping Cart. Make sure cron actually runs.
• customer_visitor and the report_* tables: visitor and report data. Magento cleans old visitor rows with a cron job, so again this depends on cron running. Report tables can be emptied if nobody uses the built in reports.
• Indexer changelog tables ending in _cl: these grow when indexers are in schedule mode and the index cron group is not processing them.
• Third party log tables from extensions that write every API call or every email to the database.

Before deleting anything by hand, take a backup and check whether the table is used by reports or integrations. Truncating the wrong table is a fast way to break an ERP sync.

Measure, change one thing, measure again

MySQL tuning goes wrong when people copy a config file from a forum and change twenty values at once. A better loop:

1. Record a baseline: slow log summary, buffer pool hit ratio, disk temp tables, and response times from your APM.
2. Change one setting or add one index.
3. Let it run under real traffic for a day or at least a few hours.
4. Compare against the baseline and keep or revert.

Tools like MySQLTuner give a useful list of hints, but read them critically. They do not know which queries matter to your store.

For the application side of the same problem, see the slow category pages checklist and the guide to profiling with New Relic, which shows which SQL statements dominate each transaction.

FAQ

How big should innodb_buffer_pool_size be for Magento 2?

On a dedicated database server, start around 70 percent of RAM. On a shared server, size it from the real data and index size of the Magento schema plus room to grow.

Why does Magento need log_bin_trust_function_creators?

Indexers in Update by Schedule mode use database triggers. With binary logging on, creating triggers needs SUPER or this setting, and hosted databases usually do not give SUPER.

Which MySQL version does Magento 2.4.8 support?

The 2.4.8 system requirements list MySQL 8.4 and MariaDB 11.4. Check the official requirements page for your exact release before upgrading the database.

Is innodb_flush_log_at_trx_commit = 2 safe for a store?

It can lose up to about a second of transactions if the server crashes. Many stores accept that for faster writes, but if you cannot accept any loss, keep the default of 1.

I am available for freelance Magento and Adobe Commerce work, including database performance reviews. If you need help with this, get in touch.