Yii2: Super slow schema queries with MySQL 8.0.16

[alaa007]

After updating to MySQL 8, I have noticed how super slow Yii2 has become.
After using the Yii Debugger, I have found the following 6 queries consuming more than 95% of total loading time:

The only way I was able to fix the issue is by adding
'enableSchemaCache' => true, to the database component and enabling caching class' => 'yii\caching\FileCache',
But since I'm using my machine for development I don't want to enable caching.

Note: I tried the same code with MySQL version 5 and it was working fine. Seems an issue with MySQL 8 and Yii2, I'm not sure.

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.22
| PHP version | 7.2.20
| Operating system | macOS Mojave 10.14.5

[alex-code]

I'd be surprised if the issue is with Yii.

I'm on PHP 7.3.7 and MySQL 8.0.16.
Loading a page of my site performs 23 queries in 129ms (9 queries in 16ms with caching enabled).

Can you run some of the schema queries directly in MySQL and see how long they take?
I'm no SQL expert but maybe you need to optimise/rebuild the tables after upgrading.

[alaa007]

Hello @alex-code

I tried this query:

Directly in MySQL:

You can notice the huge difference with time!

I'm no SQL expert but maybe you need to optimise/rebuild the tables after upgrading

That was done already and its not the issue as you can see from the previous query.

[alaa007]

@samdark what type of extra info do you need?

[samdark]

@alex-code can you measure performance with your DB instance?

[alex-code]

I'm not using the yii-user package but the schema query for the User table averages about 12.5ms for me.
I'm on a fresh install of MySQL 8 and not an upgrade from MySQL 5.

[samdark]

OK. @alex-code, @alaa007 would you please provide:

1. Operating system.
2. PHP verison.
3. Yii version.
4. MySQL version.

Thanks.

[alex-code]

My setup

| Q | A
| ---------------- | ---
| Yii version | 2.0.23
| PHP version | 7.3.7
| Operating system | Ubuntu 18.04
| MySQL | 8.0.16

[alaa007]

| Q | A
| ---------------- | ---
| Yii version | 2.0.22
| PHP version | 7.2.20
| Operating system | macOS Mojave 10.14.5
| MySQL | 8.0.16 (brew version)

[samdark]

OK. So...

1. It doesn't seem to be related to Yii version since 2.0.23 has not changed anything related: https://github.com/yiisoft/yii2/blob/master/framework/CHANGELOG.md#2023-july-16-2019
2. MySQL version is the same. Since direct queries via phpmyadmin are OK, we can assume that it's alright.
3. Operating system is unlikely to matter since, phpmyadmin is fine.

phpmyadmin uses mysql/mysqli to connect to database while Yii uses PDO. That likely means that there's something wrong with certain PDO version bundled with PHP 7.2.20 installed via brew. @alaa007 can you upgrade/downgrade?

[alaa007]

@samdark I tried php versions 7.1.30 and 7.3.7 and still getting the same numbers. no change.

[samdark]

Super-weird. Just another wild guess. If you have connection host specified as localhost try replacing it with 127.0.0.1...

[alaa007]

@samdark same results.

[samdark]

OK. Another idea. Can you create a PHP script that connects to MySQL via PDO and does the same query?

[samdark]

https://github.com/yiisoft/yii2-gii/issues/409

[samdark]

Another question for @alex-code and @alaa007 is about number of tables in your DB.

[alex-code]

Currently I've got 27, also using the utf8mb4 charset if that could make a difference.
I've got no foreign keys on the user table either.

[alaa007]

My DB has 43 tables.

I tried the following code as you suggested through a direct script and it took a lot of time 0.30296200

$db = new PDO('mysql:host=localhost;dbname=ans', 'root', '');

$db->query('set profiling=1');
$db->query("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 = 'yii_configuration' AND kcu.table_name = 'yii_configuration'");
$res = $db->query('show profiles');
$records = $res->fetchAll(PDO::FETCH_ASSOC);
$duration = $records[0]['Duration'];
echo $duration;

[alex-code]

Could you show the result of this query?

SHOW VARIABLES WHERE Variable_name LIKE 'information_schema_stats_expiry'

