MySQL Bugs: #114580: Mysql 8.0.35 - The query to get foreign keys are slower than MySQL 8.0.20

Bug #114580	Mysql 8.0.35 - The query to get foreign keys are slower than MySQL 8.0.20
Submitted:	8 Apr 2024 10:41	Modified:	9 Apr 2024 10:31
Reporter:	Trong Dinh	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	8.0.35	OS:	Ubuntu (Ubunutu 20.04 LTS)
Assigned to:		CPU Architecture:	x86

Description:
In MySQL 8.0.35 MySQL Community Server run this command:
mysql> SELECT KCU.REFERENCED_TABLE_SCHEMA PKTABLE_CAT, NULL PKTABLE_SCHEM,  KCU.REFERENCED_TABLE_NAME PKTABLE_NAME, KCU.REFERENCED_COLUMN_NAME PKCOLUMN_NAME, KCU.TABLE_SCHEMA FKTABLE_CAT, NULL FKTABLE_SCHEM, KCU.TABLE_NAME FKTABLE_NAME, KCU.COLUMN_NAME FKCOLUMN_NAME, KCU.POSITION_IN_UNIQUE_CONSTRAINT KEY_SEQ, CASE update_rule    WHEN 'RESTRICT' THEN 1   WHEN 'NO ACTION' THEN 3   WHEN 'CASCADE' THEN 0   WHEN 'SET NULL' THEN 2   WHEN 'SET DEFAULT' THEN 4 END UPDATE_RULE, CASE DELETE_RULE  WHEN 'RESTRICT' THEN 1  WHEN 'NO ACTION' THEN 3  WHEN 'CASCADE' THEN 0  WHEN 'SET NULL' THEN 2  WHEN 'SET DEFAULT' THEN 4 END DELETE_RULE, RC.CONSTRAINT_NAME FK_NAME, RC.UNIQUE_CONSTRAINT_NAME PK_NAME,7 DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME WHERE KCU.REFERENCED_TABLE_NAME = 'memory_summary_by_host_by_event_name' ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ;
Empty set (0.36 sec)

In MySQL 8.0.20 MySQL Community Server:
mysql> SELECT KCU.REFERENCED_TABLE_SCHEMA PKTABLE_CAT, NULL PKTABLE_SCHEM,  KCU.REFERENCED_TABLE_NAME PKTABLE_NAME, KCU.REFERENCED_COLUMN_NAME PKCOLUMN_NAME, KCU.TABLE_SCHEMA FKTABLE_CAT, NULL FKTABLE_SCHEM, KCU.TABLE_NAME FKTABLE_NAME, KCU.COLUMN_NAME FKCOLUMN_NAME, KCU.POSITION_IN_UNIQUE_CONSTRAINT KEY_SEQ, CASE update_rule    WHEN 'RESTRICT' THEN 1   WHEN 'NO ACTION' THEN 3   WHEN 'CASCADE' THEN 0   WHEN 'SET NULL' THEN 2   WHEN 'SET DEFAULT' THEN 4 END UPDATE_RULE, CASE DELETE_RULE  WHEN 'RESTRICT' THEN 1  WHEN 'NO ACTION' THEN 3  WHEN 'CASCADE' THEN 0  WHEN 'SET NULL' THEN 2  WHEN 'SET DEFAULT' THEN 4 END DELETE_RULE, RC.CONSTRAINT_NAME FK_NAME, RC.UNIQUE_CONSTRAINT_NAME PK_NAME,7 DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME WHERE KCU.REFERENCED_TABLE_NAME = 'memory_summary_by_host_by_event_name' ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ;
Empty set (0.02 sec)

Execution time is much higher than MySQL 8.0.20. While in MySQL 8.0.20 it takes about 0.02 sec.
This affects performance for loading all foreign keys when the system have many tables.

My questions: Is there any specific option configuration or workaround to improve the performance loading ?

How to repeat:
Run mysql command directly and compare the execution time between MySQL 8.0.35 and MySQL 8.0.20.

Suggested fix:
No solution yet. 
Seeing that there is a bug relates to performance issue: https://bugs.mysql.com/bug.php?id=111538, but not sure if this issue relates to this bug.

Hi Mr. Trong Dinh,

Thank you for your bug report.

This is indeed a performance improvement request.

However, we were not able to repeat it.

We ran your queries with several MySQL versions ....

MySQL 5.7:

Empty set (0.26 sec)

MySQL 8.0.37:

Empty set (0.03 sec)

MySQL 8.3.0:

Empty set (0.01 sec)

We probably do not have sufficient number of tables with foreign keys, so that we can repeat your performance problem.

Hence, if you can send us just a number of CREATE TABLE statements, that would enable us to repeat the performance degradation, we would be grateful.

Thanks a lot in advance.

Thank you for your updates,

In my system, there are about 350 tables including system schema(s) like mysql, performance_schema and my application (203 tables).

We did try the query from MySQL 8.0.20 to 8.0.28, the execution time is returning quickly, but not for MySQL 8.0.33 and 8.0.35

One thing I can see is that if select * from FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE; contains NULL value in REFERENCED_TABLE_SCHEMA or REFERENCED_TABLE_NAME, the query as I provide in description bug to get foreign keys on each table is slowly in MySQL 8.0.35.

