Server Optimization Request

[sloop]

Our server is struggling with a high load.. I have implemented some MySQL settings that I got off this board but am looknig for further optimizations.

1. Dedicated server

2. Server Specs

CPU: Dual Xeon 2.4ghz with hyperthreading
RAM: 2GB
hard drive: 40GB EIDE non-RAID
OS: RedHat Linux 9 with Cpanel
PHP: 4.3.3
Apache: 1.3.29
MySQL: 4.0.15 (not custom compiled)

3. not aware of any innodb type databases

4. not compiled, came with server's cpanel install

5. top

11:08:31 up 6 days, 13:05, 1 user, load average: 3.33, 5.49, 5.66
221 processes: 219 sleeping, 1 running, 1 zombie, 0 stopped
CPU0 states: 7.4% user 2.0% system 0.4% nice 0.0% iowait 90.0% idle
CPU1 states: 11.1% user 7.3% system 0.5% nice 0.0% iowait 80.4% idle
CPU2 states: 19.0% user 15.1% system 0.4% nice 0.0% iowait 64.3% idle
CPU3 states: 11.3% user 11.1% system 0.0% nice 0.0% iowait 76.4% idle
Mem: 2064400k av, 2022680k used, 41720k free, 0k shrd, 40256k buff
1475460k actv, 976k in_d, 41068k in_c
Swap: 1052248k av, 199660k used, 852588k free 1432336k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
3329 root 15 0 2132 336 232 S 99.9 0.0 138:18 2 httpd
11962 mysql 15 0 75452 71M 1280 S 51.0 3.5 1007m 2 mysqld
2828 exceltip 20 0 0 0 0 Z 3.1 0.0 0:00 0 search.pl <defunct>
13 root 15 0 0 0 0 SW 0.7 0.0 16:16 3 kscand/Normal
2814 root 15 0 1344 1344 868 R 0.3 0.0 0:00 1 top
1 root 19 0 108 80 56 S 0.0 0.0 0:24 0 init
2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0 migration/0
3 root RT 0 0 0 0 SW 0.0 0.0 0:00 1 migration/1
4 root RT 0 0 0 0 SW 0.0 0.0 0:00 2 migration/2
5 root RT 0 0 0 0 SW 0.0 0.0 0:00 3 migration/3
6 root 15 0 0 0 0 SW 0.0 0.0 0:00 1 keventd
7 root 34 19 0 0 0 SWN 0.0 0.0 0:01 0 ksoftirqd_CPU0
8 root 34 19 0 0 0 SWN 0.0 0.0 0:00 1 ksoftirqd_CPU1
9 root 34 19 0 0 0 SWN 0.0 0.0 0:00 2 ksoftirqd_CPU2
10 root 34 19 0 0 0 SWN 0.0 0.0 0:00 3 ksoftirqd_CPU3
15 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 bdflush
11 root 15 0 0 0 0 SW 0.0 0.0 3:10 3 kswapd
12 root 15 0 0 0 0 SW 0.0 0.0 0:05 2 kscand/DMA
14 root 16 0 0 0 0 SW 0.0 0.0 40:27 2 kscand/HighMem
16 root 15 0 0 0 0 SW 0.0 0.0 0:27 2 kupdated
17 root 24 0 0 0 0 SW 0.0 0.0 0:00 0 mdrecoveryd
21 root 15 0 0 0 0 SW 0.0 0.0 4:18 2 kjournald
79 root 25 0 0 0 0 SW 0.0 0.0 0:00 3 khubd
2261 root 15 0 0 0 0 SW 0.0 0.0 0:00 2 kjournald
2936 root 15 0 236 208 156 S 0.0 0.0 0:17 0 syslogd
2940 root 15 0 176 124 120 S 0.0 0.0 0:00 3 klogd
3017 root 16 0 268 120 36 S 0.0 0.0 0:38 2 sshd
3031 root 15 0 424 356 280 S 0.0 0.0 0:00 1 xinetd
3040 root 15 0 604 544 396 S 0.0 0.0 1:02 1 antirelayd
3050 root 18 0 1920 1016 612 S 0.0 0.0 0:09 0 chkservd
3065 mailnull 15 0 496 252 180 S 0.0 0.0 0:04 2 exim
3069 mailnull 15 0 312 4 0 S 0.0 0.0 0:00 1 exim
3073 root 15 0 604 540 396 S 0.0 0.0 1:01 1 antirelayd
3084 root 15 0 248 236 164 S 0.0 0.0 0:01 2 crond
3134 xfs 15 0 1832 56 32 S 0.0 0.0 0:00 0 xfs


