Most common database issues in Adobe Commerce on cloud infrastructure

This article discusses the most common database issues causing performance degradation for Adobe Commerce on cloud infrastructure 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 Adobe Commerce on cloud infrastructure plan and therefore tools availability, you can do the following.

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

Run the SHOW [FULL] PROCESSLIST statement.
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.
Take resolution steps depending on issues found.

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

Run the pt-query-digest --type=slowlog against MySQL slow query logs.
- Refer to Log locations > Service Logs in our developer documentation for information on where the slow query logs are stored in Adobe Commerce on cloud infrastructure.
- Refer to Percona Toolkit > pt-query-digest documentation.
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 Adobe Commerce, 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:

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');

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 Adobe Commerce on cloud infrastructure refer to Configure declarative schema in our developer documentation.

Duplicate indexes

Check if there are duplicate indexes in your DB

For both Starter and Pro architectures, 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 architecture merchants 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.

Database best practices for Adobe Commerce on cloud infrastructure