Topic: Board running slooooooooooow!!

[Ben_S]

Oops sorry, also check the "Max width of posted pictures" option and if its enabled set it to 0, same for height.

[mystik]

what is "Enable compressed output"  ....its enabled for me..

[Ben_S]

gzip, in 99.9% of cases you want compressed output on.

[mystik]

k....

sigh...doesnt look like there is much hope for me then huh

[Ben_S]

Can you add

$db_show_debug = true;

To your Settings.php file and see what query is taking an age on that page?

* Ben_S hopes $db_show_debug = true; still exists in the relase ver.

[mystik]

will that make all my users see the queries?......or will only admin see them?

[Ben_S]

Only admins, assuming it exists still.

[mystik]

sweeeeet it worked!!!


 SELECT
   IFNULL(lo.logTime, 0) AS isOnline, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, mem.signature,
   mem.personalText, mem.location, mem.gender, mem.avatar, mem.ID_MEMBER, mem.memberName, mem.realName,
   mem.emailAddress, mem.hideEmail, mem.dateRegistered, mem.websiteTitle, mem.websiteUrl, mem.birthdate,
   mem.memberIP, mem.location, mem.ICQ, mem.AIM, mem.YIM, mem.MSN, mem.posts, mem.lastLogin, mem.karmaGood,
   mem.ID_POST_GROUP, mem.karmaBad, mem.lngfile, mem.ID_GROUP, mem.timeOffset, mem.showOnline,
   mg.onlineColor AS member_group_color, IFNULL(mg.groupName, '') AS member_group,
   pg.onlineColor AS post_group_color, IFNULL(pg.groupName, '') AS post_group,
   IF((mem.ID_GROUP = 0 OR mg.stars = ''), pg.stars, mg.stars) AS stars,
   mem.usertitle
FROM wheels_forummembers AS mem
   LEFT JOIN wheels_forumlog_online AS lo ON (lo.ID_MEMBER = mem.ID_MEMBER)
   LEFT JOIN wheels_forumattachments AS a ON (a.ID_MEMBER = mem.ID_MEMBER)
   LEFT JOIN wheels_forummembergroups AS pg ON (pg.ID_GROUP = mem.ID_POST_GROUP)
   LEFT JOIN wheels_forummembergroups AS mg ON (mg.ID_GROUP = mem.ID_GROUP)
WHERE mem.ID_MEMBER IN ('3', '1974', '2673', '2250', '994', '246', '2972', '5555', '4086', '4189', '4544', '4821')
in /home/httpd/vhosts/wheelsjamaica.com/httpdocs/wheels_forum/Sources/Load.php line 513, which took 5.8163599967957 seconds.

SELECT *
FROM wheels_forumthemes
WHERE ID_MEMBER IN (3, 246, 994, 1974, 2250, 2673, 2972, 4086, 4189, 4544, 4821, 5555)
in /home/httpd/vhosts/wheelsjamaica.com/httpdocs/wheels_forum/Sources/Load.php line 528, which took 1.0223820209503 seconds.

SELECT
   ID_MSG, icon, subject, posterTime, posterIP, ID_MEMBER, modifiedTime, modifiedName, body,
   smileysEnabled, posterName, posterEmail,
   (GREATEST(posterTime, modifiedTime) > 1109035956) AS isRead
FROM wheels_forummessages
WHERE ID_MSG IN (290658,290660,290661,290666,290691,290696,290697,290701,290709,290711,290713,290715,290717,290722,290727)
ORDER BY ID_MSG
in /home/httpd/vhosts/wheelsjamaica.com/httpdocs/wheels_forum/Sources/Display.php line 581, which took 1.2067539691925 seconds.


 SELECT code, filename, description
FROM wheels_forumsmileys
ORDER BY LENGTH(code) DESC
in /home/httpd/vhosts/wheelsjamaica.com/httpdocs/wheels_forum/Sources/Subs.php line 1112, which took 0.30808591842651 seconds.




everything else was .000....

[mystik]

 SELECT
   IFNULL(lo.logTime, 0) AS isOnline, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, mem.signature,
   mem.personalText, mem.location, mem.gender, mem.avatar, mem.ID_MEMBER, mem.memberName, mem.realName,
   mem.emailAddress, mem.hideEmail, mem.dateRegistered, mem.websiteTitle, mem.websiteUrl, mem.birthdate,
   mem.memberIP, mem.location, mem.ICQ, mem.AIM, mem.YIM, mem.MSN, mem.posts, mem.lastLogin, mem.karmaGood,
   mem.ID_POST_GROUP, mem.karmaBad, mem.lngfile, mem.ID_GROUP, mem.timeOffset, mem.showOnline,
   mg.onlineColor AS member_group_color, IFNULL(mg.groupName, '') AS member_group,
   pg.onlineColor AS post_group_color, IFNULL(pg.groupName, '') AS post_group,
   IF((mem.ID_GROUP = 0 OR mg.stars = ''), pg.stars, mg.stars) AS stars,
   mem.usertitle
FROM wheels_forummembers AS mem
   LEFT JOIN wheels_forumlog_online AS lo ON (lo.ID_MEMBER = mem.ID_MEMBER)
   LEFT JOIN wheels_forumattachments AS a ON (a.ID_MEMBER = mem.ID_MEMBER)
   LEFT JOIN wheels_forummembergroups AS pg ON (pg.ID_GROUP = mem.ID_POST_GROUP)
   LEFT JOIN wheels_forummembergroups AS mg ON (mg.ID_GROUP = mem.ID_GROUP)
