MyBB Community Forums

First of all, thank you MyBB team for making such an awesome software. I previously had issues due to high memory usage, which were solved and I brought down the memory usage from around 50 mb to 17 mb. And now it doesn't even climb to 18 mb.

Nowadays my forum is getting a huge number of users. Around 200 members stay online continuously because it is a study site and they look for assignment solutions. I disabled the main plugins, like the shoutbox, which brought down cpu usage.


The server is a vps, Quad Core Xeon processor at 2.4 Ghz. The Ram is around 1 GB with burst memory at around 2 GB. So the server is quite capable of handling my website. But I have noticed that the high user number has drastically overloaded the MySQL server. What I basically want is to optimize the databases. Perhaps some big board owners might help me out here, as they are experienced with handling high users. The server is Apache 2.2, with PHP version 5.2.17. Here is the Debug info.

MyBB Debug Information



Page Generation Statistics
Page Generation Time:	1.28144907951 seconds	No. DB Queries:	14
PHP Processing Time:	0.5529544 seconds (43.15%)	DB Processing Time:	0.7284946 seconds (56.85%)
Extensions Used:	mysql, xml	Global.php Processing Time:	0.2974689 seconds
PHP Version:	5.2.17	Server Load:	2.56
GZip Encoding Status:	Disabled	No. Templates Used:	39 (46 Cached / 1 Manually Loaded)
Memory Usage:	17.5 MB (18350080 bytes)	Memory Limit:	100M
Database Connections (1 Total)

[READ] alimaula_newask@localhost (Connected in 0s)

Database Queries (14 Total)

#1 - Write Query
SET NAMES 'utf8'
Query Time: 0.000115871429443

#2 - Select Query
SELECT title,cache FROM mybb_datacache
table	type	possible_keys	key	key_len	ref	rows	Extra
mybb_datacache	ALL					37	
Query Time: 0.            00527596473694      

#3 - Select Query
SELECT * FROM mybb_sessions WHERE sid='95a5d4c89c37e59affbf6bc2fa1d7bb8' AND ip='110.36.90.88' LIMIT 1
table	type	possible_keys	key	key_len	ref	rows	Extra
mybb_sessions	const	PRIMARY,ip	PRIMARY	98	const	1	
Query Time: 0.00970506668091

#4 - Select Query
SELECT u.*, f.* FROM mybb_users u LEFT JOIN mybb_userfields f ON (f.ufid=u.uid) WHERE u.uid='482' LIMIT 1
table	type	possible_keys	key	key_len	ref	rows	Extra
u	const	PRIMARY	PRIMARY	4	const	1	
f	const	PRIMARY	PRIMARY	4	const	1	
Query Time: 0.0152690410614

#5 - Write Query
UPDATE mybb_sessions SET `uid`='482', `time`='1325085462', `location`='/index.php?debug=1', `useragent`='Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.7 (KHTML, like Gecko) Chrome/16.0.912.63 Safari/535.7', `location1`='0', `location2`='0', `nopermission`='0' WHERE sid='95a5d4c89c37e59affbf6bc2fa1d7bb8' LIMIT 1
Query Time: 0.0154020786285

#6 - Select Query
SELECT name, tid, properties, stylesheets FROM mybb_themes WHERE tid='13' LIMIT 1
table	type	possible_keys	key	key_len	ref	rows	Extra
mybb_themes	const	PRIMARY	PRIMARY	2	const	1	
Query Time: 0.0118100643158

