# Environment details.

# Hardware details.
$ cat /proc/cpuinfo  | grep proc | wc -l
8

$ cat /proc/meminfo  | grep MemTotal
MemTotal:       32860748 kB

# OS details.
$ cat /etc/debian_version 
12.9

$ uname -a
Linux bug117691 6.1.0-31-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.128-1 (2025-02-07) x86_64 GNU/Linux


################################################# 8.0.41

-- release/opt (binary tarball build - mysql-8.0.41-linux-glibc2.28-x86_64.tar.xz)

BugNumber=117691
rm -rf $BugNumber/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

-
bin/mysql -uroot -S/tmp/mysql.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.41 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

-- Follow steps from report

cat 1.sh

# Filling one table with data.
# Setting things for having a 3 GB table with 4 rows per 16 KB InnoDB Page.
# The pv are a trick to time command execution.
nb_rows=$((3*1024*1024*1024 / (16*1024) * 4))
{
  bin/mysql -uroot -S/tmp/mysql.sock <<< "
     CREATE DATABASE test_jfg;
     CREATE TABLE test_jfg.t (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)"

  seq 1 $nb_rows |
    awk '{print "(null)"}' |
    tr " " "," | paste -s -d "$(printf ',%.0s' {1..100})\n" |
    sed -e 's/.*/INSERT INTO t values &;/' |
    bin/mysql -uroot -S/tmp/mysql.sock test_jfg | pv -t

  { echo "ALTER TABLE t ADD COLUMN c0 CHAR(200) DEFAULT ''"
           seq -f " ADD COLUMN c%.0f CHAR(240) DEFAULT ''" 1 15
  } | paste -s -d "," | bin/mysql -uroot -S/tmp/mysql.sock test_jfg

  bin/mysql -uroot -S/tmp/mysql.sock test_jfg <<< "ALTER TABLE t FORCE"      | pv -t
  bin/mysql -uroot -S/tmp/mysql.sock test_jfg <<< "FLUSH TABLE t FOR EXPORT" | pv -t

  ls -lh 117691/test_jfg/t.ibd
}

# Below is the output of above.

bash 1.sh
0:00:37
0:05:56
0:00:00
-rw-r----- 1 umshastr common 4.7G Mar 25 13:54 117691/test_jfg/t.ibd



# Generating load.
while sleep 0.1; do bin/mysql -uroot -S/tmp/mysql.sock -N test_jfg <<< "SET @i = ROUND(RAND() * $nb_rows); SELECT * from t where id = @i;"; done

# Monitoring IOs (columns are: date, nb_ios, total_wait_usec, and avg_wait_usec).
sql4="select COUNT_STAR, SUM_TIMER_WAIT/1000/1000"; \
sql4="$sql4 from file_summary_by_event_name where EVENT_NAME = 'wait/io/file/innodb/innodb_data_file'"; \
while sleep 1; do date; bin/mysql -uroot -S/tmp/mysql.sock -N performance_schema <<< "$sql4"; done |
  stdbuf -oL paste -s -d " \n" | awk -W interactive '{
    aa=$7-a; bb=$8-b;
    a =$7;   b =$8;
    NF=6; print $0, aa, bb, bb/aa}' | tail -n +2

# Making sure nothing is in the page cache.
sudo bash -c "echo 3 > /proc/sys/vm/drop_caches"


Tue Mar 25 14:21:34 CET 2025 15 12535.2 835.679
Tue Mar 25 14:21:35 CET 2025 17 13285.4 781.494
Tue Mar 25 14:21:36 CET 2025 17 13611.3 800.664
Tue Mar 25 14:21:37 CET 2025 17 13826.2 813.307
Tue Mar 25 14:21:38 CET 2025 14 11207.5 800.537
Tue Mar 25 14:21:39 CET 2025 12 9311.75 775.979
Tue Mar 25 14:21:40 CET 2025 14 11501.4 821.525
Tue Mar 25 14:21:41 CET 2025 15 12761.9 850.79
Tue Mar 25 14:21:42 CET 2025 17 13334.5 784.381
Tue Mar 25 14:21:43 CET 2025 15 11952.3 796.82
Tue Mar 25 14:21:44 CET 2025 18 14288.6 793.809
Tue Mar 25 14:21:45 CET 2025 12 11051.5 920.956
Tue Mar 25 14:21:46 CET 2025 18 14385.1 799.171
Tue Mar 25 14:21:47 CET 2025 15 11988.1 799.203
Tue Mar 25 14:21:48 CET 2025 16 12895.9 805.996
Tue Mar 25 14:21:49 CET 2025 13 11295.6 868.889
Tue Mar 25 14:21:50 CET 2025 14 11367.7 811.981

