Should I upgrade my server hardware and how

[zjs2k]

Hello, everyone,

I am not sure if this is the place for my question. I use vBulletin and some other custom made php scripts. I have a webserver running FreeBSD 4.9 with apache 1.3 + PHP4.3 + MySQL4.1. The server has 2.4MHz Xeon, 2G ram and 40GB IDE hard drive. It used to be quite fast. But it is getting slow when there are more visitors to my site. I am considering to upgrade the server. But don't know if that's really due to the hardware.

My site uses both custom scripts and some commercial php scripts. The site is driven by MySQL+PHP and almost has no static html page. Here is some statistics of this site: about 15k daily visitors, average 80 mysql database queries per second, the server load is normally around 0.5, the server has about 40-60 httpd threads at any given time. The CPU usage varies from 30 to 70% idle and the 2G ram normally leaves less than 100MB free or none.

The following list is a typical vmstat result:

procs memory page disks faults cpu
r b w avm fre flt re pi po fr sr da0 md0 in sy cs us sy id
8 2 0 1337732 105408 153 3 1 0 342 43 0 0 84 201 670 29 9 62
4 2 0 1331684 106824 2999 10 4 0 1656 0 15 0 1355 39517 1275 65 20 15
3 2 0 1334276 99704 3904 6 2 0 2445 0 15 0 1041 38063 860 76 22 2
3 2 0 1310372 119400 1063 7 2 0 1497 0 14 0 1238 31044 1045 43 20 37
5 2 0 1288392 135848 1367 5 3 0 1604 0 48 0 1055 24340 743 45 14 41
5 2 0 1266920 153884 1915 3 2 0 1919 0 58 0 1227 34848 869 51 19 29


It seems it is waiting for CPU to process while CPU has at least 30% idle. So, what is wrong here? The second column has never been 0. I suppose a faster hard drive or RAID may help (?). Whenever the concurrent httpd connection goes over 80, the server literally stops: the load goes over 30 and server has almost no response.

Is this server hardware configuration the limit to handle this much traffic? I hope some more experienced webmaster / admin can share some information with me: typically how much traffic does your server deal with? If this hardware can't deal with the load, I won't hesitate to upgrade the server (CPU, ram, scsi HD, dedicated mysql server, loading balancing). Otherwise I will spend more time on optimize my php scripts.

Thank you very much. Any information will be very appreciated.

Jin

[Zachery]

I dont think you should be running MySQL 4.1.x as it is still in alpha and rather buggy.

Your largest drawback i would think is your IDE disks a move to SCSI might be a good move.

[eva2000]

more info would help http://www.vbulletin.com/forum/showthread.php?t=70117 :)

[zjs2k]

Thanks for the reply. Here are some details about the server:
(I am running vB3 along with other scripts including an educational program called Moodle and some custom made scripts.)

1. dedicated server
2. your server specs.
cpu speed/type single or dual cpus): single Xeon 2.4ghz
how much memory installed: 2GB ram
hard drive type/configuration: 40GB EIDE non raid
linux distributor or windows version: FreeBSD 4.9
apache/IIS version: apache 1.3.31
PHP version: php 4.3.6
MySQL version: mysql 4.0.18 (sorry I made a mistake here in my previous post)

3. no innodb type databases/tables on server
4. if possible how mysql was compiled/installed: I am not sure/don't know
5. your top stats:
last pid: 21406; load averages: 0.90, 1.05, 0.96 up 67+21:06:30 18:04:05
89 processes: 1 running, 88 sleeping
CPU states: 12.8% user, 0.0% nice, 3.1% system, 1.6% interrupt, 82.5% idle
Mem: 1104M Active, 501M Inact, 259M Wired, 88M Cache, 199M Buf, 57M Free
Swap: 512M Total, 456M Used, 56M Free, 88% Inuse

6. your mysql configuration variables located at /etc/my.cnf:
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
wait_timeout = 60
max_connections = 250
log_slow_queries
long_query_time = 1
# binary logging is required for replication
log-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

