Upgraded from 2.3.5 to 3.03 and uh oh...

[cleck673]

I have just upgraded over the weekend from vb 2.3.5 and vbportal to vb 3.03 and VBAdvanced CMPS 1.0.0. The upgrade went smooth and all looked great after I reindexed the search and updated all the other counters. I brought everything online at about 4am Saturday and went to sleep. I woke at 10am and all hell had broke loose. Server load on the php box was 15-20 and the Sql server died 2x and required restarting. I took the site offline and put things back one at a time. The server load was decent with only the forums on and vb 3.03 was lightning fast. When I added VBAdvanced CMPS 1.0.0 back into the mix, server load skyrockets. I had Brian look at the script and he tweaked a bit and he ultimately told me that I needed to optimize my server. With the same load as normal, running vb2.3 and vbportal, server load rarely saw 5 with 300 online. So here are the details the best I can find right now.
I realized I do not have alot of the info, I am still posting this, but willl update and bump when I get all the info. Thanks.



1. My sites are on 2 dedicated machines. One for the php and one for the sql db. There is one medium-large site and 4 smaller sites housed on them.
2. cpu speed/type single or dual cpus): (2) Pentium 4 2.4gb servers with 768mb ram in each of them.

http://www.aximsite.com/images/vb3stats.gif

3.
mysql Ver 12.21
MyISAM tables

4. stock mysql binary package from mysql.com

5.
Without vBadvanced CMPS 1.0.0

http://www.aximsite.com/images/topwithout.gif

With vBadvanced CMPS 1.0.0

http://www.aximsite.com/images/topwith.gif


6.

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
max_connections= 1000
thread_concurrency = 8
MASTER_PORT=3306,
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout



7.

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 4 |
| Aborted_connects | 4 |
| Bytes_received | 36896965 |
| Bytes_sent | 1891627759 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 20921 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 159 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 1850 |
| Com_insert_select | 7 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 178 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 24013 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 344 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 3 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 12421 |
| Connections | 21271 |
| Created_tmp_disk_tables | 292 |
| Created_tmp_tables | 703 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 479 |
| Handler_read_first | 39880 |
| Handler_read_key | 1132541 |
| Handler_read_next | 3392420 |
| Handler_read_prev | 9833 |
| Handler_read_rnd | 300952 |
| Handler_read_rnd_next | 15662202 |
| Handler_rollback | 0 |
| Handler_update | 17394 |
| Handler_write | 87148 |
| Key_blocks_used | 12188 |
| Key_read_requests | 2886746 |
| Key_reads | 12094 |
| Key_write_requests | 6166 |
| Key_writes | 2315 |
| Max_used_connections | 97 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 256 |
| Open_files | 409 |
| Open_streams | 0 |
| Opened_tables | 391 |
| Questions | 144688 |
| Qcache_queries_in_cache | 1436 |
| Qcache_inserts | 17828 |
| Qcache_hits | 63611 |
| Qcache_lowmem_prunes | 368 |
| Qcache_not_cached | 6186 |
| Qcache_free_memory | 1388016 |
| Qcache_free_blocks | 584 |
| Qcache_total_blocks | 4183 |
| Rpl_status | NULL |
| Select_full_join | 124 |
| Select_full_range_join | 8 |
| Select_range | 10999 |
| Select_range_check | 0 |
| Select_scan | 2581 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 2 |
| Sort_merge_passes | 0 |
| Sort_range | 9866 |
| Sort_rows | 15022580 |
| Sort_scan | 1226 |
| Table_locks_immediate | 74853 |
| Table_locks_waited | 85 |
| Threads_cached | 7 |
| Threads_created | 783 |
| Threads_connected | 83 |
| Threads_running | 1 |
| Uptime | 1789 |
+--------------------------+------------+



8. Running 4 other vbulletin forums with vb 3.01 or 3.03 and vbadvance with no troubles. (smaller sites, much smaller). I am also running Mambo Server on a domain that get 1-4 hits a day and a few other little scripts. Nothing that has ever hindered performance.

9. record online users: 544
average: 200
cookie timeout: 900 seconds

10. http://aximsite.com/phpinfo.php

11.

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 20
MinSpareServers 15
MaxSpareServers 30
StartServers 100
MaxClients 400

12. Running version 3.0.3

[Zachery]

