ÃÛ¶¹ÊÓƵ

DocumentationCommerceImplementation Playbook

MySQL configuration best practices

Last update: Fri Sep 29 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Experienced
  • Developer
NOTE
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. ÃÛ¶¹ÊÓƵ is working to remove these terms from code, documentation, and user experiences.

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

  • ÃÛ¶¹ÊÓƵ Commerce on-premises
  • ÃÛ¶¹ÊÓƵ Commerce on cloud infrastructure
WARNING
For ÃÛ¶¹ÊÓƵ Commerce on cloud projects, always test configuration changes in the Staging environment before changing the configuration for the Production environment.

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.

WARNING
ÃÛ¶¹ÊÓƵ Commerce does not support any custom triggers in the ÃÛ¶¹ÊÓƵ Commerce database because custom triggers can introduce incompatibilities with future ÃÛ¶¹ÊÓƵ Commerce versions. For best practices, see General MySQL Guidelines in the ÃÛ¶¹ÊÓƵ Commerce documentation.

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 .
  • For additional assistance, questions, or concerns, submit an ÃÛ¶¹ÊÓƵ Commerce Support ticket.

Slave connections

ÃÛ¶¹ÊÓƵ 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, ÃÛ¶¹ÊÓƵ recommends enabling the MYSQL slave connection.

When you enable the MYSQL slave connection, ÃÛ¶¹ÊÓƵ 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

ÃÛ¶¹ÊÓƵ Commerce on cloud infrastructure, Pro architecture only

Configuration

In the ÃÛ¶¹ÊÓƵ 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:

  1. On your local workstation, change to your project directory.

  2. In the .magento.env.yaml file, set the MYSQL_USE_SLAVE_CONNECTION to true.

    code language-none 
    stage:
  deploy:
    MYSQL_USE_SLAVE_CONNECTION: true

  3. 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.

recommendation-more-help
754cbbf3-3a3c-4af3-b6ce-9d34390f3a60