#7 - Select Query
SELECT title,template FROM mybb_templates WHERE title IN ('' ,'index' ,'index_whosonline' ,'index_welcomemembertext' ,'index_welcomeguest' ,'index_whosonline_memberbit' ,'forumbit_depth1_cat' ,'forumbit_depth1_forum' ,'forumbit_depth2_cat' ,'forumbit_depth2_forum' ,'forumbit_depth1_forum_lastpost' ,'forumbit_depth2_forum_lastpost' ,'index_modcolumn' ,'forumbit_moderators' ,'forumbit_subforums' ,'index_welcomeguesttext' ,'index_birthdays_birthday' ,'index_birthdays' ,'index_pms' ,'index_loginform' ,'index_logoutlink' ,'index_stats' ,'forumbit_depth3' ,'forumbit_depth3_statusicon' ,'index_boardstats' ,'css' ,'headerinclude' ,'header' ,'footer' ,'gobutton' ,'htmldoctype' ,'header_welcomeblock_member' ,'header_welcomeblock_guest' ,'header_welcomeblock_member_admin' ,'global_pm_alert' ,'global_unreadreports' ,'' ,'global_pending_joinrequests' ,'nav' ,'nav_sep' ,'nav_bit' ,'nav_sep_active' ,'nav_bit_active' ,'footer_languageselect' ,'header_welcomeblock_member_moderator' ,'redirect' ,'error') AND sid IN ('-2','-1','12') ORDER BY sid ASC
table	type	possible_keys	key	key_len	ref	rows	Extra
mybb_templates	ALL					1952	Using where; Using filesort
Query Time: 0.0195319652557

(Plugin Hook: index_start)
#8 - Select Query
SELECT u.uid,u.username,u.displaygroup,u.usergroup,u.avatar,COUNT(*) AS ptoday FROM mybb_posts p LEFT JOIN mybb_users u ON (p.uid=u.uid) WHERE p.dateline > 1324999062 GROUP BY p.uid ORDER BY ptoday DESC LIMIT 0,5
table	type	possible_keys	key	key_len	ref	rows	Extra
p	range	dateline	dateline	8		157	Using where; Using temporary; Using filesort
u	eq_ref	PRIMARY	PRIMARY	4	alimaula_vuaskari.p.uid	1	
Query Time: 0.0249288082123

(Plugin Hook: index_start)
#9 - Select Query
SELECT u.uid,u.username,u.displaygroup,u.usergroup, u.avatar ,COUNT(*) AS pweek FROM mybb_posts p LEFT JOIN mybb_users u ON (p.uid=u.uid) WHERE p.dateline > 1324480662 GROUP BY p.uid ORDER BY pweek DESC LIMIT 0,5
table	type	possible_keys	key	key_len	ref	rows	Extra
p	range	dateline	dateline	8		605	Using where; Using temporary; Using filesort
u	eq_ref	PRIMARY	PRIMARY	4	alimaula_vuaskari.p.uid	1	
Query Time: 0.01833319664

(Plugin Hook: index_start)
#10 - Select Query
SELECT u.uid,u.username,u.displaygroup,u.usergroup,u.avatar,COUNT(*) AS pmonth FROM mybb_posts p LEFT JOIN mybb_users u ON (p.uid=u.uid) WHERE p.dateline > 1322493462 GROUP BY p.uid ORDER BY pmonth DESC LIMIT 0,5
table	type	possible_keys	key	key_len	ref	rows	Extra
p	range	dateline	dateline	8		1553	Using where; Using temporary; Using filesort
u	eq_ref	PRIMARY	PRIMARY	4	alimaula_vuaskari.p.uid	1	
Query Time: 0.0290710926056

(Plugin Hook: index_start)
#11 - Select Query
SELECT * FROM mybb_users ORDER BY postnum DESC LIMIT 0,5
table	type	possible_keys	key	key_len	ref	rows	Extra
mybb_users	ALL					23634	Using filesort
Query Time: 0.527192831039

(Plugin Hook: index_start)
#12 - Select Query
SELECT template FROM mybb_templates WHERE title='itp' AND sid IN ('-2','-1','12') ORDER BY sid DESC LIMIT 1
table	type	possible_keys	key	key_len	ref	rows	Extra
mybb_templates	ALL					1952	Using where; Using filesort
Query Time: 0.            0054919719696      

