MySQL configuration best practices
Triggers
This article explains how to avoid performance issues when using MySQL triggers. Triggers are used to log changes into audit tables.
Affected products and versions
- Adobe Commerce on-premises
- Adobe Commerce on cloud infrastructure
Performance impacts
Triggers are interpreted as code meaning that MySQL does not precompile them.
Hooking into the query’s transaction space, triggers add 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 for locks on another table.
This additional overhead can negatively impact site performance on the site if many triggers are used.
Effective use
To prevent performance issues when using triggers, follow these guidelines:
- If you have custom triggers that write some data when the trigger is executed, move this logic to write directly to the audit tables instead. For example, by adding an additional query in the application code, after the query you aimed to create the trigger for.
- Review existing custom triggers and consider removing them and writing directly to the tables from the application side. Check for existing triggers in your database by using the
SHOW TRIGGERS
SQL Statement. - For additional assistance, questions, or concerns, submit an Adobe Commerce Support ticket.
Slave connections
Adobe Commerce can read multiple databases asynchronously. If you expect a high load for the MySQL database of a Commerce site deployed on cloud infrastructure Pro architecture, Adobe recommends enabling the MYSQL slave connection.
When you enable the MYSQL slave connection, Adobe Commerce uses a read-only connection to the database to receive read-only traffic on a non-master node. The performance improves through load balancing when only one node handles read-write traffic.
Affected versions
Adobe Commerce on cloud infrastructure, Pro architecture only
Configuration
In the Adobe Commerce on cloud infrastructure, you can override the default configuration for the MYSQL slave connection by setting the MYSQL_USE_SLAVE_CONNECTION variable. Set this variable to true
to automatically use a read-only connection to the database.
To enable the MySQL slave connection:
-
On your local workstation, change to your project directory.
-
In the
.magento.env.yaml
file, set theMYSQL_USE_SLAVE_CONNECTION
to true.code language-none stage: deploy: MYSQL_USE_SLAVE_CONNECTION: true
-
Commit the
.magento.env.yaml
file changes and push to the remote environment.After the deployment completes successfully, the MySQL slave connection is enabled for the cloud environment.