Bug #103271 InnoDB recovery can take a while with a lot of partitions and HDD
Submitted: 9 Apr 20:14 Modified: 15 Apr 6:24
Reporter: Arnaud Adant Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:8.0.23 OS:Linux
Assigned to: CPU Architecture:x86

[9 Apr 20:14] Arnaud Adant
Description:
The server takes a while to recover when it crashes and it has a lot of partitions.

Phase 1 :

2021-04-09T19:35:14.540600Z 1 [Note] [MY-012203] [InnoDB] Directories to scan './'
2021-04-09T19:35:14.540851Z 1 [Note] [MY-012204] [InnoDB] Scanning './'
2021-04-09T19:35:23.635038Z 1 [Note] [MY-012207] [InnoDB] Using 17 threads to scan 818102 tablespace files
2021-04-09T19:35:33.313901Z 0 [Note] [MY-012200] [InnoDB] Thread# 10 - Checked 962/51131 files
2021-04-09T19:35:33.314512Z 0 [Note] [MY-012200] [InnoDB] Thread# 11 - Checked 949/51131 files
2021-04-09T19:35:33.314637Z 0 [Note] [MY-012200] [InnoDB] Thread# 3 - Checked 941/51131 files
2021-04-09T19:35:33.314853Z 0 [Note] [MY-012200] [InnoDB] Thread# 0 - Checked 956/51131 files
2021-04-09T19:35:33.315103Z 0 [Note] [MY-012200] [InnoDB] Thread# 5 - Checked 927/51131 files
2021-04-09T19:35:33.315316Z 0 [Note] [MY-012200] [InnoDB] Thread# 1 - Checked 947/51131 files
2021-04-09T19:35:33.315477Z 0 [Note] [MY-012200] [InnoDB] Thread# 6 - Checked 930/51131 files
2021-04-09T19:35:33.316773Z 0 [Note] [MY-012200] [InnoDB] Thread# 15 - Checked 930/51131 files
2021-04-09T19:35:33.318605Z 0 [Note] [MY-012200] [InnoDB] Thread# 13 - Checked 949/51131 files

Phase 2 :

2021-04-09T19:43:50.895095Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
2021-04-09T19:44:22.277811Z 1 [Note] [MY-012356] [InnoDB] Thread# 16 - Validated 10/10  tablespaces
2021-04-09T19:44:31.312532Z 0 [Note] [MY-012350] [InnoDB] Thread# 14 - Validated 425/51131 tablespaces so far.
2021-04-09T19:44:31.315280Z 0 [Note] [MY-012350] [InnoDB] Thread# 6 - Validated 872/51131 tablespaces so far.
2021-04-09T19:44:31.317980Z 0 [Note] [MY-012350] [InnoDB] Thread# 8 - Validated 508/51131 tablespaces so far.
2021-04-09T19:44:31.320288Z 0 [Note] [MY-012350] [InnoDB] Thread# 12 - Validated 555/51131 tablespaces so far.
2021-04-09T19:44:31.322494Z 0 [Note] [MY-012350] [InnoDB] Thread# 13 - Validated 804/51131 tablespaces so far.

On this server, it took 15 min to start.

How to repeat:
Create a lot of partitions :

0.

use log_error_verbosity=3 to see the progress

flush the file system cache

1. 

create database test_partitions;
use test_partitions;
create table t(id int auto_increment primary key) PARTITION BY HASH(id) PARTITIONS 8100;
create table t1 like t;
create table t2 like t;
create table t3 like t;
create table t4 like t;
create table t5 like t;
create table t6 like t;
create table t7 like t;
create table t8 like t;
create table t9 like t;
create table t10 like t;
create table t11 like t;
create table t12 like t;
create table t13 like t;
create table t14 like t;
create table t15 like t;
create table t16 like t;
create table t17 like t;
create table t18 like t;
create table t19 like t;
create table t20 like t;
create table t21 like t;
create table t22 like t;
create table t23 like t;
create table t24 like t;
create table t25 like t;
create table t26 like t;
create table t27 like t;
create table t28 like t;
create table t29 like t;
create table t30 like t;
create table t31 like t;
create table t32 like t;
create table t33 like t;
create table t34 like t;
create table t35 like t;
create table t36 like t;
create table t37 like t;
create table t38 like t;
create table t39 like t;
create table t40 like t;
create table t41 like t;
create table t42 like t;
create table t43 like t;
create table t44 like t;
create table t45 like t;
create table t46 like t;
create table t47 like t;
create table t48 like t;
create table t49 like t;
create table t50 like t;
create table t51 like t;
create table t52 like t;
create table t53 like t;
create table t54 like t;
create table t55 like t;
create table t56 like t;
create table t57 like t;
create table t58 like t;
create table t59 like t;
create table t60 like t;
create table t61 like t;
create table t62 like t;
create table t63 like t;
create table t64 like t;
create table t65 like t;
create table t66 like t;
create table t67 like t;
create table t68 like t;
create table t69 like t;
create table t70 like t;
create table t71 like t;
create table t72 like t;
create table t73 like t;
create table t74 like t;
create table t75 like t;
create table t76 like t;
create table t77 like t;
create table t78 like t;
create table t79 like t;
create table t80 like t;
create table t81 like t;
create table t82 like t;
create table t83 like t;
create table t84 like t;
create table t85 like t;
create table t86 like t;
create table t87 like t;
create table t88 like t;
create table t89 like t;
create table t90 like t;
create table t91 like t;
create table t92 like t;
create table t93 like t;
create table t94 like t;
create table t95 like t;
create table t96 like t;
create table t97 like t;
create table t98 like t;
create table t99 like t;
create table t100 like t;

2. crash the server (kill -9)

3. restart the server and wait

Suggested fix:
Improve the recovery process speed in that particular case (crash recovery).
[10 Apr 9:25] MySQL Verification Team
Hello Arnaud,

Thank you for the report and test case.
I'll try to reproduce at my end on Monday and get back to you if anything further needed. Thank you!

Sincerely,
Umesh
[15 Apr 6:24] MySQL Verification Team
Thank you, Arnaud.
Verified as described.

regards,
Umesh
[15 Apr 7:41] MySQL Verification Team
MySQL Server 8.0.23 test results

Attachment: 103271_8.0.23.results (application/octet-stream, text), 397.79 KiB.