Magento 2 MySQL Optimization Guide For Database

• 7 Steps To Magento MySQL Database Server Optimization

• Essential Magento 2 Speed Optimization Techniques With MySQL

• 2 Steps To Perform MySQL Performance Tuning

• MySQL Monitoring Tools For Magento Website Performance

• Fixing Magento 2 Core MySQL Performance Issues Using PMM

• Advanced Optimization Strategies For Improving Magento Performance With MySQL

• Advanced MySQL Configuration Strategies For Magento 2 Performance Optimization

• Best Practices For Magento Speed Optimization With MySQL

• Magento 2 Database Optimization Tips

• Troubleshooting Common Issues With MySQL Magento Optimization

• FAQs

• Summary

7 Steps To Magento MySQL Database Server Optimization

Step 1: Remove Unnecessary Entries from Magento Database

Truncate tables that contain irrelevant data, such as bloated logs or system events. Removing these entries improves the overall performance of your Magento 2 store's database. Make sure 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: Avoid Using Flat Catalogs

Flat catalogs were previously used to help reduce database load. However, for Magento 2.1.x and above, using them can lead to performance degradation. This is because they work by denormalizing EAV data into single tables. The default EAV structure requires “5-7 joins” for a single product query. Flat catalogs combine all attributes into one table, reducing joins to “1-2”.

For Magento 2.1.x+, the improved indexing system makes flat catalogs obsolete because:

• They create duplicate data.
• Require more storage space.
• Slow down product saves due to flat table updates.
• Cause longer indexing times.

Disable flat catalogs in the admin panel to avoid indexing issues.

Step 3: Enable Logging to Identify Slow Queries

MySQL’s slow query log helps you identify inefficient queries. Set long_query_time to define which queries are considered slow. Analyze these using the MySQL EXPLAIN statement to optimize their structure.

Step 4: Update Your Database Version

Upgrading to the latest version of MySQL/MariaDB improves performance & security, & fixes bugs. Always keep your database up to date.

Step 5: Add Indexes to Large Tables

Adding indexes to large tables helps reduce query times. It occurs mainly in tables with many entries. This simple action can drastically improve your Magento store's performance.

Step 6: Use Elasticsearch/OpenSearch as the Search Engine

If you’re using Magento 2.4.x or newer, Elasticsearch or OpenSearch should be used as the default search engine. It offers faster search functionality and reduces database load compared to MySQL-based search.

Step 7: Use Advanced Tools to Discover Hidden Issues

Tools like MySQLTuner or ProfileSQL help diagnose hidden issues within your MySQL setup. Running these tools can provide recommendations to optimize Magento 2 performance further.

These tools analyze specific metrics, such as:

• Table fragmentation levels
• Index efficiency (unused or duplicate indexes)
• Memory usage patterns across different MySQL buffers
• Query cache hit rates
• Temporary table creation in memory vs. disk
• Connection usage patterns during peak loads

For example, MySQLTuner might reveal that your innodb_buffer_pool_size is only using "60%" of allocated memory, allowing you to reallocate resources to other areas.

Essential Magento 2 Speed Optimization Techniques With MySQL

Optimization Area	Implementation	Performance Impact
Query Logging	Enable slow query log with long_query_time set to "1 second".	Identifies problematic queries for targeted optimization.
Database Version	Update to "MySQL 8.0" or "MariaDB 10.6".	Increases performance by 2x speed and improves compatibility.
Table Indexing	Add indexes to large tables with frequent queries.	Reduces query processing time by "30-60%".
Database Cleanup	Truncate log tables and remove obsolete entries.	Reduces database bloat and improves query speed.
Cache Configuration	Optimize table_cache and table_open_cache settings.	Improves table access speed and session handling.
Buffer Pool Size	Adjust innodb_buffer_pool_size based on server resources.	Enhances data retrieval and storage efficiency.
Connection Management	Configure max_connections according to traffic.	Prevents database overload during peak times.
Query Optimization	Use 'EXPLAIN' statements to analyze and optimize queries.	Improves query execution plans.
Storage Engine	Use InnoDB for better transaction handling.	Improves concurrent access and data integrity.
Memory Allocation	Optimize key_buffer_size for MyISAM tables.	Enhances index caching performance.