WHERE mem.ID_MEMBER IN ('3', '1974', '2673', '2250', '994', '246', '2972', '5555', '4086', '4189', '4544', '4821')
in /home/httpd/vhosts/wheelsjamaica.com/httpdocs/wheels_forum/Sources/Load.php line 513, which took 8.2671701908112 seconds.



ok verdict:   thats the problem query..everytime

what exactly is this query getting?....if ime not mistaken is it the info need to identify the member (online status, post group, num of posts etc) on the left side of the Thread view?


and by the way.....all users could see the debug links.....i tested...no harm done tho....

[mystik]

guys ime gonna leave office now.....ile be back online in abt 20 mins from now...pleease dont abandon me....support has been excellent thus far and i feel like we are getting somewhere ...thx again....brb

[[Unknown]]

Can you run this query and runs its results?

Code: [Select]

EXPLAIN SELECT
   IFNULL(lo.logTime, 0) AS isOnline, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, mem.signature,
   mem.personalText, mem.location, mem.gender, mem.avatar, mem.ID_MEMBER, mem.memberName, mem.realName,
   mem.emailAddress, mem.hideEmail, mem.dateRegistered, mem.websiteTitle, mem.websiteUrl, mem.birthdate,
   mem.memberIP, mem.location, mem.ICQ, mem.AIM, mem.YIM, mem.MSN, mem.posts, mem.lastLogin, mem.karmaGood,
   mem.ID_POST_GROUP, mem.karmaBad, mem.lngfile, mem.ID_GROUP, mem.timeOffset, mem.showOnline,
   mg.onlineColor AS member_group_color, IFNULL(mg.groupName, '') AS member_group,
   pg.onlineColor AS post_group_color, IFNULL(pg.groupName, '') AS post_group,
   IF((mem.ID_GROUP = 0 OR mg.stars = ''), pg.stars, mg.stars) AS stars,
   mem.usertitle
FROM wheels_forummembers AS mem
   LEFT JOIN wheels_forumlog_online AS lo ON (lo.ID_MEMBER = mem.ID_MEMBER)
   LEFT JOIN wheels_forumattachments AS a ON (a.ID_MEMBER = mem.ID_MEMBER)
   LEFT JOIN wheels_forummembergroups AS pg ON (pg.ID_GROUP = mem.ID_POST_GROUP)
   LEFT JOIN wheels_forummembergroups AS mg ON (mg.ID_GROUP = mem.ID_GROUP)
WHERE mem.ID_MEMBER IN ('3', '1974', '2673', '2250', '994', '246', '2972', '5555', '4086', '4189', '4544', '4821')
-[Unknown]

[mystik]

[[Unknown]]

From that, it's clear that attachments is causing the problems.  Why, though?

Can you try the following, please?

ALTER TABLE wheels_forumattachments
TYPE = InnoDB;
ANALYZE wheels_forumattachments;
OPTIMIZE wheels_forumattachments;

The ALTER will make this table InnoDB too.  InnoDB is good for tables that get written to a lot, and attachments is one that does... the default is MyISAM, which isn't as good at often-written tables, but makes reads a bit better (don't worry, in this case it should be better as InnoDB, and we use InnoDB for attachments here.)

The second and third should tell MySQL to give another hard look at the indexes on the attachments table, essentially.  Hopefully this will convince it to actually use them .

-[Unknown]

[mystik]

the Innodb conversion worked but u iget mysql erros on both of the other statemnents

 MySQL said: Documentation
#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'wheels_forumattachments' at line 1

but the thread is super fast now!!!! 

http://www.wheelsjamaica.com/wheels_forum/index.php?topic=21221.0

[mystik]

 SELECT
   IFNULL(lo.logTime, 0) AS isOnline, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, mem.signature,
   mem.personalText, mem.location, mem.gender, mem.avatar, mem.ID_MEMBER, mem.memberName, mem.realName,
   mem.emailAddress, mem.hideEmail, mem.dateRegistered, mem.websiteTitle, mem.websiteUrl, mem.birthdate,
   mem.memberIP, mem.location, mem.ICQ, mem.AIM, mem.YIM, mem.MSN, mem.posts, mem.lastLogin, mem.karmaGood,
   mem.ID_POST_GROUP, mem.karmaBad, mem.lngfile, mem.ID_GROUP, mem.timeOffset, mem.showOnline,
   mg.onlineColor AS member_group_color, IFNULL(mg.groupName, '') AS member_group,
   pg.onlineColor AS post_group_color, IFNULL(pg.groupName, '') AS post_group,
   IF((mem.ID_GROUP = 0 OR mg.stars = ''), pg.stars, mg.stars) AS stars,
   mem.usertitle
FROM wheels_forummembers AS mem
   LEFT JOIN wheels_forumlog_online AS lo ON (lo.ID_MEMBER = mem.ID_MEMBER)
   LEFT JOIN wheels_forumattachments AS a ON (a.ID_MEMBER = mem.ID_MEMBER)
   LEFT JOIN wheels_forummembergroups AS pg ON (pg.ID_GROUP = mem.ID_POST_GROUP)
   LEFT JOIN wheels_forummembergroups AS mg ON (mg.ID_GROUP = mem.ID_GROUP)
WHERE mem.ID_MEMBER IN ('3', '1974', '2673', '2250', '994', '246', '2972', '5555', '4086', '4189', '4544', '4821')
in /home/httpd/vhosts/wheelsjamaica.com/httpdocs/wheels_forum/Sources/Load.php line 513, which took 0.0019481182098389 seconds.




big improvement!

[[Unknown]]

Oh, alright.  I missed the word TABLE, but if it works now, I'm glad.

-[Unknown]

[mystik]

ok i ran the Analyse and the Optimse..

Thx a million guys....board is lightning fast again....

this thread should help a lot of ppl so i will leave the images up for a while.....

thx again