Server Setup migrating from UBB to VB

[SteveB]

Hi,

I am nervous and would really appreciate some reassurance and advice from the wise members of this board.

We currently run three instances of UBB, and three Vbulletin 2.2.4.

Our server is experiencing heavy problems during peak times, mainly due to UBB. Server loads peak at between 50 and 70 during these peak times. We are therefore migrating two of the UBB’s to Vbulletin.

One UBB will be left alone for the time being as it is used for a journal application.

One UBB peaks at about 175 concurrent users, the other at about 60 concurrent users.

Two of the existing Vbulletin’s have about 60 concurrent users max each, the other one peaks at about 10.

I need a good starting point for a MySql configuration that we can fine tune after the migration.

There are a number of other systems that use MySql but probably nothing too significant compared to the Vbulletin load. The server itself runs about 12 websites, with a mix of perl and PHP applications.

Stats are at http://www.thelaboroflove.com/mysql_stats/mysql_stats.php

Server
Dual PIII 850Mhz
1 Gb Ram
18Gb SCSI

Software versions
PHP 4.1.2
MySql 3.23.49
Zend 1.2.0
FreeBSD 3.4
Apache 1.3.23
Vbulletin 2.2.4

MySql variables
We do not currently have a my.conf file but set the parameters from the mysql startup command line,
i.e. /usr/local/bin/safe_mysqld -O max_connections=200 -O table_cache=256 --user=mysql --datadir=/var/db/mysql

PHP info at http://www.thelaboroflove.com/test.php
MySql Stats at http://www.thelaboroflove.com/mysql_stats/mysql_stats.php

MySql has been upgraded today, so the stats cover less than a day.

These are the currently reported settings, from PHPMyAdmin.

Variable_name Value
back_log 50
basedir /usr/local/
binlog_cache_size 32768
character_set latin1
character_sets latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert ON
connect_timeout 5
datadir /usr/local/mysql/var/
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
have_bdb NO
have_gemini NO
have_innodb NO
have_isam YES
have_raid NO
have_openssl NO
init_file
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
language /usr/local/share/mysql/english/
large_files_support ON
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_long_queries ON
long_query_time 10
low_priority_updates OFF
lower_case_table_names 0
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 150
max_connect_errors 10
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 256
myisam_max_sort_file_size 2047
myisam_recover_options 0
myisam_sort_buffer_size 8388608
net_buffer_length 16384
net_read_timeout 30
net_retry_count 1000000
net_write_timeout 60
open_files_limit 0
pid_file /usr/local/mysql/var/thelaboroflove.com.pid
port 3306
protocol_version 10
record_buffer 131072
record_rnd_buffer 131072
query_buffer_size 0
safe_show_database OFF
server_id 0
slave_net_timeout 3600
skip_locking OFF
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer 2097144
sql_mode 0
table_cache 64
table_type MYISAM
thread_cache_size 0
thread_stack 65536
transaction_isolation READ-COMMITTED
timezone PST
tmp_table_size 33554432
tmpdir /var/tmp/
version 3.23.49-log
wait_timeout 28800

If any more information is required, please let me know.

My concerns are as follows.

1. Is the server capable of running the Vbulletin boards.
2. What MySql parameters are required to support the Vbulletin boards.
3. Would you recommend migrating both UBB’s at the same time?

I know in my heart that migrating to Vbulletin will certainly help the server problems, but is there any way to quantify the benefits in advance. Even a reassuring word and opinion will help.

Thanks in advance to all responses.

Steve

[eva2000]

to start with remove the mysql at startup command options and create a my.cnf file at /etc/my.cnf

and place in it below and restart mysql


[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = max_connections=650
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=512
set-variable = wait_timeout=7200
set-variable = connect_timeout=10

[safe_mysqld]
open_files_limit=8192

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[SteveB]

Thanks Eva,

I have made the changes suggested and restarted MySql. All the existing MySql applications, including the VB's are running fine.

Do I just go for the migration and see what happens? <shudder> :)

Steve

[eva2000]

Originally posted by SteveB
Thanks Eva,

I have made the changes suggested and restarted MySql. All the existing MySql applications, including the VB's are running fine.

Do I just go for the migration and see what happens? <shudder> :)

Steve yup migrate and see what happens remember to keep the old UBB data just in case you need to redo an UBB-to-vB import again