| Bug #110402 | The parallel tablespace scan in 8.0 doesn't work as expected | ||
|---|---|---|---|
| Submitted: | 16 Mar 2023 21:04 | Modified: | 18 Jul 2024 23:13 |
| Reporter: | Shu Zhou | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Mar 2023 11:54]
MySQL Verification Team
Hello Shu Zhou, Thank you for the report and feedback. regards, Umesh
[20 Mar 2023 11:56]
MySQL Verification Team
Related - Bug #96340, Bug #103743
[27 Mar 2023 17:09]
Shu Zhou
Thanks Umesh for the update. Yeah I checked those related bugs before I discovered real issue. This bug reported here seemed render the original implementation less effective as expected.
[18 Jul 2024 23:13]
Philip Olson
Posted by developer: Fixed as of the upcoming MySQL Server 8.0.39, 8.4.2, and 9.0.1 releases, and here's the proposed changelog entry from the documentation team: Improved tablespace file scan performance at startup. Thank you for the bug report.
Description: In MySQL 8.0 code where tablespaces are scanned during startup, a piece of parallel check code does not work as expected. The implementation in Validate_files::check has a mutex blocking concurrent I/Os, effectively nullifies the parallelism attempt in Validate_files::validate. How to repeat: Create large number of tables, say a few millions, then kill the server and restart. One should notice a long time spending on the tablespace scan. It does not have to have an unclean shutdown but the crash-recovery does magnify the problem because a clean shutdown will bypass the step to open each .ibd file and exam the first page. Suggested fix: Something like: *** ha_innodb.cc 2023-03-16 20:53:43.997135541 +0000 --- ha_innodb.fix.cc 2023-03-16 20:53:58.910413990 +0000 *************** *** 3602,3610 **** ++m_n_errors; break; } ! std::lock_guard<std::mutex> guard(m_mutex); state = fil_tablespace_path_equals(space_id, space_name, fsp_flags, dd_path, &new_path); --- 3602,3610 ---- ++m_n_errors; break; } ! std::unique_lock<std::mutex> guard(m_mutex); state = fil_tablespace_path_equals(space_id, space_name, fsp_flags, dd_path, &new_path); *************** *** 3744,3751 **** --- 3744,3753 ---- ++m_n_validated; continue; } + guard.unlock(); + /* The IBD filename from the DD has not yet been opened. Try to open it. It's safe to pass space_name in tablename charset because filename is already in filename charset. */ bool validate = recv_needed_recovery && srv_force_recovery == 0;