The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
#1
|
||||
|
||||
SQL Load on Server is increasing badly
I have been noting this since last 1 week, load on forums have gone very high. and it take a lot of time for a thread to get displayed, or whos online list or logging onto forums homepages properly.
here is the Online Users Load: Page generated in 0.33739090 seconds (39.90% PHP - 60.10% MySQL) with 19 queries showthread.php?p=34466#post34466 Load: Page generated in 0.25890899 seconds (55.15% PHP - 44.85% MySQL) with 18 queries Forums index.php Load: Page generated in 0.28824997 seconds (29.54% PHP - 70.46% MySQL) with 14 queries This load increases a lot when there are more then 10 users connected @ one time, and makes the entire forums go slow. Can you please help me fix this. And there are always more then 25 users :S Need some help to fix this off please Regards, Sandy... |
#2
|
||||
|
||||
What hacks are you running on your forumhome that give you that many queries? I would start there.
|
#3
|
||||
|
||||
i enabled the debug mode in config.php and now check my index.php load
Page generated in 0.19459200 seconds (46.26% PHP - 53.74% MySQL) with 20 queries wierd here is the explanation of index.php Code:
Connecting to database Time before: 0.001802921295166 Time after: 0.0022330284118652 Time taken: 0.00043010711669922 -------------------------------------------------------------------------------- Query: SELECT title, data FROM vb3_datastore WHERE title IN ('options', 'cron', 'forumcache', 'usergroupcache', 'stylecache', 'arcadesettings', 'memberoftheday', 'userstats', 'birthdaycache', 'maxloggedin', 'iconcache', 'eventcache', 'mailqueue') Time before: 0.0023479461669922 Time after: 0.0027229785919189 Time taken: 0.00037503242492676 table type possible_keys key key_len ref rows Extra vb3_datastore range PRIMARY PRIMARY 15 13 Using where -------------------------------------------------------------------------------- Parsing functions.php Time before: 0.006195068359375 Time after: 0.0094931125640869 Time taken: 0.0032980442047119 -------------------------------------------------------------------------------- Processing sessions.php Time before: 0.009800910949707 Query: SELECT * FROM vb3_session WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173' AND lastactivity > 1088125372 AND host = '203.115.83.97' AND idhash = 'b00220156f5ad4123395dfe1da00e9e3' Time before: 0.010317087173462 Time after: 0.010741949081421 Time taken: 0.00042486190795898 table type possible_keys key key_len ref rows Extra vb3_session const PRIMARY PRIMARY 32 const 1 -------------------------------------------------------------------------------- Query: SELECT userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, language.phrasegroup_holiday AS phrasegroup_holiday, language.phrasegroup_global AS phrasegroup_global, language.options AS lang_options, language.languagecode AS lang_code, language.charset AS lang_charset, language.locale AS lang_locale, language.imagesoverride AS lang_imagesoverride, language.dateoverride AS lang_dateoverride, language.timeoverride AS lang_timeoverride, language.registereddateoverride AS lang_registereddateoverride, language.calformat1override AS lang_calformat1override, language.calformat2override AS lang_calformat2override, language.logdateoverride AS lang_logdateoverride, language.decimalsep AS lang_decimalsep, language.thousandsep AS lang_thousandsep FROM vb3_user AS user INNER JOIN vb3_userfield AS userfield ON (user.userid = userfield.userid) INNER JOIN vb3_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) INNER JOIN vb3_language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid)) WHERE user.userid = 2 Time before: 0.011343002319336 Time after: 0.013185024261475 Time taken: 0.0018420219421387 table type possible_keys key key_len ref rows Extra language system PRIMARY 1 user const PRIMARY PRIMARY 4 const 1 userfield const PRIMARY PRIMARY 4 const 1 usertextfield const PRIMARY PRIMARY 4 const 1 -------------------------------------------------------------------------------- End sessions.php processing Time after: 0.015431880950928 Time taken: 0.0056309700012207 -------------------------------------------------------------------------------- Query: SELECT calendarpermission.usergroupid, calendarpermission.calendarpermissions,calendar.calendarid,calendar.title, displayorder FROM vb3_calendar AS calendar LEFT JOIN vb3_calendarpermission AS calendarpermission ON (calendarpermission.calendarid=calendar.calendarid AND usergroupid IN(15, 6)) ORDER BY displayorder ASC Time before: 0.018037080764771 Time after: 0.018287897109985 Time taken: 0.00025081634521484 table type possible_keys key key_len ref rows Extra calendar ALL 2 Using temporary; Using filesort calendarpermission range calendarid,usergroupid usergroupid 2 2 Using where -------------------------------------------------------------------------------- Query: SELECT * FROM vb3_style WHERE (styleid = 2) OR styleid = 2 ORDER BY styleid ASC LIMIT 1 Time before: 0.019032955169678 Time after: 0.019274950027466 Time taken: 0.00024199485778809 table type possible_keys key key_len ref rows Extra vb3_style system PRIMARY 1 -------------------------------------------------------------------------------- Query: SELECT title, template FROM vb3_template WHERE templateid IN (4534,4463,4208,4505,4537,4211,4538,4459,4214,4215,0,4216,4217,4218,4219,4498,4838,4835,4421,4502,4299,4310,4461,4278,4416,4419,4426,4296,4297,4298,4387,4431,4430,4437,4436,4435,4509,4434) Time before: 0.0207679271698 Time after: 0.021266937255859 Time taken: 0.00049901008605957 table type possible_keys key key_len ref rows Extra vb3_template range PRIMARY PRIMARY 4 38 Using where -------------------------------------------------------------------------------- End call of global.php: 0.028837919235229 -------------------------------------------------------------------------------- Query: SELECT COUNT(*) AS threads FROM vb3_thread WHERE dateline > '1088125948' Time before: 0.028907060623169 Time after: 0.041484117507935 Time taken: 0.012577056884766 table type possible_keys key key_len ref rows Extra vb3_thread ALL 3057 Using where -------------------------------------------------------------------------------- Query: SELECT COUNT(*) AS posts FROM vb3_post WHERE dateline > '1088125948' Time before: 0.041922092437744 Time after: 0.042051076889038 Time taken: 0.00012898445129395 table type possible_keys key key_len ref rows Extra vb3_post ALL 33119 Using where -------------------------------------------------------------------------------- Query: SELECT user.username, (user.options & 512) AS invisible, user.usergroupid, session.userid, session.inforum, session.lastactivity, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid FROM vb3_session AS session LEFT JOIN vb3_user AS user ON(user.userid = session.userid) WHERE session.lastactivity > 1088125372 ORDER BY invisible ASC, username ASC, lastactivity DESC Time before: 0.045293092727661 Time after: 0.046397924423218 Time taken: 0.0011048316955566 table type possible_keys key key_len ref rows Extra session ALL 40 Using where; Using temporary; Using filesort user eq_ref PRIMARY PRIMARY 4 session.userid 1 -------------------------------------------------------------------------------- Query: SELECT forum.forumid, lastpost, lastposter, lastthread, lastthreadid, lasticonid, threadcount, replycount FROM vb3_forum AS forum Time before: 0.048391103744507 Time after: 0.048739910125732 Time taken: 0.00034880638122559 table type possible_keys key key_len ref rows Extra forum ALL 45 -------------------------------------------------------------------------------- Query: SELECT count(*) AS posts FROM vb3_post WHERE dateline > '1088125948' Time before: 0.10724306106567 Time after: 0.1074640750885 Time taken: 0.00022101402282715 table type possible_keys key key_len ref rows Extra vb3_post ALL 33119 Using where -------------------------------------------------------------------------------- Page generated in 0.11411595 seconds with 14 queries, spending 0.020371437072754 doing MySQL queries and 0.093744512927246 doing PHP things. --------------------------------------------------------------------------------Shutdown Queries:-------------------------------------------------------------------------------- Query: UPDATE vb3_user SET lastactivity = 1088128972 WHERE userid = 2 Time before: 0.1202449798584 Time after: 0.12073993682861 Time taken: 0.00049495697021484 -------------------------------------------------------------------------------- Query: UPDATE vb3_session SET useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)', lastactivity = 1088128972, location = "/index.php?&explain=1", styleid = 0, bypass = 0 ,inforum = 0, inthread = 0, incalendar = 0, badlocation = 0 WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173' Time before: 0.12086510658264 Time after: 0.12108993530273 Time taken: 0.00022482872009277 -------------------------------------------------------------------------------- Page generated in 0.10043406 seconds (88.86% PHP - 11.14% MySQL) with 20 queries Code:
Connecting to database Time before: 0.0017991065979004 Time after: 0.0022170543670654 Time taken: 0.00041794776916504 -------------------------------------------------------------------------------- Query: SELECT title, data FROM vb3_datastore WHERE title IN ('options', 'cron', 'forumcache', 'usergroupcache', 'stylecache', 'arcadesettings', 'maxloggedin', 'wol_spiders') Time before: 0.0023200511932373 Time after: 0.0032210350036621 Time taken: 0.0009009838104248 table type possible_keys key key_len ref rows Extra vb3_datastore range PRIMARY PRIMARY 15 8 Using where -------------------------------------------------------------------------------- Parsing functions.php Time before: 0.0070469379425049 Time after: 0.010152101516724 Time taken: 0.0031051635742188 -------------------------------------------------------------------------------- Processing sessions.php Time before: 0.010451078414917 Query: SELECT * FROM vb3_session WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173' AND lastactivity > 1088125564 AND host = '203.115.83.97' AND idhash = 'b00220156f5ad4123395dfe1da00e9e3' Time before: 0.010966062545776 Time after: 0.011430025100708 Time taken: 0.00046396255493164 table type possible_keys key key_len ref rows Extra vb3_session const PRIMARY PRIMARY 32 const 1 -------------------------------------------------------------------------------- Query: SELECT userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, language.phrasegroup_wol AS phrasegroup_wol, language.phrasegroup_global AS phrasegroup_global, language.options AS lang_options, language.languagecode AS lang_code, language.charset AS lang_charset, language.locale AS lang_locale, language.imagesoverride AS lang_imagesoverride, language.dateoverride AS lang_dateoverride, language.timeoverride AS lang_timeoverride, language.registereddateoverride AS lang_registereddateoverride, language.calformat1override AS lang_calformat1override, language.calformat2override AS lang_calformat2override, language.logdateoverride AS lang_logdateoverride, language.decimalsep AS lang_decimalsep, language.thousandsep AS lang_thousandsep FROM vb3_user AS user INNER JOIN vb3_userfield AS userfield ON (user.userid = userfield.userid) INNER JOIN vb3_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) INNER JOIN vb3_language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid)) WHERE user.userid = 2 Time before: 0.012029886245728 Time after: 0.013887882232666 Time taken: 0.0018579959869385 table type possible_keys key key_len ref rows Extra language system PRIMARY 1 user const PRIMARY PRIMARY 4 const 1 userfield const PRIMARY PRIMARY 4 const 1 usertextfield const PRIMARY PRIMARY 4 const 1 -------------------------------------------------------------------------------- End sessions.php processing Time after: 0.016139030456543 Time taken: 0.005687952041626 -------------------------------------------------------------------------------- Query: SELECT calendarpermission.usergroupid, calendarpermission.calendarpermissions,calendar.calendarid,calendar.title, displayorder FROM vb3_calendar AS calendar LEFT JOIN vb3_calendarpermission AS calendarpermission ON (calendarpermission.calendarid=calendar.calendarid AND usergroupid IN(15, 6)) ORDER BY displayorder ASC Time before: 0.019282102584839 Time after: 0.01952600479126 Time taken: 0.0002439022064209 table type possible_keys key key_len ref rows Extra calendar ALL 2 Using temporary; Using filesort calendarpermission range calendarid,usergroupid usergroupid 2 2 Using where -------------------------------------------------------------------------------- Query: SELECT * FROM vb3_style WHERE (styleid = 2) OR styleid = 2 ORDER BY styleid ASC LIMIT 1 Time before: 0.020247936248779 Time after: 0.020488023757935 Time taken: 0.00024008750915527 table type possible_keys key key_len ref rows Extra vb3_style system PRIMARY 1 -------------------------------------------------------------------------------- Query: SELECT title, template FROM vb3_template WHERE templateid IN (4206,0,4230,4231,4233,4238,4540,4512,4838,4835,4421,4502,4299,4310,4461,4278,4416,4419,4426,4296,4297,4298,4387,4431,4430,4437,4436,4435,4509,4434) Time before: 0.02186393737793 Time after: 0.022325038909912 Time taken: 0.00046110153198242 table type possible_keys key key_len ref rows Extra vb3_template range PRIMARY PRIMARY 4 30 Using where -------------------------------------------------------------------------------- End call of global.php: 0.029645919799805 -------------------------------------------------------------------------------- Query: SELECT COUNT(*) AS threads FROM vb3_thread WHERE dateline > '1088125948' Time before: 0.029716014862061 Time after: 0.029887914657593 Time taken: 0.00017189979553223 table type possible_keys key key_len ref rows Extra vb3_thread ALL 3057 Using where -------------------------------------------------------------------------------- Query: SELECT COUNT(*) AS posts FROM vb3_post WHERE dateline > '1088125948' Time before: 0.030333042144775 Time after: 0.030464887619019 Time taken: 0.00013184547424316 table type possible_keys key key_len ref rows Extra vb3_post ALL 33119 Using where -------------------------------------------------------------------------------- Query: SELECT user.username, session.useragent, session.location, session.lastactivity, user.userid, user.options, session.host, session.badlocation, session.incalendar, user.aim, user.icq, user.msn, user.yahoo, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid FROM vb3_session AS session LEFT JOIN vb3_user AS user USING (userid) WHERE session.lastactivity > 1088125564 ORDER BY user.username asc Time before: 0.032867908477783 Time after: 0.034101009368896 Time taken: 0.0012331008911133 table type possible_keys key key_len ref rows Extra session ALL 35 Using where; Using temporary; Using filesort user eq_ref PRIMARY PRIMARY 4 vbdb_f1nalDbaS3.session.userid 1 -------------------------------------------------------------------------------- Query: SELECT DISTINCT userid FROM vb3_moderator Time before: 0.034611940383911 Time after: 0.034775018692017 Time taken: 0.00016307830810547 table type possible_keys key key_len ref rows Extra vb3_moderator index userid 6 76 Using index -------------------------------------------------------------------------------- Query: SELECT shortname,title,gamesettings,highscorerid,highscore,miniimage,gameid FROM vb3_games Time before: 0.03780198097229 Time after: 0.038661956787109 Time taken: 0.00085997581481934 table type possible_keys key key_len ref rows Extra vb3_games ALL 180 -------------------------------------------------------------------------------- Query: SELECT threadid, postid FROM vb3_post WHERE postid IN (0,34597,32579,34752,32579) Time before: 0.069528102874756 Time after: 0.069756031036377 Time taken: 0.00022792816162109 table type possible_keys key key_len ref rows Extra vb3_post range PRIMARY PRIMARY 4 4 Using where -------------------------------------------------------------------------------- Query: SELECT thread.title, thread.threadid, thread.forumid, thread.postuserid, thread.visible, NOT ISNULL(deletionlog.primaryid) AS isdeleted , NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed FROM vb3_thread AS thread LEFT JOIN vb3_deletionlog AS deletionlog ON (thread.threadid = deletionlog.primaryid AND type = 'thread') LEFT JOIN vb3_subscribethread AS subscribethread ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = 2) WHERE thread.threadid IN (0,2399,2028,2028,2028,3403,0,3044,2337,3044,2586,2028,2028,2244,3390,3403) Time before: 0.070334911346436 Time after: 0.07055401802063 Time taken: 0.00021910667419434 table type possible_keys key key_len ref rows Extra thread range PRIMARY PRIMARY 4 9 Using where deletionlog eq_ref PRIMARY PRIMARY 5 thread.threadid,const 1 Using index subscribethread eq_ref subscribeindex,threadid subscribeindex 8 const,thread.threadid 1 Using where -------------------------------------------------------------------------------- Query: SELECT pmtext.fromuserid, pm.pmid FROM vb3_pm AS pm LEFT JOIN vb3_pmtext AS pmtext ON (pm.pmtextid = pmtext.pmtextid) WHERE pmid IN (0,4589) Time before: 0.07145094871521 Time after: 0.071608066558838 Time taken: 0.00015711784362793 table type possible_keys key key_len ref rows Extra pm range PRIMARY PRIMARY 4 2 Using where pmtext eq_ref PRIMARY PRIMARY 4 pm.pmtextid 1 -------------------------------------------------------------------------------- Query: SELECT searchid, search.userid, query, searchuser, user.userid AS targetuserid FROM vb3_search AS search LEFT JOIN vb3_user AS user ON (user.username = search.searchuser) WHERE searchid IN (0,19364,19359,19362) Time before: 0.072097063064575 Time after: 0.072566986083984 Time taken: 0.00046992301940918 table type possible_keys key key_len ref rows Extra search range PRIMARY PRIMARY 4 4 Using where user ref username username 50 search.searchuser 1 -------------------------------------------------------------------------------- Query: SELECT userid, username, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid FROM vb3_user AS user WHERE userid IN (0,118,378) Time before: 0.073144912719727 Time after: 0.073514938354492 Time taken: 0.00037002563476562 table type possible_keys key key_len ref rows Extra user range PRIMARY PRIMARY 4 3 Using where -------------------------------------------------------------------------------- Query: SELECT forum.forumid, lastpost, lastposter, lastthread, lastthreadid, lasticonid, threadcount, replycount FROM vb3_forum AS forum Time before: 0.093667030334473 Time after: 0.094094038009644 Time taken: 0.0004270076751709 table type possible_keys key key_len ref rows Extra forum ALL 45 -------------------------------------------------------------------------------- Page generated in 0.10758400 seconds with 20 queries, spending 0.01093578338623 doing MySQL queries and 0.09664821661377 doing PHP things. --------------------------------------------------------------------------------Shutdown Queries:-------------------------------------------------------------------------------- Query: UPDATE vb3_user SET lastactivity = 1088129164 WHERE userid = 2 Time before: 0.11364102363586 Time after: 0.11438608169556 Time taken: 0.00074505805969238 -------------------------------------------------------------------------------- Query: UPDATE vb3_session SET useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)', lastactivity = 1088129164, location = "/online.php?pp=30&explain=1", styleid = 0, bypass = 0 ,inforum = 0, inthread = 0, incalendar = 0, badlocation = 0 WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173' Time before: 0.11453795433044 Time after: 0.114825963974 Time taken: 0.00028800964355469 -------------------------------------------------------------------------------- Page generated in 0.22897100 seconds (67.33% PHP - 32.67% MySQL) with 18 queries Code:
Connecting to database Time before: 0.0017850399017334 Time after: 0.0022079944610596 Time taken: 0.00042295455932617 -------------------------------------------------------------------------------- Query: SELECT title, data FROM vb3_datastore WHERE title IN ('options', 'cron', 'forumcache', 'usergroupcache', 'stylecache', 'arcadesettings', 'rankphp', 'smiliecache', 'bbcodecache', 'mailqueue', 'hidprofilecache') Time before: 0.0023159980773926 Time after: 0.0027599334716797 Time taken: 0.00044393539428711 table type possible_keys key key_len ref rows Extra vb3_datastore range PRIMARY PRIMARY 15 11 Using where -------------------------------------------------------------------------------- Parsing functions.php Time before: 0.0077250003814697 Time after: 0.010399103164673 Time taken: 0.0026741027832031 -------------------------------------------------------------------------------- Processing sessions.php Time before: 0.010664939880371 Query: SELECT * FROM vb3_session WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173' AND lastactivity > 1088125716 AND host = '203.115.83.97' AND idhash = 'b00220156f5ad4123395dfe1da00e9e3' Time before: 0.011117935180664 Time after: 0.01154088973999 Time taken: 0.00042295455932617 table type possible_keys key key_len ref rows Extra vb3_session const PRIMARY PRIMARY 32 const 1 -------------------------------------------------------------------------------- Query: SELECT userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, language.phrasegroup_posting AS phrasegroup_posting, language.phrasegroup_postbit AS phrasegroup_postbit, language.phrasegroup_showthread AS phrasegroup_showthread, language.phrasegroup_global AS phrasegroup_global, language.options AS lang_options, language.languagecode AS lang_code, language.charset AS lang_charset, language.locale AS lang_locale, language.imagesoverride AS lang_imagesoverride, language.dateoverride AS lang_dateoverride, language.timeoverride AS lang_timeoverride, language.registereddateoverride AS lang_registereddateoverride, language.calformat1override AS lang_calformat1override, language.calformat2override AS lang_calformat2override, language.logdateoverride AS lang_logdateoverride, language.decimalsep AS lang_decimalsep, language.thousandsep AS lang_thousandsep FROM vb3_user AS user INNER JOIN vb3_userfield AS userfield ON (user.userid = userfield.userid) INNER JOIN vb3_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) INNER JOIN vb3_language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid)) WHERE user.userid = 2 Time before: 0.012130975723267 Time after: 0.013999938964844 Time taken: 0.0018689632415771 table type possible_keys key key_len ref rows Extra language system PRIMARY 1 user const PRIMARY PRIMARY 4 const 1 userfield const PRIMARY PRIMARY 4 const 1 usertextfield const PRIMARY PRIMARY 4 const 1 -------------------------------------------------------------------------------- End sessions.php processing Time after: 0.01623010635376 Time taken: 0.0055651664733887 -------------------------------------------------------------------------------- Query: SELECT NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason, NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed, emailupdate, folderid, thread.* FROM vb3_thread AS thread LEFT JOIN vb3_deletionlog AS deletionlog ON (deletionlog.primaryid = thread.threadid AND deletionlog.type = 'thread') LEFT JOIN vb3_subscribethread AS subscribethread ON (subscribethread.threadid = thread.threadid AND subscribethread.userid = 2) WHERE thread.threadid = 3372 Time before: 0.01953911781311 Time after: 0.019848108291626 Time taken: 0.00030899047851562 table type possible_keys key key_len ref rows Extra thread const PRIMARY PRIMARY 4 const 1 deletionlog const PRIMARY PRIMARY 5 const,const 0 unique row not found subscribethread const subscribeindex,threadid subscribeindex 8 const,const 0 unique row not found -------------------------------------------------------------------------------- Query: SELECT * FROM vb3_style WHERE (styleid = 2) OR styleid = 2 ORDER BY styleid ASC LIMIT 1 Time before: 0.020880937576294 Time after: 0.021111011505127 Time taken: 0.00023007392883301 table type possible_keys key key_len ref rows Extra vb3_style system PRIMARY 1 -------------------------------------------------------------------------------- Query: SELECT title, template FROM vb3_template WHERE templateid IN (4204,4417,4230,4231,4233,4238,4441,4325,4326,4328,4327,4329,0,4330,4331,4819,4334,4335,4822,4144,4823,4146,4467,4356,4359,4360,4357,4318,4317,4319,4323,4321,4469,4838,4835,4421,4502,4299,4310,4461,4278,4416,4419,4426,4296,4297,4298,4387,4431,4430,4437,4436,4435,4509,4434) Time before: 0.02262806892395 Time after: 0.023199081420898 Time taken: 0.00057101249694824 table type possible_keys key key_len ref rows Extra vb3_template range PRIMARY PRIMARY 4 55 Using where -------------------------------------------------------------------------------- End call of global.php: 0.030760049819946 -------------------------------------------------------------------------------- Query: SELECT COUNT(*) AS threads FROM vb3_thread WHERE dateline > '1088125948' Time before: 0.030827045440674 Time after: 0.030998945236206 Time taken: 0.00017189979553223 table type possible_keys key key_len ref rows Extra vb3_thread ALL 3057 Using where -------------------------------------------------------------------------------- Query: SELECT COUNT(*) AS posts FROM vb3_post WHERE dateline > '1088125948' Time before: 0.031389951705933 Time after: 0.031514883041382 Time taken: 0.00012493133544922 table type possible_keys key key_len ref rows Extra vb3_post ALL 33119 Using where -------------------------------------------------------------------------------- Query: SELECT shortname,title,gamesettings,highscorerid,highscore,miniimage,gameid FROM vb3_games Time before: 0.034015893936157 Time after: 0.034852027893066 Time taken: 0.00083613395690918 table type possible_keys key key_len ref rows Extra vb3_games ALL 180 -------------------------------------------------------------------------------- Query: SELECT forum.forumid, lastpost, lastposter, lastthread, lastthreadid, lasticonid, threadcount, replycount FROM vb3_forum AS forum Time before: 0.043256044387817 Time after: 0.043632030487061 Time taken: 0.00037598609924316 table type possible_keys key key_len ref rows Extra forum ALL 45 -------------------------------------------------------------------------------- Query: SELECT user.* FROM vb3_user AS user WHERE userid IN (2,304,118,333,599,85,6,184,572,130,516,164,150,65,198,76,159,28,38,465,413,136,5,41,276,89,493,75,176,193,95,103) ORDER BY username Time before: 0.052366018295288 Time after: 0.054105997085571 Time taken: 0.0017399787902832 table type possible_keys key key_len ref rows Extra user range PRIMARY PRIMARY 4 32 Using where; Using filesort -------------------------------------------------------------------------------- Query: SELECT * FROM vb3_poll WHERE pollid = 80 Time before: 0.058939933776855 Time after: 0.059132099151611 Time taken: 0.00019216537475586 table type possible_keys key key_len ref rows Extra vb3_poll const PRIMARY PRIMARY 4 const 1 -------------------------------------------------------------------------------- Query: SELECT voteoption FROM vb3_pollvote WHERE userid = 2 AND pollid = 80 Time before: 0.066688060760498 Time after: 0.066867113113403 Time taken: 0.00017905235290527 table type possible_keys key key_len ref rows Extra vb3_pollvote ref userid userid 8 const,const 1 Using where -------------------------------------------------------------------------------- Query: SELECT postid, NOT ISNULL(deletionlog.primaryid) AS isdeleted FROM vb3_post AS post LEFT JOIN vb3_deletionlog AS deletionlog ON(post.postid = deletionlog.primaryid AND type = 'post') WHERE threadid = 3372 AND visible = 1 ORDER BY dateline Time before: 0.075968980789185 Time after: 0.076198101043701 Time taken: 0.0002291202545166 table type possible_keys key key_len ref rows Extra post ref threadid threadid 4 const 17 Using where; Using filesort deletionlog eq_ref PRIMARY PRIMARY 5 post.postid,const 1 Using index -------------------------------------------------------------------------------- Query: SELECT post.*, post.username AS postusername, post.ipaddress AS ip, user.*, userfield.*, usertextfield.*, icon.title as icontitle, icon.iconpath, avatar.avatarpath, NOT ISNULL(customavatar.avatardata) AS hascustomavatar, customavatar.dateline AS avatardateline, level, NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason, editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline, editlog.reason AS edit_reason, post_parsed.pagetext_html, post_parsed.hasimages, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid FROM vb3_post AS post LEFT JOIN vb3_user AS user ON(user.userid = post.userid) LEFT JOIN vb3_userfield AS userfield ON(userfield.userid = user.userid) LEFT JOIN vb3_usertextfield AS usertextfield ON(usertextfield.userid = user.userid) LEFT JOIN vb3_icon AS icon ON(icon.iconid = post.iconid) LEFT JOIN vb3_avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN vb3_customavatar AS customavatar ON(customavatar.userid = user.userid) LEFT JOIN vb3_reputationlevel AS reputationlevel ON(user.reputationlevelid = reputationlevel.reputationlevelid) LEFT JOIN vb3_deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND deletionlog.type = 'post') LEFT JOIN vb3_editlog AS editlog ON(editlog.postid = post.postid) LEFT JOIN vb3_post_parsed AS post_parsed ON(post_parsed.postid = post.postid) WHERE post.postid IN (0,34383,34392,34413,34447,34482,34483,34487,34489,34491,34493,34548,34555,34577,34578) ORDER BY dateline Time before: 0.077088117599487 Time after: 0.080060958862305 Time taken: 0.0029728412628174 table type possible_keys key key_len ref rows Extra post range PRIMARY PRIMARY 4 15 Using where; Using filesort user eq_ref PRIMARY PRIMARY 4 post.userid 1 userfield eq_ref PRIMARY PRIMARY 4 user.userid 1 usertextfield eq_ref PRIMARY PRIMARY 4 user.userid 1 icon eq_ref PRIMARY PRIMARY 2 post.iconid 1 avatar eq_ref PRIMARY PRIMARY 2 user.avatarid 1 customavatar eq_ref PRIMARY PRIMARY 4 user.userid 1 reputationlevel eq_ref PRIMARY PRIMARY 4 user.reputationlevelid 1 deletionlog eq_ref PRIMARY PRIMARY 5 post.postid,const 1 editlog eq_ref PRIMARY PRIMARY 4 post.postid 1 post_parsed eq_ref PRIMARY PRIMARY 4 post.postid 1 -------------------------------------------------------------------------------- Time after parsing all posts: 0.15415096282959 -------------------------------------------------------------------------------- Page generated in 0.16847706 seconds with 18 queries, spending 0.012998104095459 doing MySQL queries and 0.15547895590454 doing PHP things. --------------------------------------------------------------------------------Shutdown Queries:-------------------------------------------------------------------------------- Query: UPDATE vb3_user SET lastactivity = 1088129316 WHERE userid = 2 Time before: 0.17586708068848 Time after: 0.17638993263245 Time taken: 0.00052285194396973 -------------------------------------------------------------------------------- Query: UPDATE vb3_session SET useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)', lastactivity = 1088129316, location = "/showthread.php?t=3372&explain=1", styleid = 0, bypass = 0 ,inforum = 45, inthread = 3372, incalendar = 0, badlocation = 0 WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173' Time before: 0.17650890350342 Time after: 0.1767430305481 Time taken: 0.00023412704467773 -------------------------------------------------------------------------------- Query: INSERT INTO vb3_threadviews (threadid,+++++adid,+++++adtime) VALUES (3372, 2, '1088129316') Time before: 0.17688894271851 Time after: 0.17707800865173 Time taken: 0.00018906593322754 -------------------------------------------------------------------------------- |
#4
|
||||
|
||||
Quote:
|
#5
|
||||
|
||||
index.php != forumhome necessarily Zach Also the functions used on forumhome won't be in the forums root file either What we need to know is what mods have you added which affect/change the functionality of how your forums index page work
|
#6
|
||||
|
||||
Quote:
i had tried couple of hacks in recent time. here is my list of hacks again i am going for a long break, and uninstalling the stuff, which mite create problems for rest of people to manage it Code:
vb3 Statistic Version 1.01 v3 Arcade - Games Arcade & Challenge System [vB 3.0.1] Refer Friend How many threads and posts since your last visit! Debug-mode in Admin-CP and for super admins only New User Welcome PM [v2] [HTL] Hack Tracking Log -> will uninstall this not of any use for me [vbcron] Automatic database backup [v1.3] -> (still not working) Profile-Views [VB3 RC3] Who viewed this thread? -> (this mite be cozing problems) Download Forum Thread To Your Computer (Vb3.x) Who have you given Reputation to? . BB|Messenger v1.2 vb2 & v1.3 vb3 VB3 MicroStats -> (a few queries mite be added of this too) vbPopup for vB3 [VB3 RC3] Reputation in member's info Delete a post with only one button (in postbit) -> (a few queries mite be added of this too) [VB3 RC3] Member of the day -> (a few queries mite be added of this too) Advanced Editing Options For Mods & Admins those are the hacks, installed and adding the load to server. :S |
#7
|
||||
|
||||
may i bump this again ?
can some sql gurus help me ease my load on forums. Thnx. Sandy... |
#8
|
||||
|
||||
How many threads and posts since your last visit!
BB|Messenger v1.2 vb2 & v1.3 vb3 [VB3 RC3] Member of the day -> (a few queries mite be added of this too) These are the ones that are probaly cuasing the biggest problems |
#9
|
||||
|
||||
The new/threads post mod is notorius for adding server load as in essence. I don't know if its in the installation for the vB3 version of this mod but on the previous one it was reccomended to add an index on the postid column somewhere. Can't remember where though sorry
|
#10
|
||||
|
||||
Quote:
and BB messager imporply setup can be very server intensive., |
Thread Tools | |
Display Modes | |
|
|
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|