Massive Speed Problems and Total Site Outage Today

[Boss-Hog]

All,

I realize from the title this sounds like a hosting problem, but if my host is at all accurate, it's a vBulletin issue, so I want to confirm either way. Over the past few months, my forum has been sporadically very sluggish, but recently, it's gotten very bad to the point it's been out of commission all day. I've received legitimately more than 50,000 database error emails, and from looking at today's logs, most seem to be:

MySQL Error : MySQL server has gone away
Error Number : 2006

and

MySQL Error : Lost connection to MySQL server during query
Error Number : 2013

Right now, if you tried to connect to my forum, you'd get the generic database error ("The database has encountered a problem."),
but that's apparently what's happening on the backend.

My host has not been helpful in the least, and it's nearly a foregone conclusion we're going to switch unless what they say is true -
that this is a problem with vBulletin queries. I've gotten plenty of runaround that they can't recreate site sluggishness and so forth,
so I'm not inclined to believe anything they say at this point. In any event, since the below note from them, I have disabled plugins
by adding the appropriate code to config.php, but here is their take as to what is causing the problem(s):

Hello,

I have checked the server and could see that there are still locked queries in the server that are causing this issue.

Please remove these locked queries as this is causing a lot of mysql processes.

I have restarted the mysql server but the processes are still getting created. I ave already increased the max_user_connections to 50, when you have disabled the plugin.

However, disabling the plugin did not solve the problem in full. The following queries needs to be optimized.

==
| 62 | reds_vb | localhost | reds_vb | Query | 198 | Locked | SELECT *
FROM session
WHERE userid = 0
AND host = '157.55.32.166'
AND idhash = 'a40781954205daa42f7dd679e98837e1'
LIMIT 1

| 59 | reds_vb | localhost | reds_vb | Query | 203 | Locked | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user.languageid AS saved_languageid,
IF(user.displaygroupid=0, user.usergroupid, user.displaygroupid) AS displaygroupid,
language.phrasegroup_global AS phrasegroup_global,
language.phrasegroup_holiday AS phrasegroup_holiday,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep

FROM user AS user
LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid))


| 48 | reds_vb | localhost | reds_vb | Query | 227 | Locked | SELECT user.username, (user.options & 512) AS invisible, user.usergroupid,
session.userid, session.inforum, session.lastactivity, session.badlocation,
IF(user.displaygroupid=0, user.usergroupid, user.displaygroupid) AS displaygroupid, infractiongroupid
FROM session AS session
LEFT JOIN user AS user ON(user.userid = session.userid)
WHERE session.lastactivity > 1361919117
ORDER BY lastactivity DESC
--

From what I can see, most of the queries are related to session management. Some session data are getting updated by these queries.

Forum is loading now(refer snapshot) but it can go down as the queries are locked ones.

Can someone please confirm if the above queries are indeed stock vBulletin queries, and if so, do they truly need to be optimized? If
that is indeed the problem, how would I go about doing that? I've been running vBulletin since 2002, and I've never had any issues
along these lines, so I'm at a loss.

As mentioned above, my site has been completely inaccessible all day, and at this point, I'm extremely frustrated and would simply like
a straight answer as to whether or not there's some truth in what my host is saying or if they're merely incapable (or unwilling) of
fixing the problem.

Thank you in advance for any help you can provide - I greatly appreciate it.

[BirdOPrey5]

I don't see anything suspicious on those queries. Especially the first one, it's not a "hard" query, if the server is locking on that one I'd imagine it is a server issue. I suppose it is possible your session table is excessively large- can you check the number of rows in the database?

There is a scheduled task that runs to keep the table to manageable levels- is your "Session Timeout" setting in the Admin CP exceptionally large? Usually it's somewhere between 15 minutes and an hour (in seconds.) If you set it to 9999999999 or something it could create a very large session table.

[Boss-Hog]

Thanks Joe. That's exactly what I suspected; we decided to make the switch, so until it's made, I can't tell you the size of the session table, but I'll follow up once the move is done. I'll also confirm the "Session Timeout" setting, but I'm 99.9% sure I've never modified that.

[Boss-Hog]

Joe,

It turns out it was indeed the host - everything is lightening fast now (knock on wood).

The "Session Timeout" setting is set to 900, so that should be acceptable. I'll check the number of rows in the session table as soon as I figure out how to access phpMyAdmin with the new host.

Thanks again.

[Boss-Hog]

The "session" table has 213 rows; is that fairly normal?

[BirdOPrey5]

Yes, 213 rows is fine... Even a thousand on a busy board isn't anything to be concerned about. I wanted to make sure it wasn't 100,000 or such.

Glad you are happy on your new host.

[Boss-Hog]

That's good news; thanks again for all your help.