Advanced Magento 2 MySQL Optimization Strategies and Solutions

• 8 Steps to Optimize Your Magento 2 MySQL Database Faster

• Key Magento 2 Speed Optimization Techniques for MySQL

• 2 Steps To Perform MySQL Performance Tuning

• 7 MySQL Monitoring Tools for Magento Website Performance

• 3 Methods to Fix Performance Issues with PMM

• Advanced Optimization Strategies for Enhancing Magento Performance With MySQL

• 3 MySQL Database Software Options for Magento

• 3 Magento 2 MySQL Database Optimization Tips

• Troubleshooting Common Issues With MySQL Magento Optimization

• FAQs

• Summary

8 Steps to Optimize Your Magento 2 MySQL Database Faster

Step 1: Remove Unnecessary Entries from Magento Database

Truncate tables with unneeded data, including big logs or system events. This step makes your Magento 2 store's database run better. Also, do not forget to create a backup before truncating the following tables:

• dataflow_batch_export
• dataflow_batch_import
• log_customer
• log_quote
• log_summary
• log_summary_type
• log_url
• log_url_info
• log_visitor
• log_visitor_info
• log_visitor_online
• report_viewed_product_index
• report_compared_product_index
• report_event
• index_event
• catalog_compare_item
• catalogindex_aggregation
• catalogindex_aggregation_tag
• catalogindex_aggregation_to_tag
• adminnotification_inbox
• aw_core_logger

Step 2: Set Up Automatic Log Deletion

To keep your database from getting too large, set up log cleaning in Magento 2.4.7 following these steps:

1. Go to Stores > Configuration > Advanced > System > Log Cleaning.
2. Set 'Enable Log Cleaning' to "Yes".
3. Set 'Log Cleanup Frequency' ("daily" works well).
4. Set 'Save Log Days' to a good number ("7-14 days works" for most stores).
5. Set 'Clean Logs in Batches' to "Yes" with a batch size of around '10,000-20,000'.

This step keeps your database small without you having to clean it. It helps your store stay fast as it grows.

Step 3: Avoid Using Flat Catalogs

For Magento 2.1.x and above, they can make performance worse. They work by putting EAV data into single tables. The normal EAV structure needs "5-7 joins" for one product query. Flat catalogs put all attributes in one table, requiring "1-2" joins.

For Magento 2.1.x+, the better indexing system makes flat catalogs outdated because they:

• Create duplicate data.
• Need more storage space.
• Make product save slower.
• Make indexing take longer.
• Turn off flat catalogs in the admin panel to avoid problems.

Step 4: Enable Logging to Identify Slow Queries

MySQL's slow query log helps you identify inefficient queries. Consider the following practices:

• Set long_query_time to define which queries are slow.
• Analyze these using the MySQL EXPLAIN statement to optimize their structure.

Step 5: Update Your Database Version

Using MySQL 8.0 or MariaDB 10.11 works best with Magento 2.4.7. These newer versions run much better. Consider these practices:

• Conduct queries that run twice as fast as older versions.
• Keep your database updated to get the latest speed boosts and receive security fixes.

Step 6: Add Indexes to Large Tables

Adding indexes to large tables reduces query times in tables with many entries. This simple action can improve the performance of your Magento store.

Step 7: Use Elasticsearch/OpenSearch as the Search Engine

If you have Magento 2.4.x or newer, use Elasticsearch or OpenSearch as your search engine. It is faster and puts less load on the database than MySQL-based search. With Magento 2.4.7, OpenSearch 2.5+ works well and runs better than older versions.

Step 8: Use Advanced Tools to Find Hidden Issues

Find hidden issues in your MySQL setup using tools like:

• MySQLTuner
• ProfileSQL
• SolarWinds Database Performance Analyzer

These tools suggest ways to make Magento 2 run faster and check things like:

• How fragmented tables are
• If indexes are working well or not used
• How memory is set up in MySQL buffers
• How often the query cache is available
• If temporary tables use memory or disk
• How do connections work during busy times

Key Magento 2 Speed Optimization Techniques for MySQL

