Bug #92979 MySQL 8.0 performance degradation on INSERT with foreign_key_checks=0
Submitted: 28 Oct 2018 13:51 Modified: 30 Oct 2018 8:38
Reporter: Predrag Zivanovic Email Updates:
Status: Verified 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

[28 Oct 2018 13:51] Predrag Zivanovic
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.
[28 Oct 2018 14:14] Predrag Zivanovic
MySQL dump

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

[28 Oct 2018 14:15] Predrag Zivanovic
MySQL dump file attached - one table with foreign keys.
[30 Oct 2018 2:50] Bogdan Kecman
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
[30 Oct 2018 8:38] Predrag Zivanovic
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