XF 1.4 Severe Database Performance Issues - Site down daily

[Bob Hennessey]

A few weeks ago we migrated a high traffic site to Xenforo.

Basic Setup is:

• 2 load balanced servers on the front end
• memcache enabled for backend

The symptoms - The site seems to be running well and then out of the blue the database performance falls off a cliff and everything goes down. Sometimes it will run for a few days without issue, then days like today crash twice forcing a restart of mysql. The app servers are fine through the whole process, this is all db related.

Based on some other threads out there we've:

• Increased the max_heap_table_size setting to 500MB
• Altered the xf_session_activity table:
• ALTER TABLE xf_session_activity
• DROP PRIMARY KEY,
  ADD PRIMARY KEY (user_id, unique_key) USING BTREE;

We even added an index to the user_id column on that table as we saw slow queries joining by that column.

The common issue here seems to have been with session related tables as they can grow rapidly. Possibly storing session info in memcache may help?

Session tables like xf_session_activity, right now the 8469 rows in it, does that mean the site thinks there's that many users on the site right now or can bot traffic get caught in the mix here?

Is there anything else I should be doing to aid the database performance that the other sites with busy forums like DigitalPoint have done?

We are testing running deferred.php as a true cron on one of our smaller forums, so far it seems good but we haven't implemented it yet on our busier forum as it feels like there's been too many moving parts the last few days.

Any help is greatly appreciated, we're unsure where to turn at this point.

 

[RobParker]

Any addons? They're normally the culprit.

 

[Bob Hennessey]

I agree addons are probably part of the problem. But I'm also disappointed there wasn't a central location or resource on configuring high traffic sites (see my mods above) as I would think this is a common list of performance tweaks. Would be great to have all of those tweaks listed in 1 spot on the forum or setup instructions.

We use several addons unfortunately, mainly around reputation and now I find Advanced Reputation System has been sold to a sketchy developer, one of the queries in there is consistently showing in the slow query logs.

Addons:

• Calendar & Birthday Calendar
• Similar Threads
• Advanced Reputation System
• Waindigo
  • Force Ignore
  • Newest Post First
  • User Moderation Queue on Front End
• Tapatalk
• Xenforo Media Gallery
• Xenforo Enhanced Search
• [bd] Widget Framework
• XFA - Social Groups