New installation, two vB instances, MySQL runaway thread

[Baldy]

So we've got two vBulletin instances (www.dgrin.com and www.advrider.com), both of which got vBSEO'd last night.

dgrin ran great all day with no problems, but ADVRider conks out periodically and has to be completely reset.

The load average skyrockets (80 - 160) and pages slow to a crawl (or mostly timeout). The culprit seems to be a runaway MySQL query - it takes forever (The longest we've let one run is 9800 seconds, but killed it before it completes).

I don't know if the problem query comes from vB (and we just never use it for some reason) or vBSEO, but here are the things we do know:

- Prior to installing vBSEO there are no slowdowns at all.
- Installing on dgrin (a much smaller board) produces no noticeable slowdowns.
- Both dgrin and ADVrider share a machine, so it's not a configuration problem - they're identical.
- We use PHP 4.3.9 + fixed on Red Hat Enterprise 4 with MySQL 4.1 and APC 3.0.8
- APC continues to run fine, ionCube isn't running (I've seen the threads about APC and ionCube. This appears to be a MySQL problem, not PHP, Apache, or APC).

Here's the query:

SELECT thread.threadid , post.postid
FROM thread AS thread
INNER JOIN post AS post ON(thread.threadid = post.threadid)
WHERE post.postid IN(###,###);

where "###,###" is an enormous list of numbers which appear to be all 2,000,000+ posts. It's truly mind-boggling how large the list of numbers is.

Things we're reasonably sure aren't the problem:

- httpd.conf vs .htaccess (we're not seeing page slowdowns, excessive disk usage, or Apache thrashing). It's trivial to move the .htaccess stuff into httpd.conf, but I'm positive it's not the problem.
- ionCube (APC seems to run fine, and we have no desire to use ionCube)

I'm happy to provide any extra details anyone might need.

Any ideas?

[Juan Muriente]

hmm... the SQL query quoted is not used by vBSEO. Perhaps it is executed by vB or some other hack. The strange thing is that, it only happens when vBSEO is activated?

Do you have any other hack in the advrider instance that is not installed in the dgrin instance?

Please try to disable all products/hacks except for vBSEO and try again. Let me know if it helps.

[Baldy]

Hmmm... Interesting question. I don't know for sure whether the query is running on ADVrider and not on dgrin, only that the query brings ADVrider to its knees but not dgrin. It could be that it isn't so intensive on dgrin because it only has 200,000 posts, whereas ADVrider has 2,000,000.

But you're right that ADVrider has plugins that dgrin doesn't. They both have vBSEO sitemap and Selective Forum Filter hacks.

ADVrider also has vBSEO conditional signatures, which I could have disabled had I thought to, and the ever-popular Miserable Users hack.

I'm having trouble getting my head around why an add-on wouldn't do a big query like that until vBSEO is installed, but you know the code far better than I.

[Baldy]

Okay, we finally got to the bottom of what the query was that was causing us to screech to a halt. It's a search to return results as threads done by vBulletin and had nothing to do with vBSEO or any plugins (we disabled them all and still had the problem, although we first noticed it after we installed vBSEO because it did add a small performance hit, which is fair and expected>

More about this query and what we did about it here:

http://www.vbulletin.com/forum/showthread.php?p=1043892