Feel free to make any optimization suggestions. We'll take a look at implementing them for 1.6.2...
I know Tomm.
Doing my best to simple share my experience without being too frustrated.
btw...just added an index to threads visible column.
Seems most of my problem is from forumdisplay.php.
One at a time I'm tackling queries.
SELECT t.*, (t.totalratings/t.numratings) AS averagerating, r.uid AS rated, t.username AS threadusername, u.username FROM mybb_threads t LEFT JOIN mybb_users u ON (u.uid = t.uid) LEFT JOIN mybb_threadratings r ON(r.tid=t.tid AND r.uid='1') WHERE t.fid='25' AND (t.visible='1' OR t.visible='0') ORDER BY t.sticky DESC, t.lastpost desc LIMIT 0, 20
On that one the visible is not indexed in the threads table. I'm trying everything I can for my site to run as well as it did under 1.4x.
(2010-12-23, 12:05 AM)labrocca Wrote: [ -> ]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.
Both queries (they're the same query but there are "entries" found when you search for it) are under two if's. They get executed only if those if's are true which happens when: $threadcount is 0 and/or when the user can only view own threads. Otherwise it just uses the forum counters.
And if a datecut exists...that's why I mentioned it.
// How many posts are there?
if($datecut > 0 || $fpermissions['canonlyviewownthreads'] == 1)
{
$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $useronly $visibleonly $datecutsql");
$threadcount = $db->fetch_field($query, "threads");
}
else
{
$query = $db->simple_select("forums", "threads, unapprovedthreads", "fid = '{$fid}'", array('limit' => 1));
$forum_threads = $db->fetch_array($query);
$threadcount = $forum_threads['threads'];
if($ismod == true)
{
$threadcount += $forum_threads['unapprovedthreads'];
}
// If we have 0 threads double check there aren't any "moved" threads
if($threadcount == 0)
{
$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $useronly $visibleonly", array('limit' => 1));
$threadcount = $db->fetch_field($query, "threads");
}
}
(2010-12-23, 12:38 AM)labrocca Wrote: [ -> ]And if a datecut exists...that's why I mentioned it.
// How many posts are there?
if($datecut > 0 || $fpermissions['canonlyviewownthreads'] == 1)
{
$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $useronly $visibleonly $datecutsql");
$threadcount = $db->fetch_field($query, "threads");
}
else
{
$query = $db->simple_select("forums", "threads, unapprovedthreads", "fid = '{$fid}'", array('limit' => 1));
$forum_threads = $db->fetch_array($query);
$threadcount = $forum_threads['threads'];
if($ismod == true)
{
$threadcount += $forum_threads['unapprovedthreads'];
}
// If we have 0 threads double check there aren't any "moved" threads
if($threadcount == 0)
{
$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $useronly $visibleonly", array('limit' => 1));
$threadcount = $db->fetch_field($query, "threads");
}
}
Where's that code from? Because that's not the code I have here.
$query = $db->simple_select("forums", "threads, unapprovedthreads", "fid = '{$fid}'", array('limit' => 1));
$forum_threads = $db->fetch_array($query);
$threadcount = $forum_threads['threads'];
if($ismod == true)
{
$threadcount += $forum_threads['unapprovedthreads'];
}
// Limit to only our own threads
$uid_only = '';
if($forumpermissions['canonlyviewownthreads'] == 1)
{
$uid_only = " AND uid = '".$mybb->user['uid']."'";
$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $visibleonly $uid_only", array('limit' => 1));
$threadcount = $db->fetch_field($query, "threads");
}
// If we have 0 threads double check there aren't any "moved" threads
if($threadcount == 0)
{
$query = $db->simple_select("threads", "COUNT(tid) AS threads", "fid = '$fid' $visibleonly $uid_only", array('limit' => 1));
$threadcount = $db->fetch_field($query, "threads");
}
From the looks of it the main gripe you have here Labrocca is that these new features that are impacting performance do not have a global setting to disable them correct? Maybe using the many suggestions you've made over the past year alone (you've had more than a few threads dedicated towards optimization) the MyBB team can come up with something possibly in during the install or in the Admin CP itself for "performance mode" that will disable features that are nice for smaller boards but will cause performance issues with larger/more active forums?
Just an idea that I got while reading this thread so if it's not viable or makes no sense then pardon my ignorance.
I'm finding a lot of bugs too. Sort of expected that anyways. HF is so heavily used that bugs stand out. Every feature is used and tested by members...often they are looking for exploits or glitches.
Normally added global switches to some of these features would be easy to do for MyBB. So it should be considered imho.
While I know that for most 100k or lower post forums MyBB will run fine you do get the occasional thread about hosts that blame MyBB. To me it's always been important that the forum software be streamlined. It's my #1 complaint about VB. I even ran PunBB very successfully years ago. I'd rather go with less features than more. Having the ability to add plugins as I want or to globally turn off features is a huge plus. But if MyBB is going to start adding features without options to disable then existing forums might have difficulty upgrading under existing hardware. I can't be the only guy with 500k+ posts that after upgrading to 1.6x had problems.
I actually have very little additional load to my index, showthread and forumdisplay pages.
And I'm not griping really. Just calmly explaining. I'm hoping MyBB listens and that anyone else in my situation can be saved some aggravation.
After posting my reply I realized that "gripe" wasn't the right word I meant to use but for lack of a better one I didn't bother editing my post. But I completely agree, features are nice but not everybody will use every feature and it's important that the software doesn't force a user to use features they don't want to use.
I'm not too interested in a 'Performance Mode' in general - ultimately, a system is harder to control with one switch than with a dozen switches. That way, you can tailor it to the way you want.
Let us know where you want these switches Labrocca, either here on the development tracker. Things like this (alongside plugin hooks) is something we've been talking about behind-the-scenes for 1.6.2.