Managed alerts on Magento Commerce: MariaDB alerts

This article provides troubleshooting steps when you receive MariaDB alerts for Magento Commerce in New Relic. The MariaDB alerts monitor high query load as well as excessive Data Manipulation Language (DML) queries. Both can lead to a degraded user experience or even downtime. You can receive four kinds of alerts:

• DML Queries Warning
• DML Queries Critical 
• Read Queries Warning
• Read Queries Critical

Affected products and versions

Magento Commerce Cloud Pro.

Issue

You will receive a managed alert in New Relic if you have signed up to Managed alerts for Magento Commerce and one or more of the alert thresholds have been surpassed. These alerts were developed by Magento to give customers a standard set using insights from Support and Engineering.

Do!

• Abort any deployment scheduled until this alert is cleared.
• Put your site into maintenance mode immediately if your site is or becomes completely unresponsive. For steps refer to DevDocs Installation Guide > Enable or disable maintenance mode. Make sure to add your IP to the exempt IP address list to ensure that you are still able to access your site for troubleshooting. For steps, refer to DevDocs Maintain the list of exempt IP addresses.
• End any scripts such as imports that may be the cause of the alert if site performance is impacted.

Don't!

• Run indexers or additional crons which may cause additional stress on MariaDB.
• Do any major administrative tasks (i.e., Magento Admin, data imports / exports).
• Clear your cache.

Solution

DML Queries (queries that modify the database using UPDATE, INSERT, and DELETE)

If you receive a DML Queries Critical alert start at step one. If you receive a DML Queries Warning alert start at step two.

1. Check if a Magento support ticket exists. For steps, refer to KB Track your support tickets. Support may have received a New Relic threshold alert, created a ticket and started working on the issue. If no ticket exists, create one. The ticket should have the following information:
   1. Contact Reason: select “New Relic MariaDB alert received".
   2. Description of the alert.
   3. New Relic Incident link. This is included in your Managed alerts for Magento Commerce.

2. To identify the source of the issue, try to identify the DML queries:

   1. Review your database operations by using steps from New Relic APM UI Pages > Monitoring > Databases page.
   2. Sort by CALL COUNT, then OPERATION. Review INSERT, DELETE, and UPDATE operations.
   3. Look for high AVG.
   4. Click through to find database operation callers. This will identify transactions using that query by time.
3. Seek out either code optimizations, or operational optimizations:
   1. Code optimizations: Look to optimize queries with bulk inserts / updates, minimizing index usage, or throttling code.
   2. Operational optimizations: Offload resource intensive data modifications to lower traffic times.
   3. Additional optimizations: Ensure that you are on the latest version of ECE-Tools. For steps, refer to DevDocs Magento Commerce Cloud > Update ece-tools version.

Read Queries (SELECT statements)

If you receive a Read Queries Critical alert start at step one. If you receive a Read Queries Warning alert start at step two.

1. If you receive a Read Queries Critical alert first check if a Magento support ticket exists. For steps, refer to KB Track your support tickets. Support may have received a New Relic threshold alert, created a ticket and started working on the issue. If no ticket exists, create one. The ticket should have the following information:
   1. Contact Reason: select “New Relic MariaDB alert received".
   2. Description of the alert.
   3. New Relic Incident link. This is included in your Managed alerts for Magento Commerce. 
2. To identify the source of the issue, try to identify the DML queries:
   
   1. Review your database operations by using steps from New Relic APM UI Pages > Monitoring > Databases page. 
   2. Sort by CALL COUNT, then OPERATION. Review SELECT operations. 
   3. Look for transactions with a high AVG or high CALL COUNT.
   4. Click through to find database operation callers. This will identify transactions using that query by time. 
3. Seek out either code optimizations, or operational optimizations:
   1. Code optimizations:
      •  Use EXPLAIN to find query inefficiencies. For steps, refer to MariaDB Knowledge Base > Explain.
      • Optimize query by re-writing.
      • Add recommended indexes.
   2. Find ways to minimize number of query calls through caching or query optimization.
   3. Operational optimizations: Offload resource intensive tasks to lower traffic times.
   4. Additional optimizations:
      1. Implement MariaDB read from slaves to better distribute load. For steps, refer to MySQL high-load bottleneck in Magento Commerce Cloud. 
      2. Review table size and truncate if necessary. For steps, refer to MySQL tables are too large.  
      3. If using MariaDB for search, consider moving to Elasticsearch. For steps, refer to Magento DevDocs > Set up Elasticsearch service.

Related Reading 

• To research other common MariaDB issues, refer to Most common database issues in Magento Commerce Cloud.
• To research database best practices, refer to Database best practices for Magento Commerce Cloud.