Blog Documentation Try Vanilla Cloud
Vanilla 1 is no longer supported or maintained. If you need a copy, you can get it here.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Options

Query issue

scheremscherem
edited January 2008 in Vanilla 1.0 Help
We recently moved our forums to dreamhost, and have received the following message today:

"Hello,

I'm writing you about your database *****. The database is
causing a load of 40+ on a really new mysql server, it seems from slow
queries from the software you are running on it. To prevent this high
load from effecting other customers longer I've renamed the LUM_Comment
table. The below queries will need to be made much more efficient,
otherwise I'm afraid you will need to already look at more powerful
hosting such as VPS or dedicated solution.

phil: 08:17 AM# uptime
08:18:46 up 60 days, 16:24, 2 users, load average: 45.20, 55.37, 48.58

You can write me about this issue at ****@dreamhost.com.

# User@Host: *****[***] @ jax.dreamhost.com
[208.113.253.82]
# Query_time: 11 Lock_time: 0 Rows_sent: 20 Rows_examined: 60406
select m.CommentID as CommentID, m.DiscussionID as DiscussionID, m.Body
as Body, m.FormatType as FormatType, m.DateCreated as DateCreated,
m.DateEdited as DateEdited, m.DateDeleted as DateDeleted, m.Deleted as
Deleted, m.AuthUserID as AuthUserID, m.EditUserID as EditUserID,
m.DeleteUserID as DeleteUserID, m.WhisperUserID as WhisperUserID,
m.RemoteIp as RemoteIp, a.Name as AuthUsername, a.Icon as AuthIcon,
r.Name as AuthRole, r.RoleID as AuthRoleID, r.Description as
AuthRoleDesc, r.Icon as AuthRoleIcon, r.PERMISSION_HTML_ALLOWED as
AuthCanPostHtml, e.Name as EditUsername, d.Name as DeleteUsername,
t.WhisperUserID as DiscussionWhisperUserID, w.Name as WhisperUsername,
a.Preferences as AuthUserPrefs from LUM_Comment m inner join LUM_User a
on m.AuthUserID = a.UserID left join LUM_Role r on a.RoleID = r.RoleID
left join LUM_User e on m.EditUserID = e.UserID left join LUM_User d on
m.DeleteUserID = d.UserID inner join LUM_Discussion t on m.DiscussionID
= t.DiscussionID left join LUM_User w on m.WhisperUserID = w.UserID
left join LUM_CategoryRoleBlock crb on t.CategoryID = crb.CategoryID and
crb.RoleID = 1
where (crb.Blocked = '0' or crb.Blocked = 0 or crb.Blocked is null ) and
(m.Deleted = '0' or m.Deleted = 0 ) and (m.WhisperUserID = '0' or
m.WhisperUserID = 0 or m.WhisperUserID is null ) and m.DiscussionID = '8'
order by m.DateCreated asc limit 11400, 20;


Thanks!"

Any idea of what I can do to lower the load? Current extensions in use are:
Account Pictures
AjaxQuote
Applicant Email Verification
Audioscrobblerizer
CAPTCHA
Category Jumper
CategoryRoles
Chat Status
Discussion Filters
Foreced BBCode
Forum Statistics
GuestWelcome Message
Hiddent Text 1.4
JQmedia
JQuery
MarkAllRead
ModTools
MyComicVine
MyGamerCard
New Discussions
Notify 1.2.0
Nuggets 1.1.5
Participated Threads
Predefined Attributes
Preview Post
Private Messages
Report Post
Signatures
SpellChecker
User Filters
Vanillacons
Who's Online

Also the DB is about 50 Megs in size with 2232 discussions.

Comments

This discussion has been closed.