Bug #105710 Parallelize the "Drop all user data" DROP TABLE phase of clone

• Submitted: 25 Nov 2021 12:25
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Verified
• Category: MySQL Server: Clone Plugin
• Severity: S4 (Feature request)
• Version: 8.0.27
• OS: Any
• CPU Architecture: Any

[25 Nov 2021 12:25] Shane Bester

Description:
This phase takes far longer than it should:

2021-11-25T12:17:24.500370Z 9 [Warning] [MY-013460] [InnoDB] Clone removing all user data for provisioning: Started
2021-11-25T12:17:24.500390Z 9 [Note] [MY-011977] [InnoDB] Clone Drop all user data
2021-11-25T12:17:25.073706Z 9 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 3302 task: 0

Looking a processlist shows single threaded DROP TABLE ...

| Command | Time  | State                      | Info                     |
+---------+-------+----------------------------+--------------------------+
| Sleep   |  7924 |                            | NULL                     |
| Query   |   377 | waiting for handler commit | DROP TABLE `test`.`t801` |
| Query   |     0 | init                       | show processlist         |

How to repeat:
Create a few thousand large tables in the DB.
Clone another instance to this instance so that all user data must be dropped.

Suggested fix:
Execute multiple DROP TABLE in parallel threads.

[26 Nov 2021 12:51] MySQL Verification Team

dropping my dataset in single thread takes this long for 2TB:

2021-11-26T12:22:54.977102Z 10 [Warning] [MY-013460] [InnoDB] Clone removing all user data for provisioning: Started
2021-11-26T12:22:54.977116Z 10 [Note] [MY-011977] [InnoDB] Clone Drop all user data
2021-11-26T12:22:55.711609Z 10 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 3303 task: 0
2021-11-26T12:50:22.334577Z 10 [Note] [MY-011977] [InnoDB] Clone Drop User schemas
2021-11-26T12:50:22.334814Z 10 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 5 task: 0
2021-11-26T12:50:22.356157Z 10 [Note] [MY-011977] [InnoDB] Clone Drop User tablespaces
2021-11-26T12:50:22.356908Z 10 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 6 task: 0
2021-11-26T12:50:22.357754Z 10 [Note] [MY-011977] [InnoDB] Clone Drop: finished successfully