Description:
As the title states, when inserting a lot of data into a table containing FTS in a single transaction, a lot of memory consumption will be generated.
How to repeat:
# ======================Step 0=========================== #
# Start MySQL server with default configuration.
# ====================================================== #
# ======================Step 1========================== #
# Create a table with millions of rows, I use sysbench here.
CREATE DATABASE sbtest;
# Run sysbench to generate data to sbtest1 table.
sysbench oltp_read_write --mysql-host=$HOST --mysql-port=$PORT --mysql-user=$USER --mysql-password=$PASSWORD --tables=1 --threads=1 --table_size=4000000 --mysql-db=sbtest prepare
# the table structure is as follows:
```
CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
```
# ====================================================== #
# =======================Step 2========================= #
# Create FTS table and insert data to FTS table using 'INSERT INTO ... SELECT ...'
CREATE TABLE `t_fts` (
`id` int NOT NULL,
`c` char(120) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO t_fts (id, c) SELECT id, c FROM sbtest1;
# ====================================================== #
# =======================Step 3========================= #
# Check the memory usage. In my tests, I found that the peak RSS can reach 10.4GB!
It seems that parameters innodb_ft_cache_size and innodb_ft_total_cache_size do not limit the memory, and sometimes the accumulated memory cannot be released in time.
# ====================================================== #
Suggested fix:
I introduced jemalloc to analyze the memory and found that the memory was mainly consumed in two aspects:
1. Insert: rbt_add_node->ut::malloc_withkey
2. Commit: ib_vector_create->ib_heap_malloc
Maybe some memory can be freed early. ：）
