Bug #119279 The default value of innodb_read_io_threads uses too many system resources with mysql-test.run.pl

• Submitted: 31 Oct 10:48
• Reporter: Przemysław Skibiński (OCA)
• Status: Open
• Category: MySQL Server: Tests
• Severity: S3 (Non-critical)
• Version: 8.4.6
• OS: Any
• CPU Architecture: Any

[31 Oct 10:48] Przemysław Skibiński

Description:
I'm using AMD EPYC 9554 64-Core (128 threads available) and MySQL 8.4.6 sets the following default values:
SELECT @@global.innodb_read_io_threads;
@@global.innodb_read_io_threads
64
SELECT @@global.innodb_parallel_read_threads;
@@global.innodb_parallel_read_threads
16

When running "mysql-test.run.pl --suite=rpl --parallel=64" I see aio-nr goes from:
aio-nr=0 processes=1531
up to:
aio-nr=854658 processes=2264
aio-nr=859782 processes=2259
aio-nr=829576 processes=2250

With other parameters it reaches 1048576 which is default "fs.aio-max-nr" limit for most Linux distros and causes:
[MY-012582] [InnoDB] io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
[MY-012583] [InnoDB] io_setup() attempt 1.
[MY-012583] [InnoDB] io_setup() attempt 2.
[MY-012583] [InnoDB] io_setup() attempt 3.
[MY-012583] [InnoDB] io_setup() attempt 4.
[MY-012583] [InnoDB] io_setup() attempt 5.

For "MySQL 8.0.44 --suite=rpl --parallel=64" the highest observed values are:
aio-nr=190086 processes=2780

We also found that setting default values from MySQL 8.0.44 to MySQL 8.4.6 improves both testing speed and resource usage:
For "mtr-parallel --suite=rpl --parallel=64 --mysqld=--innodb_read_io_threads=4 --mysqld=--innodb_parallel_read_threads=4" the highest observed values are:
aio-nr=369059 processes=2447

How to repeat:
Start MySQL 8.4.6 with:
mysql-test.run.pl --suite=rpl --parallel=64
Monitor aio-nr:
while true; cat /proc/sys/fs/aio-nr; sleep 3; done

Suggested fix:
I know that we can increase "aio-max-nr" in OS but the default value of innodb_read_io_threads=(available logical processors / 2) is too big for mysql-test.run.pl. MTR works faster when innodb_read_io_threads is set to much lower values. 
I also tested `MySQL 9.4.0 --suite=rpl --parallel=64`:
innodb_read_io_threads=64 -> 975008
innodb_read_io_threads=16 -> 917168
innodb_read_io_threads=8 -> 553385
innodb_read_io_threads=4 -> 379048
It seems that innodb_read_io_threads=4 or innodb_read_io_threads=8 would be the best max value for innodb_read_io_threads for mysql-test.run.pl