MySQL Bugs: #116716: Allow Setting Nb Thread for Tablespace Duplicate Check (startup).

Bug #116716	Allow Setting Nb Thread for Tablespace Duplicate Check (startup).
Submitted:	19 Nov 2024 17:06	Modified:	3 Dec 2024 18:04
Reporter:	Jean-François Gagné	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	9.1.0, 8.4.3, 8.0.40	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
Hi,

as explained in my blog post [1], Duplicate Check is a multithreaded process that uses at most 2 threads per core, and at most 16 threads overall.  See function fil_get_scan_threads for details (call: [2]; definition: [3]; THREADS_PER_CORE limit: [4]; THREADS_PER_CORE definition: [5]; MAX_THREADS limit: [6]; MAX_THREADS definition: [7]).

[1]: https://jfg-mysql.blogspot.com/2024/11/understanding-innodb-tablespace-duplicate-check.htm...

[2]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/fil/fil0fil.cc#L11...

[3]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/fil/fil0fil.cc#L13...

[4]: https://github.com/jfg956/mysql-server/blob/mysql-9.1.0/storage/innobase/fil/fil0fil.cc#L1...

[5]: https://github.com/jfg956/mysql-server/blob/mysql-9.1.0/storage/innobase/include/fil0fil.h...

[6]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/fil/fil0fil.cc#L15...

[7]: https://github.com/mysql/mysql-server/blob/mysql-9.1.0/storage/innobase/include/fil0fil.h#...

I claim that 2 threads per core and 16 threads overall is far from optimal.  Because computing an optimal is complex, this value should be configurable.  Some justification for this claim in How to repeat.  I will soon provide a patch and show test results.

Many thanks for looking into this (and if not "verifiable" in the current state, keeping opened in "needs feedback" while I am completing the patch),

Jean-François Gagné

How to repeat:
Presented in [1.1], we have below System Analysis for Duplicate Check.

[1.1]: https://jfg-mysql.blogspot.com/2024/11/understanding-innodb-tablespace-duplicate-check.htm...

> for a disk capacity of c (example 64k iops) and an IO latency of l (example 0.5 ms), c*l threads are needed (32 for our example) to saturate IO capacity

Above already gives quite a realistic example where more than 16 threads would lead to a quicker startup.

Another example is below (quote from [1]), where more than 2 threads per core should give a potentially quicker startup (few cores and lots of iops).

> t threads (example 4) for an IO latency of l (example 0.5 ms) are able to use t/l iops (8000 for our example)
>
> with a latency of 0.5 ms and 2 cores (so 4 threads), we would only be able to use 8000 iops, while AWS gp3 EBS volumes can scale up to 16k iops

Hello Jean-François,

Thank you for the feature request!

regards,
Umesh

More about this contribution in https://github.com/jfg956/mysql-server/pull/15 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug#116716.patch (application/octet-stream, text), 4.97 KiB.

Hello Jean-François,

Thank you for your contribution.

regards,
Umesh

(use-cases about how this contribution is useful will be added soon as a new comment)

Some notes about my above contribution extracted from https://github.com/jfg956/mysql-server/pull/15

This PR merges on 9.1.0, and the patch file also applies on 8.4.3 and 8.0.40.

For allowing setting the number of threads for InnoDB Tablespace Duplicate Check, this PR introduces a new global variable : innodb_tablespace_duplicate_check_threads. The default value is -1, which means the already implemented heuristic is used for setting the number of threads. A value of 0 to MAX VAL overrides the number of threads of the heuristic.

Ideally, in addition to being included in a next Innovation Release, this would be back ported in 8.4 and 8.0. The default value of the global variable introduced by this change is backward compatible with 8.4 and 8.0, so I think this change does not introduce any compatibility problems. And as this change introduces a potential performance improvement making startup faster, I think there is value in back-porting it.

I blogged about this contribution in below.

https://jfg-mysql.blogspot.com/2024/11/innodb-tablespace-duplicate-check-threads.html

In above post, I show that:

> Duplicate Check thread tuning is useful for small vm connected to high-throughput network disks (these usually have high latency, needing many threads)

Indeed, default startup on a AWS m6i.large instance with 16k iops disk takes 3:33 (3 minutes 33 seconds), compared to 2:28 and 2:23 with 8 and 16 threads, which is 65 seconds faster (30%).