Awaiting feedback Search index build with media comments is so slow it's useless.

MySiteGuy

MySiteGuy

Well-known member
Licensed customer
Affected version
2.3.7
I have a client that I did an import for, and I'm currently rebuilding the search index.

When it hit the Media Gallery comments portion (xf_mg_comment table), it was fairly quick at first but has been getting slower as it goes.
There are 1.8 million comments, and its currently on comment number 292,342.

Its been running for over a week, and is down to taking over a minute for each comment (CLI). The speed is the same with both Elastic Search and Innodb search.
At this rate, it will literally take years to complete, and if it continues to get slower... decades.
 
When I rebuild the search index I always run it through CLI and use the "Elastic Search Essentials" (Add-on by @Xon) command - that's much quicker, because you can have multiple workers. ➡️ https://xenforo.com/community/resources/elasticsearch-essentials.7103/

You can use a command like php cmd.php xf-rebuild:search-bulk --batch=5000 --workers=16 --newest-first --delete (the number of "workers" should be less than the CPU cores you have).

Anyhow, I never had any problems with indexing XFMG comments (we have quite a couple of millions and indexing them did not take longer as expected).
 
Automated triage: Awaiting feedback

We couldn't find anything in the media comment index rebuild that should slow down as it progresses, so the slowdown is probably specific to this database. We need a little more detail to look into this.

  • Can you enable the MySQL slow query log (or XenForo debug mode) during the rebuild and send the exact slow query, plus the output of EXPLAIN for it?
  • Which MySQL or MariaDB version is the server running, and did the other content types (posts, threads, media items) index at normal speed?
  • What did you import from, and do the gallery tables still have their standard indexes? Please send the output of SHOW CREATE TABLE xf_mg_comment and SHOW CREATE TABLE xf_mg_media_item.
  • Are any add-ons installed that extend Media Gallery comments or media items?

This is an automated assessment. A member of staff may review and update it.
 
Back
Top Bottom