2 Steps To Perform MySQL Performance Tuning

1. Open the 'my.cnf' configuration file. To locate this file, use the command:

   mysqld --help --verbose

   This command will display the paths to the 'my.cnf' files, such as:

   /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

2. Configure 'my.cnf' settings to optimize performance. Suggested settings include:

• long_query_time = 1
• innodb_buffer_pool_size = [50% of RAM]
• innodb_thread_concurrency = 2 * [number of CPUs] + 2
• innodb_flush_log_at_trx_commit = 2
• thread_concurrency = [number of CPUs] * 3
• thread_cache_size = 32
• table_cache = 1024
• query_cache_size = 64M
• query_cache_limit = 2M
• join_buffer_size = 8M
• tmp_table_size = 256M
• key_buffer = 32M
• innodb_autoextend_increment = 512
• max_allowed_packet = 16M
• max_heap_table_size = 256M
• read_buffer_size = 2M
• read_rnd_buffer_size = 16M
• bulk_insert_buffer_size = 64M
• myisam_sort_buffer_size = 128M
• myisam_max_sort_file_size = 10G
• myisam_repair_threads = 1

MySQL Monitoring Tools For Magento Website Performance

Monitoring Tool	Key Features	Performance Metrics
New Relic APM	- Real-time transaction tracing - Custom Magento dashboards - Database query monitoring	- Page load times - Database performance - Revenue impact tracking
Blackfire	- PHP code profiling - Performance bottleneck detection - Query optimization insights	- Code execution time - Memory usage - Database query patterns
Google Page Speed Insights	- Mobile/desktop analysis - Core Web Vitals tracking - Optimization recommendations	- LCP, FID, CLS metrics - Server response times - Resource optimization
GTmetrix	- Waterfall analysis - YSlow recommendations - Multi-location testing	- TTFB metrics - DOM loading time - Full-page load analysis
Datadog	- Custom metric collection - Anomaly detection - Real-time alerting	- Catalog performance - Checkout rates - Customer activity
Built-in Profiler	- Native Magento integration - Basic performance tracking - Resource monitoring	- Cache hit rates - Session handling - API performance

Fixing Magento 2 Core MySQL Performance Issues Using PMM

1. Understanding CPU Usage

Identify where CPU time was being consumed: userspace, system space, iowait, and others. CPU usage is nearly "100%" in userspace, meaning the CPU was executing user code. Thus, we could rule out other potential issues. For example, when multiple threads compete for CPU resources. It would increase context switches handled by the kernel (system space).

2. Analyzing MySQL Metrics

The server handles around '500" to "800" queries per second (QPS), with 10 threads running on average. Most commands are 'SELECT' statements. They are used to examine the metrics using SHOW STATUS LIKE 'Handler%'. Specifically, the `Handler_read_rnd_next' counter is high. It indicates a large number of full table scans. For example, around "350 SELECTs" reading "2.5 million rows".

The reason for the high CPU usage, rather than IO, is that the data was stored in the InnoDB Buffer Pool (RAM). Instead of reading rows from the disk, it fetches them from memory, which puts pressure on the CPU.

3. Identifying Problematic Queries

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

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

Advanced Optimization Strategies For Improving Magento Performance With MySQL

