| Tourmeister |
10-29-2007 01:07 PM |
Identifying query intensive mod?
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:
Quote:
# 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:
Quote:
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? |
More Information