MySQL Forums
Forum List  »  InnoDB

Rename table taking too long on MySql 8
Posted by: Faheem Memon
Date: February 09, 2024 11:57AM

We recently upgraded from MySql 5.7 to 8 and have run into this issue. We have a rename table operation that runs every minute. It used to take a fraction of a second, but now it takes over 6.0 seconds, effectively halting everything else.

We have been able to narrow the issue down to views. We have a bunch of views that reference the table that's getting renamed. We see normal processing time if we get rid of the views. Has anyone else seen this? How would you recommend solving this issue? Also, this was not a problem in 5.7 so I would love to learn more about what changed in Mysql 8 innodb engine that is causing this.

We have tried the ddl-threads related configuration, but it wasn't helpful. Rename and alter the table; both operations take the same time. The following is the summarized query profiling information:

How to reproduce:
mysql> create table t1 (id int, name varchar(32));
mysql> rename table t1 to t2;
mysql> show profile for query 11;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000082 |
| Executing hook on transaction | 0.000020 |
| starting | 0.000021 |
| checking permissions | 0.000020 |
| checking permissions | 0.012003 |
| waiting for handler commit | 0.000024 |
| waiting for handler commit | 0.002365 |
| query end | 0.000023 |
| closing tables | 0.000014 |
| waiting for handler commit | 0.000373 |
| freeing items | 0.000017 |
| cleaning up | 0.000018 |
+--------------------------------+----------+
12 rows in set, 1 warning (0.01 sec)

mysql> rename table t2 to t1;
mysql> create view v1 as select * from t1;
mysql> rename table t1 to t2;
mysql> show profile for query 17;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000058 |
| Executing hook on transaction | 0.000014 |
| starting | 0.000022 |
| checking permissions | 0.000014 |
| checking permissions | 0.014052 |
| Opening tables | 0.000246 |
| checking permissions | 0.000018 |
| checking permissions | 0.000235 |
| waiting for handler commit | 0.000018 |
| waiting for handler commit | 0.003426 |
| query end | 0.000022 |
| closing tables | 0.000013 |
| waiting for handler commit | 0.000045 |
| freeing items | 0.000034 |
| cleaning up | 0.000019 |
+--------------------------------+----------+
15 rows in set, 1 warning (0.01 sec)

In this simple example, you see more check permissions requests. In our production, the difference is 20x. Any suggestions as to how to address this?

Options: ReplyQuote


Subject
Views
Written By
Posted
Rename table taking too long on MySql 8
1191
February 09, 2024 11:57AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.