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]

yiisoft/yii2-gii#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 Implement Oracle database driver #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`))

[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: doctrine/dbal#4015

[Slamdunk]

MySQL 8.0.21 could have a fix for this:

1. MySqlPlatform::getListTableForeignKeysSQL slow on MySQL 8 doctrine/dbal#4015 (comment)
2. https://bugs.mysql.com/bug.php?id=98238#c500442

[bizley]

Closing since fixed and tested yiisoft/yii2-gii#409 (comment)