準備
$ 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) 何を減らしてもメモリ使用量が増えてる気がする
0 件のコメント :
コメントを投稿