MySQL and MariaDB Tuning for Adobe Commerce: innodb_buffer_pool_size, the Slow Query Log, and the Settings Magento Actually Needs

For Magento and Adobe Commerce, the MySQL or MariaDB settings that matter most are innodb_buffer_pool_size, max_connections, tmp_table_size and max_heap_table_size for indexers, max_allowed_packet, and explicit_defaults_for_timestamp. On MariaDB 10.4 and 10.6, two optimizer flags affect reindex speed. Then turn on the slow query log and fix what it finds.

Database tuning advice for Magento tends to fall into two camps. One is a generic my.cnf copied from a forum, full of settings that no longer exist in MySQL 8. The other is “just add RAM”. Neither tells you which settings Adobe actually documents, which ones matter for indexers versus the storefront, and how to prove that the database is the bottleneck before you change anything. This guide covers those settings with their sources, the requirements that are not about speed at all, and a method for finding slow queries on Magento and Adobe Commerce.

First, confirm the database is the problem

Before touching my.cnf, check where time goes. A slow product page can be PHP, cache misses, an external API call, or the database. Tuning MySQL when the real issue is a cold full page cache wastes a maintenance window.

Three quick checks tell you whether to keep reading:

1. Profile a slow request. An APM trace shows how much of a request is spent in SQL. Our guide to profiling a slow Magento 2 request walks through doing this with Blackfire, XHProf, and New Relic.
2. Look at the process list during a slowdown. Adobe’s knowledge base article on checking slow queries and processes suggests running SHOW PROCESSLIST and looking for queries with a Time value above 1800 seconds, noting each one’s State.
3. Check the size of your tables. Bloated log and quote tables make every query on them slower. If quote or the customer visitor tables hold years of rows, cleanup comes before tuning. See our article on Magento database bloat and quote cleanup.

If SQL time is a large share of slow requests, or the process list shows queries piling up, the settings below are where to start.

The settings that matter, and what Adobe says about each

Adobe’s software recommendations for performance and its MySQL installation guidelines together cover a short list. The table summarizes them, and the sections that follow explain each one.

innodb_buffer_pool_size

The buffer pool is where InnoDB caches table and index pages. If your working set fits, most reads never touch disk. If it does not, every cache miss is a disk read, and a busy catalog page can trigger many of them.

Adobe’s recommendation is to choose a combination of instances and pool size so each instance is at least 1 GB, and to configure the buffer pool so index tables stay in memory. For the overall size, the MySQL reference manual’s general guidance for a dedicated database server is that the buffer pool can be set as high as roughly 80 percent of physical memory. If the database shares a host with PHP-FPM, Redis, or OpenSearch, leave room for them. Our guide to tuning PHP-FPM, OPcache, and Redis covers sizing those so the pieces do not compete.

You can check whether the pool is large enough by comparing two status counters:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_read_requests counts logical reads. Innodb_buffer_pool_reads counts reads that had to go to disk. If the second number grows quickly relative to the first during normal traffic, the pool is too small for your working set.

On MySQL 5.7 and later the buffer pool can be resized online with SET GLOBAL innodb_buffer_pool_size = .... Persist the value in my.cnf as well, or the change is lost on restart.

max_connections

Every PHP-FPM worker that talks to the database holds a connection. Adobe’s guidance is to correlate max_connections with the total number of PHP threads across your application servers, with 300 suggested for a small environment and 1,000 for a medium one. Add headroom for cron, queue consumers, and admin users, which also connect. If you scale web nodes horizontally, recalculate this whenever you add a node.

tmp_table_size and max_heap_table_size

Indexers build large intermediate results. When a temporary table exceeds the in-memory limit, MySQL converts it to an on-disk table, which is much slower. Adobe’s installation guide states that indexers require higher tmp_table_size and max_heap_table_size values and gives 64M as an example. Set both, because the effective in-memory limit is the smaller of the two. If you tune indexer batch sizes, revisit these values at the same time.

max_allowed_packet

Adobe documents a default of 16 MB and suggests raising it if you import large numbers of products. When a client or server receives a packet larger than this limit, MySQL rejects it and drops the connection, which shows up in Magento’s exception log during imports. On Adobe Commerce on cloud infrastructure Pro plans, Adobe states you must open a support ticket to change it.

innodb_thread_concurrency

The default of 0 means no limit. Adobe gives a formula: innodb_thread_concurrency = 2 * (NumCPUs + NumDisks). Treat it as a starting point and measure. On modern hardware with fast storage, many teams leave it at 0 unless they see contention.

Settings that are about correctness, not speed

Some database settings will not make Magento faster, but getting them wrong causes failed deploys and confusing errors.

explicit_defaults_for_timestamp must be ON. Adobe’s installation guide states this is required so TIMESTAMP columns match the declarative schema. Without it, bin/magento setup:db:status keeps reporting that the declarative schema is not up to date, even right after an upgrade. Adobe also notes that on cloud infrastructure this variable defaults to OFF for MariaDB, so check it on every environment.

Use row-based replication only. Adobe states that Commerce does not support statement-based replication.

