Lil help with queries would be greatly appreciated

[luck777jojo]

heh well as most people on here I am having some probelms with queries... recently the admin that was taking care of hacks for our board etc. left, so basically we're kinda stuck with a whole bunch of queries slowing down the server, the problem is in the forumhome page not showthread or anything else, our forumhome page seems to be loading the slowest, this is really pissing me off as I am not sure about all the hacks installed and which ones can be optimized, it has steadily been getting worse since we're getting more and more users online at once, it has almost become unbearable lately when we get 700+ users online at once, I was always more of an IPB person and with this admin leaving I sort of got left with some of the dirty work O.o


anyways I'm posting the debug page, I'd appreciate it if anyone here could help me cut down at least a few queries on it

Query: SELECT template FROM template WHERE title='options'
Time before: 0.0269079208374
Time after:  0.0274391174316

--------------------------------------------------------------------------------

Query: SELECT sessionhash,userid,host,useragent,styleid FROM session WHERE lastactivity>1084301771 AND sessionhash='d9b63955b23fba096d879a7b3818cf96' AND host='24.80.170.210' AND useragent='Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;'
Time before: 0.0316779613495
Time after:  0.0320470333099

--------------------------------------------------------------------------------

Query: SELECT user.*,userfield.* FROM user LEFT JOIN userfield ON userfield.userid=user.userid WHERE user.userid='8'
Time before: 0.0323219299316
Time after:  0.033185005188

--------------------------------------------------------------------------------

Query: SELECT * FROM afterburner_stat WHERE time='1084226400'
Time before: 0.034087896347
Time after:  0.0347929000854

--------------------------------------------------------------------------------

Query: select * FROM afterburner_sessions_day WHERE userid='8'
Time before: 0.0350360870361
Time after:  0.0363850593567

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) as anzahl FROM session WHERE lastactivity >= '1084301771'
Time before: 0.0366110801697
Time after:  0.0372800827026

--------------------------------------------------------------------------------

Query: DELETE FROM afterburner_sessions_day WHERE time<'1084226400'
Time before: 0.0374920368195
Time after:  0.0390110015869

--------------------------------------------------------------------------------

Query: select * FROM afterburner_sessions_day WHERE userid='8'
Time before: 0.0391459465027
Time after:  0.0404651165009

--------------------------------------------------------------------------------

Query: SELECT templatesetid,replacementsetid,userselect FROM style WHERE styleid='1'
Time before: 0.0406899452209
Time after:  0.0410270690918

--------------------------------------------------------------------------------

Query: SELECT template,title
                          FROM template
                          WHERE (title IN ('forumhome_birthdaybit','error_nopermission','forumhome_pmloggedin','forumhome_welcometext','forumhome_logoutcode','forumhome_newposts','forumhome_todayposts','forumhome_logincode','forumhome_loggedinuser','forumhome_loggedinusers','forumhome_lastpostby','forumhome_moderator','forumhome_forumbit_level1_nopost','forumhome_forumbit_level1_post','forumhome_forumbit_level2_nopost','forumhome_forumbit_level2_post','forumhome','forumhome_unregmessage','vbproarcade_gamelinkbit','vbproarcade_gamelinks','hl_statistics','gobutton','timezone','username_loggedout','username_loggedin','phpinclude','headinclude','header','footer','forumjumpbit','forumjump','nav_linkoff','nav_linkon','navbar','nav_joiner','pagenav','pagenav_curpage','pagenav_firstlink','pagenav_lastlink','pagenav_nextlink','pagenav_pagelink','pagenav_prevlink','home_microstats','vbpopup')
                            AND (templatesetid=-1 OR templatesetid='3'))
                          OR (title = 'maxloggedin')
                          OR (title = 'birthdays')
                          ORDER BY templatesetid
Time before: 0.0413138866425
Time after:  0.0548939704895

--------------------------------------------------------------------------------

Query: SELECT template FROM template WHERE title='search_the_internet' AND (templatesetid=-1 OR templatesetid='3') ORDER BY templatesetid DESC LIMIT 1
Time before: 0.0599300861359
Time after:  0.0607349872589

--------------------------------------------------------------------------------

Query: SELECT * FROM usergroup WHERE usergroupid=6
Time before: 0.0616159439087
Time after:  0.0621058940887

--------------------------------------------------------------------------------

Query: SHOW TABLE STATUS
Time before: 0.0631830692291
Time after:  0.0751860141754

--------------------------------------------------------------------------------

