Are you actually using innodb tables ? i.e. /var/lib/mysql are files with *.myd and *.myi extensions ? If you converted your tables to innodb using command ALTER TABLE tablename ENGINE=InnoDB; then you're using innodb for some tables possibly up to 1GB in index+data size, i'd at least at another 1GB memory minimum to your server. If not using inndob then below my.cnf might be best suited to you as below my.cnf assumes you converted to innodb tables.
Then try the following in this exact order. You can
ignore any of the suggestions that you have already done.
1. Upgrade MySQL 5.1.44 or 5.1.45 if possible and then upgrade PHP to 5.2.11 or 5.2.12 (in either case install as apache module and NOT CGI. For windows based PHP try FastCGI method or ISAPI method). If you're upgrading from MySQL 4.x, you must upgrade to MySQL 5.0.xx before moving to MySQL 5.1.x series.
Backup your databases prior to mysql upgrades where possible using mysqldump via ssh telnet and not via admincp backup options or phpmyadmin which in some cases of large databases can result in incomplete backups!
2. Edit mysql server's /etc/my.cnf or c:\my.ini for windows and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards. Make sure to restart mysql server everytime you make changes to your my.cnf for the changes to take effect.
If mysql doesn't restart properly after my.cnf changes and you're on VPS server, make sure skip-innodb entry is removed or commented out from below my.cnf
If using Innodb tables, best to use innodb_file_per_table option in my.cnf. To enable set innodb_file_per_table = 1 to enable or 0 to disable (default). Read more about this option here
MySQL :: MySQL 5.0 Reference Manual :: 13.2.2.1 Using Per-Table Tablespaces and
MySQL :: MySQL 5.0 Reference Manual :: 13.2.2 InnoDB Configuration and optional use of raw partitions for InnoDB data files
MySQL :: MySQL 5.0 Reference Manual :: 13.2.2.2 Using Raw Devices for the Shared Tablespace.
So 4 steps to utilise this function:
BACKUP all your mysql databases BEFORE you do this
1. add or enable innodb_file_per_table option in my.cnf and create a new directory with mkdir command for appropriate path for innodb files pertaining to below setting for innodb_data_home_dir i.e.
innodb_data_home_dir = /var/lib/mysql/idatadir. A separate disk or raid array from main /var mysql data partition would be beneficial if possible.
2. restart mysql server
3. do an
ALTER TABLE tablename ENGINE = InnoDB; on each database tablename you want to recreate a separate *.idb file for OR alternatively
4. if existing database tablename is InnoDB table already, use command:
mysqlcheck --optimize databasename tablename
do this for each tablename of your database you want split to separate *.idb files for.
or for entire databases
mysqlcheck --optimize databasename
In relation to vB 3.8.x read
vBulletin Community Forum
Might need to adjust innodb_buffer_pool_size = 128M depending on the total cummulative size of innodb tables data + indexes see
MySQL :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 13.2.4 InnoDB Startup Options and System Variables
If you get mysql server gone away error messages, then keep increasing wait_timeout value in my.cnf by 60 second increments, then restart mysql after my.cnf changes for it to take effect. Test for a few days and see if you get less or eliminate that error message. If it still occurs, then keep repeating the 60 second increment until the message goes away. Each vB forum and server will have different optimal wait_timeout values depending on your vB forum traffic patterns and server hardware specifications.
3. Remove Eaccelerator or APC Cache if they're installed (check phpinfo.php url of yours to see) and instead install Xcache v1.2.2
Release-1.2.2 which seems to be a bit faster than APC Cache -
vBulletin Community Forum. Xcache site
XCache, install guide
InstallFromSource and forums
lighttpd forum - XCache
Remember to set in php.ini the values for xcache.size to 128M and for xcache.count to 4. Set xcache.var.size to 3M per vB forum installed and set xcache.var.count to 1 or 2 so you'd have 3M divide by 1 or 2.
Install the xcache admin end and monitor how much memory is used and available and adjust accordingly. The size specified will be divided into the value of counts set (number of cpu cores). So for count of 4, 64MB will be split to 16MB per cpu core and xcache.count_size of 12MB will be split to 3MB per cpu core.
Now enable xcache datastore caching for each vB forum (
vB 3.7.x and higher only supported). In vB config.php file these entries
Insertar CODE, HTML o PHP:
// ****** DATASTORE CACHE CONFIGURATION *****
// Here you can configure different methods for caching datastore items.
// vB_Datastore_Filecache - to use includes/datastore/datastore_cache.php
// vB_Datastore_APC - to use APC
// vB_Datastore_XCache - to use XCache
// vB_Datastore_Memcached - to use a Memcache server, more configuration below
// $config['Datastore']['class'] = 'vB_Datastore_Filecache';
// ******** DATASTORE PREFIX ******
// If you are using a PHP Caching system (APC, XCache, eAccelerator) with more
// than one set of forums installed on your host, you *may* need to use a prefix
// so that they do not try to use the same variable within the cache.
// This works in a similar manner to the database table prefix.
// $config['Datastore']['prefix'] = '';
the 2 lines you need to modify by uncommenting (removing // from beginning of line) and changing their values
from
Insertar CODE, HTML o PHP:
// $config['Datastore']['class'] = 'vB_Datastore_Filecache';
// $config['Datastore']['prefix'] = '';
to
Insertar CODE, HTML o PHP:
$config['Datastore']['class'] = 'vB_Datastore_XCache';
$config['Datastore']['prefix'] = 'forum1';
change forum1 to whatever you want but it must be unqiue to each of your vB forums installed on same server so forum1, forum2, forum3, forum4, forum5 for 5 different vB forums' config files.
Also if you have vBSEO installed, ensure to enable vBSEO caching support for memcached or xcache or one of the supported and highlighted caching systems listed in vBSEO admin cp.
4. Upgrade to vB 3.0.17
vBulletin 3.0.17 Released if you're on vB 3.0.xx or upgrade to vB 3.5.8
vBulletin 3.5.8 Released if you're on vB 3.5.x. Or if on vB 3.6.x, upgrade to vB 3.6.12 PL2
vBulletin 3.8.4 PL1, 3.7.6 PL1 and 3.6.12 PL2 Released.
But ultimately, it's best to upgrade to the latest stable vB 3.8.5
vBulletin 3.8.5 Maintenance Release Now Available!! or at least 3.7.7. You can use my method of upgrading outlined at
eva2000's Upgrade method which is essentially same in that you make a copy of your live database and import it into a new empty database and point vB 3.7.0 config.php to that new imported database name, so you essentially do an upgrade on a copy of your database, leaving original database intact in case of any problems. This method also allows you to run the original database on a different directory so to run both original forum/database along side the upgraded forum/database so you can easily revert all changed templates on upgraded forum and then using old forum/database transfer or port your custom style/images etc to the new upgrade database.
For vB 4.0.x upgrade to latest 4.02PL4
Security Patch Release 4.0.2 PL4
Read each versions listed thread to understand the changes that have occured etc.
5. If you just upgraded to vB 3.5.x/3.6.x try to disable these 4 options:
Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?
Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums
Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread
Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search
Admin CP -> vBulletin Options -> General Settings -> Thread/Forum Read Marking Type - Database automatic forum marking is very processor intensive compared to Inactivity/Cookie based or Database no automatic forum marking, so if your mysql process is constantly pegged at 100+ % cpu usage, you don't want to be using automatic forum marking Database type.
Or relevant sections in vB 3.7.x or 3.8.x
For vB 4.x try to DISABLE = set to NO these options
Admin CP -> vBulletin Options -> General Settings -> Use Quick Navigation Menu
Admin CP -> vBulletin Options -> General Settings -> Thread/Forum Read Marking Type - Database automatic forum marking is very processor intensive compared to Inactivity/Cookie based or Database no automatic forum marking, so if your mysql process is constantly pegged at 100+ % cpu usage, you don't want to be using automatic forum marking Database type.
Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?
Admin CP -> vBulletin Options -> Forum Home Page Options -> Display Today's Birthdays?
Admin CP -> vBulletin Options -> Forum Home Page Options -> Display Calendar Events?
Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums
Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread
Admin CP -> vBulletin Options -> Thread Display Options -> Check Thread Rating
Admin CP -> vBulletin Options -> Thread Display Options -> Check Thread Subscription (disable to check if it's load related if not re-enable it)
Admin CP -> vBulletin Options -> Thread Display Options -> Full Next / Prev Links
Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search
Try to ENABLE = set to YES these options
Admin CP -> vBulletin Options -> Message Searching Options -> Queue Search Updates
Admin CP -> vBulletin Options -> Message Searching Options -> Search Result Sharing
6. Check phpinfo.php url to see if you have mod_gzip (Apache 1.3.x) or mod_deflate (Apache 2.x) loaded/installed (on phpinfo.php url page in browser you can do CTRL+F to bring up find prompt and type in mod_gzip or mod_deflate to quickly see). If you have either mod_gzip or mod_deflate loaded, then ensure vB admincp gzip compression is disabled since it's the same thing as mod_gzip or mod_deflate and double compression will just increase cpu loads. If you don't have mod_gzip or mod_deflate installed, then DO NOT set vB admincp gzip compression to a level higher than 1. Higher than 1, will only increase cpu loads unncessarily.
7. Edit httpd.conf values to following and restart apache for each change to take effect
<IfModule prefork.c>
StartServers 15
MinSpareServers 15
MaxSpareServers 20
ServerLimit 256
MaxClients 180
MaxRequestsPerChild 1000
8. Tune your hard drive/partitions with noatime - read carefully these pages before trying
vBulletin Community Forum and
The atime and noatime attribute and
Anyone using noami? - Web Hosting Talk. Use at your own risk (if any). Note, to remount partition use mount -o remount /partition where /partition is partition name.