Be careful with GTID replication. Adobe notes that Commerce uses CREATE TEMPORARY TABLE statements inside transactions, which conflicts with GTID-based replication on some managed services.

Do not add custom triggers. Commerce creates its own MySQL triggers for scheduled indexing, and Adobe states that custom triggers are not supported. Our guide to indexer Update on Schedule and mview changelogs explains what those triggers do.

Enable strict SQL mode for new data. Adobe’s guidance mentions STRICT_ALL_TABLES to block invalid values, while also noting that its MySQL 8 configuration sets SQL_MODE empty to disable strict validation, which means MySQL can truncate data. Decide this deliberately and keep it the same across environments.

MySQL vs MariaDB: the differences that matter

Both are supported for current Adobe Commerce releases, but they diverge on a few points that affect Magento directly. Check Adobe’s system requirements for the exact versions supported by your release.

The MariaDB optimizer flags are worth knowing about because the symptom is easy to misread. If a full reindex got noticeably slower after a database upgrade and nothing else changed, test those two settings on staging before you start rewriting indexers.

Finding slow queries

Once the server is configured sensibly, the remaining wins come from specific queries. The slow query log is the tool for that.

Turn it on in my.cnf:

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

According to the MySQL slow query log documentation, the default long_query_time is 10 seconds, which is far too high to catch the queries that make a storefront feel sluggish. One second is a common starting threshold. Lower it temporarily if you are hunting a specific problem, and watch disk usage, because a low threshold on a busy store writes a lot.

To read the log, a raw grep works for spotting extreme cases. Adobe’s knowledge base gives this example for queries over 50 seconds:

grep 'Query_time: [5-9][0-9]\|Query_time: [0-9][0-9][0-9]' /var/log/mysql/mysql-slow.log -A 3

For real analysis, aggregate. pt-query-digest from Percona Toolkit groups queries by fingerprint and ranks them by total time, so you fix the query that costs the most overall rather than the single slowest run. MySQL also ships mysqldumpslow for a simpler summary.

When you find an expensive query, trace it back to its source before you add an index. In Magento, slow queries usually come from one of a few places: a third-party module doing unbounded collection loads, a custom report or export run during peak hours, layered navigation over very large attribute sets, or indexers running in Update on Save mode while merchandisers edit products. The fix is often in the code or the schedule, not the database.

Correlate timestamps too. Adobe’s knowledge base suggests matching slow query times against outages and checking what else was running: cron jobs, imports and exports, bot traffic, or database dumps.

A tuning sequence that avoids guesswork

1. Measure first: profile slow requests and confirm SQL is a meaningful share.
2. Clean up bloated tables before resizing anything.
3. Set the correctness settings: explicit_defaults_for_timestamp, replication format, SQL mode.
4. Size innodb_buffer_pool_size for your working set and check the read counters.
5. Set max_connections from your real PHP worker count plus headroom.
6. Raise tmp_table_size and max_heap_table_size together for indexers.
7. On MariaDB, test the two optimizer flags if reindexing is slow.
8. Enable the slow query log at one second, aggregate with pt-query-digest, and fix the top offenders in code.
9. Change one thing at a time and record before and after numbers.

Where Bemeir fits

Database tuning sits between hosting and application code, which is why it often falls through the cracks between a hosting provider and a development team. A Hyva storefront removes a lot of frontend weight, which makes server response time, including database time, a bigger share of what customers feel. The same thinking applies if you also run Shopify, Shopware, or BigCommerce storefronts, though on hosted platforms the database is the vendor’s problem rather than yours. You can learn more about Bemeir, see our technology partners, or start at the Bemeir home page.

FAQ

What should innodb_buffer_pool_size be for Magento?

Large enough to hold your working set of catalog and index data. Adobe recommends sizing it so each buffer pool instance is at least 1 GB and keeping index tables in memory. On a dedicated database server, MySQL’s general guidance allows up to roughly 80 percent of RAM. Leave room for other services if the host is shared.

What max_connections value does Adobe recommend?

Adobe says max_connections should correlate with the total number of PHP threads on your application servers, and suggests 300 for a small environment and 1,000 for a medium one. The MySQL default is 150. Add headroom for cron, queue consumers, and admin sessions.

Why does setup:db:status always say the schema is not up to date?

The usual cause is explicit_defaults_for_timestamp being OFF. Adobe requires it to be ON so TIMESTAMP columns match the declarative schema. Adobe notes it defaults to OFF for MariaDB on cloud infrastructure. Turn it on, restart the database, and check again.

Is MariaDB slower than MySQL for Magento reindexing?

Adobe notes that reindexing on MariaDB 10.4 and 10.6 can be slower, and suggests considering optimizer_switch set to rowid_filter=off and optimizer_use_condition_selectivity set to 1. Test both on staging with a full reindex before and after, rather than assuming either database is faster.

How do I find slow queries in Magento?

Enable the MySQL slow query log with long_query_time around one second, since the default is 10 seconds. Aggregate the log with pt-query-digest to rank queries by total time, then trace the top ones back to the module, cron job, or indexer that issues them.