Not sure if it could make a difference but MySQL 8 caches schema stats by default, not sure if that would happen with an upgrade.

[almaz-free]

Have some problem on low cost hosting provider: hes changed MySQL version from 5.7.23 to 8.0.16 and i get fail.

What i have:

• Linux 4.4.184-xeon-hst #4 SMP x86_64
• Apache 2
• PHP Version 7.3.7
• MySQL 8.0.16
• Yii version 2.0.23
• 22 tables
• information_schema_stats_expiry 86400

I cant change SQL timeout (PDO::ATTR_TIMEOUT ignoring) - as i understand, it set to 120 seconds, so in log file (app.log) i have:

[error][yii\db\Exception] yii\base\ErrorException: PDOStatement::execute(): MySQL server has gone away in /home/www/vendor/yiisoft/yii2/db/Command.php:1290
Stack trace:
#0 [internal function]: yii\base\ErrorHandler->handleError(2, 'PDOStatement::e...', '/home/www/vendo...', 1290, Array)
#1 /home/www/vendor/yiisoft/yii2/db/Command.php(1290): PDOStatement->execute()
#2 /home/www/vendor/yiisoft/yii2/db/Command.php(1158): yii\db\Command->internalExecute('SELECT\n    kcu....')
#3 /home/www/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)
#4 /home/www/vendor/yiisoft/yii2/db/mysql/Schema.php(390): yii\db\Command->queryAll()
#5 /home/www/vendor/yiisoft/yii2/db/mysql/Schema.php(126): yii\db\mysql\Schema->findConstraints(Object(yii\db\TableSchema))
#6 /home/www/vendor/yiisoft/yii2/db/Schema.php(754): yii\db\mysql\Schema->loadTableSchema('db_pages')
#7 /home/www/vendor/yiisoft/yii2/db/Schema.php(193): yii\db\Schema->getTableMetadata('db_pages', 'schema', false)
#8 /home/www/vendor/yiisoft/yii2/db/ActiveRecord.php(435): yii\db\Schema->getTableSchema('db_pages')
#9 /home/www/vendor/yiisoft/yii2/db/ActiveRecord.php(509): yii\db\ActiveRecord::getTableSchema()
#10 /home/www/vendor/yiisoft/yii2/db/ActiveQueryTrait.php(123): yii\db\ActiveRecord::populateRecord(Object(app\models\DbPages), Array)
#11 /home/www/vendor/yiisoft/yii2/db/ActiveQuery.php(219): yii\db\ActiveQuery->createModels(Array)
#12 /home/www/vendor/yiisoft/yii2/db/Query.php(238): yii\db\ActiveQuery->populate(Array)
#13 /home/www/vendor/yiisoft/yii2/db/ActiveQuery.php(133): yii\db\Query->all(NULL)
#14 /home/www/models/DbPages.php(35): yii\db\ActiveQuery->all()
#15 /home/www/controllers/SiteController.php(67): app\models\DbPages::getLastNews(6)
#16 [internal function]: app\controllers\SiteController->actionIndex()
#17 /home/www/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#18 /home/www/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#19 /home/www/vendor/yiisoft/yii2/base/Module.php(528): yii\base\Controller->runAction('index', Array)
#20 /home/www/vendor/yiisoft/yii2/web/Application.php(103): yii\base\Module->runAction('site/index', Array)
#21 /home/www/vendor/yiisoft/yii2/base/Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request))
#22 /home/www/web/index.php(14): yii\base\Application->run()
#23 {main}

Next yii\db\Exception: PDOStatement::execute(): MySQL server has gone away
The SQL being executed was: 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 = 'db_pages' AND kcu.table_name = 'db_pages' in /home/www/vendor/yiisoft/yii2/db/Schema.php:674

[alex-code]

@almaz-free do you have PHPMyAdmin?
Can you try the query in that and see what happens?

[almaz-free]

Yes, i run SQL code (from my above app.log) in phpMyAdmin and after ~ 100 seconds get that:

Error in processing request
Error code: 504
Error text: Gateway Time-out (rejected)
It seems that the connection to server has been lost. Please check your network connectivity and server status.

