MySQL Bugs: #99914: update slow Opening tables phase

Bug #99914	update slow Opening tables phase
Submitted:	17 Jun 2020 11:28	Modified:	17 Jun 2020 19:26
Reporter:	Pavel Dvořák	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0.20	OS:	Debian (10 x64)
Assigned to:		CPU Architecture:	x86 (Xeon)
Tags:	innodb, opening tables, UPDATE

Description:
The query is stuck 1-2 seconds during Opening table phase.

I don't know what's causing slow down but probably that's caused by FK.

But the primary key of the row is on changed so it's not needed to check foreign keys on the row.

Table size is 20GB, PK cardinality is 54055664
There's 240 Fk's referencing to firm_db.id

CREATE TABLE `firm_db` (
  `id` int UNSIGNED NOT NULL,
  `country_id` int NOT NULL,
  `uid` varchar(10) NOT NULL,
  `date_modified` datetime DEFAULT NULL,
  `content` longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Vazební tabulka' ROW_FORMAT=DYNAMIC;

How to repeat:
run this query: UPDATE `firm`.`firm_db` SET `date_modified`='2020-06-17 11:12:15', `content`='xxx"}' WHERE id=8250278 

notice it takes 2 seconds to execute.

Suggested fix:
Don't check FKs if FK value is not updated.

main table

Attachment: firm_db.sql (application/octet-stream, text), 1.54 KiB.

table like this for each country

Attachment: firm_db_ad.sql (application/octet-stream, text), 3.99 KiB.

Hi Mr. Dvorak,

Thank you for your bug report.

However, I am not capable of repeating it on my machine.

Let me tell you what I have done. I have created 21 (twenty one) tables, each with several thousands of rows. Twenty of those tables have foreign keys on the primary key of the parent table.  Each of these tables has lots of quite long VARCHAR columns, all of those indexed, but none of those columns is a parent of child to any other table. I have UPDATED all of these VARCHAR columns only on a parent table, by searching on the primary key of the said parent table.

I have noted a time of the start of the UPDATE and the end of the UPDATE. All operations finished successfully.

This is the time it took for that UPDATE:

CURTIME()
16:23:17
CURTIME()
16:23:17

Hence, the entire operation occurred within a single second. I measured it manually and my stopwatch showed only 0.2 seconds.

Hence, I can not repeat the problem that you are reporting !!!!

Hmm I'm confused.
The same queries run much faster now in the evening. No more 1 second waits.
Any hints how to debug this?
Would some performance tables help?

Hi,

Your server could be simply much more loaded at some times then some other.

You can look at the processlist, P_S tables, etc ...

However, that discussion can not be continued on this forum. This is a forum for the bugs with repeatable test case. This is not a free support. You can try asking your questions on forums.mysql.com.