yii2: Become very slow after update from 2.0.5 to 2.0.14

I just checked my internal repo and realized that I put the first version of the query, not the optimized one. >_< I’ll make a PR tonight.

Db Schema query, in my case, has became a lot slower after upgrading from 2.0.13.1 to 2.0.14.1. Up to 1 second. If schema cache is not enabled this can be the cause of slower websites.

Using MySQL 2.0.13.1 - 0.4ms

2.0.14.1 - 888ms

EDIT: Is not the same. it is a new method loadTableConstraints added in #14105

Using the data in the information_schema database from my home pc that I exported it’s as quick on my work computer. Because of this I guess it might be an issue with the contents of the information_schema database, my experience with joins is that they get slow when there’s a lot of data being joined which led me to think it’s either the amount of databases I have running (only 20 including the MySQL databases), or the fact I have multiple databases with session tables (about 5 of them I think).

I tried booting a new docker container of MariaDB and creating multiple databases (5) with the session table, but that didn’t result in any noticeable slowdown. I tried exporting my work pc’s information_schema and re-importing it in a different database (with tables made non-temporary because it wouldn’t otherwise appear) and running the query on that is really quick.

I tried to find what part of the query is slow, and learned that each left-join is responsible for about half the execution time (900ms). Without the left joins it’s near instant. I tried changing the on-conditions so the records resulting from the left-joins is just for the current table (currently it fetches the constraints from all tables, not sure if intended), without any change. Running queries directly on the respective tables is near instant, just the joining seems to be slow even though this:

SELECT #DISTINCT
    `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` = `sch`.`name` 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 (SELECT DATABASE() AS `name`) AS `sch`
INNER JOIN `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`
    ON `kcu`.`TABLE_SCHEMA` = COALESCE(NULL, `sch`.`name`) AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = 'session'
#LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` AS `rc`
#    ON `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
#LEFT JOIN `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
#    ON `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
ORDER BY `kcu`.`ORDINAL_POSITION` ASC

takes ~16ms and results in only 1 record, so it’s not like the left-joining should be any different from a single query directly on the table.

This takes 969ms:

SELECT #DISTINCT
    `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` = `sch`.`name` 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 (SELECT DATABASE() AS `name`) AS `sch`
INNER JOIN `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`
    ON `kcu`.`TABLE_SCHEMA` = COALESCE(NULL, `sch`.`name`) AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = 'session'
#LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` AS `rc`
#    ON `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
LEFT JOIN `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
    ON `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
ORDER BY `kcu`.`ORDINAL_POSITION` ASC

This takes 78ms:

SELECT `tc`.`CONSTRAINT_TYPE` FROM `information_schema`.`TABLE_CONSTRAINTS` AS `tc` WHERE `tc`.`TABLE_SCHEMA` = 'my-database' AND `tc`.`CONSTRAINT_NAME` = 'PRIMARY'

I’m not sure how to continue debugging but in any case this looks to be an issue with MariaDB. It might be fixable by changing the query further but I’m not sure how to proceed with that as I’m not sure about everything it’s used for. e.g. I can imagine the TABLE_CONSTRAINTS query could be a subquery instead which might be quick but that would result in only one record being returned (in the case of the session table that would be right, but the same query is likely also used for other tables).

I updated MariaDB to 10.2.13 without any changes.

Next I’ll try to reproduce this in a Docker container and perhaps rebuild my database to see if it’s maybe somehow corrupt. I’d be surprised if the issue remains afterwards, but if it does I’ll start removing databases until the issues is fixed or just one Yii2-app-database remains. If it persists I’ll strip that down to the bare minimum to reproduce the issue.

@sergeymakinen did not you mean berosoboy? 😉

MariaDB just like @Eseperio. On my work desktop it was about 1900ms (first call after restarting the service was 2500ms). I ran the query via HeidiSQL as well to see the results without hacking into the code and it took as much time, even after restarting the service. Using caching restored the response times so it didn’t seem like a big deal to me,

Just now I tried reproducing it at my home pc and the same query only takes about 100ms, so there might be something else going on at my work computer. I’ll try to find out if there’s a difference in versions or settings or if I can explain it some other way tomorrow.

I suggest also mentioning it (in an obvious way) in any docs suggesting using DbSession, such as here: http://www.yiiframework.com/doc-2.0/yii-web-dbsession.html Otherwise new users of this session data storage will run into this same issue. Also it was one of the first search results I ran into when I started debugging this issue. The new query adds about 2 seconds to every single request for me.

Yes I use mysql too and store Session in Db