2

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?

Federico Razzoli
  • 1,663
  • 8
  • 24
Yodavish
  • 21
  • 1
  • 5

1 Answers1

0
  • 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?

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • 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 '20 at 18:48
  • I was reading some other post on my situation. And came across this one page: https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size 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 '20 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 '20 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 '20 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 02 '20 at 14:47
  • @Yodavish - my.ini is not changed by the code. Only you can change that file. And the file is read when mysqld (the server side of MariaDB) starts up. Do two steps: Modify my.ini so that it will have the desired value after the next restart, and do the SET so that it will be changed until the next restart. – Rick James Nov 02 '20 at 16:26
  • Thanks, I'll do that next – Yodavish Nov 02 '20 at 16:39