MyBB Community Forums

I just upgraded HF to 1.6x from 1.4x. So far it's a nightmare but I'm going to try to make suggestions and give feedback. Given I'm not eligible for official support I'm stuck doing this all by myself. I'm going to try to keep this thread positive and hope my comments are taken seriously.

I run a separate MySQL server that a dual quad-core and 18gb ram. The DB size is about 5.5Gb. So this is a big site as most of you know.

Before the upgrade I had some custom edits but not really a lot to enhance performance. Most was for display to guests or simple edits on how I wanted the site to look. My core edits I've noted with a diff file.

The upgrade script itself took 3 hours mainly from the 5000 posts per query to upgrade the longipaddress. It just ran for hours.

Next during the upgrade process the install was hacked and someone altered the settings on me. I don't have time to chase this down now but it's there. Member confirm they know how to do it and well...it was done. Didn't take me long to fix it but it caused a side effect. All my settings were tripled. I fixed that too but now I have some weirdness in settings. I'll address that later.

So I did my template checks (Updated Templates feature) and that went well imho. Then I turned the site on. Immediately load on mysql jumped very high...3-4 times higher than on 1.4x. And no there was no attack.

Here are 2 things right now I found to be a problem.

1. Group By was added to the query to get threads in forumdisplay.php. Not only was this not necessary but it's detrimental.

http://dev.mysql.com/tech-resources/arti...myths.html

Quote:What I think that is happening is that MySQL takes the GROUP BY clause literally and performs the GROUP BY algorithm for each of the specified expressions. MySQL implements GROUP BY by sorting the rows according to the GROUP BY expressions. In this particular case, adding the title column to the GROUP BY clause does not allow the server to sort the rows in-memory, and forces the GROUP BY to be evaluated using a temporary table and a file sort. This requires extra IO operations which is causing a decrease in performance.

That was results...I had constant tmp tables being created. Created from already massive tables. A real problem. After removing the Group By line mysql CPU usage dropped by nearly half.

2. The new PM search function is great but with a couple problems. The "fromid" column is not indexed. It should be. It's used often in the search queries and I had serious detrimental performance issues from not having an index there. I've confirmed that a fresh install and 1.4x upgrades do not have indexes. Technically this is a bug imho and hopefully MyBB will fix that in 1.6.2.

3. I'm having performance issues with the thread ratings query...well the whole damn query for threads actually in forumdisplay. I'd like to simply suggest that MyBB consider a switch to globally disable rating views or at least one per group.

Lots of new features are in MyBB 1.6x and it would be nice if those features had global options to disable them.

I'll update this as I go on.

Thank for reading.

Regarding point 1, it was added to fix [Issue #1307]; it's there because of a bug with guests rating threads; I guess it could only add that bit in when you're browsing as a guest as that's the only time it's needed.

http://dev.mybb.com/issues/1307

You have bad link.

And to be honest that's not a good fix for that if performance is going to take such a hit. That one query is very very important.

(2010-12-22, 10:25 PM)labrocca Wrote: [ -> ]The upgrade script itself took 3 hours mainly from the 5000 posts per query to upgrade the longipaddress. It just ran for hours.

I complained about that too and suggested an improvement; dunno if it actually found its way into MyBB. Updating the longips doesn't need a query per post, but ranging from 0 queries (if they're already correct) to 1 query per unique IP.

The rest of your story is rather obscure to me. 5.5GB may be large for a forum, but tiny as far as databases go in general. Really not sure why you need a separate server for this. Even more baffled how you can still have MySQL performance issues even with such a server. There's got to be a fundamental flaw in your setup, somewhere.

Even so I agree that lots of queries in MyBB have room for optimization. For example thread prefixes could be easily cached, instead a new join was added to every query that needs them. Means more work for the database and makes things harder for plugins, too.

I do 1500 queries per second during peak times. Does that help you figure it out?

I'm Alexa 4k right now. HF is more active than DP which post wise isn't that much larger as he has I think a 12-15GB DB. But HF does more queries per second and more posts per day. He's spending $xxx,xxx a year and has an entire rack. I think I'm doing good with just 2 servers to be honest. Serving a forum is more intense than most applications and websites.


