Loading constraints from database in framework-foreign-key library takes too long

[maksymhopei]

In order to check foreign keys in separate databases for "Scalabale OMS" and "Scalable Checkout" modules Magento loads constraints from the database with the following query:

SELECT 
  `constraints`.`CONSTRAINT_NAME` AS `name`,
  `info`.`TABLE_NAME` AS `table_name`,
  `info`.`REFERENCED_TABLE_NAME` AS `reference_table_name`,
  `info`.`COLUMN_NAME` AS `field_name`,
  `info`.`REFERENCED_COLUMN_NAME` AS `reference_field_name`,
  `constraints`.`DELETE_RULE` AS `delete_strategy` 
FROM 
  `information_schema`.`KEY_COLUMN_USAGE` AS `info` 
  INNER JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` AS `constraints` 
    ON constraints.CONSTRAINT_NAME = info.CONSTRAINT_NAME AND constraints.CONSTRAINT_SCHEMA = info.CONSTRAINT_SCHEMA
WHERE 
  (info.REFERENCED_TABLE_NAME IS NOT NULL)
  AND (info.CONSTRAINT_SCHEMA = '<Database Name>')

(See: \Magento\Framework\ForeignKey\Config\DbReader::getDbConstraints)

This query might take quite a long time (minutes) on a mysql server with many databases in use.
In our setup, which is the following:

> select count(*) from `information_schema`.`KEY_COLUMN_USAGE`;
|    66045 |
> select count(*) from `information_schema`.`REFERENTIAL_CONSTRAINTS`;
|    21222 |

this query takes: 1 min 26.70 sec.

It is also executed twice or more by Magento for each connection.
This makes the page load after clearing caches way too long and the website impossible to work with.

Preconditions

Magento EE 2.1.0 installed.
Mysql server with many databases 30+.

Steps to reproduce

1. Clear cache in any way (cli, admin panel)
2. Open any page (i.e. Homepage)

Expected result

The page takes few seconds to load.

Actual result

The page takes minutes to load.

[brobie]

+1

[SerhiyShkolyarenko]

@radio @brobie sorry, we don't disclose enterprise edition details here. Please, contact official support channel for patch.

[elvinristi]

@SerhiyShkolyarenko wise would be to make MDVA or MAGETWO ticket instead of rejecting this.

[SerhiyShkolyarenko]

@elvin-vaimo it was rejected because here we discuss community edition issues only, but this issue has nothing to do with CE.

[elvinristi]

@radio , did you ever got this improved? With following approach I got on my machine from 140sec to 1sec.

--- vendor/magento/framework-foreign-key/Config/DbReader-o1.php	2017-09-25 13:13:52.000000000 +0200
+++ vendor/magento/framework-foreign-key/Config/DbReader.php	2017-09-25 13:23:32.000000000 +0200
@@ -62,7 +62,33 @@
      */
     private function getDbConstraints($connectionName, array $connectionConfig)
     {
+        /** @var \Magento\Framework\DB\Adapter\AdapterInterface|\Magento\Framework\DB\Adapter\Pdo\Mysql $connection */
         $connection = $this->connectionFactory->create($connectionConfig);
+
+        $constraints = [];
+
+        if ($connection instanceof \Magento\Framework\DB\Adapter\Pdo\Mysql) {
+            foreach ($connection->listTables() as $tableName) {
+                $foreignKeys = $connection->getForeignKeys($tableName);
+
+                foreach ($foreignKeys as $foreignKey) {
+                    $row = [
+                        'name' => $foreignKey['FK_NAME'],
+                        'table_name' => $foreignKey['TABLE_NAME'],
+                        'reference_table_name' => $foreignKey['REF_TABLE_NAME'],
+                        'field_name' => $foreignKey['COLUMN_NAME'],
+                        'reference_field_name' => $foreignKey['REF_COLUMN_NAME'],
+                        'delete_strategy' => $foreignKey['ON_DELETE'],
+                    ];
+                    $row['connection'] = $connectionName;
+                    $row['reference_connection'] = $connectionName;
+                    $row['delete_strategy'] = 'DB ' . $row['delete_strategy'];
+                    $key = $row['table_name']. $row['reference_table_name'] . $row['field_name'] . $row['reference_field_name'];
+                    $constraintId = sha1($key);
+                    $constraints[$constraintId] = $row;
+                }
+            }
+        } else {
         $select = $connection->select()
             ->from(['info' => 'information_schema.KEY_COLUMN_USAGE'], [])
             ->joinInner(
@@ -95,6 +121,8 @@
             $constraintId = sha1($key);
             $constraints[$constraintId] = $row;
         }
+        }
+
         return $constraints;
     }
 }

[orlangur]

@elvin-vaimo I still don't see vendor/magento/framework-foreign-key as a part of Magento Open Source, please remove the code which is a part of Magento Commerce and ask your colleagues where such kind of improvement can be discussed.

[hostep]

It would be great if someone could pass along the solution of @elvinristi to the enterprise developers.

Without this change it takes tens of minutes until Magento Commerce 2.1.9 gets bootstrapped over here on my machine. I have 121 databases in my local mysql server. With the change from @elvinristi the time to bootstrap Magento is reduced significantly. I haven't got exact timings because I have no more patience to test the original code again :p

[elvinristi]

@hostep PR is done to Commerce branch and is in progress.

[orlangur]

@elvinristi great 👍 Could you remove the source code from comment or it is intended for anybody to apply for their instance?