In front panel phpMyAdmin v4.9.0.1:
Database server

• Server: 10.xx.xx.a via TCP/IP
• Server type: MySQL
• Server connection: SSL is not being used
• Server version: 8.0.16 - Source distribution
• Protocol version: 10
• User: [email protected]
• Server charset: UTF-8 Unicode (utf8)
  Web server
• nginx/1.10.3
• Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407
• PHP extension: mysqli curl mbstring
• PHP version: 7.0.33-0+deb9u3

[alaa007]

@alex-code
SHOW VARIABLES WHERE Variable_name LIKE 'information_schema_stats_expiry'
Value = 86400

[alex-code]

Could you add EXPLAIN before the SELECT, curious to see what that says.

[almaz-free]

i cut SQL query for phpMyAdmin to this:

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 get fail "Gateway Time-out (rejected)" after ~100 sec

EXPLAIN for tis SQL:

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   PRIMARY     cat     NULL    index   PRIMARY,name    name    194     NULL    1   100.00  Using index
1   PRIMARY     sch     NULL    eq_ref  PRIMARY,catalog_id  catalog_id  202     mysql.cat.id,const  1   100.00  Using index
1   PRIMARY     <derived3>  NULL    ref     <auto_key0>     <auto_key0>     388     mysql.cat.name,const    11648   10.00   Using where
1   PRIMARY     fk  NULL    ref     schema_id,table_id  schema_id   8   mysql.sch.id    175     100.00  Using where
1   PRIMARY     tbl     NULL    eq_ref  PRIMARY     PRIMARY     8   mysql.fk.table_id   1   100.00  Using where
3   DERIVED     cat     NULL    index   PRIMARY     name    194     NULL    1   100.00  Using index
3   DERIVED     sch     NULL    ref     PRIMARY,catalog_id  catalog_id  8   mysql.cat.id    20509   100.00  Using index
3   DERIVED     tbl     NULL    ref     PRIMARY,schema_id   schema_id   8   mysql.sch.id    27  100.00  NULL
3   DERIVED     idx     NULL    ref     PRIMARY,table_id    table_id    8   mysql.tbl.id    2   40.00   Using where
3   DERIVED     icu     NULL    ref     index_id,index_id_2,column_id   index_id_2  8   mysql.idx.id    1   100.00  Using index
3   DERIVED     col     NULL    eq_ref  PRIMARY     PRIMARY     8   mysql.icu.column_id     1   100.00  Using where
4   UNION   cat     NULL    index   PRIMARY     name    194     NULL    1   100.00  Using index
4   UNION   fk  NULL    ALL     PRIMARY,schema_id,table_id  NULL    NULL    NULL    195156  100.00  Using join buffer (Block Nested Loop)
4   UNION   sch     NULL    eq_ref  PRIMARY,catalog_id  PRIMARY     8   mysql.fk.schema_id  1   25.00   Using where
4   UNION   fkcu    NULL    ref     PRIMARY,foreign_key_id,column_id    PRIMARY     8   mysql.fk.id     1   100.00  NULL
4   UNION   tbl     NULL    eq_ref  PRIMARY     PRIMARY     8   mysql.fk.table_id   1   100.00  NULL
4   UNION   col     NULL    eq_ref  PRIMARY     PRIMARY     8   mysql.fkcu.column_id    1   100.00  Using where
NULL    UNION RESULT    <union3,4>  NULL    ALL     NULL    NULL    NULL    NULL    NULL    NULL    Using temporary

