Identifying query intensive mod?

[Tourmeister]

Howdy,

I run a server with two active VB forums, one is mine (twtex.com) and the other person (st-owners.com) pays me for server space. We have been seeing some very large spikes in our server load several times per day. Using mtop, we have determined that ALL of the queries causing the spikes are coming from the stowners site. So we logged the queries lasting for 2 seconds or more and here is a sampling of what we are repeatedly seeing:

# Query_time: 3 Lock_time: 0 Rows_sent: 1 Rows_examined: 56108
use stowners_vbulletin;
SELECT COUNT(post.postid) as unread
FROM post as post
INNER JOIN thread as thread ON (thread.threadid = post.threadid)
LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = 865)
INNER JOIN forum AS forum ON (forum.forumid = thread.forumid)
LEFT JOIN forumread AS forumread ON (forumread.forumid = forum.forumid AND forumread.userid = 865)
WHERE thread.forumid IN(182, 185, 231, 187, 45, 46, 49, 48, 244, 275, 188, 152, 189, 209, 223, 235, 236, 237, 47, 278, 276, 196, 267, 279, 251, 266, 228, 238, 158, 277, 265, 249, 230, 259, 226, 191, 240, 229, 227, 233, 241, 247, 248, 257, 260, 258, 263, 268, 274, 273, 272, 57, 202, 203, 56, 68, 212, 211, 206, 210, 208, 262, 63, 193, 253, 254, 61, 58, 55, 54, 59, 71, 74, 204, 64, 72, 73, 53, 243, 79, 78, 239, 186, 157, 164, 183, 86, 194, 76, 184, 156, 264, 256, 271, 261, 176, 75, 84, 81, 83, 159, 160, 42, 155, 246, 245, 161, 252, 269, 280, 52, 225, 234, 85, 192, 150, 151, 270, 173, 232, 190, 51, 50, 181, 166, 172, 170, 250, 220, 221, 222, 169, 178, 177, 179, 224)
AND thread.sticky IN (0,1) AND thread.visible IN (0,1,2)
AND thread.lastpost > IF(threadread.readtime IS NULL, 1191065676, threadread.readtime)
AND thread.lastpost > IF(forumread.readtime IS NULL, 1191065676, forumread.readtime)
AND thread.lastpost > 1191065676
AND post.dateline > IF(threadread.readtime IS NULL, 1191065676, threadread.readtime)
AND post.dateline > IF(forumread.readtime IS NULL, 1191065676, forumread.readtime)
AND post.dateline > 1191065676;

Here is a list of Mods that the stowners admin has installed and running:

Advanced PM Options 1.2.3
Created By VisionScripts (www.visionscripts.com)

AME - The Automatic Media Embeder 1.2.0
Automatically embeds media URLs with posts

Automatic Birthday Thread 1.0.1
This Hack automatically creates a birthday Thread

Automatic Welcome PM 1.0.4
This Hack will automatically send welcome PMs to new members.

Cyb - Attention Zero-Posters 1.4
Cyb - Attention Zero-Posters

Display Unread Posts 1.01
Display the number of posts you have marked as unread.

Do not show who's online for guests 1.1
Do not show who's online for guests

e-steki Unvote 1.0.1
Allows Users To Withdraw Their Votes From Polls
Farcaster's Event Attendance 1.2.1
This hack will allow users to RSVP to selected events.

Favorite Smilies 1.0.3
User selectable favorite smilies

ibProArcade for vBulletin 2.6.3
ibProArcade - professional Arcade System for vBulletin

Ignore Thread 1.0.0
Ignore Thread

Kpt - Icon Key 1.0
Kpt - Icon Key

Links and Downloads Manager 2.2.7
Manages a document and link library

Miserable Users 2.12
A way to really annoy anyone you don't want visiting your forum.

NoSpam! 3.0
NoSpam! allows you to specify a set of questions which members are required to answer correctly at registration, eliminating the ability of spam bots to register at your forums and post unwanted messages.

Personal Notepad 1.1
Give users a personal notepad they can store text in.

Random Home Images 0.1
Displays a random image from a pool of images contained in a specified directory which include a specified delimeter text in the filename. Userful for header images or for displaying random babes, random avatars, etc.

Random Images 0.1
Displays a random image from a pool of images contained in a specified directory which include a specified delimeter text in the filename. Userful for header images or for displaying random babes, random avatars, etc.

Selective Forum Filter 1.0.2
Created By VisionScripts (www.visionscripts.com)
Separate Sticky and Normal Threads 1.0.3
Separate Sticky and Normal Threads

ShowProfileFieldInPostBits 1.0
EMiN3M Show Profile Field In Post Bits

Statistics 3.1
Adds a statistics page to your forum.

Thread Title Coloring 1.05
Enables you to customize the color of your thread titles.

Un-Activated User Management 3
This hack will clean up unactivated users

vBadvanced CMPS 2.2.1 (vB 3.6)
vBadvanced Content Management & Portal System
vbgooglemap Member Edition by StonyArc 3.0.0
Display user location based on Google Maps API

vBulletin 3.6.6 Calendar Event XSS Fix 2.0
Yet Another Award System 3.6 2.1.4
Admin can give member

Does anyone know if any of these mods are prone to long MySQL queries?

[Andrew Green]

That query looks to be doing a count of how many unread posts the user has.

[Tourmeister]

That is interesting... I have the Display Unread Posts 1.01 installed on the twtex.com site and we are not seeing similar issues. Both sites have about the same number of users online at any given time. Might any of those other mods listed also result in an output like that shown above?

[bobster65]

Originally Posted by Tourmeister

Might any of those other mods listed also result in an output like that shown above?

vbadvanced (a couple of the modules)... do you both run CMPS?

[Mellow]

Yes, we both run cpms

[bobster65]

Originally Posted by Mellow

Yes, we both run cpms

Same modules?

[Mellow]

Maybe a couple, not sure, that's something we haven't compared. Doesn't seem like running the same modules would matter to me since we have our own Databases.

[bobster65]

Originally Posted by Mellow

Maybe a couple, not sure, that's something we haven't compared. Doesn't seem like running the same modules would matter to me since we have our own Databases.

You are correct, its not the same ones, its the ones that you might have running that the other site doesn't. That could very well be where the problem is. Some CMPS modules are query intensive.

[Tourmeister]

I only use the default modules that come with CMPS for twtex. I have added a few custom ones, but they do not hit the DB at all. They are just static pages that never change.

Mellow, do you have any 3rd party modules installed?

[Mellow]

Yes, sure do.

There are a couple that show newest and hottest links/downloads.

There's also a module with tabs as well.

All the others are the basic cmps modules and some that are custom but based of the basic ones.. similar to the news block , pulling from a specific forum.

Nothing else is hitting the database.

I guess I still don't see a 'smoking gun'. We could start disabling blocks and see if that makes a difference.