Here's a few guidelines you can use to make huge vBulletin boards quicker and range better (starting with the most convenient factors on down to the most difficult)... Please keep in thoughts that what performs for me, may not perform for you (so the apparent "do at your own risk" preface applies).
Disable Memberlist
There is an choice to basically turn off the memberlist under Configurations -> Choices -> Personal Record Choices.
It might be skipped at first, but ask yourself what the *real* objective of it is... associates can type it to bend their e-peen to see who has the most content, popularity or whatever else.
Do individuals really use this to look for for members? Probably not... when was the before you used it to look for for a participant that you didn't already know who they were?
In truth the primary objective of it is to provide spammers an simple way to collect your usernames so they can more quickly boost out PM spams. On top of that, the issues needed to produce the memberlist are dreadful for your DB hosts and can cause large securing issues and DB web host server plenty.
Increase Personal Concept Record Speed
If you have never brought in private details from an exterior source (via ImpEx or anything else), you can securely depend on the pmid pillar to be in the same type purchase as the PM time frame. Organizing on pmid will make it so your databases web host server doesn't need to dispose of the PMs into a short-term desk to execute the type (making the question much faster).
To do this, make a plug-in at the private_messagelist_filter connect place with the following range in it:
php Code:
if ($sortfield == 'pmtext.dateline') $sortfield = 'pm.pmid';
You just designed private.php ~20% quicker.
Find Last Publish By Personal Efficiently
In includes/class_userprofile.php, substitute this:
php Code:
$getlastposts = $this->registry->db->query_read_slave("
SELECT range.title, range.threadid, range.forumid, range.postuserid, post.postid, post.dateline
FROM " . TABLE_PREFIX . "post AS post
INNER JOIN " . TABLE_PREFIX . "thread AS range USING (threadid)
WHERE range.visible = 1
AND post.userid = " . $this->userinfo['userid'] . "
AND post.visible = 1
ORDER BY post.dateline DESC
LIMIT 20
");
with this (just changes the ORDER BY):
php Code:
$getlastposts = $this->registry->db->query_read_slave("
SELECT range.title, range.threadid, range.forumid, range.postuserid, post.postid, post.dateline
FROM " . TABLE_PREFIX . "post AS post
INNER JOIN " . TABLE_PREFIX . "thread AS range USING (threadid)
WHERE range.visible = 1
AND post.userid = " . $this->userinfo['userid'] . "
AND post.visible = 1
ORDER BY post.postid DESC
LIMIT 20
");
This creates the question be able to hit indices effectively and not need to do a filesort on a short-term desk. For customers with a ton of content, the unique question can take 10+ a few moments, but it becomes immediate when solving the ORDER BY. This is mainly used on the participant user profile web page to show the last post the consumer published in.
Check Thread Indexes
If any of your boards have a standard type purchase that is set to anything other than Last Publish Time, make an catalog for it in your range desk.
We have some boards that the standard type purchase is the range development time (the pillar used for that is "dateline"), so we designed the following index:
Code:
ALTER TABLE range ADD INDEX forumid2_dp (forumid, noticeable, challenging, dateline)
Be Very Cautious What Add-Ons You Install
Just because someone creates an add-on, it does not mean it was designed with a huge community in thoughts. An ideal example is the iTrader program. We use it here, but only after we designed MANY changes to it so that it has the capability to range.
It's best to believe add-on designers don't know what they are doing and go over all their value before you go remain with something. Ensure that the add-ons aren't resulting in databases fill issues, make sure they don't have prospective SQL hypodermic injection issues or XSS issues. Unfortunately with a large variety of add-ons out there, you will just have to do this yourself (or seek the services of someone to do it). And it's also the objective we went the path of just composing all our own plug-ins for the most aspect vs. developing on something that might be bad to begin with. (Yes, I know I have believe in issues. heh)
Replace Search
For this website, I designed a Sphinx-based look for replace the regular vBulletin look for that is quickly available to top quality associates over here.
Using this is probably the person best factor you can do for your databases hosts.
Don't Use InnoDB Platforms (maybe)
This is a hefty topic for discussion, but for us it's proved helpful well. We use 100% MyISAM tables for everything (and we are handling more than 1,000 issues per second).
If you get to the factor where you are not having any table/record securing issues (most especially after you substitute vBulletin search), modify vBulletin's InnoDB tables to MyISAM. MyISAM is quicker for individual issues (since it doesn't need to handle history hair among other things). InnoDB can be quicker overall, but only because it allows you to run issues simultaneously. If your issues run so fast under MyISAM that they don't even *need* to run simultaneously, there is no factor to run InnoDB.
In my viewpoint the best remedy to slowly databases issues is to fix the question itself so it's not slowly. Just transforming the desk to InnoDB is a sluggish way to fix it because all you are doing is creating it okay for other issues to run while the slowly question is operating.
Update: MySQL has come a lengthy way with InnoDB, and I'd say beginning with MySQL 5.5, it's a better choice in most situations than MyISAM. If your DB web host server uses MySQL 5.5 or greater, using InnoDB tables may be the best place for you.
Clean Up Storage cache - No more needed with vBulletin 4.1.8+: see this web page for more details.
vBulletin 4 has an inner databases centered cache that never purifies itself up (don't ask me why). Terminated cache products will remain in the databases consistently (until you eliminate the whole cache).
Create a plug-in at the cron_script_cleanup_hourly place with the following PHP value and cache products that are expired/unused will be cleared hourly:
php Code:
require_once(DIR . '/includes/class_bootstrap_framework.php');
vB_Bootstrap_Framework::init();
vB_Cache::instance()->clean(true);
Drop FULLTEXT Indexes - No more needed with vBulletin 4.1.7+: see this web page for more details.
If you improved from vBulletin 3, you can fall every FULLTEXT catalog in your databases EXCEPT for the ones in searchcore_text and searchgroup_text. The vBulletin 4 update procedure does not eliminate the old vBulletin 3 FULLTEXT indices that will used. By standard there are 4 of these indices (in the post, groupmessage, socialgroup and range tables) which can be eliminated.
Update: The 4.0.4 update eliminates all of these indices instantly EXCEPT for the range catalog.
showgroups.php Query Storage cache - No more needed with vBulletin 4.1.5+: see this web page for more details.
There is a question in showgroups.php that does not range (it will get more slowly and more slowly the more customers you have).
A fast fix is to just cache the question outcomes for 6 time (so it's not run more than once every 6 hours) like so...
Change this:
php Code:
// get usergroups who should be shown on showgroups
// Tests too many series. Usergroup Lines * Personal Rows
$users = $db->query_read_slave("
SELECT user.*,
usergroup.usergroupid, usergroup.title,
user.options, usertextfield.buddylist,
" . ($show['locationfield'] ? 'userfield.field2,' : '') . "
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroupid) AS displaygroupid
" . ($vbulletin->options['avatarenabled'] ? ",avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight, customavatar.width_thumb AS avwidth_thumb, customavatar.height_thumb AS avheight_thumb, filedata_thumb, NOT ISNULL(customavatar.userid) AS hascustom" : "") . "
FROM " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "usergroup AS usergroup ON(usergroup.usergroupid = user.usergroupid OR FIND_IN_SET(usergroup.usergroupid, user.membergroupids))
LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
" . ($vbulletin->options['avatarenabled'] ? "LEFT JOIN " . TABLE_PREFIX . "avatar AS character ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)" : "") . "
WHERE (usergroup.genericoptions & " . $vbulletin->bf_ugp_genericoptions['showgroup'] . ")
");
$groupcache = array();
while ($user = $db->fetch_array($users))
{
$t = strtoupper($user['title']);
$u = strtoupper($user['username']);
$groupcache["$t"]["$u"] = $user;
}
to this (really just covered the vb_Cache category procedure around the question and $groupcache generation):
php Code:
require_once(DIR . '/includes/class_bootstrap_framework.php');
vB_Bootstrap_Framework::init();
if (!$groupcache = vB_Cache::instance()->read('showgroups.groupcache')) {
// get usergroups who should be shown on showgroups
// Tests too many series. Usergroup Lines * Personal Rows
$users = $db->query_read_slave("
SELECT user.*,
usergroup.usergroupid, usergroup.title,
user.options, usertextfield.buddylist,
" . ($show['locationfield'] ? 'userfield.field2,' : '') . "
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroupid) AS displaygroupid
" . ($vbulletin->options['avatarenabled'] ? ",avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight, customavatar.width_thumb AS avwidth_thumb, customavatar.height_thumb AS avheight_thumb, filedata_thumb, NOT ISNULL(customavatar.userid) AS hascustom" : "") . "
FROM " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "usergroup AS usergroup ON(usergroup.usergroupid = user.usergroupid OR FIND_IN_SET(usergroup.usergroupid, user.membergroupids))
LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
" . ($vbulletin->options['avatarenabled'] ? "LEFT JOIN " . TABLE_PREFIX . "avatar AS character ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)" : "") . "
WHERE (usergroup.genericoptions & " . $vbulletin->bf_ugp_genericoptions['showgroup'] . ")
");
$groupcache = array();
while ($user = $db->fetch_array($users))
{
$t = strtoupper($user['title']);
$u = strtoupper($user['username']);
$groupcache["$t"]["$u"] = $user;
}
vB_Cache::instance()->write('showgroups.groupcache', $groupcache, 360);
}
For us, it creates the showgroups.php web page fill 6x quicker.
Gross Query In editpost.php - No more needed with vBulletin 4.1.4+: see this web page for more details.
When a person modifications an content, vBulletin does a question to see if that is the first post in the range (in situation the headline needs to change). For whatever objective, MySQL doesn't use the most effective catalog if the range has a ton of content (we would see the question to figure out the first post of a range take over 60 a few moments in some situations... which indicates someone modifications an content, and it would be more than a instant before they would get the modify page). The factor about it is we already know the first post ID, because vBulletin shops it with the range details for all discussions, so the question is 100% repetitive and needless.
In editpost.php, modify this:
php Code:
// discover out if first post
$getpost = $db->query_first("
SELECT postid
FROM " . TABLE_PREFIX . "post
WHERE threadid = $threadinfo[threadid]
ORDER BY dateline
LIMIT 1
");
if ($getpost['postid'] == $postinfo['postid'])
{
$isfirstpost = true;
}
else
{
$isfirstpost = false;
}
to this:
php Code:
$isfirstpost = $postinfo['postid'] == $threadinfo['firstpostid'] ? actual : false;
You get the identical details, without requiring to make a question of any type.
Fix Storage Utilization On participant.php and on the internet.php - No more needed with vBulletin 4.1.4+: see this web page for more details.
There is a deformed question in vBulletin that causes it to get and cycle through every user history in the program if the last activity of a participant was watching content by a certain user.
In includes/functions_online.php, within the convert_ids_to_titles() operate, substitute this:
php Code:
$userids .= ",userid";
with this:
php Code:
$userids .= ",$targetuserid";
For us it designed PHP go from getting 400MB and 60 a few moments to provide the website down to 7MB and 0.1 a few moments.
Use YUI 2.9.x - No more needed with vBulletin 4.1.4+: see this web page for more details.
vBulletin 4.x currently delivers with an obsolete edition of Search engines Personal User interface (version 2.7.0). You can basically substitute 2.7.0 with 2.9.x without any issues (2.8.x has a variety of bug repairs, and so does 2.9.x).
The simplest way to do this is to go to Configurations -> Choices -> Server Configurations and Marketing Choices and make sure your Use Distant YUI establishing is set to use Search engines or Search engines remote web host. Then modify your includes/class_core.php data file and modify this line:
php Code:
define('YUI_VERSION', '2.7.0'); // figure out the YUI edition we bundle
to this:
php Code:
define('YUI_VERSION', '2.9.0'); // figure out the YUI edition we bundle
Disable Quick Nav- No more needed with vBulletin 4.1.3+: see this web page for more details.
I discovered that 20-30% of the global/init features of the vBulletin framework were invested producing the quicknav HTML. For something that is hardly used, it seemed like a lot of expense. You can turn off it under AdminCP -> Configurations -> Choices -> Common Configurations -> Use Quick Routing Menu
Setting SQL Technique - No more needed with vBulletin 4.1.3+: see this web page for more details.
A empty sql_mode is the standard establishing for MySQL, so unless you've overridden your MySQL web host server to have a particular sql_mode, just thoughts the following range within includes/init.php (all it does is make sure sql_mode is set to the standard value):
php Code:
$db->force_sql_mode('');
like so:
php Code:
// $db->force_sql_mode('');
The question doesn't take lengthy to run, but it is a question to the DB web host server however. One less question per web page perspective is always a advantage.
Using Memcache Datastore More Effectively - No more needed with vBulletin 4.1.3+: see this web page for more details.
If you use Memcache as your datastore, you can make it quicker by getting several datastore important factors in only one complete (Memcache facilitates it, why not use it?).
In includes/class_datastore.php, within the vB_Datastore_Memcached::fetch() method, substitute this:
php Code:
$unfetched_items = array();
foreach ($items AS $item)
{
$this->do_fetch($item, $unfetched_items);
}
with this:
php Code:
if ($this->prefix) {
foreach ($items as $item) {
$items_fetch[] = $this->prefix . $item;
}
}
else
{
$items_fetch =& $items;
}
$items_found = $this->memcache->get($items_fetch);
$unfetched_items = array_keys(array_diff_key(array_flip($items_fetch), $items_found));
foreach ($items_found AS $key => $data)
{
$this->register(substr($key, strlen($this->prefix), 50), $data);
}
This will make getting your datastore (needed for every web page view) about 20% quicker. Memcache is very fast either way, so you probably won't observe much of a distinction, but there's no factor in *not* creating something quicker when you can in my view.
Generate Currently Effective Users More Effectively - No more needed with vBulletin 4.1.2+: see this web page for more details.
The "Currently Effective Users" list that seems to be on the community webpage revolves every user through the whole design rending motor, which needs applying a lot of international factors for each user. It's much more effective to make use of vBulletin 4's {vb:each} tag and just cycle through them from the primary design.
In community.php modify this:
php Code:
$show['comma_leader'] = ($activeusers != '');
$templater = vB_Template::create('forumhome_loggedinuser');
$templater->register('loggedin', $loggedin);
$activeusers .= $templater->render();
to this:
php Code:
$activeusers[] = $loggedin;
Then in the FORUMHOME design, modify this:
Code:
{vb:raw activeusers}
to this:
Code:
{vb:stylevar dirmark}{vb:raw loggedin.musername}{vb:raw loggedin.invisiblemark}{vb:raw loggedin.buddymark}
If you have 500 signed in customers, it helps you to save around 7,500 sub-routine involves applying factors and the net impact is your webpage plenty about twice as fast.
Fix Storage cache Table Structure - No more needed with vBulletin 4.1.1+: see this web page for more details.
The inner cache program does not spend enough area to shop huge cached products (large content for example). Modify the details pillar within the cache desk to be a MEDIUMBLOB instead of a BLOB with the following SQL command:
Code:
ALTER TABLE cache CHANGE details data MEDIUMBLOB NULL DEFAULT NULL
Slow Public Categories Query - No more needed with vBulletin 4.0.8+: see this web page for more details.
Within includes/functions_socialgroup.php (at the end) there is a question with a brilliant (but VERY ineffective sub-query). This modify creates the social groups web page in this community provide in 0.17 a few moments instead of 1.2 a few moments (7 periods faster). There are many excitement to use sub-queries (for example in the above example in forumdisplay.php). This question is not one of them.
Change this:
php Code:
$result = $vbulletin->db->query_read_slave("
SELECT user.*, socialgroupmember.groupid
" . ($vbulletin->options['avatarenabled'] ? ",avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight" : "") . "
FROM " . TABLE_PREFIX . "socialgroupmember AS socialgroupmember
LEFT JOIN " . TABLE_PREFIX . "user AS user ON (socialgroupmember.userid = user.userid)
" . ($vbulletin->options['avatarenabled'] ? "LEFT JOIN " . TABLE_PREFIX . "avatar AS character ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)" : "") . "
WHERE socialgroupmember.groupid IN (" . implode(',', $groupids) . ")
AND 10 > (
SELECT COUNT( * )
FROM " . TABLE_PREFIX . "socialgroupmember AS socialgroupmember2
WHERE socialgroupmember2.groupid = socialgroupmember.groupid
AND socialgroupmember2.dateline > socialgroupmember.dateline
AND socialgroupmember2.type = 'member'
)
ORDER BY socialgroupmember.dateline DESC
");
$group_members = array();
while ($member = $vbulletin->db->fetch_array($result))
{
fetch_avatar_from_userinfo($member, actual, true);
$group_members[$member['groupid']][] = $member;
}
to this:
php Code:
$group_members = array();
foreach ($groupids AS $groupid) {
$result = $vbulletin->db->query_read_slave("
SELECT user.*, socialgroupmember.groupid
" . ($vbulletin->options['avatarenabled'] ? ",avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight" : "") . "
FROM " . TABLE_PREFIX . "socialgroupmember AS socialgroupmember
LEFT JOIN " . TABLE_PREFIX . "user AS user ON (socialgroupmember.userid = user.userid)
" . ($vbulletin->options['avatarenabled'] ? "LEFT JOIN " . TABLE_PREFIX . "avatar AS character ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)" : "") . "
WHERE socialgroupmember.groupid = $groupid
AND socialgroupmember.type = 'member'
ORDER BY socialgroupmember.dateline DESC
LIMIT 10
");
while ($member = $vbulletin->db->fetch_array($result))
{
fetch_avatar_from_userinfo($member, actual, true);
$group_members[$member['groupid']][] = $member;
}
}
Alter post.userid Index - No more needed with vBulletin 4.0.8+: see this web page for more details.
There is a question that vBForum_Search_Result_Post::set_replydata() uses that queries the post desk by userid and parentid. There is no catalog that contains both content, so if the consumer has a lot of content in your community, this question can be really slowly.
If you basically add the parentid pillar to the current userid catalog the question will be super fast.
Code:
ALTER TABLE post DROP INDEX userid, ADD INDEX userid (userid, parentid)
Nasty Query In forumdisplay.php - No more needed with vBulletin 4.0.8+: see this web page for more details.
In vBulletin 4, the lowest needed MySQL edition is 4.1.0. This implies we can begin using sub-queries where appropriate (or just more effective in this case).
forumdisplay.php is one of the most trafficked webpages in vBulletin, so anything we can do to make this web page more effective is definitely value the attempt.
I recommended it to the vBulletin designers decades ago returning in edition 3.7.x, but nothing ever was done to fix it. Hopefully at some point this modify will be integrated into the source of vBulletin 4.
In forumdisplay.php, modify this query:
php Code:
# Consist of noticeable IN (0,1,2) to be able to hit upon the 4 pillar index
$threadscount = $db->query_first_slave("
SELECT COUNT(*) AS discussions, SUM(IF(thread.lastpost > $lastread AND begin <> 10, 1, 0)) AS newthread
$hook_query_fields
FROM " . TABLE_PREFIX . "thread AS thread
$tachyjoin
$hook_query_joins
WHERE forumid = $foruminfo[forumid]
AND challenging = 0
$prefix_filter
$visiblethreads
$globalignore
$limitothers
$datecut
$hook_query_where
");
to this:
php Code:
# Consist of noticeable IN (0,1,2) to be able to hit upon the 4 pillar index
$threadscount = $db->query_first_slave("
SELECT COUNT(*) AS discussions,
(
SELECT COUNT(*) AS newthread
FROM " . TABLE_PREFIX . "thread AS thread
WHERE forumid = $foruminfo[forumid]
AND lastpost > $lastread
AND begin <> 10
AND challenging = 0
$prefix_filter
$visiblethreads
$globalignore
$limitothers
$datecut
$hook_query_where
) AS newthread
$hook_query_fields
FROM " . TABLE_PREFIX . "thread AS thread
$tachyjoin
$hook_query_joins
WHERE forumid = $foruminfo[forumid]
AND challenging = 0
$prefix_filter
$visiblethreads
$globalignore
$limitothers
$datecut
$hook_query_where
");
The new question results in the identical outcomes, but does so with a sub-query instead of a really slowly SUM(IF()) declaration that is incapable to hit an catalog.
Look at enough a chance to run the standard query:
Code:
+---------+-----------+
| discussions | newthread |
+---------+-----------+
| 96484 | 2051 |
+---------+-----------+
1 row in set (0.49 sec)
...and evaluate it to enough time needed to run the new query:
Code:
+---------+-----------+
| discussions | newthread |
+---------+-----------+
| 96484 | 2051 |
+---------+-----------+
1 row in set (0.01 sec)
We just designed one of the primary issues in forumdisplay.php 50x quicker.
Yucky functions_newpost.php Query - No more needed with vBulletin 4.0.7+: see this web page for more details.
When a person creates an content, vBulletin assessments what the first postid is within the range they are publishing in. It's a source extensive question that does not range (the bigger the range, the more slowly it gets). The foolish factor is we already *have* the details we need without doing a question.
In functions_newpost.php, modify this:
php Code:
$getfirstpost = $vbulletin->db->query_first("SELECT postid FROM " . TABLE_PREFIX . "post WHERE threadid=$threadinfo[threadid] ORDER BY dateline LIMIT 1");
$parentid = $getfirstpost['postid'];
to this:
php Code:
$parentid = $threadinfo['firstpostid'];
CMS Article Caching - No more needed with vBulletin 4.0.4+: see this web page for more details.
CMS content are absolutely run through the BBCode parser for every web page perspective and it requires a agonizing period to do so (this is the primary objective the vBulletin CMS is so slow).
In packages/vbcms/content/article.php, modify this:
php Code:
$view->pagetext = fetch_censored_text($bbcode_parser->do_parse(
$this->content->getPageText(),
vBCMS_Permissions::canUseHtml($this->getNodeId(), $this->content->getContentTypeId(), $this->content->getUserId()),
$this->content->getHtmlState()
));
to this:
php Code:
$md5_key = 'article.' . md5($this->content->getPageText());
if (!$view->pagetext = vB_Cache::instance()->read($md5_key)) {
$view->pagetext = fetch_censored_text($bbcode_parser->do_parse(
$this->content->getPageText(),
vBCMS_Permissions::canUseHtml($this->getNodeId(), $this->content->getContentTypeId(), $this->content->getUserId()),
$this->content->getHtmlState()
));
vB_Cache::instance()->write($md5_key, $view->pagetext, 360);
}
This will make the parsing of your content only need to do so no more often than once every 6 time (or if you modify the article). This little modify designed our vBulletin CMS provide webpages 8.5x quicker (no joke).
0 comments:
Post a Comment