Server is overloaded - need optimization help

[tylers]

I'm the sysadmin - I set up the server, but not vBulletin itself. The web server is grinding with a load of 13-16. I would appreciate any tips y'all might have.

Thanks!
Tyler

Note: running Turck MMCache 2.4.6

1. Dedicated Web, Separate DB

2. System specs
Web:
dual 2.4GHz Xeon HT
2 GB ECC RAM
120 GB HD EIDE non raid
Slackware Linux 10.0, kernel 2.6.7
Apache 2.0.52
PHP 2.3.10
DB:
dual 2.4Ghz Xeon HT
2 GB ECC RAM
120 GB HD EIDE non raid
Slackware Linux 10.0, kernel 2.6.9
MySQL 4.0.21

3. Yes, using INNODB

4. MySQL Build:
./configure --prefix=/opt/myn1 --localstatedir=/var/myn1/4801 --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --enable-local-infile --enable-thread-safe-client --with-extra-charsets=all

5. top

top - 09:57:50 up 164 days, 1:56, 1 user, load average: 13.96, 13.18, 13.1Tasks: 592 total, 8 running, 584 sleeping, 0 stopped, 0 zombie
Cpu(s): 71.2% us, 1.9% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.1% hi, 26.8% si
Mem: 2075464k total, 1880264k used, 195200k free, 121316k buffers
Swap: 10008484k total, 0k used, 10008484k free, 1021560k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
25203 website 16 0 35844 17m 24m R 51.9 0.9 0:08.37 httpd
25103 website 15 0 36396 18m 24m S 49.1 0.9 0:18.71 httpd
4787 nobody 19 0 906m 4644 2508 R 39.1 0.2 11:31.58 in.identd
25387 website 15 0 34380 15m 24m S 37.5 0.8 0:03.78 httpd
25513 website 15 0 32256 11m 24m S 35.3 0.6 0:01.13 httpd
25606 website 15 0 33696 15m 24m S 31.9 0.7 0:01.56 httpd
25505 website 15 0 34996 16m 24m S 25.0 0.8 0:05.98 httpd
4788 nobody 19 0 906m 4644 2508 R 22.8 0.2 11:04.27 in.identd
25192 website 15 0 34348 16m 24m S 20.6 0.8 0:11.73 httpd
4786 nobody 19 0 906m 4644 2508 R 20.0 0.2 11:11.61 in.identd
25193 website 16 0 35268 17m 24m R 17.2 0.9 0:07.20 httpd
4785 nobody 21 0 906m 4644 2508 S 16.9 0.2 11:22.90 in.identd
25124 website 16 0 36104 18m 24m R 9.4 0.9 0:15.94 httpd
25204 website 15 0 35136 17m 24m S 5.0 0.8 0:09.13 httpd
25608 root 16 0 2244 1324 1776 R 2.5 0.1 0:00.39 top
25118 website 15 0 34916 16m 24m S 2.2 0.8 0:19.18 httpd
25382 website 16 0 34660 16m 24m S 1.2 0.8 0:08.99 httpd
25101 website 15 0 35284 17m 24m S 0.3 0.9 0:26.15 httpd
25340 website 15 0 35172 16m 24m S 0.3 0.8 0:10.99 httpd
25389 website 15 0 34640 16m 24m S 0.3 0.8 0:08.93 httpd

6. my.cnf
[client]
port = 4801
socket = /tmp/my4801.sock

[mysqld]
port = 4801
socket = /tmp/my4801.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
thread_concurrency = 8
set-variable = myisam_sort_buffer_size=64M
set-variable = long_query_time=15

set-variable = max_connections=2000

