Optimize vBulletin 4

Here's a few tips you can use to make large vBulletin forums faster and scale better (starting with the easiest stuff on down to the most difficult)... Please keep in mind that what works for me, may not work for you (so the obvious "do at your own risk" preface applies).

Disable Memberlist
There is an option to simply disable the memberlist under Settings -> Options -> User Listing Options.

It might be missed at first, but ask yourself what the *real* purpose of it is... members can sort it to flex their e-peen to see who has the most posts, reputation or whatever else.

Do people really use this to search for members? Probably not... when was the last time you used it to search for a member that you didn't already know who they were?

In reality the main purpose of it is to give spammers an easy way to harvest your usernames so they can more easily blast out PM spams. On top of that, the queries required to generate the memberlist are terrible for your DB servers and can cause massive locking issues and DB server loads.

Nasty Query In forumdisplay.php
In vBulletin 4, the minimum required MySQL version is 4.1.0. This means we can start utilizing sub-queries where appropriate (or just more efficient in this case).

forumdisplay.php is one of the most trafficked pages in vBulletin, so anything we can do to make this page more efficient is definitely worth the effort.

I suggested it to the vBulletin developers years ago back in version 3.7.x, but nothing ever was done to fix it. Hopefully someday this change will be incorporated into the source of vBulletin 4.

In forumdisplay.php, change this query:

PHP:

# Include visible IN (0,1,2) in order to hit upon the 4 column index
    $threadscount = $db->query_first_slave("
        SELECT COUNT(*) AS threads, SUM(IF(thread.lastpost > $lastread AND open <> 10, 1, 0)) AS newthread
        $hook_query_fields
        FROM " . TABLE_PREFIX . "thread AS thread
        $tachyjoin
        $hook_query_joins
        WHERE forumid = $foruminfo[forumid]
            AND sticky = 0
            $prefix_filter
            $visiblethreads
            $globalignore
            $limitothers
            $datecut
            $hook_query_where
    ");

to this:

PHP:

# Include visible IN (0,1,2) in order to hit upon the 4 column index
    $threadscount = $db->query_first_slave("
        SELECT COUNT(*) AS threads,         
        (
            SELECT COUNT(*) AS newthread
            FROM " . TABLE_PREFIX . "thread AS thread
            WHERE forumid = $foruminfo[forumid]
                AND lastpost > $lastread
                AND open <> 10
                AND sticky = 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 sticky = 0
            $prefix_filter
            $visiblethreads
            $globalignore
            $limitothers
            $datecut
            $hook_query_where
    ");

The new query yields the exact same results, but does so with a sub-query instead of a terribly slow SUM(IF()) statement that is unable to hit an index.

Look at the time to run the default query:

Code:

+---------+-----------+
| threads | newthread |
+---------+-----------+
|   96484 |      2051 | 
+---------+-----------+
1 row in set (0.49 sec)  

...and compare it to the time needed to run the new query:

Code:

+---------+-----------+
| threads | newthread |
+---------+-----------+
|   96484 |      2051 | 
+---------+-----------+
1 row in set (0.01 sec)

We just made one of the main queries in forumdisplay.php 50x faster.

Clean Up Cache
vBulletin 4 has an internal database based cache that never cleans itself up (don't ask me why). Expired cache items will stay in the database indefinitely (until you purge the entire cache).

Create a plug-in at the cron_script_cleanup_hourly location with the following PHP code and cache items that are expired/unused will be purged hourly:

PHP:

require_once(DIR . '/includes/class_bootstrap_framework.php'); 
vB_Bootstrap_Framework::init();
vB_Cache::instance()->clean(true);


Increase Private Message List Speed
If you have never imported private messages from an external source (via ImpEx or anything else), you can safely rely on the pmid column to be in the same sort order as the PM date. Sorting on pmid will make it so your database server doesn't need to dump the PMs into a temporary table to perform the sort (making the query much faster).

To do this, make a plug-in at the private_messagelist_filter hook location with the following line in it:

PHP:

if ($sortfield == 'pmtext.dateline') $sortfield = 'pm.pmid';

You just made private.php ~20% faster.

Slow Social Groups Query
Within includes/functions_socialgroup.php (at the end) there is a query with a clever (but VERY inefficient sub-query). This change makes the social groups page in this forum render in 0.17 seconds instead of 1.2 seconds (7 times faster). There are many good times to use sub-queries (for example in the above example in forumdisplay.php). This query is not one of them.

Change this:

PHP:

$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 avatar 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
        )
        ORDER BY socialgroupmember.dateline DESC
    ");

    $group_members = array();
    while ($member = $vbulletin->db->fetch_array($result))
    {
        fetch_avatar_from_userinfo($member, true, true);
        $group_members[$member['groupid']][] = $member;
    }

to this:

PHP:

$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 avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)" : "") . "
        WHERE socialgroupmember.groupid = $groupid
        ORDER BY socialgroupmember.dateline DESC
        LIMIT 10
    ");
    while ($member = $vbulletin->db->fetch_array($result))
    {
        fetch_avatar_from_userinfo($member, true, true);
        $group_members[$member['groupid']][] = $member;
    }
}

Using Memcache Datastore More Efficiently
If you use Memcache as your datastore, you can make it faster by getting multiple datastore keys in a single pass (Memcache supports it, why not use it?).

In includes/class_datastore.php, within the vB_Datastore_Memcached::fetch() method, replace this:

PHP:

$unfetched_items = array();
foreach ($items AS $item)
{
    $this->do_fetch($item, $unfetched_items);
}
 


with this:

PHP:

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 page  view) about 20% faster.  Memcache is very fast either way, so you  probably won't notice much of a difference, but there's no point in  *not* making something faster when you can in my opinion.



