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.

  • 1
    Also beware of short words. For example, matching against +a* ... would be slow. Please provide the entire SELECT; there could be other issues (such as inefficient JOINs). What is the setting of innodb_buffer_pool_size? – Rick James Dec 9 '19 at 16:19
  • innodb_buffer_pool_size = 32G. My FT table's file size is 185MB. – Codemonkey Dec 9 '19 at 17:03
  • 32G will be extremely slow on your 14.5GB VM. Drop the setting to 10G – Rick James Dec 9 '19 at 17:06
  • I have a 128GB dedicated server. Where did you get "14.5GB VM" from...? – Codemonkey Dec 9 '19 at 21:25
  • Hmmm; I guess I was mixing up two Questions. – Rick James Dec 10 '19 at 6:21

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.

  • It is indeed much less, less than 200MB. – Codemonkey Dec 10 '19 at 8:19
  • You could probably get away with a 2GB VM and not pay for 128GB. (Or are there other apps on the same machine?) @Codemonkey – Rick James Dec 10 '19 at 18:35
  • My traffic is very spiky and I need a lot of storage space. I prefer to have an excess of resources rather than worry about saving a small amount of cash. The server is actually pretty cheap considering that it's essential to my business - €140/month – Codemonkey Dec 11 '19 at 9:11
  • @Codemonkey - Available RAM should be given to innodb_buffer_pool_size. But there is no benefit of going much beyond the peak (present or future) dataset size. "Spiky traffic" probably benefits from more cpu cores; check Max_used_connections. Even so, that number is probably bigger than how many cores actually get used. – Rick James Dec 11 '19 at 16:24

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.