Installation and upgrading VERY slow on MySQL 8?
Permalink
Hi together,
I recently upgraded to MySQL 8 in my development system and now see, that installing concrete5 and packages or upgrading ist extremely slow.
I investigated and found out, that is has something to do with checking of key constraints:
This statement is executed very often and takes around 0,6s each on my system. It is part of the Doctrine MySQLPlatform class.
Did anyone else have problems with MySQL 8 when installing or upgrading ?
I recently upgraded to MySQL 8 in my development system and now see, that installing concrete5 and packages or upgrading ist extremely slow.
I investigated and found out, that is has something to do with checking of key constraints:
SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ FROM information_schema.key_column_usage k /*!50116 INNER JOIN information_schema.referential_constraints c ON c.constraint_name = k.constraint_name AND c.table_name = '...' */ WHERE k.table_name = '...' AND k.table_schema = '...' /*!50116 AND c.constraint_schema = 'bikeforcereloaded' */ AND k.`REFERENCED_COLUMN_NAME` is not NULL;
This statement is executed very often and takes around 0,6s each on my system. It is part of the Doctrine MySQLPlatform class.
Did anyone else have problems with MySQL 8 when installing or upgrading ?
My table names are camel case. Does this matter in any way?
The github issue describe the problem, but has no real solution. It was just closed after someony said "could not reproduce". So maybe it has something to do with the Mysql 8 subversion OR Mysql configuration.
mmmmhhh...
The github issue describe the problem, but has no real solution. It was just closed after someony said "could not reproduce". So maybe it has something to do with the Mysql 8 subversion OR Mysql configuration.
mmmmhhh...
Or maybe just with the number of databases on the server? I know the information_schema was heavily restructured for MySQL 8. Maybe query times are now more dependent on the number of databases on the server.
The only time I have suffered from slow installs and upgrading was when I was using a standard hard drive in my system, the read and write times were just too slow.
Switching to a decent SSD drive resolved my speed issues.
Something to consider...
Switching to a decent SSD drive resolved my speed issues.
Something to consider...
Can't be the reason, using SSD already.
Installation with MySQL 5.7 took about 2-3 minutes.
Now with MySQL 8 it is round about 15-20 minutes.
Installation with MySQL 5.7 took about 2-3 minutes.
Now with MySQL 8 it is round about 15-20 minutes.
Installing a site has been painfully slow for me for many core versions. If its slower still for some or all MySQL8 installations the core needs to work round that. I don't think there can be much done in the core to speed it up. The c5 core data is big and getting bigger with each version.
The best that could be done in the core is to break all parts of install of tables, data and sample content down into smaller slices and provide more feedback, so that even when an install is slow it never times out and a user has enough feedback to know to leave it running.
The best that could be done in the core is to break all parts of install of tables, data and sample content down into smaller slices and provide more feedback, so that even when an install is slow it never times out and a user has enough feedback to know to leave it running.
To give you some impression:
Installation with MySQL 5.7 took about 2-3 minutes.
Installation with MySQL 8 takes about 15-20 minutes.
:(
Installation with MySQL 5.7 took about 2-3 minutes.
Installation with MySQL 8 takes about 15-20 minutes.
:(
OK, after testing I can confirm:
Mysql 8 information schema is VERY SLOW when having a lot of databases on the server.
The query
is about 10-15 times slower on my dev system with roundabout 50 databases then on a fresh system. The query takes around 0.6s on the dev system.
On a fresh mysql data dir with only 1 database it is a lot faster. The query only takes around 0.04s. On the fresh system installation of concrete5 took only 2-3 minutes as with MySQL 5.7.
So despite MySQL pomising to speed up information_schema with version 8, it is completely unusable for servers with a lot of databases.
Mysql 8 information schema is VERY SLOW when having a lot of databases on the server.
The query
SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ FROM information_schema.key_column_usage k /*!50116 INNER JOIN information_schema.referential_constraints c ON c.constraint_name = k.constraint_name AND c.table_name = '...' */ WHERE k.table_name = '...' AND k.table_schema = '...' /*!50116 AND c.constraint_schema = 'bikeforcereloaded' */ AND k.`REFERENCED_COLUMN_NAME` is not NULL;
is about 10-15 times slower on my dev system with roundabout 50 databases then on a fresh system. The query takes around 0.6s on the dev system.
On a fresh mysql data dir with only 1 database it is a lot faster. The query only takes around 0.04s. On the fresh system installation of concrete5 took only 2-3 minutes as with MySQL 5.7.
So despite MySQL pomising to speed up information_schema with version 8, it is completely unusable for servers with a lot of databases.
1. Do you have table names as lower case or camel case?
2. This report may help you...
https://github.com/concrete5/concrete5/issues/8216...