Note: #1003 /* select#1 */ select `kcu`.`CONSTRAINT_NAME` AS `constraint_name`,`kcu`.`COLUMN_NAME` AS `column_name`,`kcu`.`REFERENCED_TABLE_NAME` AS `referenced_table_name`,`kcu`.`REFERENCED_COLUMN_NAME` AS `referenced_column_name` from `mysql`.`foreign_keys` `fk` join `mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` join `information_schema`.`KEY_COLUMN_USAGE` `kcu` where ((`tbl`.`id` = `mysql`.`fk`.`table_id`) and (`mysql`.`fk`.`schema_id` = `sch`.`id`) and (`sch`.`catalog_id` = `cat`.`id`) and (`kcu`.`TABLE_SCHEMA` = <cache>(database())) and (`sch`.`name` = database()) and (`kcu`.`CONSTRAINT_SCHEMA` = database()) and (`kcu`.`CONSTRAINT_CATALOG` = `cat`.`name`) and (`kcu`.`CONSTRAINT_NAME` = (`mysql`.`fk`.`name` collate utf8_tolower_ci)) and can_access_table(`sch`.`name`,`tbl`.`name`) and is_visible_dd_object(`tbl`.`hidden`))

but if i cut from SQL query this expression:

AND kcu.constraint_name = rc.constraint_name

all work ok (and fast - 0.0173 sec)

EXPLAIN for SQL without "AND kcu.constraint_name = rc.constraint_name":

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   PRIMARY     cat     NULL    index   PRIMARY,name    name    194     NULL    1   100.00  Using index
1   PRIMARY     sch     NULL    eq_ref  PRIMARY,catalog_id  catalog_id  202     mysql.cat.id,const  1   100.00  Using index
1   PRIMARY     fk  NULL    ref     schema_id,table_id  schema_id   8   mysql.sch.id    175     100.00  NULL
1   PRIMARY     tbl     NULL    eq_ref  PRIMARY     PRIMARY     8   mysql.fk.table_id   1   100.00  Using where
1   PRIMARY     <derived3>  NULL    ALL     NULL    NULL    NULL    NULL    1164737     0.10    Using where; Using join buffer (Block Nested Loop)
3   DERIVED     cat     NULL    index   PRIMARY     name    194     NULL    1   100.00  Using index
3   DERIVED     sch     NULL    ref     PRIMARY,catalog_id  catalog_id  8   mysql.cat.id    20509   100.00  Using index
3   DERIVED     tbl     NULL    ref     PRIMARY,schema_id   schema_id   8   mysql.sch.id    27  100.00  NULL
3   DERIVED     idx     NULL    ref     PRIMARY,table_id    table_id    8   mysql.tbl.id    2   40.00   Using where
3   DERIVED     icu     NULL    ref     index_id,index_id_2,column_id   index_id_2  8   mysql.idx.id    1   100.00  Using index
3   DERIVED     col     NULL    eq_ref  PRIMARY     PRIMARY     8   mysql.icu.column_id     1   100.00  Using where
4   UNION   cat     NULL    index   PRIMARY     name    194     NULL    1   100.00  Using index
4   UNION   fk  NULL    ALL     PRIMARY,schema_id,table_id  NULL    NULL    NULL    195156  100.00  Using join buffer (Block Nested Loop)
4   UNION   sch     NULL    eq_ref  PRIMARY,catalog_id  PRIMARY     8   mysql.fk.schema_id  1   25.00   Using where
4   UNION   fkcu    NULL    ref     PRIMARY,foreign_key_id,column_id    PRIMARY     8   mysql.fk.id     1   100.00  NULL
4   UNION   tbl     NULL    eq_ref  PRIMARY     PRIMARY     8   mysql.fk.table_id   1   100.00  NULL
4   UNION   col     NULL    eq_ref  PRIMARY     PRIMARY     8   mysql.fkcu.column_id    1   100.00  Using where
NULL    UNION RESULT    <union3,4>  NULL    ALL     NULL    NULL    NULL    NULL    NULL    NULL    Using temporary

Note: #1003 /* select#1 */ select `kcu`.`CONSTRAINT_NAME` AS `constraint_name`,`kcu`.`COLUMN_NAME` AS `column_name`,`kcu`.`REFERENCED_TABLE_NAME` AS `referenced_table_name`,`kcu`.`REFERENCED_COLUMN_NAME` AS `referenced_column_name` from `mysql`.`foreign_keys` `fk` join `mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` join `information_schema`.`KEY_COLUMN_USAGE` `kcu` where ((`tbl`.`id` = `mysql`.`fk`.`table_id`) and (`mysql`.`fk`.`schema_id` = `sch`.`id`) and (`sch`.`catalog_id` = `cat`.`id`) and (`kcu`.`TABLE_SCHEMA` = <cache>(database())) and (`sch`.`name` = database()) and (`kcu`.`CONSTRAINT_SCHEMA` = <cache>(database())) and (`kcu`.`CONSTRAINT_CATALOG` = `cat`.`name`) and can_access_table(`sch`.`name`,`tbl`.`name`) and is_visible_dd_object(`tbl`.`hidden`))

