increase mysql load in 2.0.14.2 version

[sepidemahmoodi]

Hi,
i was updated my core to 2.0.14.2 version,
then mysql load was increased while cache is enabled,
it seems this query makes it

   Id: 180216
   User: portal
   Host: localhost
     db: portal
Command: Query
   Time: 20
  State: Opening tables

   Info: SELECT
    `kcu`.`CONSTRAINT_NAME` AS `name`,
    `kcu`.`COLUMN_NAME` AS `column_name`,
    `tc`.`CONSTRAINT_TYPE` AS `type`,

    CASE

        WHEN NULL IS NULL AND `kcu`.`REFERENCED_TABLE_SCHEMA` = DATABASE() THEN NULL
        ELSE `kcu`.`REFERENCED_TABLE_SCHEMA`
    END AS `foreign_table_schema`,
    `kcu`.`REFERENCED_TABLE_NAME` AS `foreign_table_name`,
    `kcu`.`REFERENCED_COLUMN_NAME` AS `foreign_column_name`,
    `rc`.`UPDATE_RULE` AS `on_update`,
    `rc`.`DELETE_RULE` AS `on_delete`,
    `kcu`.`ORDINAL_POSITION` AS `position`
FROM

    `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`,
    `information_schema`.`REFERENTIAL_CONSTRAINTS` AS `rc`,
    `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
WHERE

    `kcu`.`TABLE_SCHEMA` = COALESCE(NULL, DATABASE()) AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = 'session'
    AND `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`TABLE_NAME` = 'session' AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
    AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`TABLE_NAME` = 'session' AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME` AND `tc`.`CONSTRAINT_TYPE` = 'FOREIGN KEY'

UNION

SELECT

    `kcu`.`CONSTRAINT_NAME` AS `name`,
    `kcu`.`COLUMN_NAME` AS `column_name`,
    `tc`.`CONSTRAINT_TYPE` AS `type`,
    NULL AS `foreign_table_schema`,
    NULL AS `foreign_table_name`,
    NULL AS `foreign_column_name`,
    NULL AS `on_update`,
    NULL AS `on_delete`,
    `kcu`.`ORDINAL_POSITION` AS `position`

FROM

    `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`,
    `information_schema`.`TABLE_CONSTRAINTS` AS `tc`

WHERE

    `kcu`.`TABLE_SCHEMA` = COALESCE(NULL, DATABASE()) AND `kcu`.`TABLE_NAME` = 'session'
    AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`TABLE_NAME` = 'session' AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME` AND `tc`.`CONSTRAINT_TYPE` IN ('PRIMARY KEY', 'UNIQUE')
ORDER BY `position` ASC

how to solve it?

[samdark]

That's schema cache. If caching schema is enabled, this query should not happen more than once. Check your config.

[sepidemahmoodi]

yes, schema cache is enable but it takes too much time to load page for first time, actually in previous yii version i didnt have this problem. this query makes down my website

[sepidemahmoodi]

@samdark please reopen the issue

[samdark]

Would you please try code from master first?

[sepidemahmoodi]

same problem was happened :(
cache process takes long time and cause down my website

[sepidemahmoodi]

my application has thousand subdomain and each of them has own database,
when application loaded for first time in mysql process list that query has shown.
so a large number of databases and load all of them cause this problem.

[samdark]

As a workaround, you can try warming up these DBs one by one. That's one time action and should not cause issues as long as cache is filled.

[sepidemahmoodi]

this query in 2.0.13.1 version of yii2 has no problem and this query took 0.0004(s) in mysql,

SELECT kcu.constraint_name, kcu.column_name, kcu.referenced_table_name, kcu.referenced_column_name FROM information_schema.referential_constraints AS rc JOIN information_schema.key_column_usage AS kcu ON ( kcu.constraint_catalog = rc.constraint_catalog OR (kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL) ) AND kcu.constraint_schema = rc.constraint_schema AND kcu.constraint_name = rc.constraint_name WHERE rc.constraint_schema = database() AND kcu.table_schema = database() AND rc.table_name = 'session' AND kcu.table_name = 'session'

but query of version 2.0.14.2 took 12.64(s) time in mysql.
im checking thats warming up databases solution,

[samdark]

What's your MySQL version btw?

[sepidemahmoodi]

5.7.22

[mahsa92]

I have the same problem and I didn't have problem in 2.0.13.1.
It seems it's getting all Ram capacity and even swap memory and it increasing server load caused by mysql.scheme caching is slow and because it's running for multi database at the same time, it causes overloading.