View Full Version : FractalizeR: VBulletin 4 extensive optimization guide (Part II)

03 May 2010, 14:42
Continuation from Part I (http://www.vbulletin.org/forum/showthread.php?t=241782)

Install mod_evasive and DDOSDeflate script

If your forum is a constant target for DDOS attacks, to lower their performance impact, you can install Apache module mod_evasive (http://www.zdziarski.com/blog/?page_id=442) and DDOS Deflate (http://www.inetbase.com/scripts/ddos/) scripts. mod_evasive installation guides are scattered across the net, so I leave you with Google on it’s installation. As for DDOS Deflate, just wget http://www.inetbase.com/scripts/ddos/install.sh and run it (don’t forget to chmod u+x it first). Generally these two doesn’t need to be tuned and their default settings are ok.

Move Avatars and Profile Pics to the file system

You need to move pictures to the filesystem as well. Go to your AdminCP, expand the Avatars menu on the left side and click User Picture Storage Type.

Move CSS to the file system

Go to your AdminCP, expand the vBulletin Options menu and select vBulletin options. From there select Style and Language Options in the drop down menu. Then set “Store CSS Stylesheets as Files?” to Yes and click Submit.

Enable caching of static content

This option will tell user browser to download files from your website less often thus decreasing server load and improving page load times for user. You will have to have mod_expires active in Apache. Add the following to .htaccess in the forum root

## Expires
<IfModule mod_expires.c>
ExpiresActive On
ExpiresDefault "access plus 1 seconds"
ExpiresByType text/html "access plus 1 seconds"
ExpiresByType image/gif "access plus 3456000 seconds"
ExpiresByType image/jpeg "access plus 3456000 seconds"
ExpiresByType image/png "access plus 3456000 seconds"
ExpiresByType text/css "access plus 3456000 seconds"
ExpiresByType text/javascript "access plus 3456000 seconds"
ExpiresByType application/x-javascript "access plus 3456000 seconds"
ExpiresByType application/javascript "access plus 3456000 seconds"
</IfModule> I don’t recommend here to turn off E-tags because I am not currently convinced this will improve performance. Also turning them off together with enabling mod_expires may lead to server reporting files are up to date when they have updated and client should fetch updated versions of them.

Install and attach MemCached

Consider installing and attaching MemCached to VBulletin (for example, from RPMForge (http://rpmrepo.org/RPMforge) repository. If you are on RedHat or CentOS you can install RPMForge repository quickly this way (http://wiki.centos.org/VladislavRastrusny/AutoRPMForgeRepo) and install MemCached from there using yum install memcached after. Don’t forget to issue chkconfig memcached on to make sure memcached will auto-start on next server reboot).
You will need to edit your /includes/config.php and uncomment the following lines there:

$config['Datastore']['class'] = 'vB_Datastore_Memcached';
$i = 0;
// First Server
$config['Misc']['memcacheserver'][$i] = '';
$config['Misc']['memcacheport'][$i] = 11211;
$config['Misc']['memcachepersistent'][$i] = true;
$config['Misc']['memcacheweight'][$i] = 1;
$config['Misc']['memcachetimeout'][$i] = 1;
$config['Misc']['memcacheretry_interval'][$i] = 15; Also, memcached from RPMForge repository is insecure by default and is listening on all IPs by default (it allows external connections). Either close the port 11211 with firewall, or consider to always start memcached with “-l″ option to listen only on localhost. On RedHat or CentOS you can do that by editing /etc/sysconfig/memcached file and adding a setting to OPTIONS line like this:


InnoDB: Migrating

Consider migrating to InnoDB (http://www.vbulletin.com/forum/entry.php?2413-Part-2-vB4mance-vBulletin4-Optimization-Basic-Guide-to-InnoDB-Conversion) to improve concurrency if you have a really large forum and you want to make it faster. Please note, that on small forums (or on servers with a little of physical memory) this option can even slow things a bit. Be sure you have at least 2-4Gb RAM on your server before considering InnoDB migration.

InnoDB: increase innodb_buffer_pool_size

If you migrated to InnoDB, you need to increase innodb_buffer_pool_size (http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/) to at least 512Mb depending on your server free RAM available. It is the memory area, used to cache data for InnoDB tables and by default it is very small (8Mb only, suitable for Pentium 166MMX home machine with database of your video tapes).
If your server is running something else together with VBulletin, please consult with your server administrator for this change since it is server-wide.

InnoDB: lower transaction isolation level

VBulletin is running in autocommit mode and does not really use transactions. Default InnoDB transaction isolation level is REPEATABLE READ, which leads to excessive use of system resources in autocommit mode. You can lower it to READ COMMITTED. If you run ONLY VBulletin on your server, to lower transaction isolation level you need to add the following line to /etc/my.cnf (MySQL main configuration file):

transaction-isolation = READ-COMMITTED If your server is also running something else, that is using InnoDB transactions with another isolation level, you will have to patch /includes/class_core.php instead of changing MySQL default configuration. Open it and add to vB_Database::db_connect before “return $link” the following:

$this->execute_query(true, $link); Remember to do this (class_core.php patch) after VBulletin upgrade also. However, if you forget, nothing dangerous will happen. You will just loose this optimization’s benefits.

Tune MySQL for your forum

There are several ways to optimize your MySQL server even if you are not a professional DBA. One is to open PHPMyAdmin, go to Status tab to see server status and look for values in red color there. You will find some hints next to them (although, they could be not so clear).
Another is to use various MySQL analysis scripts, that analyze MySQL status reported by server themselves. You can get one here (http://www.day32.com/MySQL/) (it’s a bash script, you need to wget it, then chmod u+x tuning-primer.sh, and then execute: ./tuning-primer.sh) and another in Perl here (http://blog.mysqltuner.com/download/). They report more understandable resuts.
Before making changes to your my.cnf, please, consider making a backup of it so that you could easily revert corrupted or unoptimal settings.

Optimize your MySQL tables regularly

Consider running the following simple script every several weeks depending on forum load (of course, you should run it at nights when your forum is the least loaded)


//Change the following line depending on where you placed this script.
require_once (dirname(__FILE__) . '/../www/includes/config.php');

ini_set("memory_limit", "50M");

//Connecting DB
mysql_connect($config['MasterServer']['servername'], $config['MasterServer']['username'],
$config['MasterServer']['password']) or die(mysql_error());
mysql_select_db($config['Database']['dbname']) or die(mysql_error());

$sql = 'SHOW TABLES';
$alltables = mysql_query("$sql") or die(mysql_error());
while ($table = mysql_fetch_assoc($alltables))
foreach ($table as $db => $tablename) {
mysql_query("OPTIMIZE TABLE `".$tablename."`;") or die(mysql_error());
sleep(10); //Let some queries to execute to lower forum load
} Don’t put this script inside www root because it can be used as ddos tool if it will be publicly accessible ;) Keep in mind, that running this script may take a long time depending on your forum (especially, if you switched into InnoDB, because OPTIMIZE TABLE for InnoDB currently is equivalent to ALTER TABLE with full table rebuild. This will also effectively write-lock the table during OPTIMIZE operation).
You can add it to Linux cron if you like.

Consider moving to newer MySQL server version

Generally, newer MySQL server versions are more clever. They know how to optimize queries better, how to handle resources better etc. So, you may receive a small benefit from moving to a newer MySQL server version. If you are on CentOS or RedHat this is complicated, because default repositories have only 5.0 version of MySQL server. So, I cannot recomment that. But you can always get RPM from MySQL website (http://www.mysql.com/) and install it. If you are an IT geek, you can even move to Percona MySQL server. This is a tweaked MySQL server with some optimization patches (from Google for example) applied and improved InnoDB performance. Unlike Oracle’s MySQL, Percona has own RPM repository, that can be installed using this (http://www.percona.com/percona-builds/percona-release/) rpm.
Despite performance gains, I don’t recommend this until you are really in need because maintaining another version of MySQL different from the one provided with your OS by default will take much time and resources from you.

Turn off apache modules, that you don’t need

Consider turning off all apache modules you don’t use on server, that holds your forum. For example, on my server httpd.conf has the following LoadModule configuration part:
LoadModule auth_basic_module modules/mod_auth_basic.so
#LoadModule auth_digest_module modules/mod_auth_digest.so
LoadModule authn_file_module modules/mod_authn_file.so
#LoadModule authn_alias_module modules/mod_authn_alias.so
#LoadModule authn_anon_module modules/mod_authn_anon.so
#LoadModule authn_dbm_module modules/mod_authn_dbm.so
#LoadModule authn_default_module modules/mod_authn_default.so
LoadModule authz_host_module modules/mod_authz_host.so
LoadModule authz_user_module modules/mod_authz_user.so
#LoadModule authz_owner_module modules/mod_authz_owner.so
#LoadModule authz_groupfile_module modules/mod_authz_groupfile.so
#LoadModule authz_dbm_module modules/mod_authz_dbm.so
#LoadModule authz_default_module modules/mod_authz_default.so
#LoadModule ldap_module modules/mod_ldap.so
#LoadModule authnz_ldap_module modules/mod_authnz_ldap.so
LoadModule include_module modules/mod_include.so
LoadModule log_config_module modules/mod_log_config.so
LoadModule logio_module modules/mod_logio.so
LoadModule env_module modules/mod_env.so
LoadModule ext_filter_module modules/mod_ext_filter.so
LoadModule mime_magic_module modules/mod_mime_magic.so
LoadModule expires_module modules/mod_expires.so
LoadModule deflate_module modules/mod_deflate.so
LoadModule headers_module modules/mod_headers.so
#LoadModule usertrack_module modules/mod_usertrack.so
LoadModule setenvif_module modules/mod_setenvif.so
LoadModule mime_module modules/mod_mime.so
#LoadModule dav_module modules/mod_dav.so
#LoadModule status_module modules/mod_status.so
#LoadModule autoindex_module modules/mod_autoindex.so
#LoadModule info_module modules/mod_info.so
#LoadModule dav_fs_module modules/mod_dav_fs.so
#LoadModule vhost_alias_module modules/mod_vhost_alias.so
LoadModule negotiation_module modules/mod_negotiation.so
LoadModule dir_module modules/mod_dir.so
#LoadModule actions_module modules/mod_actions.so
#LoadModule speling_module modules/mod_speling.so
#LoadModule userdir_module modules/mod_userdir.so
LoadModule alias_module modules/mod_alias.so
LoadModule rewrite_module modules/mod_rewrite.so
#LoadModule proxy_module modules/mod_proxy.so
#LoadModule proxy_balancer_module modules/mod_proxy_balancer.so
#LoadModule proxy_ftp_module modules/mod_proxy_ftp.so
#LoadModule proxy_http_module modules/mod_proxy_http.so
#LoadModule proxy_connect_module modules/mod_proxy_connect.so
#LoadModule cache_module modules/mod_cache.so
#LoadModule suexec_module modules/mod_suexec.so
#LoadModule disk_cache_module modules/mod_disk_cache.so
#LoadModule file_cache_module modules/mod_file_cache.so
#LoadModule mem_cache_module modules/mod_mem_cache.so
LoadModule cgi_module modules/mod_cgi.so
LoadModule version_module modules/mod_version.soDisabled modules are prefixed with “#” (commented). After you edited httpd.conf, please don’t restart apache right away;) First you need to check syntax of the file to be sure, apache will startup normally. You can do this issuing apachectl configtest command. Generally, for example, disabling autoindex module requires some more commenting of httpd.conf because corresponding setting lines are not wrapped into IfModule directives.

Enable KeepAlive in Apache settings

KeepAlive setting allows Apache server to send replies to several requests over the same TCP connection. This is of course faster than to open new TCP connection for each resource sent. To enable KeepAlive search for “KeepAlive” in apache configuration file (httpd.conf) and set it to On:

KeepAlive On If there is no such option, you may add it. This will make your forum pages to load faster for endusers. However, this could lead to a need to slightly increase a number of MaxConnections allowed to be accepted by Apache and a little more resources will be consumed especially on forums with many users online.

Turn off PHP modules you don’t need

VBulletin uses the following PHP modules: gd, iconv, mbstring, mysql or mysqli (depending on your VBulletin database connection settings). All others (except for those, that are compiled with PHP by default and were not disabled using –disable-XXX switch during PHP compilation) are not required. You can skip loading them to make PHP occupy less memory and start faster. On RedHat and CentOS you can just comment “extension=” lines in each corresponding file inside /etc/php.d directory.

Turn on PHP bytecode caching

your PHP is running as apache module (mod_php) or
PHP is configured as fast-cgiyou can benefit from bytecode caching. By default, PHP is parsing and compiling to internal byte-codes (like Java) each script and on every client request. This takes time and resources. PHP bytecode caches allows to store once compiled script and reuse already compiled bytecodes. I recomment using APC as your bytecode cache. It can be installed from PECL repository. On RedHat and CentOS you can install it the following commands:

yum install php-devel
pecl install APC
The first command installs php development package, that is used to compile PHP extensions. The second installs APC itself. You may need to activate it inside your php.ini after compilation. You may need to install basic prerequisites for source compilation:

yum install autoconf libtool gcc gcc-c++ Please note, that if PHP is running as CGI, turning on bytecode cache will give you no benefits as the cache will be invalidated on each new request.

Optimizations, that will speed up forum for users, but may increase server load

Enable compression of content

If you have some spare CPU power and want to make your forum load faster for users, you can also enable static content compression. You will have to have both mod_headers and mod_deflate active in Apache. Add the following to your .htaccess in the forum root:

<IfModule mod_headers.c>
<IfModule mod_deflate.c>
AddOutputFilterByType DEFLATE text/css text/xml text/javascript application/x-javascript application/javascript
BrowserMatch ^Mozilla/4 gzip-only-text/html
BrowserMatch ^Mozilla/4\.0[678] no-gzip
BrowserMatch \bMSIE !no-gzip !gzip-only-text/html
</IfModule> You can also set Cookies and HTTP Header Options -> GZIP HTML Output to true to speed up HTML delivery to end user.

Forum settings, that seriously affect database performance

You can tune all below if your forum causes great load to database
General Settings ->Thread/Forum Read Marking Type
Server Settings and Optimization Options -> Cached Posts Lifespan
Server Settings and Optimization Options -> Update Thread Views Immediately
Server Settings and Optimization Options -> Update Attachment Views Immediately
Message Searching Options -> Maximum Search Results to Return
Message Searching Options -> Automatic Similar Thread Search
Message Searching Options -> Search Result Sharing
Forums Home Page Options -> Display Logged in Users?
Forum Display Options (forumdisplay) -> Show Users Browsing Forums
Forum Display Options (forumdisplay) -> Highlight Threads in Which User Has Posted
Thread Display Options (showthread) -> Show Users Browsing Threads
Thread Display Options (showthread) -> Check Thread Rating
Thread Display Options (showthread) Check Thread Subscription

03 May 2010, 19:14
great article - thanks for sharing

03 May 2010, 21:33
Nice one!

I actually cant change the directories for attachments and so on, because it is saying that there is an error with the folder ... i am pretty shure i messed up the path ... is it a full path or a relative one ? or is it from /srv/ ... ?

DDOSDeflate doesnt work for me on opensuse its kind of difficult : (

03 May 2010, 22:13
great article - thanks for sharing

Welcome ;)

is it a full path or a relative one ? or is it from /srv/ ... ?
Full one from root (/var/www/html/site.com/..... for example).

Create a PHP script somewhere and put to it the following code:
echo __FILE__;

It will show full script name. You will see dir there.

DDOSDeflate doesnt work for me on opensuse its kind of difficult : (
It may miss necessary tools (netstat and awk must be present at least as I know. Anyway, need to know what exacly the problem is.

03 May 2010, 23:29
than you again for part 2 of the article great job
even though is kind of difficult for me to deal with some stuff you've mention but its a great start for me to learn those

thanx for every word

03 May 2010, 23:48
Thank you very much indeed FractalizeR!

I will pop in later again here, gotta do a nerd root session later on this night however...

All the best

05 May 2010, 20:07
Great article! Really thank you! :)

In my httpd.conf I've this directives:

<IfModule mod_deflate.c>
DeflateCompressionLevel 9

<Location / >
SetOutputFilter DEFLATE
SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$ \
no-gzip dont-vary
SetEnvIfNoCase Request_URI \
\.(?:exe|t?gz|zip|bz2|sit|rar)$ \
no-gzip dont-vary
SetEnvIfNoCase Request_URI \.pdf$ no-gzip dont-vary

BrowserMatch ^Mozilla/4 gzip-only-text/html
BrowserMatch ^Mozilla/4\.0[678] no-gzip
BrowserMatch \bMSIE !no-gzip !gzip-only-text/html
Header append Vary User-Agent env=!dont-vary

<ifModule mod_expires.c>
ExpiresActive On
ExpiresDefault "access plus 1 seconds"
ExpiresByType text/html "access plus 1 seconds"
ExpiresByType image/gif "access plus 259200000 seconds"
ExpiresByType image/jpeg "access plus 259200000 seconds"
ExpiresByType image/png "access plus 259200000 seconds"
ExpiresByType text/css "access plus 60480000 seconds"
ExpiresByType text/javascript "access plus 21600000 seconds"
ExpiresByType application/x-javascript "access plus 21600000 seconds"
ExpiresByType application/javascript "access plus 3456000 seconds"

<ifModule mod_headers.c>
<filesMatch "\\.(ico|pdf|flv|jpg|jpeg|png|gif|swf)$">
Header set Cache-Control "max-age=25920000, public"
<filesMatch "\\.(css)$">
Header set Cache-Control "max-age=6048000, public"
<filesMatch "\\.(js)$">
Header set Cache-Control "max-age=2160000, private"
<filesMatch "\\.(xml|txt)$">
Header set Cache-Control "max-age=2160000, public, must-revalidate"
<filesMatch "\\.(html|htm|php)$">
Header set Cache-Control "max-age=1, private, must-revalidate"
<ifModule mod_headers.c>
Header unset ETag
FileETag None
<ifModule mod_headers.c>
Header unset Last-Modified

Are correct?

17 May 2010, 02:17
Nice tutorial. Didn't get the script part. Could explain better? I've created the php file with your script in it but just get a blank page.

17 May 2010, 07:40
ini_set("display_errors", true);
to the beginning of the file after php tag and see what error is that.

17 May 2010, 12:14
Thanks Fractalizer, it was a wrong path problem. But tel me something, does the script prints any results in the screen or?...

18 May 2010, 07:28
No, it does not. If you put something in cron, all printed results will be sent to your root email. If nothing is printed - nothing is sent. So, it's good for having all problems on your email.

21 May 2010, 19:40
*scratches me head*
nice work. :) I will probably be need these articles in a year or 2.

23 May 2010, 00:32
Very Usefull :)

Thanks for share.

23 May 2010, 16:21
after turning on saving ccs to filesystem i get this error. what could be wrong ?

24 May 2010, 07:22
after turning on saving ccs to filesystem i get this error. what could be wrong ?

Upgrade to latest VB (4.0.3 for today) please and revert old templates. When you add a new style to VB, make it a child of main default style. Don't make it separate style.

19 Jun 2010, 04:10
Do we need to configure MemCached in any way or is it tuned out of the box?

19 Jun 2010, 08:38
Actually, it is turned on and is listening on all Ips. So, you might need to close it's port 11211 to make it inaccessible externally.

04 Aug 2010, 07:56
Where do we check for this on a VPS?

mod_expires active in Apache

04 Aug 2010, 08:55
Where do we check for this on a VPS?

if you are trying to cache then your VPS may not support this, i know that cpanel VPS does not, so enabling it in config.pph is not correct

or you can read this more more information

04 Aug 2010, 09:14
if you are trying to cache then your VPS may not support this, i know that cpanel VPS does not, so enabling it in config.pph is not correct

or you can read this more more information
I have the code above in my domain root htaccess but I don't know where to see if it is enabled. There has to be somewhere it tells you, doesn't there?

EDIT: I found it. I do have it installed on the VPS. I ran this line from the root:

/usr/local/apache/bin/apachectl -l

and it listed all the modules installed. And mod_expires was there. ;)

06 Aug 2010, 23:12
Thanks Fractalizer , very usefull .

Can we use MemCached & eaccelerator togather ? If no then which one is better with attachment on file system?

09 Aug 2010, 10:10
Yea, you can surely use both at the same time.

10 Aug 2010, 06:55
Really thanks for the article ....

17 Aug 2010, 08:23
Yea, you can surely use both at the same time.

Thanks for your response ,

Will you recommend using both ? if not then which one you prefer ?

18 Aug 2010, 08:05
They are a little bit different. Vbulletin uses memcached to cache it's datastore, and eaccelerator caches PHP bytecode to speedup execution speed. So, you should use both if you can.

07 Oct 2010, 21:08
Too complicated for me to try.

08 Oct 2010, 07:25
Life is also a difficult thing ;)

08 Oct 2010, 08:09

01 Nov 2010, 17:44
Very nice Tutorial, helps also much with windows servers

final kaoss
08 Dec 2010, 03:40
Hi instead of that file to optimize the database once per week can't we just make a plugin to do this for us in the admin cp via the Plugin & Products System -> add new plugin option?

If so what code do we use for that?

18 Feb 2011, 20:16
I'm trying the "InnoDB: Migrating" and managed to convert all tables successfully except

alter table thread engine=InnoDB;

Which gives me this error
#1214 - The used table type doesn't support FULLTEXT indexes

Is their a way to fix it, and if not is it ok to leave it as it is?

19 Feb 2011, 08:30
Did you convert all tables without exceptions?

19 Feb 2011, 11:50
Did you convert all tables without exceptions?

Only the tables you listed here:

and thread was the only one that gave the full text error

20 Feb 2011, 08:10
Well, it's some legacy indexes. You can safely drop them:
alter table post drop index title;
alter table thread drop index title;

20 Feb 2011, 11:40
Well, it's some legacy indexes. You can safely drop them:
alter table post drop index title;
alter table thread drop index title;

thanks you, worked great

21 Feb 2011, 07:33

09 Mar 2011, 06:34
Great article. :)

15 Mar 2011, 10:40
One thing that also speeds up your community is switching from Apache to Litespeed(web server).