Drop FULLTEXT Indexes
If you upgraded from vBulletin 3, you can drop every FULLTEXT index in your database EXCEPT for the ones in searchcore_text and searchgroup_text. The vBulletin 4 upgrade process does not remove the old vBulletin 3 FULLTEXT indexes that are no longer used. The two biggest FULLTEXT indexes that are not used any longer are in your post and thread table.

Alter post.userid Index
There is a query that vBForum_Search_Result_Post::set_replydata() uses that searches the post table by userid and parentid. There is no index that includes both columns, so if the user has a lot of posts in your forum, this query can be terribly slow.

If you simply add the parentid column to the existing userid index the query will be lightning fast.

Code:

ALTER TABLE post DROP INDEX userid, ADD INDEX userid (userid, parentid) 

Check Thread Indexes
If any of your forums have a default sort order that is set to anything other than Last Post Time, make an index for it in your thread table.

We have some forums that the default sort order is the thread creation time (the column used for that is "dateline"), so we created the following index:

Code:

ALTER TABLE thread ADD INDEX forumid2_dp (forumid, visible, sticky, dateline) 

Use YUI 2.8.x
vBulletin 4.x currently ships with an outdated version of Yahoo User Interface (version 2.7.0). You can simply replace 2.7.0 with 2.8.x without any problems (2.8.x has a number of bug fixes).

The easiest way to do this is to go to Settings -> Options -> Server Settings and Optimization Options and make sure your Use Remote YUI setting is set to use Yahoo or Google remote hosting. Then edit your includes/class_core.php file and change this line:

<a href="http://www.php.net/define" target="_blank">

PHP:

define('YUI_VERSION', '2.7.0'); // define the YUI version we bundle

to this:

<a href="http://www.php.net/define" target="_blank">

PHP:

define('YUI_VERSION', '2.8.1'); // define the YUI version we bundle

Setting SQL Mode
A blank sql_mode is the default setting for MySQL, so unless you've overridden your MySQL server to have a specific sql_mode, just comment the following line within includes/init.php (all it does is make sure sql_mod is set to the default value):

PHP:

$db->force_sql_mode('');

like so:

PHP:

// $db->force_sql_mode('');

The query doesn't take long to run, but it is a query to the DB server nonetheless. One less query per page view is always a good thing.

CMS Article Caching
CMS articles are completely run through the BBCode parser for every page view and it takes a painful amount of time to do so (this is the main reason the vBulletin CMS is so slow).

In packages/vbcms/content/article.php, change this:

PHP:

$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:
<font color="#000088"><font face="monospace">

PHP:

$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 articles only need to do so no more often than once every 6 hours (or if you edit the article). This small change made our vBulletin CMS render pages 8.5x faster (no joke).

showgroups.php Query Cache
There is a query in showgroups.php that does not scale (it will get slower and slower the more users you have).

A quick fix is to just cache the query results for 6 hours (so it's not run more than once every 6 hours) like so...

Change this:

PHP:

// get usergroups who should be displayed on showgroups
// Scans too many rows. Usergroup Rows * User 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 avatar 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 wrapped the vb_Cache class mechanism around the query and $groupcache generation):

PHP:

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 displayed on showgroups
    // Scans too many rows. Usergroup Rows * User 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 avatar 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 makes the showgroups.php page load 6x faster.

Be Very Careful What Add-Ons You Install
Just because someone makes an add-on, it does not mean it was made with a large forum in mind. A perfect example is the iTrader system. We use it here, but only after we made MANY changes to it so that it has the ability to scale.

It's best to assume add-on developers don't know what they are doing and go over all their code before you go live with something. Make sure the add-ons aren't causing database load issues, make sure they don't have potential SQL injection issues or XSS issues. Unfortunately with thousands of add-ons out there, you will just have to do this yourself (or hire someone to do it). And it's also the reason we went the route of just writing all our own plug-ins for the most part vs. building on something that might be bad to begin with. (Yes, I know I have trust issues. heh)

Replace Search
For this site, I developed a Sphinx-based search replacement for the normal vBulletin search that is freely available to premium members over here.

Using this is probably the single best thing you can do for your database servers.

Don't Use InnoDB Tables
If you get to the point where you are not having any table/record locking issues (most notably after you replace vBulletin search), changing vBulletin's InnoDB tables to MyISAM. MyISAM is faster for individual queries (since it doesn't need to manage record locks among other things). InnoDB is faster, but only because it allows you to run queries concurrently. If your queries run so fast under MyISAM that they don't even *need* to run concurrently, there is no point to run InnoDB.

In my opinion the best solution to slow database queries is to fix the query itself so it's not slow. Just converting the table to InnoDB is a lazy way to fix it because all you are doing is making it okay for other queries to run while the slow query is running.