spinaltoad
Tue 28th Feb '06, 11:06pm
1) Dedicated Machine. 1 for Apache/php , 1 for Mysql Active users 1500~2600
2)
Server 1
P4 3 Ghz
2gig Ram
4 x 80gig ATA100 7200RPM RAID 10
FreeBSD 4.8
apache-2.0.52_3
php4-4.3.10
Server 2
Dual Opteron 64-bit 2ghz
4gig Ram
4x36gig ULTRA320 15k RPM RAID 10
Solaris 10
Mysql 4.1.18 64-bit
3) No Innodb databases
4) MySQL is the official binary release from MySQL.com
5)
Server 1
last pid: 93154; load averages: 4.79, 8.26, 11.86 up 8+18:50:44 20:53:26
133 processes: 3 running, 130 sleeping
CPU states: % user, % nice, % system, % interrupt, % idle
Mem: 455M Active, 354M Inact, 283M Wired, 80M Cache, 199M Buf, 837M Free
Swap: 2031M Total, 41M Used, 1990M Free, 2% Inuse
PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
93064 www 2 0 44356K 13676K poll 0:03 3.03% 3.03% httpd
93019 www 2 0 43656K 12548K poll 0:02 1.61% 1.61% httpd
93065 www 2 0 43816K 12564K poll 0:01 1.57% 1.56% httpd
93128 www 2 0 42060K 9344K poll 0:00 2.07% 1.56% httpd
93044 www 30 0 45432K 15240K RUN 0:04 1.47% 1.46% httpd
93114 www 2 0 44216K 17760K poll 0:01 1.63% 1.46% httpd
93146 www 2 0 44176K 12688K poll 0:01 1.84% 1.37% httpd
92709 www 2 0 44488K 18512K poll 0:17 1.22% 1.22% httpd
93112 www 2 0 41704K 14252K poll 0:01 1.35% 1.22% httpd
93139 www 2 0 44372K 12560K accept 0:01 1.58% 1.17% httpd
93046 www 2 0 44140K 18228K poll 0:02 1.08% 1.07% httpd
93151 www 2 0 41900K 9212K poll 0:01 1.45% 1.07% httpd
93130 www 2 0 44184K 12792K accept 0:00 1.43% 1.07% httpd
93071 www 2 0 43664K 12988K poll 0:02 0.98% 0.98% httpd
93122 www 2 0 41948K 9332K poll 0:00 1.26% 0.98% httpd
93121 www 2 0 43776K 12040K poll 0:01 1.18% 0.93% httpd
93095 www 2 0 44208K 17644K accept 0:01 0.89% 0.88% httpd
Server 2
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
1350 mysql 630M 602M sleep 59 0 26:00:48 9.6% mysqld/176
4404 root 4704K 4192K cpu0 59 0 0:00:00 0.0% prstat/1
693 root 140M 70M sleep 59 0 0:08:04 0.0% java/27
129 root 5156K 4064K sleep 59 0 0:03:04 0.0% nscd/55
4388 spinalto 7372K 1896K sleep 59 0 0:00:00 0.0% sshd/1
246 root 1060K 600K sleep 59 0 0:00:01 0.0% utmpd/1
258 root 4260K 3112K sleep 59 0 0:00:06 0.0% inetd/4
257 root 1800K 1028K sleep 59 0 0:00:00 0.0% ttymon/1
226 daemon 2068K 1304K sleep 60 -20 0:00:00 0.0% lockd/2
224 daemon 2336K 1540K sleep 59 0 0:00:00 0.0% statd/2
242 root 1768K 996K sleep 59 0 0:00:00 0.0% ttymon/1
215 root 2236K 888K sleep 59 0 0:00:00 0.0% cron/1
114 root 2120K 1252K sleep 59 0 0:00:00 0.0% syseventd/14
321 root 4716K 1932K sleep 59 0 0:00:02 0.0% automountd/3
130 daemon 3832K 1968K sleep 59 0 0:00:01 0.0% kcfd/3
580 root 3360K 1920K sleep 59 0 0:00:00 0.0% snmpXdmid/2
236 root 1696K 892K sleep 59 0 0:00:00 0.0% sac/1
738 nobody 2852K 1892K sleep 59 0 0:00:00 0.0% xfs/1
219 daemon 2312K 1068K sleep 59 0 0:00:00 0.0% rpcbind/1
127 root 2576K 1684K sleep 59 0 0:00:00 0.0% picld/4
9 root 9196K 8232K sleep 59 0 0:00:10 0.0% svc.configd/16
Total: 40 processes, 376 lwps, load averages: 0.21, 0.21, 0.21
6) my.cnf
client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
datadir = /opt/mysql/mysql/data
socket = /tmp/mysql.sock
max_connections = 800
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 192M
query_cache_type = 1
# Following values valid only for MySQL 4.1.15
#query_prealloc_size = 16384
#query_alloc_block_size = 16384
# new value
read_rnd_buffer_size = 524288
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
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
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
7) exteded status
+----------------------------+---------------+
| Variable_name | Value |
+----------------------------+---------------+
| Aborted_clients | 14882 |
| Aborted_connects | 45 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 13631978252 |
| Bytes_sent | 1085789352909 |
| Com_admin_commands | 102 |
| Com_alter_db | 0 |
| Com_alter_table | 148 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 61686 |
| Com_change_db | 4866020 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 52298 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 851 |
| Com_dealloc_sql | 0 |
| Com_delete | 66632 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 840 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 5 |
| Com_grant | 7 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 721145 |
| Com_insert_select | 6066 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 21986 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 707496 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 12813 |
| Com_savepoint | 0 |
| Com_select | 12913150 |
| Com_set_option | 68 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 35 |
| Com_show_collations | 35 |
| Com_show_column_types | 0 |
| Com_show_create_db | 4 |
| Com_show_create_table | 0 |
| Com_show_databases | 17 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 1 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 6 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 104 |
| Com_show_variables | 99 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 23295 |
| Com_update | 4587072 |
| Com_update_multi | 0 |
| Connections | 4228308 |
| Created_tmp_disk_tables | 13306 |
| Created_tmp_files | 32567 |
| Created_tmp_tables | 646438 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 2 |
| Handler_commit | 0 |
| Handler_delete | 1051085 |
| Handler_discover | 0 |
| Handler_read_first | 1976720 |
| Handler_read_key | 1735405095 |
| Handler_read_next | 1952372197 |
| Handler_read_prev | 11744012 |
| Handler_read_rnd | 2116045559 |
| Handler_read_rnd_next | 20003218383 |
| Handler_rollback | 0 |
| Handler_update | 455506327 |
| Handler_write | 686880321 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 53585 |
| Key_read_requests | 3796044812 |
| Key_reads | 4573806 |
| Key_write_requests | 15771250 |
| Key_writes | 2024094 |
| Max_used_connections | 167 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1967 |
| Open_streams | 0 |
| Open_tables | 1562 |
| Opened_tables | 3583 |
| Qcache_free_blocks | 20195 |
| Qcache_free_memory | 132135800 |
| Qcache_hits | 32103491 |
| Qcache_inserts | 12792676 |
| Qcache_lowmem_prunes | 265144 |
| Qcache_not_cached | 83908 |
| Qcache_queries_in_cache | 40811 |
| Qcache_total_blocks | 102232 |
| Questions | 60372097 |
| Rpl_status | NULL |
| Select_full_join | 20890 |
| Select_full_range_join | 0 |
| Select_range | 2452657 |
| Select_range_check | 0 |
| Select_scan | 2714432 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 2059 |
| Sort_merge_passes | 16285 |
| Sort_range | 2630703 |
| Sort_rows | 3082746997 |
| Sort_scan | 790747 |
| Table_locks_immediate | 35543041 |
| Table_locks_waited | 119206 |
| Threads_cached | 155 |
| Threads_connected | 12 |
| Threads_created | 170 |
| Threads_running | 1 |
| Uptime | 846351 |
+----------------------------+---------------+
8) Server is also running ezpublisher, and photopost. Both use Server 1 for Apache/php and Server 2 for MySQL
9) We are getting 1500 users average with 2400 users during peak periods. The server continues to spike hire and hire every week.
10) Using vbulletin 3.0.6. Will be upgrading to vbulletin 3.5.x next week.
11) Apache
KeepAlive On
MaxKeepAliveRequests 120
KeepAliveTimeout 5
StartServers 50
MinSpareServers 10
MaxSpareServers 10
MaxClients 180
MaxRequestsPerChild 100
12) Log files are proccessed and truncated each night.
2)
Server 1
P4 3 Ghz
2gig Ram
4 x 80gig ATA100 7200RPM RAID 10
FreeBSD 4.8
apache-2.0.52_3
php4-4.3.10
Server 2
Dual Opteron 64-bit 2ghz
4gig Ram
4x36gig ULTRA320 15k RPM RAID 10
Solaris 10
Mysql 4.1.18 64-bit
3) No Innodb databases
4) MySQL is the official binary release from MySQL.com
5)
Server 1
last pid: 93154; load averages: 4.79, 8.26, 11.86 up 8+18:50:44 20:53:26
133 processes: 3 running, 130 sleeping
CPU states: % user, % nice, % system, % interrupt, % idle
Mem: 455M Active, 354M Inact, 283M Wired, 80M Cache, 199M Buf, 837M Free
Swap: 2031M Total, 41M Used, 1990M Free, 2% Inuse
PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
93064 www 2 0 44356K 13676K poll 0:03 3.03% 3.03% httpd
93019 www 2 0 43656K 12548K poll 0:02 1.61% 1.61% httpd
93065 www 2 0 43816K 12564K poll 0:01 1.57% 1.56% httpd
93128 www 2 0 42060K 9344K poll 0:00 2.07% 1.56% httpd
93044 www 30 0 45432K 15240K RUN 0:04 1.47% 1.46% httpd
93114 www 2 0 44216K 17760K poll 0:01 1.63% 1.46% httpd
93146 www 2 0 44176K 12688K poll 0:01 1.84% 1.37% httpd
92709 www 2 0 44488K 18512K poll 0:17 1.22% 1.22% httpd
93112 www 2 0 41704K 14252K poll 0:01 1.35% 1.22% httpd
93139 www 2 0 44372K 12560K accept 0:01 1.58% 1.17% httpd
93046 www 2 0 44140K 18228K poll 0:02 1.08% 1.07% httpd
93151 www 2 0 41900K 9212K poll 0:01 1.45% 1.07% httpd
93130 www 2 0 44184K 12792K accept 0:00 1.43% 1.07% httpd
93071 www 2 0 43664K 12988K poll 0:02 0.98% 0.98% httpd
93122 www 2 0 41948K 9332K poll 0:00 1.26% 0.98% httpd
93121 www 2 0 43776K 12040K poll 0:01 1.18% 0.93% httpd
93095 www 2 0 44208K 17644K accept 0:01 0.89% 0.88% httpd
Server 2
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
1350 mysql 630M 602M sleep 59 0 26:00:48 9.6% mysqld/176
4404 root 4704K 4192K cpu0 59 0 0:00:00 0.0% prstat/1
693 root 140M 70M sleep 59 0 0:08:04 0.0% java/27
129 root 5156K 4064K sleep 59 0 0:03:04 0.0% nscd/55
4388 spinalto 7372K 1896K sleep 59 0 0:00:00 0.0% sshd/1
246 root 1060K 600K sleep 59 0 0:00:01 0.0% utmpd/1
258 root 4260K 3112K sleep 59 0 0:00:06 0.0% inetd/4
257 root 1800K 1028K sleep 59 0 0:00:00 0.0% ttymon/1
226 daemon 2068K 1304K sleep 60 -20 0:00:00 0.0% lockd/2
224 daemon 2336K 1540K sleep 59 0 0:00:00 0.0% statd/2
242 root 1768K 996K sleep 59 0 0:00:00 0.0% ttymon/1
215 root 2236K 888K sleep 59 0 0:00:00 0.0% cron/1
114 root 2120K 1252K sleep 59 0 0:00:00 0.0% syseventd/14
321 root 4716K 1932K sleep 59 0 0:00:02 0.0% automountd/3
130 daemon 3832K 1968K sleep 59 0 0:00:01 0.0% kcfd/3
580 root 3360K 1920K sleep 59 0 0:00:00 0.0% snmpXdmid/2
236 root 1696K 892K sleep 59 0 0:00:00 0.0% sac/1
738 nobody 2852K 1892K sleep 59 0 0:00:00 0.0% xfs/1
219 daemon 2312K 1068K sleep 59 0 0:00:00 0.0% rpcbind/1
127 root 2576K 1684K sleep 59 0 0:00:00 0.0% picld/4
9 root 9196K 8232K sleep 59 0 0:00:10 0.0% svc.configd/16
Total: 40 processes, 376 lwps, load averages: 0.21, 0.21, 0.21
6) my.cnf
client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
datadir = /opt/mysql/mysql/data
socket = /tmp/mysql.sock
max_connections = 800
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 192M
query_cache_type = 1
# Following values valid only for MySQL 4.1.15
#query_prealloc_size = 16384
#query_alloc_block_size = 16384
# new value
read_rnd_buffer_size = 524288
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
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
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
7) exteded status
+----------------------------+---------------+
| Variable_name | Value |
+----------------------------+---------------+
| Aborted_clients | 14882 |
| Aborted_connects | 45 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 13631978252 |
| Bytes_sent | 1085789352909 |
| Com_admin_commands | 102 |
| Com_alter_db | 0 |
| Com_alter_table | 148 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 61686 |
| Com_change_db | 4866020 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 52298 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 851 |
| Com_dealloc_sql | 0 |
| Com_delete | 66632 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 840 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 5 |
| Com_grant | 7 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 721145 |
| Com_insert_select | 6066 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 21986 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 707496 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 12813 |
| Com_savepoint | 0 |
| Com_select | 12913150 |
| Com_set_option | 68 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 35 |
| Com_show_collations | 35 |
| Com_show_column_types | 0 |
| Com_show_create_db | 4 |
| Com_show_create_table | 0 |
| Com_show_databases | 17 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 1 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 6 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 104 |
| Com_show_variables | 99 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 23295 |
| Com_update | 4587072 |
| Com_update_multi | 0 |
| Connections | 4228308 |
| Created_tmp_disk_tables | 13306 |
| Created_tmp_files | 32567 |
| Created_tmp_tables | 646438 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 2 |
| Handler_commit | 0 |
| Handler_delete | 1051085 |
| Handler_discover | 0 |
| Handler_read_first | 1976720 |
| Handler_read_key | 1735405095 |
| Handler_read_next | 1952372197 |
| Handler_read_prev | 11744012 |
| Handler_read_rnd | 2116045559 |
| Handler_read_rnd_next | 20003218383 |
| Handler_rollback | 0 |
| Handler_update | 455506327 |
| Handler_write | 686880321 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 53585 |
| Key_read_requests | 3796044812 |
| Key_reads | 4573806 |
| Key_write_requests | 15771250 |
| Key_writes | 2024094 |
| Max_used_connections | 167 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1967 |
| Open_streams | 0 |
| Open_tables | 1562 |
| Opened_tables | 3583 |
| Qcache_free_blocks | 20195 |
| Qcache_free_memory | 132135800 |
| Qcache_hits | 32103491 |
| Qcache_inserts | 12792676 |
| Qcache_lowmem_prunes | 265144 |
| Qcache_not_cached | 83908 |
| Qcache_queries_in_cache | 40811 |
| Qcache_total_blocks | 102232 |
| Questions | 60372097 |
| Rpl_status | NULL |
| Select_full_join | 20890 |
| Select_full_range_join | 0 |
| Select_range | 2452657 |
| Select_range_check | 0 |
| Select_scan | 2714432 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 2059 |
| Sort_merge_passes | 16285 |
| Sort_range | 2630703 |
| Sort_rows | 3082746997 |
| Sort_scan | 790747 |
| Table_locks_immediate | 35543041 |
| Table_locks_waited | 119206 |
| Threads_cached | 155 |
| Threads_connected | 12 |
| Threads_created | 170 |
| Threads_running | 1 |
| Uptime | 846351 |
+----------------------------+---------------+
8) Server is also running ezpublisher, and photopost. Both use Server 1 for Apache/php and Server 2 for MySQL
9) We are getting 1500 users average with 2400 users during peak periods. The server continues to spike hire and hire every week.
10) Using vbulletin 3.0.6. Will be upgrading to vbulletin 3.5.x next week.
11) Apache
KeepAlive On
MaxKeepAliveRequests 120
KeepAliveTimeout 5
StartServers 50
MinSpareServers 10
MaxSpareServers 10
MaxClients 180
MaxRequestsPerChild 100
12) Log files are proccessed and truncated each night.