Do you have ammount of new posts since last visit hack installed, and or enabled for vBadvanced?

[cleck673]

none. I installed vbadvanced and enabled it right after the upgrade and before i went live. Maybe a test post or two.

[cleck673]

This is the top stats this am.....

http://www.aximsite.com/images/normalmorningwith.gif

[Floris]

To get support on these forums you are required to goto the members area and fill in the email address you registered with on these forums in the priority support page.

[cleck673]

Not looking for support, when I get the rest of the details I have yet to fill in, I am looking for optimization suggestions.

[Floris]

You show up unlicensed, but you say you use vBulletin - and you want to know why the forums run slow.

[cleck673]

You show up unlicensed, but you say you use vBulletin - and you want to know why the forums run slow.

I have paid for 5 licenses. No clue why I show "unlicensed". Anyhow, you have not read my post? I am not wondering why vb is running slow. vb 3.03 on its own runs lightning fast. With VBAdvanced it runs slow. Brian (VBAdavanced) suggested I go here to request help with server optimization.

[conqsoft]

But, you are not supposed to get any support, related in any way to vBulletin without a license. Just log into the member area and add the e-mail address you used to sign up here on the forums, to the authorized user list, and all will be fine.

[Floris]

I have paid for 5 licenses. No clue why I show "unlicensed". Anyhow, you have not read my post? I am not wondering why vb is running slow. vb 3.03 on its own runs lightning fast. With VBAdvanced it runs slow. Brian (VBAdavanced) suggested I go here to request help with server optimization.
It was probably because you changed your email. Thank you for keeping it up to date. You show up correctly again.

[Floris]

It was probably because you changed your email. Thank you for keeping it up to date. You show up correctly again.

You are showing up unlicensed again. Do you switch email address all the time or something? :)

[cleck673]

Changed email the other day, 1st time since I joined in 3/2003.

[Floris]

Time to goto priority_support again I guess. :)

[cleck673]

I added the missing info now. Am I still showing unlicensed again?

[Floris]

Yes, hopefully this is not a bug in the license system :\

[vBrat]

Maybe he keeps changing his vB forum account email to something which isnt in priority support, Try asking him to stick to one email and one only on his vB forums account?

[cleck673]

Maybe he keeps changing his vB forum account email to something which isnt in priority support, Try asking him to stick to one email and one only on his vB forums account?

Read 3 posts prior... Changed email one time. Have not done anything to vb forums or member area account since yesterday when I showed back up as registered.

[Steve Machol]

If you are absolutely sure that your forum email address is entered on the Priority Support form, then send an email to support@vbulletin.com with your forum user name and customer number so we can investigate this.

[cleck673]

Just to make sure, see attached pic to make sure these are the areas that matter.

[url removed so others won't abuse your license number]

[cleck673]

oops... so, are those the places that they need to match up?

[Steve Machol]

Please send an email to support@vbulletin.com so we can investigate this license issue.

[cleck673]

Email sent.

Back to optimization. The problem is in cpu consumption. That script is using a lot of cpu cycles, and the db server load is still low, so I think we can rule out most DB server issues now. If the script was waiting on DB data, the cpu usage would be lower, load would be high because those processes would be waiting on DB IO, but instead they seem to be waiting on CPU time.


BTW - the images of server load are of the frontend server since the DB server's load is never going high at all.

[cleck673]

Bump. Would someone mind offering some server optimization advise now? I think Steve fixed my account and should show registered now.

Thanks!

[Steve Machol]

I fixed your account but for some reason it reverts back to unlicensed. I've reported this problem but no fix yet. At any rate eva2000 is the server guru. You'll need to be patient until he has time to check this.

[cleck673]

Thanks!

[eva2000]

cleck673, sorry for the delays

my gut feeling is with your forum activity and server loads you're web server needs upgrading to at least dual p4 2.66ghz xeons with at least 2GB ram.

here's what i suggest you do first to optimise the current hardware/config

1. upgrade to mysql 4.0.20
2. upgrade to PHP 4.3.8
3. remove Zend Optimiser
4. install Turck Mmache http://www.vbulletin.com/forum/showthread.php?t=75878
5. change /etc/my.cnf to below and restart mysql

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

[cleck673]

Thanks. http://www.ipaqhq.com/forums/images/smilies/beer.gif There server guy is working some changes now. I appreciate the help.