This topic discusses a solution when high load from MySQL causes a performance bottleneck issue in Magento Commerce Cloud.
Affected products and versions
- Magento Commerce Cloud 2.x.x
- ECE Tools version 2002.0.16 and higher
- New Relic APM service (Your Magento Commerce Cloud account includes the software for the New Relic APM service along with a license key.)
Steps To See If The Issue Affects You
- In your New Relic APM Overview Chart, check for the first indication that MySQL has become a bottleneck. See the sample picture below where MySQL has become a bottleneck and takes most of the web transactions time:
Notice how the red dashed line in the image shows a discernible upward trend in the MySQL web transactions time and then peaks at even higher levels.
- From here you can then go to your Database screen where you can see the second indication of high throughput or slow
SELECTqueries in MySQL, and in the below sample image you can see when sorting by Most time consuming, your store, in this example, is slow on
Analyze the slow transactions in New Relic APM.
If you see a high volume of queries or high pressure on a MySQL database, you can spread out the load across different nodes by enabling
Your Magento Commerce Cloud store has high throughput or is slow on
SELECT MySQL queries.
If experiencing these 2 indications, enabling
SLAVE connections for the MySQL database and Redis can help to spread out the load across different nodes.
Magento can read multiple databases or Redis asynchronously. Updating the
.magento.env.yaml file by setting to
true the values
REDIS_USE_SLAVE_CONNECTION to use a read-only connection to the database to receive read-only traffic on a non-master node. This improves performance through load balancing, because only one node needs to handle read-write traffic. Set to
false to remove any existing read-only connection array from the
- Edit your
.magento.env.yamlfile, and add the following content: You can find more details in Deploy Variables in DevDocs.
- Commit your changes, and push your changes.
- Pushing changes will initiate a new deploy process. Once deployment is successfully completed, you should have your Magento Commerce Cloud instance now configured to use slave connections.
Below are the common questions you may ask when you consider to use the Slave Connections functionality for your Magento Commerce Cloud Store.
- Is there any known issues or limitation to use Slave Connections?
We do not have any known issues from using Slave Connections.
Just make sure you are using the most recently updated ece-tools package.
Instructions are here on how to update your ece-tools package.
- Is there any extra latency by using Slave Connections?
Yes, cross-AZ (cross-Availability Zones) latency is higher and reduces performance of a Magento Commerce Cloud instance in the case if the instance is not overloaded and can carry the whole load.
But clearly if the instance is overloaded – master-slave will help with performance by spreading out the load on the MySQL Database or Redis across different nodes.
On not-overloaded clusters – Slave Connections will slow down performance by 10-15%, which is one of the reasons it’s not default.But on overloaded clusters, there is a performance boost because these 10-15% are mitigated by reducing load by traffic.
- Should I enable these settings for my store?
If you have high load or expect high load on the MySQL Database or Redis, you definitely need to enable Slave Connections.
For a regular customer with average traffic, this is not an optimal setting to be enabled.