Strategy Type	Implementation Method	Performance Impact
Full Page Cache	- Configure Varnish Cache - Set cache lifetime - Implement ESI blocks	- Reduces server load. - Decreases page load to milliseconds.
Database Optimization	- Configure InnoDB buffer pool - Implement query caching - Regular table cleanup	- Improves query response time. - Reduces database load.
Redis Implementation	- Session storage management - Magento cache management - Configure memory allocation	- Handles 60,000+ connections. - Processes 50,000 queries/second.
Indexing Strategy	- Schedule-based indexing - Selective reindexing - Optimize index tables	- Reduces a vast number of database queries. - Improves catalog performance.
Query Optimization	- Implement MySQLTuner - Configure table cache - Optimize JOIN operations	- Reduces query execution time. - Improves database efficiency.
Server Configuration	- Adjust PHP memory limits - Configure OPcache - Optimize max_execution_time	- Handles more concurrent users. - Faster request processing.
Content Delivery	- Implement CDN - Asset minification - Image optimization	- Reduces server load. - Improves global access speed.
Performance Monitoring	- New Relic integration - Blackfire profiling - Regular performance audits	- Identifies bottlenecks. - Enables proactive optimization.

Advanced MySQL Configuration Strategies For Magento 2 Performance Optimization

The MySQL database can impact site performance, especially if it's not properly optimized. Slow Magento commands often indicate the need for optimization. MySQL holds all your website data, from product listings to wishlists.

To begin optimizing, gather MySQL internal statistics using the mysqltuner script. It provides insights into what issues your database server might have and what needs to be fixed. Note that the script requires a Perl interpreter on the server.

Check the MySQL Table Cache Hit Rate (table_cache). If it's at "0%", it means you're not using a database table cache. Configuring the table cache allows MySQL to keep frequently accessed tables in memory. It speeds up data retrieval during configuration. Adjust the table_cache parameter in the my.cnf file to achieve a cache hit rate as close to "100%" as possible.

Another important setting is the Table Open Cache (table_open_cache). It affects performance by keeping the same table available for multiple sessions.

Consult an expert to review your Magento 2.1 (or above) database server settings. If you're using shared hosting, your hosting provider will likely handle database tuning. However, for dedicated servers, either learn MySQL optimization or hire an expert.

Best Practices For Magento Speed Optimization With MySQL

Best Practice	Implementation Details	Performance Impact
Database Version	Upgrade to MySQL 8.0 or MariaDB for Magento 2.4+.	Up to 2x faster query processing.
Query Logging	Enable slow query log to identify problematic queries.	Helps identify performance bottlenecks.
Flat Catalog	Enable flat tables for products and categories.	Reduces complex joins and improves query speed.
Database Cleanup	Regularly remove log tables and expired sessions.	Reduces database size and improves query efficiency.
Index Management	Set indexers to "Update on Schedule" mode.	Prevents performance impact during peak times.
Buffer Pool	Configure InnoDB buffer pool size based on data size.	Optimizes memory usage for frequent queries.
Table Partitioning	Implement partitioning for large catalog tables.	Improves query efficiency for big datasets.
Query Cache	Optimize query cache settings for frequent requests.	Reduces database load for common queries.
Database Maintenance	Regularly optimize table and defragment.	Maintains consistent performance over time.
Connection Pool	Configure max_connections based on traffic patterns.	Prevents database overload during peak times.
Storage Engine	Use InnoDB for better transaction management.	Improves concurrent access handling.
Backup Strategy	Implement efficient backup scheduling.	Minimizes impact on live database performance.

Magento 2 Database Optimization Tips

1. Verify Your Hardware

Before making software changes, ensure that your hardware isn’t causing the database to run slowly. No amount of software optimization can compensate for hardware limitations.

Common hardware issues that impact performance include:

• Servers located far from users
• Slow CPUs
• Low bandwidth
• Limited RAM

With the rise in containerization, many merchants implement split database systems. They help to reduce the load on a single database, expecting efficiency gains. However, this approach is only practical for Magento Commerce users. Synchronization between the Slave & Master databases often leads to Magento slow performance issues.

2. Consider Alternative Database Management Software

MySQL is one of the most widely 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. However, if MySQL doesn’t meet your needs, consider alternatives like MariaDB & Percona.

