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.

 

[Snog]

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.

Yes please.

That query is checking the MySQL schema plugins not anything in XenForo itself, and it's not something the forms system changes or adds to at all. So, it's not the actual cause of that query problem.

 

[S Thomas]

Did you copy-paste that query? Since it's invalid and should not run at all.

 

[Snog]

Did you copy-paste that query? Since it's invalid and should not run at all.

No I didn't. It's not something that XenForo itself does (and I have no idea why the schema would be queried), so I really have no need to run it.

 

[S Thomas]

I meant @fotografik

 

[fotografik]

I meant @fotografik

Yes, it's copied from MySQL Workbench performance reports. I can still see this query running on the production server.

 

[S Thomas]

SELECT COUNT ( * ) FRM `information_schema` . `PLUGINS` WHERE `PLUGIN_NAME` = ?
This typo really should not work, unless my basic understanding of mysql is flawed. You should pinpoint where that is coming from, might be from your container itself or some kind of monitoring.