Simple Machines Community Forum

SMF Support => Server Performance and Configuration => Topic started by: mystik on February 21, 2005, 03:31:52 PM

Title: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 03:31:52 PM
as of lately my SMF has been running slowly.....pages suddenly starting to take longer to generate...

....i have attached two screenshots : one showing a snapshot of my Mysql prscesses at an instance where the board was running slowly and the other showing the page i was loading (which incidentaly is a thread with only one post) and the time taken to generate that page (146 seconds)  :-[ :


(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.wheelsjamaica.com%2Ftroy%2F01.gif&hash=1ecf3603bc813a5771557fbee76e4775)


(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.wheelsjamaica.com%2Ftroy%2F02.gif&hash=74b12f3919df4c223e6c1f16f0fb7307)


Pleeeeease help...i can afford for my board to be this slow periodically.....
Title: Re: Board running slooooooooooow!!
Post by: [Unknown] on February 21, 2005, 03:35:13 PM
A query taking 55 seconds on the log_online table?!?!

That doesn't even look like an SMF query.  Where did it come from?  Do you have any mods installed or anything?

Try converting log_online to InnoDB instead of MyISAM.

-[Unknown]
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 03:45:46 PM
yeh i have a few mods......heres di list...wat do u think it mite be a Mod?

(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fhttp%3A%2F%2Fwww.wheelsjamaica.com%2Ftroy%2Fmods.gif&hash=f581d42d10b93dcfdb561cd1e70669fc)
Title: Re: Board running slooooooooooow!!
Post by: [Unknown] on February 21, 2005, 03:50:09 PM
Very much so, that query is really eating your forum.  It's the problem right there - I'm not sure which mod it would be, though.

Online users today, perhaps?

-[Unknown]
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 03:52:22 PM
sorry abt that..just realised the list didnt show....


(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.wheelsjamaica.com%2Ftroy%2Fmods.gif&hash=2a71984c4de47db8fed4c534a6d0e64e)


hmm....maybe i should remove the last few mods i installed and see what happens....that would be the Location thing..... >:(

what do u think?
Title: Re: Board running slooooooooooow!!
Post by: [Unknown] on February 21, 2005, 03:55:00 PM
Could be the location mod, I suppose.  That could be the Who's online query, now that I look at it again...

What if you disable who's online temporarily?

By the way, if you see something going off like that, at 50+ seconds with everything else locked, I suggest you KILL it.

-[Unknown]
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 05:54:48 PM
well i removed it and it the board seems to be moving a Tad faster.....

are there any other tips u can give me that may help speed up generation time?
Title: Re: Board running slooooooooooow!!
Post by: [Unknown] on February 21, 2005, 07:09:37 PM
Your forum is rather slow... but, 200,000 posts and 41 people online shouldn't cause that :P.

Are you on a shared or dedicated server?  Do you have "check avatar size every time it's displayed" disabled? (you should have it off!)

I'm getting page load times in the range of 14/5 seconds; that really isn't acceptable.  Are any other queries ever holding things up longer than a second?  Except for searching, no queries should run that long.

-[Unknown]
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 07:22:51 PM
i agree....!!! :(

unfortunately we are on a shared box however theres not much other activity going on in MYSQL at the same time.....i think my site has the most Mysql activity on that box daily..

also...we dont have that avatar setting on...

heres another shot....much less queries at any time than before....

(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.wheelsjamaica.com%2Ftroy%2F03.gif&hash=ed24761801828b6d04d2edfb32f7093d)
Title: Re: Board running slooooooooooow!!
Post by: [Unknown] on February 21, 2005, 07:31:19 PM
That's still taking too long.

How many posts do you have showing per page?  Does it help if you make the topics and attachments tables InnoDB?

How many rows are in attachments and log_online?

Can you tell me the output of this query:

SHOW CREATE TABLE wheels_forumlog_online;

-[Unknown]
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 07:38:17 PM
Maximum topics to display in the message index  30
Maximum messages to display in a topic page       15
Posts to show on topic summary                          15


rows in attachment       25,137
rows in log_online          44


Result of query:

CREATE TABLE `wheels_forumlog_online` (
 `session` varchar(32) NOT NULL default '',
 `logTime` timestamp(14) NOT NULL,
 `ID_MEMBER` mediumint( 8 ) unsigned NOT NULL default '0',
 `ip` int(11) unsigned NOT NULL default '0',
 `url` text NOT NULL,
 PRIMARY KEY  (`session`),
 KEY `online` (`logTime`,`ID_MEMBER`),
 KEY `ID_MEMBER` (`ID_MEMBER`)
) TYPE=MyISAM



(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwheelsjamaica.com%2Ftroy%2F04.gif&hash=12fb6587920530b7d9313c473a32a7d3)
Title: Re: Board running slooooooooooow!!
Post by: [Unknown] on February 21, 2005, 07:47:24 PM
ALTER TABLE wheels_forumlog_online
TYPE=InnoDB;

It looks fine, but the above will make it faster, most likely.

What version of MySQL are you on?  I can't imagine why any of these queries would take this long...

What about:

SHOW CREATE TABLE wheels_forumattachments;

-[Unknown]
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 07:51:30 PM
CREATE TABLE `wheels_forumattachments` (
 `ID_ATTACH` int(11) unsigned NOT NULL auto_increment,
 `ID_MSG` int(10) unsigned NOT NULL default '0',
 `ID_MEMBER` int(10) unsigned NOT NULL default '0',
 `filename` tinytext NOT NULL,
 `size` int(10) unsigned NOT NULL default '0',
 `downloads` mediumint(8) unsigned NOT NULL default '0',
 PRIMARY KEY  (`ID_ATTACH`),
 UNIQUE KEY `ID_MEMBER` (`ID_MEMBER`,`ID_ATTACH`),
 KEY `ID_MSG` (`ID_MSG`)
) TYPE=MyISAM


I'me on Mysql 4.022......ime kinda scared to do that update :-[...hmm.....should i put the board in MAintenace mode?
Title: Re: Board running slooooooooooow!!
Post by: [Unknown] on February 21, 2005, 07:55:24 PM
I'me on Mysql 4.022......ime kinda scared to do that update :-[...hmm.....should i put the board in MAintenace mode?

It's only the log_online table.  If you accidentally deleted that table, it'd be no great loss, and you could create it again.  I'm here - just make sure you're careful and do it to the right table.

The attachments table looks fine too!  Strange...

-[Unknown]
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 07:59:05 PM
ok done!

CREATE TABLE `wheels_forumlog_online` (
 `session` varchar(32) NOT NULL default '',
 `logTime` timestamp(14) NOT NULL,
 `ID_MEMBER` mediumint( 8 ) unsigned NOT NULL default '0',
 `ip` int(11) unsigned NOT NULL default '0',
 `url` text NOT NULL,
 PRIMARY KEY  (`session`),
 KEY `online` (`logTime`,`ID_MEMBER`),
 KEY `ID_MEMBER` (`ID_MEMBER`)
) TYPE=InnoDB
Title: Re: Board running slooooooooooow!!
Post by: Ben_S on February 21, 2005, 08:10:42 PM
Assuming the obvious guess is the url, it loads fast for me, would be interested seeing the load on the server when it's taking that long.

Have personally seen my board take ages to load when msnbot was on a test install of my board (hidden I thought) and whatever it was trying to do put a big load on MySQL and effectivly stopped it doing anything else.
Title: Re: Board running slooooooooooow!!
Post by: [Unknown] on February 21, 2005, 08:13:08 PM
This topic still takes 6+ seconds to load:

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

I'm nto sure why though, if you're certain the "check size every time" option is off...

-[unknown]
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 08:18:25 PM
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.wheelsjamaica.com%2Ftroy%2F05.gif&hash=fcc1d2a8d76856fa60dcbaa39c159320)
Title: Re: Board running slooooooooooow!!
Post by: Ben_S on February 21, 2005, 08:23:10 PM
Wrong section, theres another option somwhere, cant say exactly where though.
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 08:26:04 PM
he asked me if i have the "check avatar size everytime.."  option on......thats wat i just posted....its rite there in the image above...  :-\
Title: Re: Board running slooooooooooow!!
Post by: Ben_S on February 21, 2005, 08:32:37 PM
Oops sorry, also check the "Max width of posted pictures" option and if its enabled set it to 0, same for height.
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 08:36:54 PM
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwheelsjamaica.com%2Ftroy%2F06.gif&hash=8488af310b0baf2f2b63cd9cb20b6d93)



what is "Enable compressed output"???  ....its enabled for me..
Title: Re: Board running slooooooooooow!!
Post by: Ben_S on February 21, 2005, 08:39:13 PM
gzip, in 99.9% of cases you want compressed output on.
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 08:41:28 PM
k....

sigh...doesnt look like there is much hope for me then huh :-[
Title: Re: Board running slooooooooooow!!
Post by: Ben_S on February 21, 2005, 08:42:24 PM
Can you add

$db_show_debug = true;

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

/me hopes $db_show_debug = true; still exists in the relase ver.
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 08:44:20 PM
will that make all my users see the queries?......or will only admin see them?
Title: Re: Board running slooooooooooow!!
Post by: Ben_S on February 21, 2005, 08:46:36 PM
Only admins, assuming it exists still.
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 08:52:19 PM
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....
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 08:56:48 PM
 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....


Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 09:24:50 PM
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
Title: Re: Board running slooooooooooow!!
Post by: [Unknown] on February 21, 2005, 09:30:48 PM
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]
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 10:11:32 PM
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwheelsjamaica.com%2Ftroy%2F07.gif&hash=23e4e025b65eb2899e5b6eb03cb526b9)
Title: Re: Board running slooooooooooow!!
Post by: [Unknown] on February 21, 2005, 10:43:54 PM
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 :P.

-[Unknown]
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 10:53:16 PM
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!!!!  :D :D :D :D :D

http://www.wheelsjamaica.com/wheels_forum/index.php?topic=21221.0
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 21, 2005, 11:22:43 PM
 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! 8)
Title: Re: Board running slooooooooooow!!
Post by: [Unknown] on February 22, 2005, 01:34:26 AM
Oh, alright.  I missed the word TABLE, but if it works now, I'm glad.

-[Unknown]
Title: Re: Board running slooooooooooow!!
Post by: mystik on February 22, 2005, 11:15:37 AM
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  :D