# Loading the table in cache.
pv -etbr 117691/test_jfg/t.ibd > /dev/null
4.63GiB 0:01:18 [60.5MiB/s]


# When the table is in cache, we have fast IOs (~7-10 usec).

Tue Mar 25 14:24:49 CET 2025 12 121.183 10.0986
Tue Mar 25 14:24:50 CET 2025 11 102.979 9.36174
Tue Mar 25 14:24:51 CET 2025 17 159.828 9.40163
Tue Mar 25 14:24:52 CET 2025 11 85.1276 7.73887
Tue Mar 25 14:24:53 CET 2025 13 119.085 9.16039
Tue Mar 25 14:24:54 CET 2025 18 136.456 7.58091
Tue Mar 25 14:24:55 CET 2025 16 120.43 7.52689
Tue Mar 25 14:24:56 CET 2025 16 123.451 7.71572
Tue Mar 25 14:24:57 CET 2025 15 123.48 8.232
Tue Mar 25 14:24:58 CET 2025 15 137.66 9.17731
Tue Mar 25 14:24:59 CET 2025 16 141.735 8.85842
Tue Mar 25 14:25:00 CET 2025 15 135.251 9.01675
Tue Mar 25 14:25:01 CET 2025 16 187.234 11.7021
Tue Mar 25 14:25:02 CET 2025 14 118.534 8.4667
Tue Mar 25 14:25:03 CET 2025 17 122.848 7.22638


################################################# 8.4.4

-- release/opt (binary tarball build - mysql-8.4.4-linux-glibc2.28-x86_64.tar.xz)

BugNumber=117691
rm -rf $BugNumber/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

bin/mysql -uroot -S/tmp/mysql.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.4 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like 'innodb_flush_method';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| innodb_flush_method | O_DIRECT |
+---------------------+----------+
1 row in set (0.00 sec)

mysql>

--


bash 1.sh

[umshastr@bug117691:/export/home/tmp/ushastry/mysql-8.4.4]$ bash 1.sh
0:00:32
0:03:45
0:00:00
-rw-r----- 1 umshastr common 4.7G Mar 25 15:38 117691/test_jfg/t.ibd

# Generating load.
while sleep 0.1; do bin/mysql -uroot -S/tmp/mysql.sock -N test_jfg <<< "SET @i = ROUND(RAND() * $nb_rows); SELECT * from t where id = @i;"; done


# Making sure nothing is in the page cache.
sudo bash -c "echo 3 > /proc/sys/vm/drop_caches"


# Monitoring IOs (columns are: date, nb_ios, total_wait_usec, and avg_wait_usec).
sql4="select COUNT_STAR, SUM_TIMER_WAIT/1000/1000"; \
sql4="$sql4 from file_summary_by_event_name where EVENT_NAME = 'wait/io/file/innodb/innodb_data_file'"; \
while sleep 1; do date; ./use -N performance_schema <<< "$sql4"; done |
  stdbuf -oL paste -s -d " \n" | awk -W interactive '{
    aa=$7-a; bb=$8-b;
    a =$7;   b =$8;
    NF=6; print $0, aa, bb, bb/aa}' | tail -n +2

# Making sure nothing is in the page cache.
sudo bash -c "echo 3 > /proc/sys/vm/drop_caches"

# Without a cache, we have avg_wait_usec ~600-700 usec.

Tue Mar 25 15:44:34 CET 2025 12 7960.02 663.335
Tue Mar 25 15:44:35 CET 2025 15 8976.12 598.408
Tue Mar 25 15:44:36 CET 2025 17 10384.4 610.849
Tue Mar 25 15:44:37 CET 2025 12 8801.69 733.474
Tue Mar 25 15:44:38 CET 2025 12 8267.14 688.929
Tue Mar 25 15:44:39 CET 2025 17 10603.1 623.714
Tue Mar 25 15:44:40 CET 2025 14 8747.01 624.786


# Loading the table in cache.
pv -etbr 117691/test_jfg/t.ibd > /dev/null
4.63GiB 0:01:19 [59.6MiB/s]