Technique	Description	Impact
Query Logging	Enable slow query log with long_query_time set to '1 second' to identify and fix slow queries.	Helps pinpoint bottlenecks and optimize query performance.
Database Version Upgrade	Use MySQL 8.0 or MariaDB 10.11 for faster processing and better compatibility.	Improves database speed by up to "2x".
Table Indexing	Add indexes to queried large tables to speed up queries.	Reduces query execution time.
Database Cleanup	Clear log tables and remove outdated entries to reduce database size & improve efficiency.	Enhances query speed and reduces storage overhead.
Cache Settings	Adjust table_cache and table_open_cache for faster table access and session handling.	Optimizes memory usage and speeds up table operations.
Buffer Pool Size Changes	Configure innodb_buffer_pool_size based on server capacity for faster data storage/retrieval.	Improves data access efficiency.
Connection Limits	Set max_connections according to traffic needs to prevent overload during peak times.	Ensures stable performance under high load.
Query Optimization	Use EXPLAIN to analyze queries for better execution plans.	Optimizes query logic for faster execution.
Storage Engine Selection	Use InnoDB for better transaction handling and concurrent access capabilities.	Enhances database reliability and speed.
Memory Setup for MyISAM	Adjust key_buffer_size for MyISAM tables to improve index caching efficiency.	Speeds up index-based queries.
Disable Product Count	Turn off "product count" in layered navigation to reduce database load during browsing.	Cuts database load.

2 Steps To Perform MySQL Performance Tuning

1. Open the 'my.cnf' file using this command:

mysqld --help --verbose

Note: This step shows where 'my.cnf' files are.

2. Change the 'my.cnf' file settings for better performance. Try these settings:

Setting	Description	Recommended Value
long_query_time	Logs queries taking longer than the specified time ("in seconds").	1
innodb_buffer_pool_size	Main memory area for InnoDB that allocates 50% of server RAM.	50% of RAM
innodb_thread_concurrency	Defines the "number of threads InnoDB can execute".	2×number of CPUs+22×number of CPUs+2
innodb_flush_log_at_trx_commit	Balances performance and durability by controlling log flushing frequency.	2
thread_concurrency	Specifies the "number of threads" that can execute.	3×number of CPUs3×number of CPUs
thread_cache_size	"Number of threads cached for reuse" to reduce overhead.	32
table_cache	"Number of open tables to keep in the cache" for faster access.	1024
query_cache_size	Memory allocated for caching query results.	64M
query_cache_limit	"Largest size of individual query results" to cache.	2M
join_buffer_size	"Size of buffer" used for full joins.	8M
tmp_table_size	"Largest size of in-memory temporary" tables.	256M
key_buffer	Cache for MyISAM indexes to improve performance.	32M
innodb_autoextend_increment	Increment size ("in MB") for extending InnoDB tablespace files.	512
max_allowed_packet	"Largest size of one packet or row" sent to the database.	16M
max_heap_table_size	"Largest size for user-created MEMORY" tables.	256M
read_buffer_size	Buffer size used for sequential scans.	2M
read_rnd_buffer_size	Buffer size used for random reads.	16M
bulk_insert_buffer_size	Buffer size used for bulk inserts to improve performance.	64M
myisam_max_sort_file_size	"Largest temporary file size" for the MyISAM index creation.	10G

Note: These values need alteration based on your server specifications and traffic patterns. You can use higher values for buffer settings if you have more RAM.

7 MySQL Monitoring Tools for Magento Website Performance

Tool	Features	Key Metrics/Capabilities
New Relic APM	Real-time transaction tracing, custom Magento dashboards, and database query monitoring.	Tracks page load times, database performance, cache hit rates, session handling, & API performance.
Blackfire	PHP code profiling, performance bottleneck detection, and query optimization insights.	Measures code execution time, memory usage, and database query patterns.
SolarWinds DPA	AI-powered query anomaly detection and automated tuning recommendations.	Reduces troubleshooting time and identifies optimization opportunities.
Google Page Speed Insights	Analyzes mobile/desktop performance and tracks Core Web Vitals ("LCP", "FID", "CLS").	Measures server response times and resource usage and provides optimization recommendations.
GTmetrix	Waterfall analysis, YSlow recommendations, and multi-location testing.	Tracks TTFB metrics, DOM loading time, and full-page load performance.
Datadog	Custom metric collection, anomaly detection, and real-time alerting.	Tracks catalog performance, checkout rates, and customer activity and detects unusual patterns.
Built-in Profiler	Native Magento integration with basic performance tracking and resource monitoring.	Tracks cache hit rates, session handling, and API performance.