Query: 
        SELECT privatemessageid, messageread, dateline, folderid
        FROM privatemessage
        WHERE userid = 8 AND fromuserid NOT IN (37607)
Time before: 0.0797200202942
Time after:  0.0813300609589

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS todayreg FROM user WHERE joindate > 1084226400
Time before: 0.083251953125
Time after:  0.668168067932

--------------------------------------------------------------------------------

Query: SELECT userid,username,posts FROM user ORDER BY posts DESC LIMIT 5
Time before: 0.668404102325
Time after:  1.37540102005

--------------------------------------------------------------------------------

Query: SELECT userid,posts,username,joindate FROM user ORDER BY joindate DESC LIMIT 5
Time before: 1.37581706047
Time after:  1.90660810471

--------------------------------------------------------------------------------

Query: SELECT thread.replycount,thread.threadid,thread.lastpost,thread.lastposter,thread.views,thread.title,user.userid FROM thread LEFT JOIN user ON (user.username=thread.lastposter) WHERE forumid NOT IN(26,27,69,82,86,113) ORDER BY lastpost DESC LIMIT 5
Time before: 1.90704011917
Time after:  2.03565001488

--------------------------------------------------------------------------------

Query: SELECT userid, username FROM user ORDER BY joindate DESC LIMIT 1
Time before: 2.03707790375
Time after:  2.65010595322

--------------------------------------------------------------------------------

Query: SELECT style.title, user.styleid, COUNT(*) AS count FROM user LEFT JOIN style USING (styleid) WHERE style.styleid IS NOT NULL AND style.userselect=1 GROUP BY user.styleid
Time before: 2.65030097961
Time after:  3.58653306961

--------------------------------------------------------------------------------

Query: SELECT template FROM template WHERE title='forumhome_dropdownbit' AND (templatesetid=-1 OR templatesetid='3') ORDER BY templatesetid DESC LIMIT 1
Time before: 3.58675289154
Time after:  3.58739495277

--------------------------------------------------------------------------------

Query: SELECT * FROM forum WHERE displayorder<>0 AND active=1 ORDER BY parentid,displayorder
Time before: 3.59092092514
Time after:  3.59282898903

--------------------------------------------------------------------------------

Query: SELECT forumid,canview,canpostnew FROM forumpermission WHERE usergroupid='6'
Time before: 3.59972810745
Time after:  3.60013508797

--------------------------------------------------------------------------------

Query: SELECT user.userid,user.username,moderator.forumid
                                  FROM moderator
                                  LEFT JOIN user
                                    ON (moderator.userid=user.userid)
                                  ORDER BY user.username
Time before: 3.60035610199
Time after:  3.60583305359

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS sessions FROM session WHERE userid=0 AND lastactivity>1084301775
Time before: 3.60757303238
Time after:  3.60820388794

--------------------------------------------------------------------------------

Query: SELECT session.useragent FROM session WHERE userid=0 AND lastactivity>1084301775
Time before: 3.60838603973
Time after:  3.60939598083

--------------------------------------------------------------------------------

Query: SELECT DISTINCT session.userid,username,invisible,usergroupid
                              FROM session
                              LEFT JOIN user ON (user.userid=session.userid)
                              WHERE session.userid>0 AND session.lastactivity>1084301775
                              ORDER BY invisible ASC, username ASC
Time before: 3.65076899529
Time after:  3.65990400314

--------------------------------------------------------------------------------

Query: SELECT findword,replaceword FROM replacement WHERE replacementsetid IN(-1,'3') ORDER BY replacementsetid DESC,replacementid DESC
Time before: 3.74667406082
Time after:  3.75431799889

--------------------------------------------------------------------------------


Page generated in 3.73481702805 seconds with 28 queries,
spending 3.56357741356 doing MySQL queries and 0.171239614487 doing PHP things.
Query: UPDATE session SET lastactivity=1084302671,location='/mpc/forum/index.php?&showqueries=1' WHERE sessionhash='d9b63955b23fba096d879a7b3818cf96' Time before: 3.77678203583 Time after: 3.77711391449 
--------------------------------------------------------------------------------
Query: UPDATE user SET lastactivity=1084302671,inforum='0' WHERE userid='8' Time before: 3.77724504471 Time after: 3.77809309959 
--------------------------------------------------------------------------------

I'm thinking that perhaps some some queries can be added to the $templatesused= part of index.php but I just wanted to make sure which ones I can safely add

This is the current $templatesused= we have in our index.php