i. MariaDB

MariaDB is an open-source fork of MySQL, offering 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. Switching from MySQL to MariaDB doesn’t cause downtime. However, switching back may lead to compatibility issues.

ii. Percona

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

3. Optimize Flat Catalog for Your Magento Version

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. However, 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. This is because it can lead to performance issues and long-running cron jobs.

For Magento versions below 2.1.x:

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

For versions 2.1.x and above, disable the flat catalog to improve performance:

1. Log in to the Magento 2 admin panel.
2. Navigate to Stores > Settings > Configuration > Catalog > Catalog.
3. Expand the Storefront section.
4. Set 'Use Flat Catalog Category' and 'Use Flat Catalog Product' to "No".

4. Use Elasticsearch Instead of Default Database Search

Since Magento 2.4, Adobe requires Elasticsearch as the default catalog search engine. While it may add complexity, the performance and functionality gains make it worthwhile. The default database search works for smaller stores. However, 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 horizontally. It also provides advanced features without impacting performance. For example, multiple language support and real-time search insights.

Troubleshooting Common Issues With MySQL Magento Optimization

Issue	Symptoms	Solution	Prevention
Slow Query Execution	- Page load delays - High server load	- Enable slow query log. - Analyze with an 'EXPLAIN' statement. - Optimize problematic queries.	- Regularly monitor queries. - Implement proper indexing.
Database Bloat	- Decreased performance - Storage issues	- Truncate log tables. - Remove obsolete entries. - Clean expired sessions.	- Schedule regular cleanup. - Implement log rotation.
Indexing Problems	- Slow catalog updates - Search delays	- Switch to "Update by Schedule". - Reindex during off-peak hours.	- Regularly maintain indexes. - Monitor indexer status.
Memory Issues	- Database timeouts - Connection errors	- Adjust innodb_buffer_pool_size. - Optimize key_buffer_size.	- Regularly monitor performance. - Allocate resources properly.
Query Cache Issues	- Inconsistent performance - Cache misses	- Configure query cache size. - Optimize cache settings.	- Monitor cache hit ratio. - Regular monitor cache.
Connection Overload	- Database crashes - Timeout errors	- Adjust max_connections. - Implement connection pooling.	- Monitor connection usage. - Implement load balancing.
Table Fragmentation	- Slow table scans - Increased disk I/O	- Use 'OPTIMIZE TABLE'. - Defragment databases.	- Schedule maintenance windows. - Monitor fragmentation levels.
Version Compatibility	- Performance degradation - Feature limitations	- Upgrade to MySQL 8.0/MariaDB 10.6. - Update your Magento configurations.	- Regularly conduct version checks. - Run Magento planned upgrades.

FAQs

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

Magento 2 offers improved database architecture over Magento 1.x. It supports split databases. The newer platform handles multiple database connections. It leads to faster query processing. The Magento platform also includes better indexing features. These changes make database operations much smoother.

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 new optimization features.

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

Fast-loading pages reduce customer bounce rates. Quick database responses improve user experience. Better speed increases conversion rates. Search engines favor faster websites. Optimized databases directly 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. They maintain proper database server settings. Professional hosting includes regular maintenance services. It supports smooth database operations and ensures your store runs faster.

5. What should I check before installing Magento on a new server?

Check MySQL version compatibility and verify that the server meets minimum requirements. Review database configuration settings to test server performance metrics. Set up proper database user permissions and create separate databases for development.

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

Upgrades modify database structures as they add new tables. Old data might need migration as database indexes often change. Some tables get optimized, so plan for temporary performance impact.

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

Configure proper innodb_buffer_pool_size settings. Set optimal query cache size and adjust max_connections for your traffic. Use appropriate table_open_cache values. Enable slow query logging and set 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 performance and reliability of your Magento 2 store.
• Maintain an efficient store with proper indexing and caching.
• Ensure a seamless 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.