MySQL Bugs: #92979: MySQL 8.0 performance degradation on INSERT with foreign_key

Bug #92979	MySQL 8.0 performance degradation on INSERT with foreign_key_checks=0
Submitted:	28 Oct 2018 13:51	Modified:	25 Jan 4:35
Reporter:	Predrag Zivanovic	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0.13 Communty Server	OS:	Any
Assigned to:		CPU Architecture:	x86
Tags:	dump, foreign keys

Description:
There is significant performance degradation between MySQL 5.7 and MySQL 8.0 when importing SQL dump with foreign keys and with foreign_key_checks=0. It looks like MySQL 8.0 is checking foreign keys references even with foreign_key_checks=0, only without error message.

How to repeat:
Here is MySQL dump file attached. On new fresh installation of MySQL 5.7 it took 15 seconds to import ... on MySQL 8.0 it took more then 400 seconds. InnoDB storage engine, default settings in both cases.

MySQL dump

Attachment: robno1.rar (application/octet-stream, text), 2.79 MiB.

MySQL dump file attached - one table with foreign keys.

Hi Predrag,

Verified as described both on Windows and on Linux. I was not able to reproduce the 25x slowdown you reported but I'm getting a consistent 5-6x slowdown between latest 5.7 and latest 8.0 releases both on Windows and Linux.

Insert block on 5.7 last 0.00 - 0.01sec while on 8.0 it goes 0.05 to 0.07sec, total insert time around 6 times slower on 8.0.

Now, this is not a bug, it's a performance issue most probably caused by the way data dictionary works but thanks for reporting, we'll see if there's anything we can do to change that.

pozdrav
Bogdan

I have tested this on 3 different PCs (Windows) and my conclusion is that total slowdown depends on CPU speed: higher CPU speed = lower slowdown (but not less then 5x). Anyway, thank you for verifying.

Pozdrav,
Predrag

Posted by developer:
 
Testing on Linux comparing MySQL 5.7.44 with MySQL 8.2.0 when executing the attached robno1.sql file gives the following results:
MySQL 5.7.44 robno1.sql takes approximately 15 seconds with default MySQL configuration
MySQL 8.2.0  robno1.sql takes approximately 21 seconds with default MySQL configuration
MySQL 8.2.0  robno1.sql takes approximately 14 seconds with binary log disabled (--skip-log-bin)

Note that the binary log is disabled by default in MySQL 5.7 but is enabled by default in MySQL 8.0.

Posted by developer:
 
Closed as duplicate of Bug#27859086

Fixed in 8.0.14.
 
A metadata locking deadlock could occur when opening a foreign key
parent table.

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-14.html