innodb_data_home_dir = /var/myn1/4801/
innodb_data_file_path = ibdata1:100M:autoextend
innodb_log_group_home_dir = /var/myn1/4801/
innodb_log_arch_dir = /var/myn1/4801/
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[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. MySQL Extended Status
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 64882 |
| Aborted_connects | 203 |
| Bytes_received | 3597180528 |
| Bytes_sent | 1290523837 |
| Com_admin_commands | 14358 |
| Com_alter_table | 351 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 3991018 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 4 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 359 |
| Com_delete | 382854 |
| Com_delete_multi | 0 |
| Com_drop_db | 1 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 235 |
| Com_flush | 1 |
| Com_grant | 4 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 2969549 |
| Com_insert_select | 250492 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 111 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 901126 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 16398929 |
| Com_set_option | 6398 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 3252 |
| Com_show_databases | 84 |
| Com_show_fields | 7742 |
| Com_show_grants | 32 |
| Com_show_keys | 47 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 5 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 10340 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 3873 |
| Com_show_variables | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 111 |
| Com_update | 5331919 |
| Connections | 3458670 |
| Created_tmp_disk_tables | 576556 |
| Created_tmp_tables | 1486019 |
| Created_tmp_files | 17003 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 859779 |
| Handler_read_first | 3068438 |
| Handler_read_key | 3847560987 |
| Handler_read_next | 3520822620 |
| Handler_read_prev | 80632465 |
| Handler_read_rnd | 729344790 |
| Handler_read_rnd_next | 1256119708 |
| Handler_rollback | 1 |
| Handler_update | 576402758 |
| Handler_write | 311455947 |
| Key_blocks_used | 249380 |
| Key_read_requests | 3488024890 |
| Key_reads | 13431661 |
| Key_write_requests | 1412644795 |
| Key_writes | 44813691 |
| Max_used_connections | 295 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 112 |
| Open_streams | 0 |
| Opened_tables | 484620 |
| Questions | 114927996 |
| Qcache_queries_in_cache | 1447 |
| Qcache_inserts | 16286857 |
| Qcache_hits | 81253517 |
| Qcache_lowmem_prunes | 1664134 |
| Qcache_not_cached | 109452 |
| Qcache_free_memory | 7545672 |
| Qcache_free_blocks | 1009 |
| Qcache_total_blocks | 4082 |
| Rpl_status | NULL |
| Select_full_join | 79554 |
| Select_full_range_join | 0 |
| Select_range | 4457127 |
| Select_range_check | 0 |
| Select_scan | 3115437 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 130 |
| Sort_merge_passes | 8500 |
| Sort_range | 3164690 |
| Sort_rows | 803912491 |
| Sort_scan | 2434737 |
| Table_locks_immediate | 48901190 |
| Table_locks_waited | 268593 |
| Threads_cached | 1 |
| Threads_created | 126496 |
| Threads_connected | 41 |
| Threads_running | 3 |
| Uptime | 1938543 |
+--------------------------+------------+

8. vB is the only thing on the server

9. Avg users: 400
Max users: 754
Cookie Lifetime = 0

10. http://forums.adoption.com/pinfo.php

11.
Timeout 30
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 5

<IfModule prefork.c>
StartServers 5
MinSpareServers 5
MaxSpareServers 15
MaxClients 130
MaxRequestsPerChild 0
</IfModule>

12. Running vBulletin 3.0.3

[Steve Machol]

To get support on these forums you first need to be a licensed customer and register for Priority Forum Support. To do this, please go here:

http://www.vbulletin.com/members/membersupport_priority.php

...and enter your email address in one of the boxes. You'll need to have your customer number and password to access the page.

If you still have problems after doing this, send an email to support@vbulletin.com. Please include your user name, the email address you registered with and your customer number so we can fix the problem.

[tylers]

I'm now in the priority forum support list

[eva2000]

hi tylers, any reason cookie timeout set at 0 ? and not 900 ?

I'd upgrade to MySQL 4.0.23 on db server first and change to this new my.cnf below


[client]
port= 4801
socket = /tmp/my4801.sock

[mysqld]
port= 4801
socket = /tmp/my4801.sock
skip-locking
key_buffer = 128M
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 = 10800
connect_timeout = 10
tmp_table_size = 192M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1

innodb_data_home_dir = /var/myn1/4801/
innodb_data_file_path = ibdata1:100M:autoextend
innodb_log_group_home_dir = /var/myn1/4801/
innodb_log_arch_dir = /var/myn1/4801/
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqld_safe]
open_files_limit = 8192

[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


also in httpd.conf i'd increase maxclients from 130 to 256 on web server and restart apache

FYI, IDE disks are slow and consume cpu resources and innodb also involves some extra disk activity so could be part of the reason for load spikes.. you get any disk swapping on database server ? May help if you added a 2nd IDE disk on separate IDE channel on the motherboard and set the innodb table space to that drive. Or change to SCSI disks