Server optimization after migration from vB

I migrated from vBulletin 4 (+ vBSEO) recently and would like to ask which settings I should change, because my server settings were optimized for vBulletin.

My forum has on average 1100 users online with 60 minutes cookie timeout (maximum 2200 users) and 1.3M posts.

Server (dedicated):

- Intel® Core™ i7-2600 Quad Core with HT 3.40GHz (8 cores total)
- 16GB DDR3 RAM
- 2 x 3TB SATA 6GB/s 7200 rpm (Software-RAID 1)
- CentOS 6.5 x86_64
- Apache/2.2.25
- PHP 5.3.27
- MySQL 5.1.70-cll
- phpinfo: http://www.matkapuhelinfoorumi.fi/phpinfo.php

Optimized settings for vBulletin:

my.cnf:

[mysqld]
datadir=/var/lib/mysql
back_log = 100
#skip-innodb
max_connections = 500
key_buffer_size = 640M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 256K
table_definition_cache = 8000
table_open_cache = 8000
thread_cache_size = 384
wait_timeout = 90
interactive_timeout = 60
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 128M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 512K
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
range_alloc_block_size = 4096
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 8
server-id=1
collation-server=latin1_general_ci

innodb_open_files = 500
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 32M

innodb_log_files_in_group = 2
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT

slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 128M

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 1M
read_buffer_size = 1M
write_buffer_size = 1M

[mysqlhotcopy]
interactive-timeout


httpd.conf:

KeepAlive: On
MaxKeepAliveRequests: 100
KeepAliveTimeout: 2
MinSpareServers: 10
MaxSpareServers: 15
StartServers: 10
MaxClients: 150
MaxRequestsPerChild: 1000

if you don't have values in httpd.conf as per above format then most likely you're using apache prefork or worker mpm compiled versions, for prefork you should see values and format similar to this, edit them as per below and restart apache

Timeout 60
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 2

<IfModule mpm_prefork_module>
StartServers 10
MinSpareServers 10
MaxSpareServers 15
ServerLimit 256
MaxClients 150
MaxRequestsPerChild 1000
</IfModule>

xcache:
Remember to set in php.ini the values for xcache.size to 160M and for xcache.count to a value of equal to number of processor cores you have so single core cpu = 1 or single dual core cpu = 2 or single quad core cpu = 4 or dual cpus each with dual core = 4 or dual cpus each with quad core = 8. 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 http://xcache.lighttpd.net/wiki/InstallAdministration 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.
 
Allocate more memory to InnoDB buffer pool. You currently have 512MB

1.3M posts must have a DB well over 1GB, so allocate as much as possible to the buffer (at least a couple of GB)
 
MySQL recommends key_buffer_size value to be 25% of the total available RAM on a machine.
25% of 16GB RAM is 4GB. You have 640 mega bytes which is conservative, but if you have a lot off traffic which needs memory as well go up to 3GB. But not lower than that.
 
The first thing I'd recommend is to get off Apache. Check out NGINX or LiteSpeed. The differences are amazing.

The second thing is to run mysqltuner after the site has been running for several days.

Here is a graph to show what I mean. Can you see when a change was made to the my.cnf? ;)

image.webp
 
MySQL recommends key_buffer_size value to be 25% of the total available RAM on a machine.
25% of 16GB RAM is 4GB. You have 640 mega bytes which is conservative, but if you have a lot off traffic which needs memory as well go up to 3GB. But not lower than that.
key_buffer_size is for MyISAM tables. Since XF uses by default, and recommends, INNODB this setting will have little, if any, affect.
 
Top Bottom