6. my.cnf

[mysqld]
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


7. mysql extended status

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 160 |
| Aborted_connects | 1 |
| Bytes_received | 641928396 |
| Bytes_sent | 3773031119 |
| Com_admin_commands | 27 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 55823 |
| 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 | 8894 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 2 |
| Com_grant | 1 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 30120 |
| Com_insert_select | 1986 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 280 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 5476 |
| Com_replace_select | 94 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 787692 |
| Com_set_option | 1 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 9 |
| Com_show_fields | 2 |
| Com_show_grants | 1 |
| Com_show_keys | 2 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 291 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 5 |
| Com_show_variables | 3 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 280 |
| Com_update | 205806 |
| Connections | 323806 |
| Created_tmp_disk_tables | 374946 |
| Created_tmp_tables | 402936 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 17643 |
| Handler_read_first | 54352 |
| Handler_read_key | 103434863 |
| Handler_read_next | 220883578 |
| Handler_read_prev | 1957694 |
| Handler_read_rnd | 59091640 |
| Handler_read_rnd_next | 2698019279 |
| Handler_rollback | 0 |
| Handler_update | 67711892 |
| Handler_write | 87200905 |
| Key_blocks_used | 15586 |
| Key_read_requests | 689216966 |
| Key_reads | 880939 |
| Key_write_requests | 15520011 |
| Key_writes | 341752 |
| Max_used_connections | 47 |
| Not_flushed_key_blocks | 1 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 741 |
| Open_files | 1223 |
| Open_streams | 0 |
| Opened_tables | 757 |
| Questions | 2476255 |
| Qcache_queries_in_cache | 13059 |
| Qcache_inserts | 762561 |
| Qcache_hits | 1055648 |
| Qcache_lowmem_prunes | 57724 |
| Qcache_not_cached | 25267 |
| Qcache_free_memory | 5417048 |
| Qcache_free_blocks | 1103 |
| Qcache_total_blocks | 27776 |
| Rpl_status | NULL |
| Select_full_join | 430512 |
| Select_full_range_join | 1058 |
| Select_range | 65598 |
| Select_range_check | 20 |
| Select_scan | 124110 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 213 |
| Sort_merge_passes | 0 |
| Sort_range | 61774 |
| Sort_rows | 4072357 |
| Sort_scan | 407501 |
| Table_locks_immediate | 1766993 |
| Table_locks_waited | 2503 |
| Threads_cached | 42 |
| Threads_created | 48 |
| Threads_connected | 6 |
| Threads_running | 3 |
| Uptime | 87251 |
+--------------------------+------------+


8. one large VB forum is on this server, a few other smaller VB forums, and about 20 other sites. Two large sites do make use of perl CGI scripts. the only people logging in are admins.

9. Average concurrent users for the forums: 150-200. Max concurrent users for the forums: 300.

10. http://hosting.irubin.com/hotsanic/test.php

11.
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 10
MaxSpareServers 60
StartServers 50
MaxClients 260

12. VB 2.3.2

thanks

[eva2000]

Sloop,

Could you please enter your vBulletin.com forum registered email address on the form on this page to receive support for this issue: http://www.vbulletin.com/members/forums.php
- you will need your vB license customer number and password to access this form.

[sloop]

Eva2000, just did it.. thanks

Mark

[sloop]

any ideas and suggestions would be appreciated. thanks.

[eva2000]

sorry could of sworn i replied :o

can you post your live mysqlinfo.php url as well

do you have an estimate of the total vB users online on all vB forums hosted on your server ?

i can see possible 3 things you need doing

1. upgrade php to 4.3.4
2. add more memory especially if you're hosting other web sites you need at least 2GB ram
3. 40GB IDE is probably slowing you down as well... fast scsi disk 1 for apache/web/home files and 1 scsi disk for mysql data partition itself /var if mysql data resides at /var/lib/mysql

[sloop]

Max total vb users online on all VB forums on this server is around 300.

Mysqlinfo.php is at http://www.excelforum.com/admin/mysqlinfo.php

thanks