MySQL catalog search engine will be removed in Adobe Commerce 2.4.0. You must have Elasticsearch host setup and configured prior to installing version 2.4.0. Refer to Install and configure Elasticsearch.
This article explains how to improve the performance of your Adobe Commerce on cloud infrastructure store by working efficiently with the database. The recommendations are relevant for both Starter architecture and Pro architecture customers.
Click on the links below to see recommendations:
- Convert all MyISAM tables to InnoDb
- Use ElasticSearch instead of MySQL native FULLTEXT indexes
- Avoid triggers
- Upgrade ECE-Tools to version 2002.0.21 or higher
- Switch indexer mode safely
- Avoid running DDL (Data Definition Language) statements
- Enable order archiving
Convert all MyISAM tables to InnoDb
Adobe recommends using the InnoDb database engine, and in the out-of-the-box Adobe Commerce installation, all tables in the database are stored using the InnoDb engine. However, some third-party modules (extensions) can introduce tables in the MyISAM format. After you install a third-party module, you should check the database to identify any tables in MyISAM format and convert them to InnoDb.
Determine if you have MyISAM tables
You can analyze the third-party module code before installing it, to determine if it uses MyISAM tables.
If you have already installed an extension, run the following query to determine whether the database has any MyISAM tables:
SELECT table_schema, CONCAT(ROUND((index_length+data_length)/1024/1024),'MB')
AS total_size FROM information_schema. TABLES WHERE engine='myisam' AND table_schema
NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');
Change the storage engine to InnoDb
In the db_schema.xml file declaring the table, set the engine attribute value for the corresponding table node to innodb. For reference, see Configure declarative schema > table node in our developer documentation.
The declarative scheme was introduced in Adobe Commerce on cloud infrastructure version 2.3.
Related reading:
Use ElasticSearch instead of native MySQL search
Adobe recommends replacing the default MySQL search engine in Adobe Commerce on cloud infrastructure with Elasticsearch, because Elasticsearch is a better performing search engine than the MySQL search engine.
To determine which search engine is currently in use, run the following command:
./bin/magento config:show catalog/search/engine
To enable and configure the Elasticsearch engine, see the Configure Adobe Commerce to use Elasticsearch instructions in our developer documentation.
Avoid custom triggers
Avoid using custom triggers if possible.
Triggers are used to log changes into audit tables. Adobe recommends configuring the application to write directly to the audit tables instead of using the trigger functionality for these reasons:
- Triggers are interpreted as code and MySQL does not precompile them. Hooking onto your query’s transaction space, they add the overhead to a parser and interpreter for each query performed with the table.
- The triggers share the same transaction space as the original queries, and while those queries compete for locks on the table, the triggers independently compete on locks on another table.
To learn about alternatives to using custom triggers, refer to Best Practice triggers usage in our support knowledge base.
Upgrade ECE-Tools to version 2002.0.21 or higher
To avoid potential issues with cron deadlocks, upgrade ECE-Tools to version 2002.0.21 or higher. For instructions, see Update ece-tools version in our developer documentation.
Switch indexer mode safely
Switching indexers generates DDL statements to create triggers and can cause database locks.
Follow the process below to switch an indexer mode in a way that prevents creating locks:
- Enable maintenance mode. For instructions, refer to Enable or disable maintenance mode in our developer documentation.
- Disable cron. For instructions, refer to Set up cron jobs > Disable cron jobs in our developer documentation.
- Switch indexer mode. For information, refer to Manage the indexers in our developer documentation.
- Enable cron.
- Disable maintenance mode.
Avoid running DDL statements
Avoid running DDL (Data Definition Language) statements on Production environments, to ensure you do not create conflicts (like table modifications, creations). The setup:upgrade process is an exception.
If you need to run a DDL statement, put the website to maintenance mode and disable cron (see the instructions for switching indexes safely in the previous section).
Enable order archiving
Sales tables might take a lot of space overtime, so enabling archiving would save MySQL disk space and improve checkout performance. To enable the feature, follow the instructions in Archive > To enable archiving in our user guide.
Related reading
Most common database issues in Adobe Commerce on cloud infrastructure in our support knowledge base.