XF 2.0 Looking for Community assistance - Search Index Rebuild extremely slow

[fotografik]

Appealing to the collective knowledge of the Xenforo community here. Since moving our large VB4 forums over to XF2, we have tried to speed up the search index rebuild, but it is taking an extremely long time.

The CLI process takes roughly 8 minutes (+/- 30 seconds) to process between 550-600 posts. At this rate, we will need to leave the process running for 70+ days to complete the index rebuild.

We are hosted on AWS (EC2 + RDS) and I have tried various combinations of RDS instances (high memory, high CPU, 10 Gbps network) and it's always the same - 500 posts takes roughly 7-8 mins to process.

The rebuild process write a lot of temp tables to disk (according the Performance Schema report pane on MySQLworkbench) but changing the temp table size variables does not seem to help (at one point, I tried a 512MB temp table max size).

Coming from a Sphinx search implementation which took less than 30 mins to reindex our whole site previously, this extreme slowness is literally killing me.

Appreciate if anyone can shed some light on where I can improve on the index rebuild time.

 

[TPerry]

Maybe using ElasticSearch option (it's an additional add-on) if your site is large - you don't say how large it is, but generally 1 million + post sites benefit from it.

 

[RobParker]

Is there a CLI option for how many posts to process at once? You can likely up that limit significantly with a powerful server.

 

[fotografik]

Maybe using ElasticSearch option (it's an additional add-on) if your site is large - you don't say how large it is, but generally 1 million + post sites benefit from it.

We are using the Elastic Search add-on as we have close to 9M posts. But the search is useless without the search index

 

[fotografik]

Is there a CLI option for how many posts to process at once? You can likely up that limit significantly with a powerful server.

The CLI does not expose any additional options as far as I am aware.

 

[RobParker]

We are using the Elastic Search add-on as we have close to 9M posts. But the search is useless without the search index

ES doesn't use the mysql search index. The ES search should rebuild super fast.

Edit: I'm not even sure if you can do the ES search index via the CLI. Are you sure you're not rebuilding the normal mysql search index which isn't used anyway?

 

[fotografik]

ES doesn't use the mysql search index. The ES search should rebuild super fast.

Edit: I'm not even sure if you can do the ES search index via the CLI. Are you sure you're not rebuilding the normal mysql search index which isn't used anyway?

Hmmm from what I have read in the forums, the CLI will do both ES and standard MySQL search rebuilds depending on whether the ES add on is enabled.

In any case, I can see the ES documents number increase so it looks as though it is populating the ES index.

 

[RobParker]

Hmm our ES search build (after upgrading to XF2) took a few minutes (at most) to do 5 million posts. That wasn't through the CLI though, that was via the browser.

 

[S Thomas]

What ES, XFES, PHP and mysql versions are you running?

 

[fotografik]

Elastic Seach V6.2
XF Enhanced Search V2.01
PHP 7
MySQL 5.7

Forum is running on an EC2 t2.medium instance - loads are in the 10%-15% range.
Database is on a RDS m4.large instance - loads are in the 30-40% range. I will post some graphs from CloudWatch.

As I mentioned earlier, I have tried various different RDS and EC2 setups (using separate EC2 and RDS instances set up for testing, not on the production/live site) and the results are almost always the same.

I will try running the rebuild via browser in the test environment to see if that helps after I to boot up a new RDS instance with this mornings snapshot.

 

[fotografik]

Update: Trying the browser rebuild method did not yield any measurable increase in speed.

BUT ... Yay! Hooray!! Yippeekayay!!! ... I found the issue after looking at the SQL statement that was writing a ****load of TMP tables. This particular statement was creating TMP tables at the rate of almost 1 per second when the rebuild was running:

SELECT COUNT ( * ) FRM `information_schema` . `PLUGINS` WHERE `PLUGIN_NAME` = ?

I turned off all non-XF plugins (we only have 3 in any case) and tested ... reindex goes really fast as it should at 5000+ posts indexed per second.

Turned on the plugins one-by-one, testing along the way, and the culprit was Snog's Advanced Forms.

 

[dehness]

We're facing the same problem. I just attempted to rebuild the search index for about the 4th time in the last couple of days, this time after completely rebuilding ES and updating to the latest version, and it took close to 5 hours to do ~200,000 posts.

I have rebuilt the search index in the past, both with and without ES, and it was wicked fast, processing 5000 posts per query. Now I'm lucky to get 500 done without it timing out...

I've had to resort to the CLI, even going as far as to write a script for it and attempting to run it in the background so I can disconnect the SSH session (unsuccessfully), but without luck.

 

[dehness]

Update: Trying the browser rebuild method did not yield any measurable increase in speed.

BUT ... Yay! Hooray!! Yippeekayay!!! ... I found the issue after looking at the SQL statement that was writing a sh*tload of TMP tables. This particular statement was creating TMP tables at the rate of almost 1 per second when the rebuild was running:

SELECT COUNT ( * ) FRM `information_schema` . `PLUGINS` WHERE `PLUGIN_NAME` = ?

I turned off all non-XF plugins (we only have 3 in any case) and tested ... reindex goes really fast as it should at 500+ posts indexed per second.

Turned on the plugins one-by-one, testing along the way, and the culprit was Snog's Advanced Forms.

Ohhhh yessss! This reply is perfect timing personified...

/me goes to disable Snog's Advanced Forms and try again.

Thanks heaps mate.

 

[dehness]

Yeesssssss. Pumping it at 5000 posts a second now.

Can't thank you enough @fotografik!

Little bit pissed I went through all the trouble of deleting the ES docker container and installing ES and its dependencies directly on my server tho.

 

[fotografik]

Can't thank you enough @fotografik!

Most welcome, glad to have been able to help a fellow XF user.

CLI is actually built into the XF system (but not well documented).

To run search rebuild from CLI, go to the root folder where XF is installed and run the following:-

php cmd.php xf-rebuild:search

You can do a search here to find the other CLI commands available.

I use the screen command (on Ubuntu) to run terminal screens in background.

screen -S screenname - start a screen session
CTRL A D to disconnect from screen session
screen -r screenname - reconnect to running screen session

 

[sbj]

Maybe tag @Snog to let him know about the issue, if all is good or not.

 

[fotografik]

Postscript: Really kicking myself for not disabling the Addons and testing. In fact, now that I look back, at the early stages of test migration/import, we ran a bare minimum setup with only XF addons and we didn't come across any issues with reindexing at that time.

We only installed non-XF addons at the last stages of migration testing, with the major one being the Forms addon which we use to format the postings in our Personal Classifieds sections.

Anyway, happy to now being able to inform that we had 11M+ items re-indexed in 45 minutes

 

[Snog]

I haven't been able to duplicate this on my development server or my live site. So, I have to ask...

Was this tested with only the forms add-on enabled?

Would one or both of you allow me to access your sites so I can try to find the problem? Preferably with FTP access so I can edit the add-on files as needed. Or, at the very least be willing to spend some time following file edit instructions one at a time to locate the problem.

The forms interface to XenForo itself is minimal and there are only a couple of things that might cause something like this.

 

[Snog]

Update: Trying the browser rebuild method did not yield any measurable increase in speed.

BUT ... Yay! Hooray!! Yippeekayay!!! ... I found the issue after looking at the SQL statement that was writing a sh*tload of TMP tables. This particular statement was creating TMP tables at the rate of almost 1 per second when the rebuild was running:

SELECT COUNT ( * ) FRM `information_schema` . `PLUGINS` WHERE `PLUGIN_NAME` = ?

I turned off all non-XF plugins (we only have 3 in any case) and tested ... reindex goes really fast as it should at 5000+ posts indexed per second.

Turned on the plugins one-by-one, testing along the way, and the culprit was Snog's Advanced Forms.

What is triggering that query? I can't seem to find that query in XF itself.

 

[fotografik]

I haven't been able to duplicate this on my development server or my live site. So, I have to ask...

Was this tested with only the forms add-on enabled?

Would one or both of you allow me to access your sites so I can try to find the problem? Preferably with FTP access so I can edit the add-on files as needed.

The forms interface to XenForo itself is minimal and there are only a couple of things that might cause something like this.

I have turned off the test server but can fire it up again with a restored DB snapshot so that you can try out. I can also try with just the forms add on active, but the only other add ons we have installed are the Uix themes add on besides the standard XF Media Gallery and Enhanced Search. We have Resource Manager installed but not active.

After some more sleuthing, it may not be something directly related to the Forms add on as I still see the same query running on the production server after the indexing was done, but at a lesser frequency.