How to speed up full text query on a table with 4 million rows? (MariaDB)

Question

I have an InnoDB full-text table that serves the Ajax-powered search box at the top of my website. I generate it with a daily script that pulls data from a dozen entity tables on the site and amalgamates them all into one FT table for searching.

To give users the best experience (IMHO) I take whatever their input is, clean certain characters out of it (all full-text modifiers, for example), and then prepend every term with + and append them all with *.

So a search for "stack overflow" becomes +stack* +overflow*

The column that I'm searching on the FT table is small, with a typical character length of 30 characters. Event names, people's names, geographical locations, that sort of thing. Not huge passages of text.

It works, but queries take on the order of 1 second to be returned.

EDIT: just after posting I've rebuilt the index and it's down to 0.4 seconds now - but I'd still like to improve it, if possible.

How could I change that to 0.1 seconds, or is that a pipe dream?

My server is a dual Xeon with 16 cores/32 threads and 128GB of memory. I serve a million pages or so each month, and rarely see server load above 1-2, with plenty of spare memory.

I wonder if I can somehow force this table to reside permanently in memory (rebuilding it after a server reboot or MySQL restart only takes 30 seconds or so), and if that would help? Or maybe MySQL is already holding it in memory - how can I check?

I'm happy with the query itself, I don't think there's much that I can improve about it, but I know very little about how to maximize server potential through configuration.

FWIW SELECT VERSION() gives me 10.3.20-MariaDB-log.

Answer 1

How big is the dataset? SHOW TABLE STATUS.

It sounds like it is much less than innodb_buffer_pool_size, so it will probably reside in RAM once it is loaded.

There is no automatic way to get it loaded. You could write a script to access every row in the data and every index. But that is tricky and slow.

On the other hand, you can keep it loaded (at least across graceful shutdowns) by turning ON these: innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_shutdown.

Answer 2

I wonder if I can somehow force this table to reside permanently in memory (rebuilding it after a server reboot or mysql restart only takes 30 seconds or so), and if that would help? Or maybe MySQL is already holding it in memory - how can I check?

I believe there is an easy way - a hack - that will allow you to load the entire table into memory assuming you have a large enough innodb_buffer_pool_size system variable, that is: Simply by dumping the table to a file using a backup tool such as mysqldump (but not mariabackup or xtrabackup), all the table data will be loaded into memory. You don't have to dump it to an actual file - /dev/null should be sufficient:

mysqldump the_database the_table > /dev/null

As for verifying that the whole table is loaded into memory, I'm not sure about just for the one table, but you can calculate the total InnoDB buffer pool hit ratio for all InnoDB tables. First get these values:

SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';

Then the InnoDB Buffer Pool hit percentage is given by: Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

According to this FromDual article:

A InnoDB Buffer Pool hit ratio below 99.9% is a weak indicator that your InnoDB Buffer Pool could be increased.