MySQL 9.6.0とMySQL 9.7.0-er (Source build vs Built binary)の測り比べ

MySQL 9.7.0-erではPGO(Profile Guided Optimization)が有効になっているらしいので、それまでのバイナリよりもちょっと性能が良さそう。あと、これはビルドの中で有効にするので手でビルドしたものよりもちょっと性能が良いはず。

• MySQL コミュニティ版のEarly Access リリースビルド | mysql-jp

というわけで雑ベンチ。

$ cat /etc/os-release

NAME="Oracle Linux Server"

VERSION="8.10"

ID="ol"

ID_LIKE="fedora"

VARIANT="Server"

VARIANT_ID="server"

VERSION_ID="8.10"

PLATFORM_ID="platform:el8"

PRETTY_NAME="Oracle Linux Server 8.10"

ANSI_COLOR="0;31"

CPE_NAME="cpe:/o:oracle:linux:8:10:server"

HOME_URL="https://linux.oracle.com/"

BUG_REPORT_URL="https://github.com/oracle/oracle-linux"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8"

ORACLE_BUGZILLA_PRODUCT_VERSION=8.10

ORACLE_SUPPORT_PRODUCT="Oracle Linux"

ORACLE_SUPPORT_PRODUCT_VERSION=8.10

$ nproc
4

$ free -h
              total        used        free      shared  buff/cache   available
Mem:           15Gi       2.2Gi       376Mi       682Mi        12Gi        12Gi
Swap:         4.0Gi       318Mi       3.7Gi

MySQL 9.6.0のビルド済みバイナリ。 innodb_dedicated_server に設定は任せてしまうことにした。

$ wget https://dev.mysql.com/get/Downloads/MySQL-9.6/mysql-9.6.0-linux-glibc2.28-x86_64.tar.xz
$ tar xf mysql-9.6.0-linux-glibc2.28-x86_64.tar.xz

$ cd mysql-9.6.0-linux-glibc2.28-x86_64/
$ bin/mysqld --no-defaults --initialize-insecure --lower_case_table_names=1
$ bin/mysqld --no-defaults --lower_case_table_names=1 --innodb-dedicated-server=1 --log-error-verbosity=3 --innodb-monitor-enable=all --daemonize

ベンチマーククライアント側は雑にサーバー側の倍のスレッドになるように。100万行10テーブルでサーバ側のサイズは2.4GB。

$ nproc
8

$ free -h
               total        used        free      shared  buff/cache   available
Mem:           3.4Gi       674Mi       798Mi       2.0Mi       2.2Gi       2.7Gi
Swap:          3.4Gi       137Mi       3.2Gi

$ sudo dnf install --enablerepo="*" -y sysbench

$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=60 --report-interval=1 prepare
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 1000000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 1000000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 1000000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 1000000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 1000000 records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 1000000 records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 1000000 records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 1000000 records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 1000000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'...

1スレッド9.6

