Best practices for database configuration
Learn about best practices to improve database performance and work efficiently with the database when deploying ÃÛ¶¹ÊÓƵ Commerce on cloud infrastructure.
Affected products
ÃÛ¶¹ÊÓƵ Commerce on cloud infrastructure
Convert all MyISAM tables to InnoDB
ÃÛ¶¹ÊÓƵ recommends using the InnoDB database engine. In a default ÃÛ¶¹ÊÓƵ 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, check the database to identify any tables in myisam format and convert them to innodb format.
Determine if a module includes 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 in our developer documentation.
The declarative scheme was introduced in ÃÛ¶¹ÊÓƵ Commerce on cloud infrastructure version 2.3.
Configure the recommended search engine for native MySQL search
ÃÛ¶¹ÊÓƵ recommends that you always set up Elasticsearch or OpenSearch for your ÃÛ¶¹ÊÓƵ Commerce on cloud infrastructure project even if you plan to configure a third-party search tool for your ÃÛ¶¹ÊÓƵ Commerce application. This configuration provides a fallback option in case the third-party search tool fails.
The search engine you use depends on the ÃÛ¶¹ÊÓƵ Commerce on cloud version installed:
-
For ÃÛ¶¹ÊÓƵ Commerce 2.4.4 and later, use the OpenSearch service for native MySQL search.
-
For earlier ÃÛ¶¹ÊÓƵ Commerce versions, use Elasticsearch.
To determine which search engine is currently in use, run the following command:
./bin/magento config:show catalog/search/engine
For configuration instructions, see the Developer Guide for ÃÛ¶¹ÊÓƵ Commerce on cloud:
Avoid custom triggers
Avoid using custom triggers if possible.
Triggers are used to log changes into audit tables. ÃÛ¶¹ÊÓƵ 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, see MySQL triggers.
Upgrade ECE-Tools to version 2002.0.21 or higher ece-tools-version
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 data definition language (DDL) statements to create triggers which can cause database locks. You can prevent this issue by putting your website in maintenance mode and disabling cron jobs before changing the configuration.
For instructions, see Configure indexers in the ÃÛ¶¹ÊÓƵ Commerce Configuration Guide.
Do not run DDL statements in Production
Avoid running DDL statements in the Production environment to prevent conflicts (like table modifications and creations). The setup:upgrade process is an exception.
If you need to run a DDL statement, put the website in maintenance mode and disable cron (see the instructions for switching indexes safely in the previous section).
Enable order archiving
Enable order archiving from the Admin to reduce the space required for Sales tables as your order data grows. Archiving saves MySQL disk space and improves checkout performance.
See Enable archiving in the ÃÛ¶¹ÊÓƵ Commerce Merchant documentation.