VB3.0.0b7 CPU pegged??? Config within...

[Brains]

Been pulling my hair out over getting my CPU utilization under control.. In the midst of troubleshooting the horrendous performance, I split things up over two boxes: Apache on one, MySQL on the other. The hardware is identical. Here's the pertinant info:


1. Dedicated - only thing on the box is this copy of VBulletin
2. CPU: single Celeron 1.7GHz
Memory: 1GB
HDD: single IDE Seagate ST360014A (60GB Barracuda 7200RPM)
Distro: Redhat 7.3
Webserver: Apache 1.3.27
PHP version: 4.1.2
MySQL version: 4.0.15

3. InnoDB - not used nor loaded
4. MySQL installed using the RPMS as-compiled by mysql.com
5. Output of top -i
(Apache server):
5:14pm up 119 days, 8:12, 1 user, load average: 14.05, 17.95, 20.98
94 processes: 90 sleeping, 4 running, 0 zombie, 0 stopped
CPU states: 0.2% user, 0.3% system, 0.0% nice, 0.2% idle
Mem: 1023128K av, 775152K used, 247976K free, 0K shrd, 66120K buff
Swap: 1020116K av, 9740K used, 1010376K free 472892K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
31640 apache 16 0 9332 8356 2896 R 32.4 0.8 0:22 httpd
31596 apache 19 0 8564 7592 2852 R 21.6 0.7 0:27 httpd
32605 root 10 0 1060 1060 808 R 0.9 0.1 0:00 top
6598 root 9 0 548 208 116 R 0.0 0.0 0:17 sshd

(MySQL server)
17:15:12 up 62 days, 16:34, 1 user, load average: 0.45, 0.26, 0.26
227 processes: 222 sleeping, 5 running, 0 zombie, 0 stopped
CPU states: 5.1% user 4.5% system 0.0% nice 0.0% iowait 90.2% idle
Mem: 1022796k av, 1003336k used, 19460k free, 0k shrd, 81456k buff
610784k actv, 12684k in_d, 62780k in_c
Swap: 2048276k av, 6344k used, 2041932k free 725108k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
2780 root 15 0 1168 1168 808 R 0.9 0.1 0:00 0 top

6. /etc/my.cnf:
[mysqld]
max_connections = 200
key_buffer = 16M
myisam_sort_buffer_size = 8M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 256
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size=4M
record_buffer=1M
back_log=512
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 | 186 |
| Aborted_connects | 0 |
| Bytes_received | 547776862 |
| Bytes_sent | 3859808724 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 122458 |
| 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 | 3145 |
| 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 | 55566 |
| Com_insert_select | 761 |
| 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 | 2758 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1222877 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 1 |
| Com_show_fields | 85 |
| 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 | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1242 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 4 |
| Com_show_variables | 3 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 203341 |
| Connections | 121700 |
| Created_tmp_disk_tables | 936 |
| Created_tmp_tables | 162722 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 18598 |
| Handler_read_first | 106642 |
| Handler_read_key | 127775559 |
| Handler_read_next | 106558284 |
| Handler_read_prev | 1823960 |
| Handler_read_rnd | 2371827 |
| Handler_read_rnd_next | 33391474 |
| Handler_rollback | 0 |
| Handler_update | 204990 |
| Handler_write | 2222836 |
| Key_blocks_used | 15586 |
| Key_read_requests | 179314842 |
| Key_reads | 31791 |
| Key_write_requests | 103187 |
| Key_writes | 85480 |
| Max_used_connections | 167 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 226 |
| Open_files | 307 |
| Open_streams | 0 |
| Opened_tables | 232 |
| Questions | 1992044 |
| Qcache_queries_in_cache | 4718 |
| Qcache_inserts | 476365 |
| Qcache_hits | 259465 |
| Qcache_lowmem_prunes | 68 |
| Qcache_not_cached | 746518 |
| Qcache_free_memory | 8475320 |
| Qcache_free_blocks | 2273 |
| Qcache_total_blocks | 11848 |
| Rpl_status | NULL |
| Select_full_join | 412 |
| Select_full_range_join | 31095 |
| Select_range | 394481 |
| Select_range_check | 0 |
| Select_scan | 328563 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 120282 |
| Sort_rows | 2607590 |
| Sort_scan | 162358 |
| Table_locks_immediate | 2366573 |
| Table_locks_waited | 1821 |
| Threads_cached | 141 |
| Threads_created | 168 |
| Threads_connected | 27 |
| Threads_running | 1 |
| Uptime | 64779 |
+--------------------------+------------+

8. VBulletin is the *ONLY* application on this server. The only custom code added is a rotating banner script, which when removed provided no load savings.

9. Avg users: 200-250 Max users: 320 so far, expecting 400-500 once performance problems are solved.

10. http://www.ls1tech.com/phpinfo.php

11. KeepAlive = On
MaxKeepAliveRequests = 100
KeepAliveTimeout = 15
MinSpareServers = 5
MaxSpareServers = 20
StartServers = 8
MaxClients = 180

12. VBulletin version 3.0.0 beta 7


Thanks in advance for any help or insight!

[eva2000]

so there's 2 servers 1 for apache and 1 for mysql ? both same cpu specs ?

your problem is the apache web server celery 1.7ghz is underpowered to cope with the loads (as expected from celery cpus)


try changing your my.cnf to below to help with mysql loads (although mysql isn't your problem)


[mysqld]
max_connections = 450
key_buffer = 32M
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



i'd also upgrade to apache 1.3.28 and php 4.3.3 and mysql 4.0.16 to eliminate several bugs in previous releases with MAY contribute to load issues

[Brains]

Thank you very much for the help and suggestions, George!! Yesterday I put Zend Accelerator on the apache box and tried it. Great improvements there! :D

I've since gone back and put everything on one box -- didn't want to use up all my allocated bandwidth with traffic going between two colocated boxes. I also brought the box up to RH9, latest patches, 2.4.20 kernel, apache 2.0.47 compiled lean, MySQL 4.0.16 rpms from mysql.com, php 4.3.4 RC2 complied lean, and installed mmcache 2.4.4 .. I'm waiting for the bulk of my users to hit the box, and see how it fares this morning. So far with about 80 users I haven't broken a load avg. of 1.00 yet.. We'll see how things stack up when there's 350 users. ;)

[eva2000]

instead of Zend Accelerator, try Turck MMcache at http://www.vbulletin.com/forum/showthread.php?t=75878 :)

[Brains]

I did, and I'm using it now... performance is wonderful !!! Thanks again George, you're a huge asset to this site...

[eva2000]

no probs mate. glad to here it's all well with you now :)