[alex-code]

Would you be able to update to MySQL 8.0.17 and see if it's any faster?

[alaa007]

Same problem.

[alex-code]

@alaa007 It's more of a pain but are you able to do a fresh install of MySQL 8 instead of an upgrade?

[hirenbhut93]

Same problem.

[samdark]

@hirenbhut93 do you have a fresh install or update?

[hirenbhut93]

@samdark fresh install, but I think is not yii2/php problem but MySQL because of the same amount of time required in the console.

[samdark]

@hirenbhut93 Is it possible to get exact version of MySQL and database dump to finally reproduce it?

[hirenbhut93]

@samdark mysql Ver 8.0.15 for Linux on x86_64 (MySQL Community Server - GPL)

[alex-code]

This question has some config options that may be worth trying.
https://dba.stackexchange.com/questions/227742/extreme-performance-loss-due-to-moving-from-mysql-5-7-23-to-mysql-8-0-status-c

[kamarton]

I tested with docker:

• mysql 8.0.17
• 90 tables

my local hosted mysql 5.7 vs 8.0.17: 0.0006 (one table)

• 5.7: 0.0006 sec (in pma) / 0.00 sec (in console)
• 8.0.17: 0.03-0.05 sec (in console)

Though bigger times in 8.0.17 (possibly due to the docker), it never exceeded 50ms / table. Obviously, with 90 tables, this is quite a few seconds.

[kamarton]

The goal pursued so far is not necessarily right: delay time minimization.

Need it at all? I'm thinking here that normally SELECT is the majority of the requests, but I don't really see what it is used for for INSERT, UPDATE and DELETE.

If, for example, only needed to edit or get the table schema, why not run it only then?

[robsch]

Have the problem in a backend yii2 site. If the user is not logged in, no db queries are done and the page if rendered immediately. After log in, the first query regards the user. Then the schema information will be queried:

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 = 'user' AND kcu.table_name = 'user'

That takes about 8 sec for each request. And CPU usage increases in that time. MySQL 8.0.17 with about 200 databases on a RHEL 8 system. Yii 2.0.22.

This query executed in phpMyAdmin takes the same time.

What I read was that those schema tables are not real ones. And when a query is done with them, all databases and tables gets taken into account somehow. That would mean the query will be slower the more databases exist. Not sure if this correct.

I have another solution with another MySQL 8 instance with only 3 databases. When I execute the same query there, it is much faster ('no' delay, so I did not find this problem with that system).

What could be a workaround?

[samdark]

@robsch schema caching should solve the issue for production environments.

[robsch]

@samdark Thanks. This is a workaround, right? Especially when there are many tables, the cache has to be populated before the solution gets live. So it would be necessary to do this for all tables on deployment.

Will and can there be a fix from the Yii side? Can the issue be seen as a bug in MySQL? I'm wondering how Yii will deal with that issue in the long term..

[samdark]

This is a workaround, right?

Yes.

Will and can there be a fix from the Yii side?

There can be a fix but we're not actively working on it focusing on Yii 3. There were multiple attempts to fix it but none succeeded yet. If you want to spent some time looking for a solution, that would be helpful.

[robsch]

@samdark Alright. Is there a solution in Yii3? One additional reason to upgrade...

[samdark]

@robsch Yii 3 isn't ready yet. We're using Cycle ORM there instead of our own active record. At least for now.

[Slamdunk]

Doctrine's affected by the same issue: https://github.com/doctrine/dbal/issues/4015

[Slamdunk]

MySQL 8.0.21 could have a fix for this:

1. https://github.com/doctrine/dbal/issues/4015#issuecomment-640613042
2. https://bugs.mysql.com/bug.php?id=98238#c500442