Changelog

[Q]

FullText Search sped up, with DB speeding up

...

So I was fiddling with our database for that issue.

When it comes down to search, this particular partial database query is what has to be optimized for search: MyBB source code.

To get that I worked with AI to determine a few more sensible settings on our database, in particular:

Code:

innodb_buffer_pool_size = 500M
#innodb_redo_log_capacity=200M
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_io_capacity_max=4000
innodb_ft_min_token_size=4
innodb_ft_max_token_size=84
innodb_ft_cache_size=16000000 # Per-table FULLTEXT index cache while creating/updating the index
innodb_ft_total_cache_size=160000000 # Global limit for FULLTEXT index cache memory
innodb_ft_sort_pll_degree=4 # Number of parallel tokenizer/sort threads when building FULLTEXT indexes
innodb_ft_result_cache_limit=200000000

Additionally I added a FULLTEXT index to the respective table (that ran yesterday for like half an hour, sorry for any inconvenience there).

Unfortunately this has actually made our search a bit slower ...


Yeah, we will eventually have to get a server with more RAM, than our current 1.9 GB RAM, since a large "innodb_buffer_pool_size" previously was very successful at producing search results within just a mere 3 seconds (beginning with the second query after server restart). Only, the server bogged up all of our precious little RAM within minutes there.

Speaking about RAM, I reconfigured our database server's Systemd service file, to use the OOMKiller (OOM=Linux Out-Of-Memory-Event) to restart the database server, should it reach it's RAM limit again.

After all this I am kind of happy, 'though we don't need to throw like an elasticsearch at our fulltext search issue, but simply having about 950 MB RAM dedicated just to the table of all the posts, with that innodb buffer pool size will be sufficient.