$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 1s ] thds: 1 tps: 127.89 qps: 2572.72 (r/w/o: 1804.40/511.55/256.77) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 133.06 qps: 2664.15 (r/w/o: 1862.81/535.23/266.12) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 123.00 qps: 2458.00 (r/w/o: 1722.00/490.00/246.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1 tps: 128.00 qps: 2562.96 (r/w/o: 1791.97/514.99/256.00) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 1 tps: 137.00 qps: 2736.04 (r/w/o: 1918.03/544.01/274.00) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1 tps: 142.00 qps: 2840.00 (r/w/o: 1988.00/568.00/284.00) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 1 tps: 144.00 qps: 2880.03 (r/w/o: 2016.02/576.01/288.00) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1 tps: 150.00 qps: 2990.96 (r/w/o: 2090.97/599.99/300.00) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 1 tps: 150.00 qps: 3013.02 (r/w/o: 2109.01/604.00/300.00) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1 tps: 153.00 qps: 3058.97 (r/w/o: 2141.98/610.99/306.00) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00

..
[ 298s ] thds: 1 tps: 199.00 qps: 3976.96 (r/w/o: 2782.97/795.99/398.00) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 1 tps: 197.00 qps: 3933.99 (r/w/o: 2751.99/788.00/394.00) lat (ms,95%): 5.77 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 1 tps: 199.00 qps: 3991.97 (r/w/o: 2797.98/795.99/398.00) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            784952
        write:                           224272
        other:                           112136
        total:                           1121360
    transactions:                        56068  (186.89 per sec.)
    queries:                             1121360 (3737.81 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0038s
    total number of events:              56068

Latency (ms):
         min:                                    4.42
         avg:                                    5.35
         max:                                   36.29
         95th percentile:                        6.79
         sum:                               299950.61

Threads fairness:
    events (avg/stddev):           56068.0000/0.00
    execution time (avg/stddev):   299.9506/0.00

起動しているmysqldを公式ビルドの9.6から9.7に取り換え。

$ pkill mysqld

$ bin/mysqld --no-defaults --initialize-insecure --lower_case_table_names=1
$ bin/mysqld --no-defaults --lower_case_table_names=1 --innodb-dedicated-server=1 --log-error-verbosity=3 --innodb-monitor-enable=all --daemonize

$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=60 --report-interval=1 prepare

$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 1s ] thds: 1 tps: 136.87 qps: 2752.47 (r/w/o: 1930.22/547.50/274.75) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 129.06 qps: 2581.26 (r/w/o: 1806.88/516.25/258.13) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 126.00 qps: 2523.99 (r/w/o: 1764.00/508.00/252.00) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1 tps: 139.00 qps: 2775.96 (r/w/o: 1945.97/551.99/278.00) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 1 tps: 148.00 qps: 2964.02 (r/w/o: 2072.01/596.00/296.00) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1 tps: 154.00 qps: 3076.03 (r/w/o: 2156.02/612.01/308.00) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 1 tps: 159.00 qps: 3180.02 (r/w/o: 2226.01/636.00/318.00) lat (ms,95%): 7.84 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1 tps: 156.00 qps: 3123.00 (r/w/o: 2184.00/627.00/312.00) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 1 tps: 161.00 qps: 3220.98 (r/w/o: 2253.98/645.00/322.00) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1 tps: 163.00 qps: 3256.00 (r/w/o: 2282.00/648.00/326.00) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00

..
[ 295s ] thds: 1 tps: 214.00 qps: 4280.01 (r/w/o: 2996.01/856.00/428.00) lat (ms,95%): 5.09 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 1 tps: 212.99 qps: 4259.88 (r/w/o: 2981.92/851.98/425.99) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 1 tps: 213.01 qps: 4260.10 (r/w/o: 2982.07/852.02/426.01) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 1 tps: 212.00 qps: 4239.96 (r/w/o: 2967.97/847.99/424.00) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 1 tps: 215.00 qps: 4300.02 (r/w/o: 3010.01/860.00/430.00) lat (ms,95%): 5.18 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 1 tps: 210.00 qps: 4200.05 (r/w/o: 2940.04/840.01/420.01) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            848134
        write:                           242324
        other:                           121162
        total:                           1211620
    transactions:                        60581  (201.93 per sec.)
    queries:                             1211620 (4038.70 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0021s
    total number of events:              60581

Latency (ms):
         min:                                    3.99
         avg:                                    4.95
         max:                                   41.05
         95th percentile:                        6.55
         sum:                               299950.65

Threads fairness:
    events (avg/stddev):           60581.0000/0.00
    execution time (avg/stddev):   299.9506/0.00

おおちょっと良さそう。抜粋。

	9.6.0	9.7.0-er
min	4.42	3.99
avg	5.355	4.95
max	36.29	41.05
95%ile	6.79	6.55
QPS	3737.81	4038.70

95%ileで3%くらいレイテンシーが減ってる。sysbenchでプロファイルして当ててる可能性はあるのでsysbenchでのスコアが余計に良く出る可能性はあるので、あとで tpcc-mysql でも当ててみたい（が、1行引いて云々…なワークロードだったら全体的にレイテンシ下がるかな？）

スレッド数8(MySQL側のスレッド数を超えさせる）でもやってみる。daemon起動し直すのが面倒な関係で9.7.0-erが先。

$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 cleanup
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 prepare

$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 --threads=8 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 8
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 1s ] thds: 8 tps: 557.63 qps: 11257.47 (r/w/o: 7892.72/2241.50/1123.25) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 8 tps: 728.16 qps: 14585.28 (r/w/o: 10213.30/2915.66/1456.33) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 8 tps: 816.00 qps: 16307.02 (r/w/o: 11412.01/3263.00/1632.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 8 tps: 817.99 qps: 16397.83 (r/w/o: 11470.88/3290.97/1635.98) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 8 tps: 717.99 qps: 14305.89 (r/w/o: 10021.92/2847.98/1435.99) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 8 tps: 869.01 qps: 17422.16 (r/w/o: 12192.11/3492.03/1738.02) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 8 tps: 914.97 qps: 18271.37 (r/w/o: 12790.56/3650.87/1829.94) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 8 tps: 917.03 qps: 18337.55 (r/w/o: 12835.38/3668.11/1834.05) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 8 tps: 925.00 qps: 18490.97 (r/w/o: 12939.98/3700.99/1850.00) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 8 tps: 921.00 qps: 18443.03 (r/w/o: 12915.02/3686.01/1842.00) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00

..
[ 295s ] thds: 8 tps: 1018.00 qps: 20369.91 (r/w/o: 14260.94/4071.98/2036.99) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 8 tps: 1004.99 qps: 20091.88 (r/w/o: 14064.91/4016.98/2009.99) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 8 tps: 987.99 qps: 19727.73 (r/w/o: 13806.81/3946.95/1973.97) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 8 tps: 1001.00 qps: 20052.10 (r/w/o: 14036.07/4012.02/2004.01) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 8 tps: 1010.01 qps: 20216.15 (r/w/o: 14156.10/4040.03/2020.01) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 8 tps: 1006.00 qps: 20080.93 (r/w/o: 14049.95/4018.99/2011.99) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            4088658
        write:                           1168188
        other:                           584094
        total:                           5840940
    transactions:                        292047 (973.46 per sec.)
    queries:                             5840940 (19469.20 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0089s
    total number of events:              292047

Latency (ms):
         min:                                    4.66
         avg:                                    8.22
         max:                                  117.24
         95th percentile:                       12.08
         sum:                              2399733.89

Threads fairness:
    events (avg/stddev):           36505.8750/71.89
    execution time (avg/stddev):   299.9667/0.00

同じく9.6.0にバイナリを取り換えて。

$ pkill mysqld

$ cd ../mysql-9.6.0-linux-glibc2.28-x86_64
$ bin/mysqld --no-defaults --lower_case_table_names=1 --innodb-dedicated-server=1 --log-error-verbosity=3 --innodb-monitor-enable=all --daemonize

$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 --threads=8 cleanup
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 --threads=8 prepare

$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 --threads=8 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 8
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 1s ] thds: 8 tps: 534.59 qps: 10793.69 (r/w/o: 7567.17/2149.34/1077.17) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 8 tps: 609.20 qps: 12222.01 (r/w/o: 8555.81/2447.80/1218.40) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 8 tps: 700.99 qps: 13986.84 (r/w/o: 9786.89/2797.97/1401.98) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 8 tps: 724.98 qps: 14506.62 (r/w/o: 10163.73/2892.92/1449.96) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 8 tps: 677.02 qps: 13543.40 (r/w/o: 9476.28/2713.08/1354.04) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 8 tps: 755.98 qps: 15115.67 (r/w/o: 10584.77/3018.93/1511.97) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 8 tps: 791.99 qps: 15830.89 (r/w/o: 11075.92/3170.98/1583.99) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 8 tps: 802.01 qps: 16039.30 (r/w/o: 11231.21/3204.06/1604.03) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 8 tps: 809.01 qps: 16229.17 (r/w/o: 11351.12/3260.04/1618.02) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 8 tps: 821.01 qps: 16401.14 (r/w/o: 11487.10/3272.03/1642.01) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00

..
[ 295s ] thds: 8 tps: 883.02 qps: 17675.43 (r/w/o: 12373.30/3534.09/1768.04) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 8 tps: 884.00 qps: 17699.00 (r/w/o: 12389.00/3541.00/1769.00) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 8 tps: 873.99 qps: 17499.79 (r/w/o: 12258.85/3492.96/1747.98) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 8 tps: 883.99 qps: 17660.87 (r/w/o: 12351.91/3540.97/1767.99) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 8 tps: 894.99 qps: 17912.73 (r/w/o: 12538.81/3584.95/1788.97) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 8 tps: 794.01 qps: 15863.25 (r/w/o: 11110.18/3164.05/1589.03) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            3589908
        write:                           1025688
        other:                           512844
        total:                           5128440
    transactions:                        256422 (854.72 per sec.)
    queries:                             5128440 (17094.31 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0082s
    total number of events:              256422

Latency (ms):
         min:                                    5.09
         avg:                                    9.36
         max:                                  132.09
         95th percentile:                       13.95
         sum:                              2399787.50

Threads fairness:
    events (avg/stddev):           32052.7500/66.58
    execution time (avg/stddev):   299.9734/0.00

スレッド数が上がっても同傾向だった。

	9.6.0	9.7.0-er
min	5.09	4.66
avg	9.36	8.22
max	132.09	117.24
95%ile	13.95	12.08
QPS	17094.31	19469.20

パッと見良さそう。

他には

• 今はバッファプールに全てデータが収まっているはずなので、収まりきらないサイズでの比較（これはI/Oが支配的になるのでどっちでも大して変わらなくなるんじゃないかなあ）
• sysbench oltp*以外のベンチマークスイート（1行引いて1行更新系のやつだったら同じ傾向を示すような気がする、OLAP系はもともと完走できる気がしないしどうしようかな…）
• もうちょっと真面目にチューニングして比較
• ビルドオプション揃えてPGOが効いてないだけのソースビルドとPGOが効いてるビルド済みバイナリの比較
  あたりが思いつくので誰か試してください。

​

InnoDB FULLTEXT KEYがメモリを食う件(未解決)

準備

$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_common --table_size=1000000 prepare

mysql80 30> SELECT format_bytes(@@innodb_buffer_pool_size);
+-----------------------------------------+
| format_bytes(@@innodb_buffer_pool_size) |
+-----------------------------------------+
| 64.00 MiB                               |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql80 30> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;

..
| performance_schema | performance_schema.memory                                   | 240987688 |
+--------------------+-------------------------------------------------------------+-----------+
248 rows in set (0.00 sec)

$ ll -h /usr/mysql/8.0.45/data/sbtest/sbtest1.ibd
-rw-r-----. 1 yoku0825 yoku0825 248M Mar  9 06:03 /usr/mysql/8.0.45/data/sbtest/sbtest1.ibd

まずはそのままrun

$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date
Mon Mar  9 05:59:57 GMT 2026
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 1s ] thds: 1 tps: 1166.75 qps: 7002.48 (r/w/o: 0.00/4667.99/2334.49) lat (ms,95%): 1.70 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 1155.29 qps: 6930.74 (r/w/o: 0.00/4620.16/2310.58) lat (ms,95%): 1.89 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 1182.97 qps: 7096.83 (r/w/o: 0.00/4731.89/2364.94) lat (ms,95%): 1.76 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1 tps: 1174.02 qps: 7046.14 (r/w/o: 0.00/4697.09/2349.05) lat (ms,95%): 1.82 err/s: 0.00 reconn/s: 0.00

..
[ 597s ] thds: 1 tps: 1167.86 qps: 7008.16 (r/w/o: 0.00/4672.44/2335.72) lat (ms,95%): 1.93 err/s: 0.00 reconn/s: 0.00
[ 598s ] thds: 1 tps: 1180.12 qps: 7082.71 (r/w/o: 0.00/4722.47/2360.24) lat (ms,95%): 1.86 err/s: 0.00 reconn/s: 0.00
[ 599s ] thds: 1 tps: 1194.96 qps: 7168.75 (r/w/o: 0.00/4778.84/2389.92) lat (ms,95%): 1.79 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           2791284
        other:                           1395642
        total:                           4186926
    transactions:                        697821 (1163.03 per sec.)
    queries:                             4186926 (6978.17 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.0019s
    total number of events:              697821

Latency (ms):
         min:                                    0.31
         avg:                                    0.86
         max:                                   61.44
         95th percentile:                        1.86
         sum:                               599120.68

Threads fairness:
    events (avg/stddev):           697821.0000/0.00
    execution time (avg/stddev):   599.1207/0.00

Mon Mar  9 06:09:57 GMT 2026

$ while true ; do ps -Ao pid,fname,rss | grep $(pidof mysqld) | tsadd | tee -a /tmp/mysql8045_rss.txt; sleep 1; done
..
[2026/03/09 05:59:55]  1025869 mysqld   451000
[2026/03/09 05:59:56]  1025869 mysqld   451260
[2026/03/09 05:59:57]  1025869 mysqld   454952
[2026/03/09 05:59:58]  1025869 mysqld   472896
[2026/03/09 05:59:59]  1025869 mysqld   474364
[2026/03/09 06:00:00]  1025869 mysqld   474364

..
[2026/03/09 06:09:50]  1025869 mysqld   479124
[2026/03/09 06:09:51]  1025869 mysqld   479124
[2026/03/09 06:09:53]  1025869 mysqld   479124
[2026/03/09 06:09:54]  1025869 mysqld   479124
[2026/03/09 06:09:55]  1025869 mysqld   479124
[2026/03/09 06:09:56]  1025869 mysqld   479124
[2026/03/09 06:09:57]  1025869 mysqld   479124
[2026/03/09 06:09:58]  1025869 mysqld   479124
[2026/03/09 06:09:59]  1025869 mysqld   479124
[2026/03/09 06:10:00]  1025869 mysqld   479124
[2026/03/09 06:10:01]  1025869 mysqld   479124
[2026/03/09 06:10:02]  1025869 mysqld   479036

次、これにそのままFULLTEXT INDEXを貼ってリスタート

mysql80 55> ALTER TABLE sbtest.sbtest1 ADD FULLTEXT KEY (c);
Query OK, 0 rows affected, 1 warning (1 min 37.83 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql80 55> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

mysql80 55> SELECT @@innodb_buffer_pool_load_at_startup;
+--------------------------------------+
| @@innodb_buffer_pool_load_at_startup |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql80 55> RESTART;
Query OK, 0 rows affected (0.01 sec)

で、sysbench run。1スレッドだというのにtpsが0になる瞬間が度々ある。。この時点で嫌な予感しかしない。。

$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date
Mon Mar  9 06:14:07 GMT 2026
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 1s ] thds: 1 tps: 415.17 qps: 2493.00 (r/w/o: 0.00/1661.67/831.33) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 375.14 qps: 2251.86 (r/w/o: 0.00/1501.57/750.29) lat (ms,95%): 5.18 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 375.97 qps: 2254.82 (r/w/o: 0.00/1502.88/751.94) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00

..
[ 69s ] thds: 1 tps: 309.01 qps: 1856.04 (r/w/o: 0.00/1238.03/618.01) lat (ms,95%): 6.67 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 1 tps: 330.00 qps: 1979.98 (r/w/o: 0.00/1319.99/659.99) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00
[ 71s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 72s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 73s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 74s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

..
[ 98s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 99s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 101s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 102s ] thds: 1 tps: 19.00 qps: 113.00 (r/w/o: 0.00/75.00/38.00) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 103s ] thds: 1 tps: 311.99 qps: 1868.95 (r/w/o: 0.00/1244.97/623.98) lat (ms,95%): 5.88 err/s: 0.00 reconn/s: 0.00
[ 104s ] thds: 1 tps: 353.97 qps: 2123.83 (r/w/o: 0.00/1415.89/707.94) lat (ms,95%): 5.67 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 1 tps: 360.04 qps: 2160.25 (r/w/o: 0.00/1440.17/720.08) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00

..
[ 647s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 648s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 649s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 650s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           208020
        other:                           104010
        total:                           312030
    transactions:                        52005  (79.93 per sec.)
    queries:                             312030 (479.56 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          650.6557s
    total number of events:              52005

Latency (ms):
         min:                                    0.46
         avg:                                   12.51
         max:                                58936.07
         95th percentile:                        7.17
         sum:                               650556.58

Threads fairness:
    events (avg/stddev):           52005.0000/0.00
    execution time (avg/stddev):   650.5566/0.00

Mon Mar  9 06:24:58 GMT 2026

$ while true ; do ps -Ao pid,fname,rss | grep $(pidof mysqld) | tsadd | tee -a /tmp/mysql8045_rss.txt; sleep 1; done
..
[2026/03/09 06:14:02]  1036381 mysqld   403772
[2026/03/09 06:14:03]  1036381 mysqld   403772
[2026/03/09 06:14:04]  1036381 mysqld   403772
[2026/03/09 06:14:05]  1036381 mysqld   403772
[2026/03/09 06:14:06]  1036381 mysqld   405136
[2026/03/09 06:14:07]  1036381 mysqld   405136
[2026/03/09 06:14:08]  1036381 mysqld   423324
[2026/03/09 06:14:09]  1036381 mysqld   468492
[2026/03/09 06:14:10]  1036381 mysqld   471908

..
[2026/03/09 06:24:53]  1036381 mysqld   562712
[2026/03/09 06:24:54]  1036381 mysqld   562712
[2026/03/09 06:24:55]  1036381 mysqld   562712
[2026/03/09 06:24:56]  1036381 mysqld   562712
[2026/03/09 06:24:57]  1036381 mysqld   562712
[2026/03/09 06:24:58]  1036381 mysqld   562712
[2026/03/09 06:24:59]  1036381 mysqld   562712
[2026/03/09 06:25:00]  1036381 mysqld   562712
[2026/03/09 06:25:01]  1036381 mysqld   562712
[2026/03/09 06:25:02]  1036381 mysqld   562712
[2026/03/09 06:25:03]  1036381 mysqld   562712
[2026/03/09 06:25:04]  1036381 mysqld   562712
[2026/03/09 06:25:05]  1036381 mysqld   562712
[2026/03/09 06:25:06]  1036381 mysqld   562712

遅いのは良いとして(InnoDB Fulltext Indexの仕組み上書き込みするファイルが莫大に増えるので)、 FULLTEXT INDEX を貼っただけで想像以上にQPSも安定しないしない場合に比べて100MB近くも余計にメモリを使っている。
(100MB弱というとインパクトは小さそうだが、バッファプールの1.5倍と考えると結構恐怖である)
なお oltp_write_only なので、実行されているのは下記の4パターンだけ。

# Time: 2026-03-09T06:23:59.708497-00:00
# User@Host: root[root] @ localhost []  Id:    12
# Query_time: 0.000088  Lock_time: 0.000001 Rows_sent: 0  Rows_examined: 1 Thread_id: 12 Errno: 0 Killed: 0 Bytes_received: 24 Bytes_sent: 52 Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2026-03-09T06:23:59.708409-00:00 End: 2026-03-09T06:23:59.708497-00:00
SET timestamp=1773037439;
UPDATE sbtest1 SET k=k+1 WHERE id=504014;
# Time: 2026-03-09T06:23:59.708599-00:00
# User@Host: root[root] @ localhost []  Id:    12
# Query_time: 0.000086  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 1 Thread_id: 12 Errno: 0 Killed: 0 Bytes_received: 144 Bytes_sent: 52 Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2026-03-09T06:23:59.708513-00:00 End: 2026-03-09T06:23:59.708599-00:00
SET timestamp=1773037439;
UPDATE sbtest1 SET c='88212461572-85833769425-67430101277-82161871917-42390199524-62707776009-18076322187-41876970240-69115335595-77733484768' WHERE id=499195;
# Time: 2026-03-09T06:23:59.709583-00:00
# User@Host: root[root] @ localhost []  Id:    12
# Query_time: 0.000965  Lock_time: 0.000001 Rows_sent: 0  Rows_examined: 1 Thread_id: 12 Errno: 0 Killed: 0 Bytes_received: 24 Bytes_sent: 11 Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2026-03-09T06:23:59.708618-00:00 End: 2026-03-09T06:23:59.709583-00:00
SET timestamp=1773037439;
DELETE FROM sbtest1 WHERE id=526199;
# Time: 2026-03-09T06:23:59.709686-00:00
# User@Host: root[root] @ localhost []  Id:    12
# Query_time: 0.000080  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0 Thread_id: 12 Errno: 0 Killed: 0 Bytes_received: 212 Bytes_sent: 14 Read_first: 0 Read_last: 0 Read_key: 0 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2026-03-09T06:23:59.709606-00:00 End: 2026-03-09T06:23:59.709686-00:00
SET timestamp=1773037439;
INSERT INTO sbtest1 (id, k, c, pad) VALUES (526199, 499060, '94977596404-90535763498-48349239206-18885582465-18756331065-84602218017-85305678009-81368039801-39635475206-86387067089', '54494238140-99187086479-72783598041-89209528431-78795269286');

さて、このメモリの増分をパラメータだけで何とかしたい。
取り敢えず一度取っ散らかった（はずの）Fulltext Indexを一度OPTIMIZEで綺麗にしてRESTART。

mysql80 12> OPTIMIZE TABLE sbtest.sbtest1;

+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| sbtest.sbtest1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| sbtest.sbtest1 | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 hour 27 min 51.56 sec)

mysql80 12> RESTART;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> SHOW GLOBAL VARIABLES LIKE 'innodb\_ft%\_size';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| innodb_ft_cache_size       | 8000000   |
| innodb_ft_max_token_size   | 84        |
| innodb_ft_min_token_size   | 3         |
| innodb_ft_total_cache_size | 640000000 |
+----------------------------+-----------+
4 rows in set (0.00 sec)

innodb_ft_total_cache_size で抑えられるかなと思って変更しようとしたらread_onlyだった。

mysql80 12> SET GLOBAL innodb_ft_total_cache_size = 32000000;
ERROR 1238 (HY000): Variable 'innodb_ft_total_cache_size' is a read only variable

mysql80 12> SET PERSIST_ONLY innodb_ft_total_cache_size = 32000000;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> RESTART;
Query OK, 0 rows affected (0.00 sec)

PERSIST_ONLY が効くならSQLだけで済むのは良い時代になったものだ…

mysql80 12> SHOW GLOBAL VARIABLES LIKE 'innodb\_ft%\_size';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    12
Current database: *** NONE ***

+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| innodb_ft_cache_size       | 8000000  |
| innodb_ft_max_token_size   | 84       |
| innodb_ft_min_token_size   | 3        |
| innodb_ft_total_cache_size | 32000000 |
+----------------------------+----------+
4 rows in set (0.01 sec)

これでsysbench run

$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date
Mon Mar  9 08:12:30 GMT 2026
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 1s ] thds: 1 tps: 801.57 qps: 4810.42 (r/w/o: 0.00/3206.28/1604.14) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 701.09 qps: 4210.56 (r/w/o: 0.00/2808.37/1402.19) lat (ms,95%): 3.13 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 660.27 qps: 3958.63 (r/w/o: 0.00/2638.09/1320.54) lat (ms,95%): 3.19 err/s: 0.00 reconn/s: 0.00

..
[ 655s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 656s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 657s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           355052
        other:                           177526
        total:                           532578
    transactions:                        88763  (134.99 per sec.)
    queries:                             532578 (809.93 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          657.5574s
    total number of events:              88763

Latency (ms):
         min:                                    0.47
         avg:                                    7.41
         max:                                62161.87
         95th percentile:                        4.74
         sum:                               657402.59

Threads fairness:
    events (avg/stddev):           88763.0000/0.00
    execution time (avg/stddev):   657.4026/0.00

Mon Mar  9 08:23:28 GMT 2026

[2026/03/09 08:12:25]  1115433 mysqld   411844
[2026/03/09 08:12:26]  1115433 mysqld   411844
[2026/03/09 08:12:27]  1115433 mysqld   411844
[2026/03/09 08:12:28]  1115433 mysqld   411844
[2026/03/09 08:12:29]  1115433 mysqld   411844
[2026/03/09 08:12:30]  1115433 mysqld   411844
[2026/03/09 08:12:31]  1115433 mysqld   472512
[2026/03/09 08:12:32]  1115433 mysqld   485548
[2026/03/09 08:12:33]  1115433 mysqld   491852
[2026/03/09 08:12:34]  1115433 mysqld   497860
[2026/03/09 08:12:35]  1115433 mysqld   503836
[2026/03/09 08:12:36]  1115433 mysqld   509548
[2026/03/09 08:12:37]  1115433 mysqld   514148
[2026/03/09 08:12:38]  1115433 mysqld   520156
[2026/03/09 08:12:39]  1115433 mysqld   525948

..
[2026/03/09 08:23:23]  1115433 mysqld   658016
[2026/03/09 08:23:24]  1115433 mysqld   658016
[2026/03/09 08:23:25]  1115433 mysqld   658016
[2026/03/09 08:23:26]  1115433 mysqld   658016
[2026/03/09 08:23:27]  1115433 mysqld   658016
[2026/03/09 08:23:28]  1115433 mysqld   658016
[2026/03/09 08:23:29]  1115433 mysqld   658016
[2026/03/09 08:23:30]  1115433 mysqld   658016
[2026/03/09 08:23:31]  1115433 mysqld   658016
[2026/03/09 08:23:32]  1115433 mysqld   658016
[2026/03/09 08:23:33]  1115433 mysqld   658016

sysbenchのtpsが0に張り付く時間が増えた…(でもスループットは上がってるみたいなんだよな)安定しなくなる上にメモリ使用量もむしろ増えているのでボツ。

mysql80 59> RESET PERSIST innodb_ft_total_cache_size;
Query OK, 0 rows affected (0.00 sec)

mysql80 59> OPTIMIZE TABLE sbtest.sbtest1;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| sbtest.sbtest1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| sbtest.sbtest1 | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 hour 27 min 55.98 sec)

mysql80 59> RESTART;

Query OK, 0 rows affected (0.00 sec)

次は innodb_ft_cache_size …と思ったらコイツも再起動が必要。

mysql80 12> SET PERSIST_ONLY innodb_ft_cache_size = 1600000;
Query OK, 0 rows affected (0.01 sec)

mysql80 12> RESTART;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> SHOW GLOBAL VARIABLES LIKE 'innodb\_ft%\_size';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| innodb_ft_cache_size       | 1600000   |
| innodb_ft_max_token_size   | 84        |
| innodb_ft_min_token_size   | 3         |
| innodb_ft_total_cache_size | 640000000 |
+----------------------------+-----------+
4 rows in set (0.01 sec)

で、sysbench run

$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date
Tue Mar 10 01:28:36 GMT 2026
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 1s ] thds: 1 tps: 775.59 qps: 4653.52 (r/w/o: 0.00/3102.35/1551.17) lat (ms,95%): 2.43 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 556.13 qps: 3338.79 (r/w/o: 0.00/2225.53/1113.26) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 527.97 qps: 3167.83 (r/w/o: 0.00/2111.89/1055.94) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00

...
[ 597s ] thds: 1 tps: 456.97 qps: 2745.84 (r/w/o: 0.00/1831.90/913.95) lat (ms,95%): 4.57 err/s: 0.00 reconn/s: 0.00
[ 598s ] thds: 1 tps: 445.99 qps: 2675.94 (r/w/o: 0.00/1783.96/891.98) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00
[ 599s ] thds: 1 tps: 457.00 qps: 2741.03 (r/w/o: 0.00/1827.02/914.01) lat (ms,95%): 4.82 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           268980
        other:                           134490
        total:                           403470
    transactions:                        67245  (112.07 per sec.)
    queries:                             403470 (672.45 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.0019s
    total number of events:              67245

Latency (ms):
         min:                                    0.48
         avg:                                    8.92
         max:                                56495.87
         95th percentile:                        4.57
         sum:                               599877.71

Threads fairness:
    events (avg/stddev):           67245.0000/0.00
    execution time (avg/stddev):   599.8777/0.00

Tue Mar 10 01:38:36 GMT 2026

[2026/03/10 01:28:33]  1825300 mysqld   407444
[2026/03/10 01:28:34]  1825300 mysqld   407444
[2026/03/10 01:28:35]  1825300 mysqld   407444
[2026/03/10 01:28:36]  1825300 mysqld   407708
[2026/03/10 01:28:37]  1825300 mysqld   471732
[2026/03/10 01:28:38]  1825300 mysqld   479340
[2026/03/10 01:28:39]  1825300 mysqld   484292

..
[2026/03/10 01:38:31]  1825300 mysqld   597808
[2026/03/10 01:38:32]  1825300 mysqld   597808
[2026/03/10 01:38:33]  1825300 mysqld   597808
[2026/03/10 01:38:34]  1825300 mysqld   597808
[2026/03/10 01:38:35]  1825300 mysqld   597808
[2026/03/10 01:38:36]  1825300 mysqld   597808
[2026/03/10 01:38:37]  1825300 mysqld   597808
[2026/03/10 01:38:38]  1825300 mysqld   597808
[2026/03/10 01:38:39]  1825300 mysqld   597808
[2026/03/10 01:38:40]  1825300 mysqld   597808

これも効果なさそう。

mysql80 995> OPTIMIZE TABLE sbtest.sbtest1;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| sbtest.sbtest1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| sbtest.sbtest1 | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 hour 26 min 29.82 sec)

mysql80 995> RESET PERSIST innodb_ft_cache_size ;
Query OK, 0 rows affected (0.00 sec)

mysql80 995> RESTART;
Query OK, 0 rows affected (0.00 sec)

流石に innodb_ft_result_cache_limit は効果ないだろうと思いつつ一応（だって全文検索していないわけだし）

mysql80 12> SET GLOBAL innodb_ft_result_cache_limit = 1000000;
Query OK, 0 rows affected (0.00 sec)

$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date
Tue Mar 10 08:24:37 GMT 2026
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 1s ] thds: 1 tps: 785.45 qps: 4713.68 (r/w/o: 0.00/3141.79/1571.89) lat (ms,95%): 2.39 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 663.15 qps: 3979.93 (r/w/o: 0.00/2653.62/1326.31) lat (ms,95%): 3.25 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 631.00 qps: 3788.98 (r/w/o: 0.00/2526.99/1261.99) lat (ms,95%): 3.30 err/s: 0.00 reconn/s: 0.00

..
[ 597s ] thds: 1 tps: 372.00 qps: 2230.00 (r/w/o: 0.00/1486.00/744.00) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00
[ 598s ] thds: 1 tps: 364.99 qps: 2193.92 (r/w/o: 0.00/1463.95/729.97) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00
[ 599s ] thds: 1 tps: 368.02 qps: 2208.11 (r/w/o: 0.00/1472.07/736.04) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           342848
        other:                           171424
        total:                           514272
    transactions:                        85712  (142.85 per sec.)
    queries:                             514272 (857.12 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.0019s
    total number of events:              85712

Latency (ms):
         min:                                    0.49
         avg:                                    7.00
         max:                                61040.07
         95th percentile:                        4.82
         sum:                               599845.92

Threads fairness:
    events (avg/stddev):           85712.0000/0.00
    execution time (avg/stddev):   599.8459/0.00

Tue Mar 10 08:34:37 GMT 2026

[2026/03/10 08:24:32]  2106989 mysqld   409968
[2026/03/10 08:24:33]  2106989 mysqld   409968
[2026/03/10 08:24:34]  2106989 mysqld   409968
[2026/03/10 08:24:35]  2106989 mysqld   409968
[2026/03/10 08:24:36]  2106989 mysqld   409968
[2026/03/10 08:24:37]  2106989 mysqld   466576
[2026/03/10 08:24:38]  2106989 mysqld   482332
[2026/03/10 08:24:39]  2106989 mysqld   488388
[2026/03/10 08:24:40]  2106989 mysqld   494672
[2026/03/10 08:24:41]  2106989 mysqld   500664
[2026/03/10 08:24:42]  2106989 mysqld   506408
[2026/03/10 08:24:44]  2106989 mysqld   512168

..
[2026/03/10 08:34:33]  2106989 mysqld   639492                                                                                        
[2026/03/10 08:34:34]  2106989 mysqld   639492
[2026/03/10 08:34:35]  2106989 mysqld   639492
[2026/03/10 08:34:36]  2106989 mysqld   639492
[2026/03/10 08:34:37]  2106989 mysqld   639492
[2026/03/10 08:34:38]  2106989 mysqld   639492
[2026/03/10 08:34:39]  2106989 mysqld   639492
[2026/03/10 08:34:40]  2106989 mysqld   639492

Σ(ﾟдﾟlll) 何を減らしてもメモリ使用量が増えてる気がする

​