Description:
Hi,
as shown in Bug#115988 (part of that bug is copied in How to repeat), MySQL startup slowed down significantly from 5.7.44 to 8.0.39. I am opening this new bug to formally report this, share details, and allow tracking the progress of fixing this regression.
Other bugs have been opened on this subject in the past. To my knowledge, only workarounds have been provided so far, nothing actually addressed / fixed the regression introduced in 8.0. An example of such bugs is Bug#96340.
In Bug#96340, there is an interesting context about why this regression was introduced, below are quotes from the comment made on 13 Aug 2019 17:33 in that bug.
> The 5.7 startup can be faster because 8.x reverts the 5.7 changes where we wrote the open file descriptors in the redo log on checkpoint. The scheme introduced in 5.7 was very buggy and it has a runtime cost (and lots of edge case bugs during recovery) and if you have lots of tables open and written to since the last checkpoint well good luck.
>
> We decided to revert to 5.6 behavior to pay the cost on startup and will fix it properly later.
In above, I would like to emphasize "fix it properly later": I am opening this new bug to make sure that "fix it later" is not forgotten and is traced in public.
Also and interestingly, long startup is acknowledged in WL#14008 (InnoDB: Make tablespace filename validation optional via --innodb-validate-tablespace-paths), quote below. Making "tablespace filename validation optional" is another example of workarounds for long startups without a real solution.
> By default we take a conservative/pessimistic approach. On startup we always check the path of all known table spaces against the information stored in the data dictionary. In case the user has moved files around. This can be very costly when the number of tablespace [sic] is high e.g., more than 100K.
Filing this as a S2 / Serious, because normal startup speed is not a minor thing. Such long startups impede agility when running MySQL in containers, where we want container startup to be fast.
Note that I understand startup might take a longer time on crash-recovery or on version upgrades. IMHO, these two (crash-recovery and upgrades) are out of scope of this work.
I listed the impacts I know of in Suggested fix below.
Many thanks for looking into this,
Jean-François Gagné
How to repeat:
Below, copied from Bug#115988, is the startup time of MySQL versions 5.7.44, 8.0.39, 8.4.2 and 9.0.1.
5.7.44 start: 0:00:50
8.0.39 start: 0:02:43
8.4.2 start: 0:02:44
9.0.1 start: 0:02:43
In below, also copied from Bug#115988, we see that even with the optimization of Bug#115988, startup of 9.0.1 is still slower than 5.7.44, and it consumes significantly more disk resources (9.694.921 vs 2.809.958 sector read).
5.7.44 start: 0:00:50
5.7.44 diskstats: 2809958 sector read.
9.0.1 light start: 0:01:09
9.0.1 light diskstats: 9694921 sector read.
Suggested fix:
In here, I am putting the things that I know should be addressed for faster startup. I list them first, then detail each element.
1. Move Duplicate Check from Startup to Runtime;
2. Move rename_partition_files to Upgrade (or get rid of it);
3. Better Moving Tablespace Files;
4. Undo Tablespace House-Keeping;
5. Non-empty Change Buffer Considerations;
6. Make DD Tablespace Files Reading 100% optional.
-----------------------------------------------
1. Move Duplicate Check from Startup to Runtime
There is a phase of MySQL startup that is doing a "space ID check" / Duplicate Check [1.1]. This part needs getting the space id of each data / tablespace file, which takes a long time. It is unclear to me why this check is needed at each startup, especially after a normal shutdown. If it is needed, it should be moved to runtime to make startup faster.
[1.1]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/fil/fil0fil.cc#L11...
Note that the "space ID" to "filename" mapping, collected while doing Duplicate Check, is also used for Moving Tablespace Files. So "just" removing "space ID check" / Duplicate Check does not fully remove the need of getting the space id of each data / tablespace file. See #3 below for more detail.
I give a little more detail about the MySQL Startup Phase that I call "Duplicate Check" in a blog post [1.2], and many more details in a follow-up post [1.3].
[1.2]: https://jfg-mysql.blogspot.com/2024/11/long-and-silent-stressful-mysql-startup.html#phases
[1.3]: https://jfg-mysql.blogspot.com/2024/11/understanding-innodb-tablespace-duplicate-check.htm...
------------------------------------------------------------
2. Move rename_partition_files to Upgrade (or get rid of it)
The call to rename_partition_files [2.1] needs collecting the list of ibd files [2.2], which takes time. I already open a bug about this (Bug#116713), the details are in this other report, this section is "just" for completeness.
[2.1]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/fil/fil0fil.cc#L11...
[2.2]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/fil/fil0fil.cc#L11...
---------------------------------
3. Better Moving Tablespace Files
There is a MySQL feature which allows "Moving Tablespace Files While the Server is Offline" [3.1]. It is possible to disable this feature, but even when disabled, the mapping of "space ID" to "filename" is still collected during Duplicate Check (see #1 above). When Duplicate Check is removed, we can also remove building this mapping when Tablespace Path Validation is disabled.
[3.1]: https://dev.mysql.com/doc/refman/8.0/en/innodb-moving-data-files-offline.html
As a side note, _always_ paying the startup price for "maybe" moving tablespace files looks like a very questionable choice (to say the least). In that regard, Tablespace Path Validation [3.2] should be disabled by default, and only enabled for when moving files around is needed. Also, MySQL should provide a feature to move tablespace files at runtime to avoid having to do too much wasteful work on startup. This might deserve its own bug report, I might expand on this one day.
[3.2]: https://dev.mysql.com/doc/refman/8.0/en/innodb-disabling-tablespace-path-validation.html
Also, MySQL runtime should be resilient to the absence of a tablespace file (error on query), and I would even not be completely shocked if MySQL asserts / crashes in the absence of a tablespace file that should be there. For me and in this situation, a query error (or a crash) is better than a slow (or failed) startup (and after startup, users might be provided with a SQL command to tell the Server that a file has been moved, which would be better than paying the price at each startup).
--------------------------------
4. Undo Tablespace Housekeeping
It was discovered the hard way, via Bug#32493885 [4.1], that InnoDB Tablespace Path validation is not only doing Path Validation (and this is why in my blog post [1.2], I call this phase of startup Tablespace Validation and not "only" Path Validation). For this specific bug, Tablespace Validation is also doing what I call Undo Tablespace Housekeeping. The "quick fix" for this bug was to not fully bypass iterating on the tablespaces when Path Validation is disabled, but quickly iterating on them, and do special work only for Undo Tablespace. This Undo Tablespace housekeeping should have its own dedicated startup phase, and maybe only be done when needed (link to relevant code in [4.2], [4.3] and [4.4], and I do not claim this is exhaustive).
[1.2]: https://jfg-mysql.blogspot.com/2024/11/long-and-silent-stressful-mysql-startup.html#phases
[4.1]: https://github.com/mysql/mysql-server/commit/eef88fb2565a0fd9d9b123ce4c7c969f678f6831
[4.2]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/handler/ha_innodb....
[4.3]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/handler/ha_innodb....
[4.4]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/handler/ha_innodb....
-----------------------------------------
5. Non-empty Change Buffer Considerations
It was also discovered the hard way, via Bug #35208990 [5.1], that InnoDB Tablespace Path validation is not only doing Path Validation, but also doing essential work for data consistency of buffered changes. The "quick fix" for this bug was to not honor a disabled Path Validation when the Change Buffer is non-empty, and below quote from the code explains this [5.2].
[5.1]: https://github.com/mysql/mysql-server/commit/2646b4bfc100
[5.2]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/handler/ha_innodb....
> Change buffer applier background thread will skip the change buffer entries of the tablespaces which are not loaded which will cause corruption of secondary indexes, so it is important to load the tablespaces for which entry is present in the change buffer. Presently we are loading all the tablespaces.
The right way to make startup faster is to move the loading of tablespaces to the runtime of the Change Buffer applier background thread, or on startup, to only load the tablespaces present in the Change Buffer (might need scanning a significant amount of data), or dump on shutdown the list of tablespaces in the Change Buffer for loading on startup (and on a crash, a full load would be required, but in its own dedicated crash-recovery phase of startup).
As a side note, I know the default for the Change Buffer has been set to None in 8.4 [5.3], and there might be plans to deprecate and remove this feature. IMHO, this would be a big loss. I could expand on this subject if needed.
[5.3]: https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html
-------------------------------------------------
6. Make DD Tablespace Files Reading 100% optional
DD Tablespace Files Reading is one of the phases of long MySQL startups (name my own, more about this in [1.2]). Once #3, #4 and #5 above (and maybe more) are done, this can be completely skipped, which will make startup faster. The relevant code for this phase is in [6.1].
[1.2]: https://jfg-mysql.blogspot.com/2024/11/long-and-silent-stressful-mysql-startup.html#phases
[6.1]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/handler/ha_innodb....
© 2024, Oracle Corporation and/or its affiliates