Problem query overloading server

[dfc005]

OK,

Have just had word from our server manager who has told us that there is a query that is overloading the server at random intervals but happening regularly throughout the day.

I've try disabling all plugins and custom mods, using the default style and a few other tricks but it doesn't seem to be stopping the query.

I was hoping someone could indicate where this query is being initated from and then hopefully that would help me track done what's causing the overload.


SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
language.phrasegroup_global AS phrasegroup_global,
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))
WHERE
user.userid = 2282549


Some additional points are that our user database is very large with over one million registered users and we have quite a lot of traffic, approx. 150K users (combo of guest and registered) visiting per day.

Hoping someone can help me out here!

[TheInsaneManiac]

Dude I think I have some problem similar to this. Whenever you get overloaded does your forums give you a database error and cause you to have to call your host to get your site restarted? If so let me know.

[dfc005]

No, not really. We own our servers and have a part-time server manager who looks after them all.

And we don't have to restart the servers to get the sites back up and running. We'll just get slammed by heaps of the same query at different intervals. While this is happening, no pages on the site will load. After 5-10 minutes, the queries will clear and everything works as it should till next time the query overload happens.

[TheInsaneManiac]

No, not really. We own our servers and have a part-time server manager who looks after them all.

And we don't have to restart the servers to get the sites back up and running. We'll just get slammed by heaps of the same query at different intervals. While this is happening, no pages on the site will load. After 5-10 minutes, the queries will clear and everything works as it should till next time the query overload happens.
Is it the same EXACT query every time? If so just find the user:
2282549

and if he's not active remove him. However if the user id changes in the query (which it probably does) you may have a modification made to your default vbulletin code that you are not aware of.

[Marco van Herwaarden]

If this is not with 1 specific user, then it is strange that you woudl only sometimes get problems. This query is part of fetch_language_fields_sql() which in turn is called by fetch_userinfo() (and 1 more), so if this query was the problem i would expect it to happen all the time.

More likely is that there is another query that locks up some tables used in the reported query. The reported query is exected often and might be waiting on that previous query to release the tables. If this is the case, then you should try to locate the query that locks up everything, as that will be the clue to solving this.

You might try opening a support ticket, but based on the info provided thusfar i doubt they can help you unless you can locate the query that locks the tables.