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=9bfc65f65bfa60a37f0d0cbb1479d6322adead0b)
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.wheelsjamaica.com%2Ftroy%2F02.gif&hash=542d5fb1f5ec942d50556504c573c641a11e2933)
Pleeeeease help...i can afford for my board to be this slow periodically.....
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]
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=1e62aa6e9cb4ae93073b9ccaea0ab097042ce4f3)
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]
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=8bd3dd28d77b56093fd3989101ae7ef81fedecec)
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?
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]
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?
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]
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=743a7cbbe591ea97cee288fe7aa7b5e209160929)
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]
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=65bb1c2bb717ed0051389cfc32a5148ea86d78eb)
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]
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?
Quote from: mystik on February 21, 2005, 07:51:30 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]
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
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.
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]
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.wheelsjamaica.com%2Ftroy%2F05.gif&hash=c99ae591e9c60905378fc2c99a2b3d22df4d450c)
Wrong section, theres another option somwhere, cant say exactly where though.
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... :-\
Oops sorry, also check the "Max width of posted pictures" option and if its enabled set it to 0, same for height.
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwheelsjamaica.com%2Ftroy%2F06.gif&hash=f9e99ee3be7d1b51ac8eca93d7a024edfd968a06)
what is "Enable compressed output"??? ....its enabled for me..
gzip, in 99.9% of cases you want compressed output on.
k....
sigh...doesnt look like there is much hope for me then huh :-[
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.
will that make all my users see the queries?......or will only admin see them?
Only admins, assuming it exists still.
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....
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....
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
Can you run this query and runs its results?
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]
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwheelsjamaica.com%2Ftroy%2F07.gif&hash=9ba080fe25077b53bdb2828b35d45beae54f9391)
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]
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
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)
Oh, alright. I missed the word TABLE, but if it works now, I'm glad.
-[Unknown]
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