Most common database issues in Magento Commerce Cloud

This article discusses the most common database issues causing performance degradation for Magento Commerce Cloud sites. 

Click on each issue description to see the details:

• Long running queries.
• Primary keys are not defined.
• Duplicate indexes.

Long running queries

Investigate if you have slow running MySQL queries. Depending on your Magento Cloud plan and therefore tools availability, you can do the following. 

Investigate long running queries using MySQL (available for all plans)

1. Run the SHOW [FULL] PROCESSLIST statement.
2. If you see long running queries, run MySQL EXPLAIN and EXPLAIN ANALYZE for each of them, to find out what makes the query run for a long time.
3. Take resolution steps depending on issues found.

Investigate long running queries using Percona Toolkit (for Pro plan only)

1. Run the pt-query-digest --type=slowlog against MySQL slow query logs. 
   • Refer to Log locations > Service Logs in Magento Developer Documentation for information on where the slow query logs are stored in Magento Commerce Cloud. 
   • Refer to Percona Toolkit > pt-query-digest documentation. 
2. Take resolution steps depending on issues found.

Primary keys are not defined

Defining primary keys (PK) is a requirement for a good database and table design. They provide a way to uniquely identify a single row in any table. When using InnoDB engine, which is the default in Magento, in tables where no PK is defined the first unique not null key is the primary key. If none is available, InnoDB creates a hidden primary key (6 bytes). The problem with such a key is that you do not have control over it and this value is global for all tables without primary keys. This might cause contention problems if you perform simultaneous writes on these tables. This might lead to performance issues, as they will all share that global hidden PK index increment. 

Take the following steps to identify missing primary keys and add them:

1. To identify the tables that do not have PK, run the following query:

   SELECT table_catalog, table_schema, table_name, engine
       FROM information_schema.tables
       WHERE (table_catalog, table_schema, table_name) NOT IN
       (SELECT table_catalog, table_schema, table_name
       FROM information_schema.table_constraints
       WHERE constraint_type = 'PRIMARY KEY')
       AND table_schema NOT IN ('information_schema', 'pg_catalog');
   

2. To add a PK to a table, update the db_schema.xml (the declarative schema) of the table, by adding a node similar to the following:

   <constraint xsi:type="primary" referenceId="PRIMARY"> 
       <column name="id_column"/> 
   </constraint>
   

   Where referenceID and column name must have your custom values. 

   For more information about using declarative schema in Magento Commerce Cloud refer to Configure declarative schema in Magento Developer Documentation.

Duplicate indexes 

Check if there are duplicate indexes in your DB

For both Starter and Pro plans, you can run the following query to check if you have duplicate indexes: 

SELECT s.INDEXED_COL,GROUP_CONCAT(INDEX_NAME) FROM (
SELECT INDEX_NAME,GROUP_CONCAT(CONCAT(TABLE_NAME,'.',COLUMN_NAME) ORDER BY CONCAT(SEQ_IN_INDEX,COLUMN_NAME)) 'INDEXED_COL' FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'db?' 
GROUP BY INDEX_NAME
)as s GROUP BY INDEXED_COL HAVING COUNT(1)>1

The query returns the column names and the names of the duplicated indexes.

Pro customers can also use the Percona Toolkit to check for duplicate indexes, by running the pt-duplicate-key checker command. For more information refer to Percona Toolkit > pt-duplicate-key-checker documentation. 

Remove duplicate indexes

Use the DROP INDEX statement to remove the duplicate indexes. See DROP INDEX Statement MySQL documentation for details.

Related reading

Database best practices for Magento Commerce Cloud