$templatesused='forumhome_birthdaybit,error_nopermission,forumhome_pmloggedin,forumhome_welcometext,forumhome_logoutcode,forumhome_newposts,forumhome_todayposts,forumhome_logincode,forumhome_loggedinuser,forumhome_loggedinusers,forumhome_lastpostby,forumhome_moderator,forumhome_forumbit_level1_nopost,forumhome_forumbit_level1_post,forumhome_forumbit_level2_nopost,forumhome_forumbit_level2_post,forumhome,forumhome_unregmessage,vbproarcade_gamelinkbit,vbproarcade_gamelinks';
$templatesused .=',hl_statistics';

[Zachery]

What hacks are you using if any? that you know of.

You could probaly just upload the default forumhome file and revert the forumhome template.

[Xenon]

regarding the queries, i can see, you're running vb2.

i suggest to upgrade to vb3, that way, you will get rid of all hacks, and make your board faster and also more secure.

[luck777jojo]

yes we are running vb2, there are several reasons because of which we aren't really thinking of upgrading now.... one of them being that the board has over 60,000 users registered and converting that entire db to vb3 would take a while.... since we don't have another server which we can use to convert the db on while keeping the forums running it is not really an option

the hack installed on the board have also been modified quite a bit to suit our forum needs therefore loosing all of them wouldn't be something I would like very much either.... it will be done as a last resort but as of now we're not that extremely desperate.

I know that there are some people saying that adding the search queries to $templatesused= will cut down a few queries.... would it be safe to do so, are there any drawbacks, etc.

I'll try and get a list of the hacks we have installed specifically the ones affecting the forumdisplay page, and I'll post them here tomorrow or the day after.

[Zachery]

Originally Posted by luck777jojo

yes we are running vb2, there are several reasons because of which we aren't really thinking of upgrading now.... one of them being that the board has over 60,000 users registered and converting that entire db to vb3 would take a while.... since we don't have another server which we can use to convert the db on while keeping the forums running it is not really an option

the hack installed on the board have also been modified quite a bit to suit our forum needs therefore loosing all of them wouldn't be something I would like very much either.... it will be done as a last resort but as of now we're not that extremely desperate.

I know that there are some people saying that adding the search queries to $templatesused= will cut down a few queries.... would it be safe to do so, are there any drawbacks, etc.

I'll try and get a list of the hacks we have installed specifically the ones affecting the forumdisplay page, and I'll post them here tomorrow or the day after.

The longer you wait the harder it will be to upgrade. How large is your database. besides you couldnt use a backup server to upgrade and keep the live boards open there would be no way to recover the data from that time.

What hacks have you installed that you really need on vB3?

[luck777jojo]

the database is around 500 MB


the thing is that one of the most important "hack" I guess you can call it that is the intergration of our forums with the porta we have running and I don't really want to switch to running the VB3 built-in portal as it would..... well..... suck, compared to the one we have now.


about the not being able to recover the data from the time during which we're upgrading..... that's not a problem, the thing is that a large portion of our members are in their teens and, well, they won't stick around for log if we go down for a week or so while we do the upgrade, they'll come back eventually, but we will still have a dip in traffic, which is not something that we're really looking forward to.... if/when our server crashes, that is when we're gonna do the upgrade, untill then we're gonna wait, this is not just my decision this is the decision of the other admins on the board as well.

I'm just asking if there is a way to reduce the current queries we have on the board a bit, I know myself that an upgrade to vb3 would solve most of our problems but..... it is not just my decision to make.... if it were I wouldn't be here asking O.o

[Zachery]

A week?

I've upgrade some rather huge forums, not quite as large as yours, however the last one i did was about 300 megs, took us 4-5 hours to upgrade and another 3-4 to do the reindex.

What portal are you using now?

vB3 has a fairly good line for portals, and the new version of vBadvanced is due out in a few weeks here and is really powerfull compared to vBadvanded 1.0

[Xenon]

vb.org has 40k users, and a db with over 0.5 GB, and with reindexing, it took exactly 8 hours, so nothing to fear

[luck777jojo]

okay well, the vbulleti.com forums seemed alot scarier about upgrading then you guys here do... perhaps that is something we can look into then, there is just one thing, perhaps you guys can help me out with this.... our attachments on the forum are not stored in the normal vbb 2.x way, we're using a hack to compress them further so that they use less space/bandwidth, I'll find out what hack exactly we're using, it's just that is there perhaps a way to save the attachments or are they all lost if we upgrade to vb3?

[Zachery]

Not sure it depends. id try a test upgrade and see what happens, if the test upgrade is successfull, attempt the real deal