3 Methods to Fix Performance Issues with PMM

Method 1: Understand CPU Usage

Identify where CPU time was being consumed, including:

• Userspace
• System space
• iowait

The statement 'CPU usage is "100%" in userspace' means the CPU executes user code. Thus, we could rule out other potential issues. When threads compete for CPU resources, the "no. of context switches" handled by the kernel (system space) increases.

Method 2: Analyze MySQL Metrics

The server handles around "500" to "800" queries per second (QPS), with an average of "10 threads" running. Commands like 'SELECT' statements examine the metrics using 'SHOW STATUS LIKE Handler%'. For example, around "350 SELECTs" can read "2.5 million rows".

Method 3: Identify Problematic Queries

Use PMM's Query Analytics (QAN). It lets you identify a specific query on the catalog_product_entity_varchar table. A missing index in the value column leads to full table scans. For example, "98% of the load" has an execution time of "over 20 seconds".

Adding an index to the value column resolves the problem. It leads to a significant drop in CPU usage, which ranges from "100%" to less than "25%." The Handler_read_rnd_next value also drops, indicating fewer full table scans. QPS increases fivefold. It suggests a drop in the average query time from "20 seconds" to "66 milliseconds".

Advanced Optimization Strategies for Enhancing Magento Performance With MySQL

Optimization Technique	Description	Impact
Full Page Cache	Configure Varnish Cache with ESI blocks and set cache lifetime.	Reduces server load and decreases page load times to milliseconds.
Redis Implementation	Use Redis for session and cache management with optimized memory allocation.	Handles connections and processes over a thousand queries/second.
Indexing Strategy	Set up scheduled indexing, selective reindexing, and optimized index tables.	Reduces database queries and improves catalog performance.
Server Configuration	Adjust PHP memory limits, configure OPcache, and optimize max_execution_time.	Handles more concurrent users and processes requests faster.
Edge Caching	Integrate with Fastly-Compute@Edge, set up geo-distributed caching, and configure region-specific optimizations.	Reduces database reads and lowers latency for global audiences.
Hardware Optimization	Use ARM-based servers (e.g., "AWS Graviton3") for improved MySQL throughput & energy efficiency.	Increases MySQL throughput per watt and reduces power consumption.

3 MySQL Database Software Options for Magento

1. MySQL

MySQL is one of the most used database solutions. It follows a client-server architecture suitable for applications & databases on the same system. MySQL supports heavy-load systems for companies like Netflix, Uber, and Amazon.

2. MariaDB

MariaDB is an open-source fork of MySQL that offers identical database structures. It maintains compatibility with MySQL through monthly code merges. Magento supports MariaDB as a database system. Adobe provides a list of compatible versions for upgrading or building new stores. For Magento 2.4.7, MariaDB 10.11 offers excellent performance and compatibility. Switching from MySQL to MariaDB doesn't cause downtime.

3. Percona

Percona is another MySQL fork that provides enhanced performance for large datasets. It includes performance features like XtraDB. Instead of InnoDB, it offers various monitoring tools for checking your database. Percona's base code is equal to MySQL, reducing compatibility risks. It includes open-source tools to simplify database management.

3 Magento 2 MySQL Database Optimization Tips

1. Configure Flat Catalog

Magento uses the EAV model to store catalog data. It can create long and complex SQL queries, affecting performance. To address this, Magento allows switching to a flat catalog model through the admin panel. The flat catalog reduces the "number of data calls". It improves indexing efficiency and processing speed.

Using a flat catalog is beneficial only for versions below 2.1.x. Adobe advises against using it for versions 2.1.x and above. It is because it can lead to performance issues and long-running cron jobs. For Magento versions below 2.1.x, follow the steps below:

• Open the Magento admin panel.
• Navigate to Stores > Settings > Configuration > Catalog > Catalog.
• Expand the 'Storefront' section.
• Set 'Use Flat Catalog Category' and 'Use Flat Catalog Product' to "Yes".

Note: Disable the flat catalog to improve performance for versions 2.1.x and above, including the latest 2.4.7.

2. Verify Product Count in Layered Navigation

For Magento 2.4.7 stores with large catalogs, turn off the product count in layered navigation. Follow the steps given below:

1. Log in to the Magento 2 admin panel.
2. Go to Stores > Configuration > Catalog > Layered Navigation
3. Set Display Product Count to "No".
4. Clear the cache after saving.

This small change can cut database load when customers browse products. It stops extra-count queries whenever someone uses the filters.

3. Set Up Elasticsearch/OpenSearch

The default database search works for smaller stores. But, it struggles as product counts increase and traffic grows. Elasticsearch offloads search-related queries and supports layered navigation. It frees up the database for other tasks and scales in a horizontal manner. It also provides advanced features without impacting performance. Examples include language support and real-time search insights.

Troubleshooting Common Issues With MySQL Magento Optimization

Issue	Symptoms	Solution	Prevention
Slow Query Execution	Page load delays and high server load.	Enable slow query log, analyze with EXPLAIN, and optimize problematic queries.	Track queries and set up proper indexing.
Database Bloat	Decreased performance and storage issues.	Truncate log tables, remove obsolete entries, and clean expired sessions.	Schedule regular cleanup and set up log rotation.
Indexing Problems	Slow catalog updates and search delays.	Switch to "Update by Schedule" and reindex during off-peak hours.	Maintain indexes and track indexer status.
Memory Issues	Database timeouts and connection errors.	Adjust innodb_buffer_pool_size & optimize key_buffer_size.	Track performance and assign resources.
Connection Overload	Database crashes and timeout errors.	Adjust max_connections and set up connection pooling.	Track connection usage and set up load balancing.
Version Compatibility	Performance degradation and feature limitations.	Upgrade to MySQL 8.0/MariaDB 10.11 and update Magento configurations.	Conduct version checks and plan Magento upgrades.

FAQs

1. How does Magento compare to Magento 1.x for database handling?

Magento 2 offers enhanced database architecture over Magento 1.x. It handles several database connections and supports split databases. The Magento platform also includes better indexing features. It leads to faster query processing and smoother database operations.

2. When should you update your Magento to the latest versions?

Regular updates every 3-4 months keep your store secure and fast. The latest versions of Magento include performance improvements. Updates fix known database issues and add optimization features that improve performance.

3. What makes speed one of the key factors in Magento 2 website success?

Fast-loading pages reduce customer bounce rates, and quick database responses improve user experience. Search engines favor faster websites, and better speed increases conversion rates. Optimized databases impact page load times to optimize your Magento 2 database.

4. How does Magento 2 hosting affect database performance?

Quality hosting provides dedicated database resources. Good hosts offer optimized server configurations, which help maintain proper database server settings. Professional hosting includes regular maintenance services. It supports smooth database operations and ensures your store runs faster.

5. What to check before setting up Magento 2 on a new server?

Check MySQL version compatibility and verify that the server meets specific requirements. Then, review database configuration settings to test server performance metrics. Also, set up proper database user permissions and create separate databases for development.

6. How does upgrading the Magento affect my existing database?

Upgrades adjust database structures as they add new tables. Some tables get optimized, so plan for temporary performance impacts.

7. Which database settings work well with Magento for best performance?

Configure the proper innodb_buffer_pool_size settings and set the optimal query cache size. Then, adjust the max_connections for your traffic and use the appropriate table_open_cache values. Enable slow query logging & set the correct tmp_table_size limits to speed up Magento.

Summary

Magento 2 MySQL optimization solves the performance and security issues related to Magento. It helps:

• Ensure smooth website operations.
• Enhance the reliability & performance of your Magento 2 store.
• Maintain an efficient store with proper indexing and caching.
• Ensure a smooth shopping experience for your users.
• Improve website performance and Magento store speed with MySQL tuning.

Consider Magento optimized server to optimize MySQL and reduce server load.

[Updated on April 14, 2025]