# When repeating above with 8.4.4 with cached IOs

Tue Mar 25 15:47:22 CET 2025 16 11007.3 687.954
Tue Mar 25 15:47:23 CET 2025 14 9340.69 667.192
Tue Mar 25 15:47:24 CET 2025 16 11178.8 698.674
Tue Mar 25 15:47:25 CET 2025 14 9271.51 662.251
Tue Mar 25 15:47:26 CET 2025 16 11282.4 705.151
Tue Mar 25 15:47:27 CET 2025 15 10777.8 718.519
Tue Mar 25 15:47:28 CET 2025 14 9356.48 668.32
Tue Mar 25 15:47:29 CET 2025 15 9813.19 654.212
Tue Mar 25 15:47:30 CET 2025 18 13000.9 722.274
Tue Mar 25 15:47:31 CET 2025 15 10597.5 706.497
Tue Mar 25 15:47:32 CET 2025 16 11053.2 690.824
Tue Mar 25 15:47:33 CET 2025 17 11746.6 690.976
Tue Mar 25 15:47:34 CET 2025 16 10782.8 673.927
Tue Mar 25 15:47:35 CET 2025 15 10168.1 677.87
Tue Mar 25 15:47:36 CET 2025 16 11264.5 704.031


##### Trying with innodb_flush_method= fsync ( to match with 8.0's default)


    
BugNumber=117691
rm -rf $BugNumber/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 --innodb-flush-method=fsync 2>&1 &



 bin/mysql -uroot -S/tmp/mysql.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.4.4 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like 'innodb_flush_method';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_flush_method | fsync |
+---------------------+-------+
1 row in set (0.00 sec)

mysql>

--
# Below is the output of above 1.sh

0:00:31
0:03:46
0:00:00

-rw-r----- 1 umshastr common 4.7G Mar 25 15:58 117691/test_jfg/t.ibd




# When repeating above with 8.4.4

# Without a cache, we have avg_wait_usec ~600 usec.

Tue Mar 25 16:01:31 CET 2025 13 8299.31 638.408
Tue Mar 25 16:01:32 CET 2025 11 7178.65 652.605
Tue Mar 25 16:01:33 CET 2025 13 8681.35 667.796
Tue Mar 25 16:01:34 CET 2025 15 11388.8 759.251
Tue Mar 25 16:01:35 CET 2025 13 9458.64 727.587
Tue Mar 25 16:01:36 CET 2025 15 10556.5 703.769
Tue Mar 25 16:01:37 CET 2025 13 8501.82 653.987
Tue Mar 25 16:01:38 CET 2025 17 11398.5 670.497
Tue Mar 25 16:01:39 CET 2025 15 10996.1 733.074
Tue Mar 25 16:01:40 CET 2025 15 10505.6 700.371
Tue Mar 25 16:01:41 CET 2025 18 12467.1 692.615


# Loading the table in cache.

pv -etbr 117691/test_jfg/t.ibd > /dev/null
4.63GiB 0:01:19 [60.0MiB/s]



Tue Mar 25 16:10:07 CET 2025 13 87.9517 6.76552
Tue Mar 25 16:10:08 CET 2025 18 103.733 5.76295
Tue Mar 25 16:10:09 CET 2025 16 99.9117 6.24448
Tue Mar 25 16:10:10 CET 2025 16 89.2203 5.57627
Tue Mar 25 16:10:11 CET 2025 14 109.808 7.84345
Tue Mar 25 16:10:12 CET 2025 16 95.2137 5.95086
Tue Mar 25 16:10:13 CET 2025 12 80.852 6.73767
Tue Mar 25 16:10:14 CET 2025 14 86.3944 6.17103
Tue Mar 25 16:10:15 CET 2025 17 113.6 6.68236
Tue Mar 25 16:10:16 CET 2025 16 107.373 6.71081
Tue Mar 25 16:10:17 CET 2025 14 88.6674 6.33338

-

date; pv -etbr 117691/test_jfg/t.ibd > /dev/null
Tue Mar 25 16:04:59 CET 2025
4.63GiB 0:00:01 [4.03GiB/s]


##################################################### Conclusion
Conclusion from my tests - 8.4.4 with --innodb-flush-method=fsync is same as that of 8.0.41 i.e When the table is in cache, 8.4 and 8.0 have fast IOs