7. your mysql extended-status output:

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 91313 |
| Aborted_connects | 9 |
| Bytes_received | 65358250 |
| Bytes_sent | 483961007 |
| Com_admin_commands | 53393 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 3150438 |
| 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 | 14 |
| Com_delete | 87902 |
| 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 | 560419 |
| Com_insert_select | 133 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 4 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 4094 |
| Com_replace_select | 2 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 31167459 |
| Com_set_option | 4978 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 4978 |
| Com_show_databases | 128 |
| Com_show_fields | 5933 |
| Com_show_grants | 0 |
| Com_show_keys | 21 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 3 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 5741 |
| Com_show_variables | 7 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 1 |
| Com_unlock_tables | 4 |
| Com_update | 2948603 |
| Connections | 4020642 |
| Created_tmp_disk_tables | 198738 |
| Created_tmp_tables | 986231 |
| Created_tmp_files | 66076 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 123196 |
| Handler_read_first | 943700 |
| Handler_read_key | 2092468831 |
| Handler_read_next | 784804815 |
| Handler_read_prev | 3002532 |
| Handler_read_rnd | 384916870 |
| Handler_read_rnd_next | 819276124 |
| Handler_rollback | 0 |
| Handler_update | 619461785 |
| Handler_write | 479336128 |
| Key_blocks_used | 15586 |
| Key_read_requests | 2371618628 |
| Key_reads | 4157440 |
| Key_write_requests | 1268945 |
| Key_writes | 1000289 |
| Max_used_connections | 172 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 123 |
| Open_streams | 0 |
| Opened_tables | 1551563 |
| Questions | 41916545 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 23271 |
| Select_full_range_join | 11879 |
| Select_range | 1980649 |
| Select_range_check | 49 |
| Select_scan | 7642100 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 575 |
| Slow_queries | 11055 |
| Sort_merge_passes | 33038 |
| Sort_range | 1616787 |
| Sort_rows | 785739520 |
| Sort_scan | 5758972 |
| Table_locks_immediate | 39124235 |
| Table_locks_waited | 110080 |
| Threads_cached | 0 |
| Threads_created | 4020641 |
| Threads_connected | 33 |
| Threads_running | 1 |
| Uptime | 506532 |
+--------------------------+------------+


8. is your vB the only thing on the server?
My whole site is driven by php+mySQL and has almost no static page. The site uses Moodle (moodle.org) and other custom made scripts.

9. how many average and max concurrent users on your vB forum ?
Normally less than 20. Cookie time 900 seconds. But many visitors don't go to the forum at all.

10. phpinfo:
http://66.230.174.51/test/test.php

11. httpd.conf:
KeepAlive Off
MaxKeepAliveRequests 0
KeepAliveTimeout 5
MinSpareServers 10
MaxSpareServers 30
StartServers 100
MaxClients 100

12. what version of vB are you running ?
vb3.0.0

[eva2000]

4 things to do

1. upgrade to mysql 4.0.20
2. upgrade PHP to 4.3.8
3. change /etc/my.cnf to below to disable mysql binary logging and better optimised mysql settings and restart mysql

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

[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 = 16M
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

[mysql]
no-auto-rehash

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout


4. upgrade to vB 3.0.3 for optimised code for better performance

part of your load issue is your old my.cnf had mysql binary logging enabled which eats up server resources + you had set table_cache to default mysql value which would of cause memory consumption to rise and swap to disk...

above should help if not i'd upgrade your memory by another 512-1024MB

[zjs2k]

I tried your my.cnf and seems it helps a bit. But the memory usage still goes close to 0% free and starts to use swap. I may need to add more memory, then. Thank you for your help.

[eva2000]

yeah could be you need more memory... but how much is 'starts to use swap' in terms of memory amount ? a bit of swap like 10-100MB should be fine

[zjs2k]

Thank you for asking. Once the system started to use the swap, it would gradually use up to over 90% within a couple days. For example, I noticed my server was using 16% of total 512MB swap. After about 8 hours, it's using 40%. It will go to even higher. I am planning to get another server with more ram and probably with scsi hard drive. Thanks.

[eva2000]

ah that's too much swap.. might need ram upgrade but also check to make sure PHP/MySQL and apache and linux kernel all are updated to fix bugs such as memory leaks etc