Xenforo throws MySQL timeout errors while searching even with Elasticsearch installed

#1
About two months ago, we moved our forum over to a new server. During that move there was an issue installing elasticsearch, so we decided to just run with Xenforo's built-in search and figure out the elasticsearch stuff later. Xenforo's built-in search doesn't work well for us - searches usually trigger an error identical to this:

Code:
Error Info
ErrorException: Fatal Error: Maximum execution time of 30 seconds exceeded - library/Zend/Db/Statement/Mysqli.php:250
Generated By: Unknown Account, Today at 9:02 PM
Stack Trace
#0 [internal function]: XenForo_Application::handleFatalError()
#1 {main}
Request State
array(3) {
  ["url"] => string(43) "https://website.com/forum/search/search"
  ["_GET"] => array(1) {
    ["/forum/search/search"] => string(0) ""
  }
  ["_POST"] => array(4) {
    ["keywords"] => string(8) "test query"
    ["users"] => string(0) ""
    ["date"] => string(0) ""
    ["_xfToken"] => string(8) "********"
  }
}
And along with that, searching with Xenforo's built-in searcher usually causes PHP-FPM to undergo high CPU usage, and will eventually make any access to the forum return a 504 Gateway Timeout error from NGINX for a few minutes until the search requests end. As such, I'm working on moving back to Elasticsearch. I started off at a clean slate - I uninstalled any old elasticsearch installation, installed the most recent Elasticsearch 6, configured it and confirmed that it's working:

Code:
[root@srv elasticsearch]# curl -XGET http://localhost:9200
{
  "name" : "m4_j9tM",
  "cluster_name" : "xenforo_search",
  "cluster_uuid" : "U5FFug4SQN6SuRJOLOmqJw",
  "version" : {
    "number" : "6.2.2",
    "build_hash" : "10b1edd",
    "build_date" : "2018-02-16T19:01:30.685723Z",
    "build_snapshot" : false,
    "lucene_version" : "7.2.1",
    "minimum_wire_compatibility_version" : "5.6.0",
    "minimum_index_compatibility_version" : "5.0.0"
  },
  "tagline" : "You Know, for Search"
}
From there, I uninstalled and reinstalled the Enhanced Search addon (ours was 1.1.6, we now have 1.1.8), and disabled the ElasticSearch Essentials addon. After that, I rebuilt the search index under Tools -> Rebuild Caches, ensuring to delete the index before rebuilding. Going based off of the instructions, I should have a working Elasticsearch installation that works with Xenforo. The Tools -> Elasticsearch Setup page does show that searches are being performed against it and that the data is present in its cache:

Code:
Elasticsearch Server: http://127.0.0.1:9200/xenforo_search
Version:6.2.2
Documents:750,917 (156.9 MB)
Index Updates:750,979 (0.0001 seconds average)
Searches:1,055 (0.0015 seconds average)
Fetches:897 (0.0022 seconds average)
However, even with Elasticsearch enabled and apparently being used, we still receive that above error - any search request that doesn't hit that 30 second timeout will still take a very long time to complete, usually within the 25-29 second range. I think it's odd as it doesn't make sense that it would still touch the database with Elasticsearch installed, but perhaps there's something I'm misunderstanding. Our Elasticsearch logs don't show anything wrong, PHP-FPM's logs are filled with these two lines:

Code:
[04-Mar-2018 06:02:38] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 8 children, there are 2 idle, and 46 total children
[04-Mar-2018 06:02:44] WARNING: [pool www] server reached pm.max_children setting (50), consider raising it
Which is understandable seeing that searching causes PHP-FPM to stress out for some reason. This is all on Xenforo 1.5.14, can anyone shed some light on what's going wrong?

Edit: Just realized that this probably fits better under Enhanced Search's support forum rather than here, sorry!
 
Last edited:
#2
This can be closed/deleted. I tried every imaginable configuration of Xenforo, Enhanced Search, and Elasticsearch, and could not get it working. I decided that a complete upgrade was in order - to Xenforo 2 and PHP 7.2. On my test forum that I'm using to prepare everything for this update, it appears to all function perfectly.
 
Top