I'm using Maria DB, version 10.2.22, where one database column uses FULLTEXT for a broad document searching. However, I've ran into a "Table handler out of memory" on some searches. The table itself is only 4.4 GB. I've read on stackoverflow, that changing some of the InnoDB variables such as:

• innodb_buffer_pool_size

• innodb_ft_result_cache_limit

from their default value to say 4 GB could potential solve to the memory issue. My question is three parts, I suppose.

  1. Are there any other variables I should consider changing.

• innodb_buffer_pool_instances
• innodb_ft_cache_size
• innodb_ft_total_cache_size

  1. Because this DB is fairly critical to run, after running the command lines to change the variables would I need to stop and start the MariaDB service to fetch these changes?

  2. If, restarting MariaDB services is needed, can anyone point me to a guide as far as how to safely change these variables?

1 Answer 1

  • innodb_buffer_pool_size -- This should be set to about 70% of RAM. But, if the other settings encroach too much into this, then lower it. Swapping is bad.
  • innodb_buffer_pool_instances -- Simply set it to innodb_buffer_pool_size / 1G -- rounded down, but at least 1
  • innodb_ft_cache_size -- big enough for at least one row of text being indexed via FULLTEXT. Bigger if practical, but don't squeeze out the buffer_pool
  • innodb_ft_total_cache_size -- If you are [re]building FULLTEXT in only one thread at a time, this is not relevant.
  • innodb_ft_result_cache_limit -- This is relevant only to SELECTs that involve a FULLTEXT index. The max is 4G, but don't set it that large unless you have lots of RAM. Start with, say, 1% of RAM; if you get an error, then raise it.

The settings are not dynamic, so change the config file and restart mysqld.

How much RAM do you have? What is a typical and the max size of the column(s) in the fulltext index?

  • Thanks for the response Rick James. The server has 24 GB for memory. When I did a Max(char_length([column])) the return value was 461. The avg(char_length([column])) is 181.3428. I hope that answers the question?
    – Yodavish
    Oct 29, 2020 at 18:48
  • I was reading some other post on my situation. And came across this one page: dba.stackexchange.com/questions/27328/… they provided to queries one for reccomended innodb buffer pool size and the other is GB in use. I get a 238 RIBPS and 2.88 GB, this is without setting the new RIBPS yet. Is that information still relevant today?
    – Yodavish
    Oct 29, 2020 at 20:51
  • @Yodavish - The buffer_pool_size must be less than RAM size, else there is a serious risk of swapping, which is terrible for performance. So, maybe 18GB for your 24GB machine.
    – Rick James
    Oct 29, 2020 at 21:45
  • @Yodavish - Max of 461 -- I think this means that innodb_ft_result_cache_limit does not need to be very big. Perhaps 10% of RAM. Leave the others at defaults.
    – Rick James
    Oct 29, 2020 at 21:48
  • Since I'm running on version 10.2.22 the MariaDB documents says changing the innodb_buffer_pool_size is dynamic "Yes (>= MariaDB 10.2.2)". So I've tried changing it "SET GLOBAL innodb_buffer_pool_size= 4000000000;" and verifying the changes occured by "show variables like 'innodb_buffer_pool_size'" which returned innodb_buffer_pool_size | 4294967296. However, when I got to the my.ini file it still shows innodb_buffer_pool_size=3066M. Am I doing this wrong?
    – Yodavish
    Nov 2, 2020 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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