long selects

onetwentyeight

Hello all!

As some of you know, ive had some problems with my forum lately, but I've got it mostly figured out. All my wierd errors were resultant of my server guy accidentally following the upgrade instructions from 1.1.x to 1.1.2 rather than from 1.0.3. oops! Everything is pretty much back to normal now, though, thank god.

However, my forum is running VERY slowly. We did some digging and this is what we found. I appologize if some of this is poorly worded or confusing, I'm a bit in over my head with database stuff but here goes...

Apparently theres a lot of what my server guy (hence forth referred to as Joshua) calls "long selects" going on. Here is an example query he showed me...

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 = 3 where (crb.Blocked = '0' or crb.Blocked = 0 or crb.Blocked is null ) and (m.Deleted = '0' or m.Deleted = 0 ) and (m.WhisperUserID = '88' or m.WhisperUserID is null or m.WhisperUserID = 0 or m.WhisperUserID = '0' or m.AuthUserID = '88' ) and m.DiscussionID = '2645' order by m.DateCreated asc limit 6950, 50

This one apparently generates the index page of a particular thread, and its taking a long time to run. The problem is it joins a particular table (LUM_User) which gets written to a lot. I think it indicates something like the last time a user visited a page. The problem is that mysql locks the entire table behind the query that wants to write it. So if query "A" is very long and selects on LUM_User, and query "b" wants to write to LUM_User, too, it has to wait for A to finish. Meanwhile query "C" also wants to do a select with LUM_User, but cant because B, the update, still has it locked, so C has to wait for A to finish and... well you get the idea. We're seeing messages like this,

| 400 | sffixed | localhost | sffixed | Query | 0 | Locked |update LUM_User set DateLastActive=now() where UserID = '81' |

Which isn't good. So we're thinking theres either a problem with the schema, or queries, or an index isn't being used or something... We're not sure if enabling extensions would make much of a difference because it seems to be part of the core vanilla, which doesnt seemed to be coping well with high volumes. according to vanillazilla my board averages 337 posts a day, but i believe that accounts for the time when my board was getting off the ground, it believe its much higher now. not sure though.

What do you all think?

enabled extensions

AjaxQuote 1.0
Applicant Discovery 1.2
Audioscrobblerizer .2
bbinsertbar 0.1.5
better bb code 1.0
comment links 1.1
confirm go back .1
discussion filters 2.0
discussion view count 1.2.1
extended text formatter 1.2
flickr feed 1.0
hide success 1.0
html formatter 2.0.5
IP History 1.0.1
Markallread 1.0
new applicants 1.3
next unread discussions 1.0
page manager 2.4.3
preview post 2.4
quick keys 1.0
short stat 1.3
sidepanel 1.0
signatures 1.0.1
simplified discussions 1.0
spellchecker 1.1
text mode switch 2.0
user filters .9
vanillacons 1.3
vanillazilla 1.0.1
whisper notification .1
whos online 1.1
window links 1.0
yellowfade .1

WallPhone

You and Andy must have the same host: Help with some messy Vanilla MySql Queries (Mysql gurus welcome!) # 1

Not sure about the locking issue...

onetwentyeight

I doubt we have the same host, I'm actually hosted by a well known site that has had its own fair share of scaling issues.

I forgot to mention that this was not an issue with 1.0.3, its deffinately resulting from the upgrade to 1.1.2. I know there still may be something funky going on from my goofy upgrade but thats why im asking for help! heh.

onetwentyeight

I tried disabling whispers for 10 minutes to see if that would make a difference, but it didnt.

onetwentyeight

I also attempted disabling all extensions that I thought involved the database and it made a slight improvement, but not much of one

Mark

As far as I can remember, the queries for the discussion listing haven't changed since before Vanilla 1... The fact that it is updating "DateLastActive" on the user table all the time might be the tip-off here. I *believe* that field should only be updated when a user signs in or re-validates his/her session. If it is being updated constantly, that might imply that your sessions aren't working properly and instead it is using your remember-me cookies to validate you on every page load. You can test this by going to your profile and looking at the visitcount number. Then refresh the page and see if it increases. If it does, your php sessions are hooched.

onetwentyeight

I refreshed my profile, the visit count remained unchanged. any other ideas of things to check? thanks for the help.

Mark

I don't know that I'd call this a "long select", either. I mean - once you remove the fields that it is retrieving, here's the gyst of it:

select [SOME FIELDS] from LUM_Comment m inner join LUM_Discussion t on m.DiscussionID = t.DiscussionID left join LUM_CategoryRoleBlock crb on t.CategoryID = crb.CategoryID and crb.RoleID = 3 inner join LUM_User a on m.AuthUserID = a.UserID left join LUM_User e on m.EditUserID = e.UserID left join LUM_User d on m.DeleteUserID = d.UserID left join LUM_User w on m.WhisperUserID = w.UserID left join LUM_Role r on a.RoleID = r.RoleID

When joining to the user table, it is pulling information about the author, who has edited it, who has deleted it, and who whispered it. The latter three are left joins, and all of them join to the primary key of the table... That's a pretty straight forward query.

Mark

What kind of traffic are you getting?
Does your database have any indexes on it?

onetwentyeight

im in a bit over my head here, so im gonna have to get Joshua in here either tonight or tomorrow morning to answer specific database questions, as i dont have direct access to it. In regards to traffic, it averages a little over 10k page views a day.

Mark

That's so strange. I average around 80k page views per day and I have no problems at all...

I'd really like to get to the bottom of it - db optimization is something I'd love to work on - but not many people seem to know how to make mysql faster.

lech

Hmmm, it's been a while but could Vanilla silently be checking the validity of the role each time a page is called to see whether the user is meeting the requirements? As a result causing the process of that check to drag the server? I know it shouldn't cause a drag but that could be an angle to look into if optimization is a concern.

onetwentyeight

Thanks. Id like to get to the bottom of it, too

I'll get joshua in here as soon as I can.

onetwentyeight

Hey mark! create yourself an account at sffixed.com and check the discussion "sffixed is slow"

we have a long discussion about this problem that may be of interest to you. it gets pretty technical, and there are several vanilla admins in there talking about things encountered while running our forums.

nifkin

was there ever a conclusion reached from this discussion? i'm having what sounds like similar issues (though with far fewer extensions enabled).