4. This issue is related to the new build_forum_breadcrumb feature in showthread.php. It's a real bummer there isn't a global switch for this. Not only do I find it annoying but now I see it's a real detriment.

Here is query:
$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $visibleonly $uid_only", array('limit' => 1));

This is actually a huge query that was also locking up tables and taking forever to complete. The query is not using the datecut of a forums that is handy to minimize server load. This should immediately be reviewed to be optimized by MyBB. With 800k threads and this query sorting the entire table with ever thread view it's a monster. I'm forced to simply comment out the lines in showthread.php for now.

I feel like I'm butchering MyBB 1.6x just so my forum is usable. How did 1.4x go from 20% CPU load to 80%?

Reviewing some of the queries to count threads..why doesn't MyBB do a datacache to the forums? Wouldn't updating one row be 100x faster than reading and sorting constantly? It's like how there is a postnum for users table. I know this is probably way too late to implement now but I feel this is a design flaw.

(2010-12-22, 10:47 PM)labrocca Wrote: [ -> ]I do 1500 queries per second during peak times. Does that help you figure it out?

I'm Alexa 4k right now. HF is more active than DP which post wise isn't that much larger as he has I think a 12-15GB DB. But HF does more queries per second and more posts per day. He's spending $xxx,xxx a year and has an entire rack. I think I'm doing good with just 2 servers to be honest. Serving a forum is more intense than most applications and websites.


4. This issue is related to the new build_forum_breadcrumb feature in showthread.php. It's a real bummer there isn't a global switch for this. Not only do I find it annoying but now I see it's a real detriment.

Here is query:
$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $visibleonly $uid_only", array('limit' => 1));

This is actually a huge query that was also locking up tables and taking forever to complete. The query is not using the datecut of a forums that is handy to minimize server load. This should immediately be reviewed to be optimized by MyBB. With 800k threads and this query sorting the entire table with ever thread view it's a monster. I'm forced to simply comment out the lines in showthread.php for now.

I feel like I'm butchering MyBB 1.6x just so my forum is usable. How did 1.4x go from 20% CPU load to 80%?

Reviewing some of the queries to count threads..why doesn't MyBB do a datacache to the forums? Wouldn't updating one row be 100x faster than reading and sorting constantly? It's like how there is a postnum for users table. I know this is probably way too late to implement now but I feel this is a design flaw.

MyBB should probably use the forums table and get the amount of threads from there, it's just a simple query instead of a such intensive one (datecut wouldn't be useful there, in the query you provided, since it must count all threads)

(2010-12-22, 11:42 PM)Pirata Nervo Wrote: [ -> ]MyBB should probably use the forums table and get the amount of threads from there, it's just a simple query instead of a such intensive one (datecut wouldn't be useful there, in the query you provided, since it must count all threads)

Exactly. I had thought that was how it was...but it's not. By the time I'm done with all these changes HF will practically be a fork.

That query has always been there, it has to do that to see how many of the threads in that forum the user can actually see.

1.4 query:

$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $visibleonly", array('limit' => 1));

1.6 query:

$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $useronly $visibleonly", array('limit' => 1));

(2010-12-22, 11:48 PM)labrocca Wrote: [ -> ]

(2010-12-22, 11:42 PM)Pirata Nervo Wrote: [ -> ]MyBB should probably use the forums table and get the amount of threads from there, it's just a simple query instead of a such intensive one (datecut wouldn't be useful there, in the query you provided, since it must count all threads)

Exactly. I had thought that was how it was...but it's not. By the time I'm done with all these changes HF will practically be a fork.

Hold on a second, I'll

(2010-12-22, 11:53 PM)MattRogowski Wrote: [ -> ]That query has always been there, it has to do that to see how many of the threads in that forum the user can actually see.

1.4 query:

$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $visibleonly", array('limit' => 1));

1.6 query:

$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $useronly $visibleonly", array('limit' => 1));

I just saw that. That query only gets executed if either we can only view our own threads OR if there are 0 threads and MyBB wants to make sure there are no threads that have been "copied" but are still there.

So, this is not affecting your server performance at all labrocca.

Not true. The $visibleonly and $useronly just changes the query if the variables exist. Other wise it gets executed. I'm also using a datecut on most of my forums.

Reviewing this I think I'm making queries worse by having a datecut. Sort of stupid imho if that's true.