XF 2.3 Search failures - FTS query exceeds result cache limit

[david.steadson]

We're regularly getting errors when people do searches on our forum, running on a DigitalOcean managed database. We recently migrated from VB3.8. I've tried adjusting cache limits to no avail. One example of a query that crashes below. Googling indicatesthat complex queries with lots of Boolean (which this is) can cause this problem.

This is, in theory a relatively simply query, a single word on a single thread. Why such a complex query? Any idea on fixes? Have been increasing the cache size as per some online recommendations, but no help. Also rebuilt the index.

XF\Db\Exception: MySQL query error [188]: FTS query exceeds result cache limit src/XF/Db/AbstractStatement.php:230
Generated by: icerat Dec 24, 2024 at 1:07 PM
Stack trace
SELECT search_index.content_type, search_index.content_id
            FROM xf_search_index AS search_index
            
            WHERE MATCH(search_index.title, search_index.message, search_index.metadata) AGAINST ('+dayz +_md_thread_368075 -_md_node_17 -_md_node_29 -_md_node_32 -_md_node_33 -_md_node_36 -_md_node_44 -_md_node_45 -_md_node_51 -_md_node_54 -_md_node_59 -_md_node_61 -_md_node_66 -_md_node_74 -_md_node_94 -_md_node_95 -_md_node_103 -_md_hidden_1 +(_md_content_post _md_content_thread)' IN BOOLEAN MODE)
            
            ORDER BY search_index.item_date DESC
            LIMIT 100
------------

#0 src/XF/Db/Mysqli/Statement.php(207): XF\Db\AbstractStatement->getException('MySQL query err...', 188, 'HY000')
#1 src/XF/Db/Mysqli/Statement.php(83): XF\Db\Mysqli\Statement->getException('MySQL query err...', 188, 'HY000')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(162): XF\Db\AbstractAdapter->query('SELECT search_i...', Array)
#4 src/XF/Search/Source/MySqlFt.php(359): XF\Db\AbstractAdapter->fetchAllNum('SELECT search_i...')
#5 src/addons/SV/SearchImprovements/XF/Search/Source/XF22/MySqlFt.php(51): XF\Search\Source\MySqlFt->search('SELECT search_i...', 100)
#6 src/XF/Search/Search.php(337): SV\SearchImprovements\XF\Search\Source\XF22\MySqlFt->search(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), '100')
#7 src/XF/Search/Search.php(366): XF\Search\Search->XF\Search\{closure}(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), '100')
#8 src/XF/Search/Search.php(332): XF\Search\Search->executeSearch(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), '100', Object(Closure), true)
#9 src/XF/Repository/SearchRepository.php(37): XF\Search\Search->search(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery))
#10 src/addons/SV/SearchImprovements/XF/Repository/XF22/SearchPatch.php(45): XF\Repository\SearchRepository->runSearch(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), Array, true)
#11 src/XF/Pub/Controller/SearchController.php(562): SV\SearchImprovements\XF\Repository\XF22\SearchPatch->runSearch(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), Array, true)
#12 src/XF/Pub/Controller/SearchController.php(182): XF\Pub\Controller\SearchController->runSearch(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), Array)
#13 src/addons/SV/SearchImprovements/XF/Pub/Controller/Search.php(92): XF\Pub\Controller\SearchController->actionSearch()
#14 src/XF/Mvc/Dispatcher.php(362): SV\SearchImprovements\XF\Pub\Controller\Search->actionSearch(Object(XF\Mvc\ParameterBag))
#15 src/XF/Mvc/Dispatcher.php(264): XF\Mvc\Dispatcher->dispatchClass('XF:Search', 'Search', Object(XF\Mvc\RouteMatch), Object(SV\SearchImprovements\XF\Pub\Controller\SearchPatchFirst), NULL)
#16 src/XF/Mvc/Dispatcher.php(121): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(SV\SearchImprovements\XF\Pub\Controller\SearchPatchFirst), NULL)
#17 src/XF/Mvc/Dispatcher.php(63): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#18 src/XF/App.php(2826): XF\Mvc\Dispatcher->run()
#19 src/XF.php(806): XF\App->run()
#20 index.php(23): XF::runApp('XF\\Pub\\App')
#21 {main}
Request state
array(4) {
  ["url"] => string(31) "/forums/index.php?search/search"
  ["referrer"] => string(106) "https://internationalskeptics.com/forums/index.php?threads/what-game-did-you-play-today-iii.368075/page-15"
  ["_GET"] => array(1) {
    ["search/search"] => string(0) ""
  }
  ["_POST"] => array(4) {
    ["keywords"] => string(4) "dayz"
    ["constraints"] => string(44) "{"search_type":"post","c":{"thread":368075}}"
    ["c"] => array(1) {
      ["users"] => string(0) ""
    }
    ["_xfToken"] => string(8) "********"
  }
}

 

[david.steadson]

Seems the managed database actually ignores requests to change innodb_ft_result_cache_limit .
Any tips on how we can optimise things? Searching with Xenforo is literally causing the database server to crash (bringing down several sites) on a regular basis.

 

[Digital Doctor]

the managed database

it's a host / MySQL issue, not a xenforo issue.

you are the first xenforo user to have this error out of thousands.

 

[david.steadson]

Thanks for the really helpful advice. How many of those thousands have 40Gb databases with over 13 millions posts and xf_search_index is over 15Gb?

The query is what intrigues me. A lot of boolean exclusions I think is the root of the issue and likely creates a massive cached query. Are these nodes that are unsearchable? Someone knowledgeable .... and helpful ... might be able to shed some light so I can keep troubleshooting.

ETA: Yup, if I use Advanced Search and do a "search everything", then it works fine. If I do "search threads" for the same query, then it crashes every time. Same if I use the dropdown Search in a thread and select anything other than "Everywhere"

 

[Chromaniac]

Tbh xenforo does advice forums with more than a million posts to switch to enhanced search

 

[david.steadson]

Tbh xenforo does advice forums with more than a million posts to switch to enhanced search

Thanks. I have not seen that advice. I've been reviewing the ElasticSearch website and frankly it's confusing as hell to work out what's needed .... I'll keep educating myself.

 

[Chromaniac]

yeah i mean it is not an official guideline and i cannot specifically find a post on it. here is one that definitely suggests ES for a forum with 100 million posts lol. but 1 million is generally where it is a good idea to start looking at dumping mysql search. i moved to ES at around the same milestone and search performance was improved significantly. in the end, it depends upon how powerful your server is and how optimized mysql is. 14m posts is a pretty large number and 15GB search index table seems a lot based on what i have read about here in search related discussions. my post was mostly suggestive in nature as this would have been advised here sooner than later. i hope you find a solution for your issue though! Cheers.

so i did find one post hehe. there are few others from another (former?) support team member. here is another related post.