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

[Radio]

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?

[elvinristi]

@orlangur I would keep it here due not all have access to make new PR there and maybe someone has better idea/input to that which could be taken over to magento2ee.

[ghost]

Why is it a common trend for M2 issues that some contributor/dev comes in and complains about procedural rules rather than taking the literally free work being done and being happy with it ? Its a problem I've seen in multiple bug reports where the bug itself is totally ignored in lieu of decorum.

Yes, this is the CE repo, but facets of EE and CE overlap, so if someone does post something about EE it may still be relevant to the community.

[orlangur]

@sylink,

taking the literally free work

This is never true due to process restrictions.

if someone does post something about EE it may still be relevant to the community

I'm not even sure such code snippet posting is allowed by EE license. My main concern here is that there is no reason to create mess in CE repo when there is working partners program to process contributions into B2B/EE.