full-text-searchmariadbmatchmatch-againstagainst

Mariadb query runs out of memory with use of match() against()


I have a very large table (around 50 GB) but with few columns:

CREATE TABLE my_table (
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    id_sec BIGINT(20) NULL,
    full_name VARCHAR(100) NULL DEFAULT NULL,
    PRIMARY KEY (id),
    FULLTEXT INDEX fts_full_name (full_name)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

when I execute the following queries:

SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ' IN BOOLEAN MODE)

SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ')

SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ' IN BOOLEAN MODE) LIMIT 1000

SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ') LIMIT 1000

SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ' IN BOOLEAN MODE) LIMIT 100

SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ') LIMIT 100

Sometimes send me the following error message:

SQL Error (128): Table handler out of memory

But it does not happen when I execute:

SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('+lastname +middle +first ' IN BOOLEAN MODE)

SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('+lastname +middle +first ')

I have tried with the following values and there is no improvement:

--key-buffer-size=20M 
--tmp-table-size=2G 
--thread-pool-max-threads=10 
--max-connections=100 
--max-heap-table-size=2G 
--innodb-buffer-pool-size=4G

Does anyone know what could be happening? or how can I correct it?

My server is running in windows 10 (x64), 10.3.12-MariaDB (x64), 32 GB of RAM, core i7

Thanks.


Solution

  • Read about the 3 Variables below and experiment with them:

    | innodb_ft_cache_size            | 8000000    |
    | innodb_ft_result_cache_limit    | 2000000000 |
    | innodb_ft_total_cache_size      | 640000000  |
    

    If you don't have success, then file a bug report at bugs.mysql.com (and post the bug number here).