Not sure if this is the cause of performance issue but this is strange with <= MySQL 8.0.28 versions since the query runs quickly in these versions with the same command.
=> This makes about query performance concern.

mysql>  select * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
+--------------------+--------------------+------------------------------+---------------+--------------------+------------------------------------------------------+-----------------------------+------------------+-------------------------------+-------------------------+--------------------------------+------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA  | CONSTRAINT_NAME              | TABLE_CATALOG | TABLE_SCHEMA       | TABLE_NAME                                           | COLUMN_NAME                 | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME          | REFERENCED_COLUMN_NAME |
+--------------------+--------------------+------------------------------+---------------+--------------------+------------------------------------------------------+-----------------------------+------------------+-------------------------------+-------------------------+--------------------------------+------------------------+
| def                | mysql              | PRIMARY                      | def           | mysql              | innodb_table_stats                                   | database_name               |                1 |                          NULL | NULL                    | NULL                           | NULL                   |
| def                | mysql              | PRIMARY                      | def           | mysql              | innodb_table_stats                                   | table_name                  |                2 |                          NULL | NULL                    | NULL                           | NULL                   |
| def                | mysql              | PRIMARY                      | def           | mysql              | innodb_index_stats                                   | database_name               |                1 |                          NULL | NULL                    | NULL                           | NULL                   |
| def                | mysql              | PRIMARY                      | def           | mysql              | innodb_index_stats                                   | table_name                  |                2 |                          NULL | NULL                    | NULL                           | NULL                   |
| def                | mysql              | PRIMARY                      | def           | mysql              | innodb_index_stats                                   | index_name                  |                3 |                          NULL | NULL                    | NULL                           | NULL                   |
| def                | mysql              | PRIMARY                      | def           | mysql              | innodb_index_stats                                   | stat_name                   |                4 |                          NULL | NULL                    | NULL                           | NULL                   |
| def                | performance_schema | PRIMARY                      | def           | performance_schema | cond_instances                                       | OBJECT_INSTANCE_BEGIN       |                1 |                          NULL | NULL                    | NULL                           | NULL                   |
....

For MySQL Community Server 8.0.37, has it bean released yet ?

Hi Mr. Dinh,

First of all, 8.0.37 will be released relatively soon .... may be until the end of the month.

But, we got the same results with 8.0.36.

The fact is that we cannot repeat your results. The newer the version, the query gets faster.

Do note that we use binaries that are built  by us and downloadable from htttps://dev/mysql.com. We do not use binaries built by third parties, because nobody makes such good binaries as we do.

Still, if you supply us with 20 - 30 CREATE TABLE statements, connected by foreign keys, may be we manage to repeat your results. However, we truly doubt it ......

Can't repeat.

Hi Mr. Dinh,

We created 51 tables, all of them in foreign key hierarchy.

These are the results:

MySQL 5.7:

Empty set (0.03 sec)

MySQL 8.0

Empty set (0.00 sec)

MySQL 8.3

Empty set (0.01 sec)

Hence, this bug report is not repeatable.

Hi team,
I still got the same performance issue on my system ( having ~200 tables ). In step of creating table, I did:
1. Drop existing tables
2. Create tables: create table(...) engine=InnoDB;
3. Add constraint keys for those tables:
   alter table <Table name> 
       add constraint FKd0a1nj703tcd016uhqgvbdrr 
       foreign key (MYPID) 
       references <Referenced table> (PID);
      
I also tried to randomly generate about 60 tables on MySQL 8.0.35, it run quickly as your case
I think if the system has a large number of tables it may have problems.

Another things, I check the explanation of query and I don't know why they are different between MySQL 8.0.20 and MySQL 8.0.35 (same query command, same database schema)
Please see the attached file.

- MySQL 8.0.20 execution time : 0.02s
- MySQL 8.0.20 execution time : 0.25s ( 10 times larger even though they are the same database).

Concern:
 Why are the query steps in the explanation different between the two MySQL versions? Does it cause performance issues on MySQL 8.0.35?
- In your environment, do you run MySQL server with default configuration?

Thanks

Hi Mr. Dinh,

We made a test case very similar to yours.

We still get the same results.

Do note, that the performance for  even this type of SQL statements depends a lot on how you have configured MySQL server. We configure our servers in an optimal manner. 

Configuring the server properly is fully described in our Reference Manual.

Can't repeat.

Thanks for your information

Can you tell why the query steps in the explanation are different between the two MySQL versions? I have attached the output command (ExplainQueryOfPerfIssue.txt) in the comment above. 

Besides, I also try using default configuration and got the same result on MySQL 8.0.35

BTW, if you can share me about your mysql.conf file to check and compare.
I focus on MySQL documentation to custom some configurations in this file (eg. max_connection, max_heap_table_sie, innodb_redo_log_capacity,...). This file has not changed much between MySQL 8.0.20 and MySQL 8.0.35.

Thank you

Hi Mr. Dinh,

The explain of the queries are different since we are constantly working on improving our Optimiser.

We also tried the default configuration, but got better results with higher versions.

Do note that this is a forum for the bugs with fully repeatable test cases and we were not able to repeat it. More important, this is  not a site for support. Hence, we cannot provide you with any advice on the configuration. Our Reference Manual has all the information that you need.

We do have sites for the free and paid support. If you wish, we can send you links for the both.