This topic contains industry-standard software terms that some may find racist, sexist, or oppressive and which may make the reader feel hurt, traumatized, or unwelcome. Adobe is working to remove these terms from code, documentation, and user experiences.
This article explains how to avoid performance issues when using MySQL triggers. Triggers are used to log changes into audit tables.
For Adobe Commerce on cloud projects, always test configuration changes in the Staging environment before changing the configuration for the Production environment.
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.
Adobe Commerce does not support any custom triggers in the Adobe Commerce database because custom triggers can introduce incompatibilities with future Adobe Commerce versions. For best practices, see General MySQL Guidelines in the Adobe Commerce documentation.
To prevent performance issues when using triggers, follow these guidelines:
SHOW TRIGGERS SQL Statement.
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.
Adobe Commerce on cloud infrastructure, Pro architecture only
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.
.magento.env.yaml file, set the
MYSQL_USE_SLAVE_CONNECTION to true.
.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.