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
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)
- 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 plan only)
- 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.
- 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:
- 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
andcolumn 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