Adobe Commerce: MySQL queries stuck in “Waiting for Certification” state cause performance issues
The Waiting for Certification state in MySQL is part of the two-phase commit process used in Galera-based clusters that powers Adobe Commerce. This mechanism ensures there are no conflicts in the database whenever a transaction is submitted. While brief appearances of this state are normal, prolonged durations indicate performance bottlenecks. These delays can be caused by long-running queries, high contention, or infrastructure issues.
To fix this, analyze slow queries, manage cache flushing, implement SWAT recommendations, and optimize database statistics.
Description description
Environment
- Product: Adobe Commerce
- Database cluster type: Galera-based MySQL
Issue/Symptoms
Performance degradation caused by a large number of MySQL queries stuck in the Waiting for Certification state. Symptoms include:
- Delayed product visibility in the cart.
- Slow query execution.
- Large slow query log files.
- Cache flushes coincide with peak usage times.
Resolution resolution
To resolve the issue:
-
Review the MySQL slow query logs to identify long-running queries. If the log file size grows into hundreds of megabytes, it indicates a serious issue. Optimize any problematic queries to reduce bottlenecks and improve overall performance. Refer to Checking slow queries and processes MySQL in the Commerce KB documentation for more details.
-
Schedule cache flushing during non-peak hours, such as late night or early morning. This helps avoid increased system load during high-demand periods and prevents performance degradation. Refer to Manage the cache documentation in the Commerce Configuration Guide for more details.
-
Implement recommendations from the SWAT report. These may include adding missing primary keys to tables and minimizing the use of triggers, both of which can reduce contention and improve certification speed. Learn more about Site-Wide Analysis Tool in the Commerce Tools documentation.
-
Note: This operation may temporarily lock tables. Run it outside business hours and ensure cron jobs are disabled to avoid disruptions.
Run
mysqlcheckwith table analysis to ensure database statistics are up to date:code language-none mysqlcheck -h<host_name> -u<user_name> -p -a <db_name> -
Make sure table statistics are up to date. Outdated statistics can lead to inefficient query execution plans, which in turn degrade performance across the cluster.