1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Massive XF_SEARCH_INDEX - Help - Search Function Not Needed

Discussion in 'General PHP and MySQL Discussions' started by cjesuele, May 19, 2016.

  1. cjesuele

    cjesuele New Member


    My site's index.php (basically runs XenForo) is intermittently running extremely high CPU usage, which locks up my site for a minute or so.

    I have disabled XenForo's user-based search function, as I don't need that functionality on my site.

    XF_SEARCH_INDEX is by far my largest data-table in my database, with over 200,000 rows and counting.

    QUESTION: If I am not using the front-end search function, can I DROP the entire XF_SEARCH_INDEX table? Will this stop it from having rows added?

    Is anything else tied to or being using by this specific data-table? Like for SEO or other purposes?

    If so, can I just select DROP table in phpmyadmin? And do you think this will reduce the CPU usage for my index.php as the database will be nearly cut in half if I were to drop this table.

    Thank you!
  2. RoldanLT

    RoldanLT Well-Known Member

    You can TRUNCATE or Empty that table but not drop.
  3. cjesuele

    cjesuele New Member

    Ok excellent, thank you!

    Will that suppress this table from being updated or added to when new threads/posts are created?
  4. RoldanLT

    RoldanLT Well-Known Member

    Just disable search engine on admin option and don't rebuild search index.
  5. cjesuele

    cjesuele New Member

    Ok, I've had it disabled for a few days now and rows were still being actively added (new posts and threads).

    And I haven't manually rebuilt the search index for a few weeks. Is there another Cron that may be rebuilding the search index incrementally?
  6. cjesuele

    cjesuele New Member

    I just truncated the Table, and new rows were created when a new post & thread were created. Any thoughts of how to suppress this?

    Also, my index.php is still running 95%+ CPU usage... even after truncating the table...

    Thoughts on high CPU usage for index.php, which locks up the site?
  7. RoldanLT

    RoldanLT Well-Known Member

    Disable all addons and enable one by one.
  8. cjesuele

    cjesuele New Member

    Ok, what will be achieved by doing so? Suppress new rows to the Search_Index table? Or fix the high CPU usage index.php issue?
  9. Snog

    Snog Well-Known Member

    All posts are automatically added to the search index when they are made.

    So far as I know, a high index.php usage has nothing to do with the search index.

    How many users are accessing your site?

    Disabling add-ons would basically set the system to it's lowest number of database reads and only run the stock XF code for index.php.
  10. cjesuele

    cjesuele New Member

    I have over 25,000 forums, and I am only noticing the homepage locking up during these periods when index.php is running high.

    And I constantly have new posts & threads being creating.

    I have backend and front-end cache enabled. I am using 'bd Cache' XenForo add-on for front-end caching. I am thinking it is running a full cache of the site during this period of high CPU usage?

    I already stripped sub-forums from from node_category_2 and node_forum_2 templates, so it is a not a homepage size loading issue as my homepage is less than 1.0MB in size.
  11. Brogan

    Brogan XenForo Moderator Staff Member

    Regardless of what changes you have made to the templates, with that number of forums you're going to have issues.

    Just loading the quick navigation page took seconds.
    Snog likes this.
  12. cjesuele

    cjesuele New Member

    It's not the worst thing in the world I guess. This occurs every few minutes or so when loading the homepage.

    Once the page loads, it refreshes instantly as it is cached properly I am assuming. Most of my forums are RSS feed based (registered feeds).

    Do you think these periods of high CPU usage only occurs when new posts or threads are posted? Possible solution being to schedule the Feeder to run only late at night, so the majority of new posts?

    Would that help? Or is the index.php solely dependent upon the overall size of the site, regardless of when new posts are made?

    Any suggestions of how to reduce this period of High CPU usage would be greatly appreciated.
  13. cjesuele

    cjesuele New Member

    Any other suggestions?
  14. Brogan

    Brogan XenForo Moderator Staff Member

    My suggestion would be to reduce the number of forums from over 25,000 to a more realistically manageable figure.

    Typically fewer than 1,000.
  15. cjesuele

    cjesuele New Member

    Unfortunately not an option :(.

    Thank you for the help regardless!

    Do you know if I was correct in observing that only the Homepage is affected by this index.php high CPU usage on the front-end?
  16. EQnoble

    EQnoble Well-Known Member

    I would guess that anything that has to recall your whole forum tree is going to be effected (like the quick navigation menu as Brogan mentioned).
  17. The Forum Heroes

    The Forum Heroes Well-Known Member


Share This Page