#13 - Select Query
SELECT s.sid, s.ip, s.uid, s.time, s.location, s.location1, u.username, u.invisible, u.usergroup, u.displaygroup FROM mybb_sessions s LEFT JOIN mybb_users u ON (s.uid=u.uid) WHERE s.time>'1325084562' ORDER BY u.username ASC, s.time DESC
table	type	possible_keys	key	key_len	ref	rows	Extra
s	range	time	time	8		275	Using where; Using temporary; Using filesort
u	eq_ref	PRIMARY	PRIMARY	4	alimaula_vuaskari.s.uid	1	
Query Time: 0.01722407341

#14 - Select Query
SELECT f.*, fr.dateline AS lastread FROM mybb_forums f LEFT JOIN mybb_forumsread fr ON (fr.fid=f.fid AND fr.uid='482') WHERE f.active != 0 ORDER BY pid, disporder
table	type	possible_keys	key	key_len	ref	rows	Extra
f	ALL					743	Using where; Using filesort
fr	eq_ref	fid	fid	8	alimaula_vuaskari.f.fid,const	1	
Query Time: 0.0229618549347

As you can see, 69 % of the load time is actually MySQL request time.
Any help would be greatly appreciated.

1. Join the Big Board group. We have some optimizations you can do.

2. Your stats look normal for a forum your size.

A load of 2.56 is perfectly fine if you have a quad core. Plus the fact MySQL is using 69% of the load time is fine too, usually MySQL will always take more time to process than PHP. The only query that is taking longer than expected though is #11, which is being added by a plugin and is something that runs on the index, so disable any plugins that run on the index and see what the load times are then.

He has 13 queries. Very reasonable for any page. And only 1 template loading. He can add that to the preload ($templates) to remove the one query easily.

But yeah..it's normal load and unless OP is seeing slowdown shouldn't worry about it. Unless he has a real growth spurt he should be fine.

Hmm this debug info later showed a load of 4.67 at 230 users. Got me freaking out. Labrocca I remember that you posted a detailed optimization trick posted originally at vbum.com. Would converting to innodb work? I just couldn't understand the thread view update query, where should I add it?
And yeah we are having a growth spurt, I had over 100 registrations today and we reached 24000 members . Enjoying this though.

---

Since it is a site related to assignment solutions, we do not surpass the required post quota ( students only download the solutions (and don't even say thanks), which has resulted the overall attachments bandwidth usage to around 1TB!! ), 100k, which currently is just 36k, but the user activity is going through the roof, new term's startin' . Is there still a way to join the big board owners group?

You can request to join it from the Group Memberships section of the User CP I believe.

Upgrading to MySQL 5.5 and switching to InnoDB would probably work but labrocca knows more about that than I do.

As for load, IIRC a load of 4 on a 4 core server is fine, so 4.67 on a 4 core server isn't really that high.

@labrocca, well we dont see shutdowns, but other users have reported that they see a lot of server limit reached pages, those times the server load averages around 5.23, 5.2, 5.4. Which slows the server down. I will disable the plugins and see which one is using the index_start query. About the templates, I do know the template name, how do I add it to preload variable?

---

@Matt, I shall post the request to join the group, hope that the mods approve my request.

Quote:Would converting to innodb work? I just coiuldn't understand the thread view update query, where should I add it?

Yes it will but only really if you're using MySQL 5.5.

Join big board group. Send me URL too via PM so I can confirm you're owner.

There are some good threads you can read in private forum about optimizing. I'm sure with some work your server load can be dropped. You just need to make custom changes and have server root.

Yes I have server root, sending the request now, and sending you the pm too.

---

MySQL version: 5.0.something
PHP version: 5.2.17
As I have root access, I will definitely upgrade them using WHM. But have been afraid to do so, will probably ask the host to do it, since I am afraid that I may ruin the modules.
Previously tried to install ffmpeg, ruined the cr*p out of PHP
That day onwards, I'm downright scared...

---

Bump, edit* they are dual core processors.....

You don't have a lot of posts. Are you sure you actually have 200 users online at a time? You have a buttload of ads too. A load of 2.57 is high for a dual-core. You're over 100% cpu.

Is this a VPS? Likely oversold garbage if that's the case.