mccollin
Tue 18th Sep '07, 7:35pm
I have a vBulletin instance that has started running pretty poorly over the last few weeks. The server has been going for about 3 years now and it has run well for the most part during that time. Lately it is starting to spike heavy loads even though the number of active users hasn't really increased that much. It seems like it can handle 700-750 OK with a load of around 8-10, but if it starts to get to 800-900, the load spikes up to 20-25 and is over 30 by the time it gets to 1000. It is not unusual for us to have 1000 users at times during the day, so this is a big concern. Because of the age of the server, I've considered moving to a new one, but not sure if this will help or not. Just in terms of drive life, its probably good to do it. I'd prefer to stay with a single box, but would consider other options.
Current box - Dual Xenon 2.8Ghz w/2G memory, 73GB EIDE drives
Options for new box and delta expense.... I'd obviously like to keep an eye on costs. I don't anticipate significant new traffic... we seem to have a cyclical pattern that goes up and down, but tends to hover around 1000 active users... +/- 300.
-$30/month
Conroe 3060 Dual Core 2.4GHz, 2GB RAM, 146GB 10K RPM SAS Drives, RedHat Enterprise 4 Linux
Net Same/month
Woodcrest 5130 Dual Core 2.0GHz, 2GB RAM, 146GB 10K RPM SAS, RedHat Enterprise 4 Linux
+100/month
Dual Woodcrest 5130 Dual Core 2.0GHz, 2GB RAM, 146GB 10K RPM SAS, RedHat Enterprise 4 Linux
Here are the answers to optimization questions...
1) Dedicated, although I have a couple dozen very small sites also running on the server.
2) Server Specs,
CPU/CPU Speed: Dual Xenon 2.8Mhz
RAM Installed: 2G
Disk Installed: Dual 73GB EIDE
OS Installed: RedHat Enterprise 3 i686
Apache Version: 1.3.37
PHP Version: 4.4.7
MySQL Version: 4.1.22-standard
3) vBulletin 3.6.5
4) I am not aware of any innodb stuff
5) I'm running Cpanel, so I believe its part of that.
6) Top Stats
Members - 61,166
Threads - 82,386
Posts - 644,726
Newest member to the forums [jayzay]
Record online users 1467 (06-20-2007 09:50 PM)
Top poster [Marctronixx - 17070]
Most replied to thread [Samsung i760 - Release/Rumor Thread] - 5,844 replies
Most viewed thread [Samsung i760 - Release/Rumor Thread] - 665,343 views
Most popular forum [PPC-6700 / XV6700] - 10,608 threads, 103,631 posts
16:04:20 up 11 days, 13:57, 2 users, load average: 7.07, 8.42, 11.24
344 processes: 340 sleeping, 2 running, 1 zombie, 1 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 59.4% 0.0% 3.8% 0.2% 3.5% 1.9% 30.9%
cpu00 52.8% 0.0% 2.8% 0.0% 12.5% 0.0% 31.7%
cpu01 58.6% 0.0% 6.7% 0.0% 0.0% 0.0% 34.6%
cpu02 57.6% 0.0% 3.8% 0.9% 0.0% 3.8% 33.6%
cpu03 68.5% 0.0% 1.9% 0.0% 1.9% 3.8% 23.8%
Mem: 2054944k av, 1889900k used, 165044k free, 0k shrd, 20228k buff
1437404k actv, 170048k in_d, 24604k in_c
Swap: 1052248k av, 277080k used, 775168k free 785468k cached
7) mysql.cnf
------ start my.cnf -------
[mysqld]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-innodb
old-passwords=1
max_connections = 2000
key_buffer = 64M
myisam_sort_buffer_size = 128M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 2048
thread_cache_size = 256
wait_timeout = 100
connect_timeout = 20
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
thread_concurrency = 4
[mysql.server]
user=mysql
basedir=/var/lib/mysql
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[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
[mysqlhotcopy]
interactive-timeout
8) MySQL Stats from ssh telnet as root user, type:
+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| connect_timeout | 20 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 2093056 |
| key_buffer_size | 67108864 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 16776192 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 2000 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 134217728 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 10000 |
| pid_file | /var/lib/mysql/server1.srvsys.com.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 2093056 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 4194296 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | CDT |
| table_cache | 2048 |
| table_type | MyISAM |
| thread_cache_size | 256 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.22-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 100 |
+---------------------------------+------------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 426 |
| Aborted_connects | 3338 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 456119869 |
| Bytes_sent | 3866579738 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 114 |
| Com_change_db | 351740 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 114 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 3108 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 80 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 55184 |
| Com_insert_select | 13 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 512 |
| 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 | 5628 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 385834 |
| Com_set_option | 1330 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 933 |
| Com_show_databases | 40 |
| Com_show_errors | 0 |
| Com_show_fields | 973 |
| Com_show_grants | 69 |
| 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 | 192 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 39 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 1046 |
| Com_show_variables | 51 |
| 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 | 520 |
| Com_update | 164183 |
| Com_update_multi | 54 |
| Connections | 345156 |
| Created_tmp_disk_tables | 2871 |
| Created_tmp_files | 500 |
| Created_tmp_tables | 34210 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 1 |
| Delayed_writes | 86 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 18997 |
| Handler_discover | 0 |
| Handler_read_first | 31727 |
| Handler_read_key | 21413906 |
| Handler_read_next | 39372481 |
| Handler_read_prev | 733360 |
| Handler_read_rnd | 7627070 |
| Handler_read_rnd_next | 186280905 |
| Handler_rollback | 0 |
| Handler_update | 299176 |
| Handler_write | 8108847 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 1901 |
| Key_blocks_used | 56089 |
| Key_read_requests | 114258467 |
| Key_reads | 56643 |
| Key_write_requests | 84788 |
| Key_writes | 66900 |
| Max_used_connections | 140 |
| Not_flushed_delayed_rows | 1 |
| Open_files | 2439 |
| Open_streams | 0 |
| Open_tables | 1555 |
| Opened_tables | 1556 |
| Qcache_free_blocks | 2391 |
| Qcache_free_memory | 17036928 |
| Qcache_hits | 1090709 |
| Qcache_inserts | 380121 |
| Qcache_lowmem_prunes | 27936 |
| Qcache_not_cached | 4043 |
| Qcache_queries_in_cache | 3706 |
| Qcache_total_blocks | 11527 |
| Questions | 2403753 |
| Rpl_status | NULL |
| Select_full_join | 546 |
| Select_full_range_join | 19 |
| Select_range | 141827 |
| Select_range_check | 0 |
| Select_scan | 77026 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 127 |
| Sort_merge_passes | 250 |
| Sort_range | 74910 |
| Sort_rows | 22552710 |
| Sort_scan | 27862 |
| Table_locks_immediate | 1108121 |
| Table_locks_waited | 3336 |
| Threads_cached | 131 |
| Threads_connected | 10 |
| Threads_created | 140 |
| Threads_running | 2 |
| Uptime | 46198 |
+----------------------------+------------+
Uptime: 46198 Threads: 10 Questions: 2403759 Slow queries: 127 Opens: 1556 Flush tables: 1 Open tables: 1555 Queries per second avg: 52.032
mysqladmin Ver 8.41 Distrib 4.1.22, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.22-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 12 hours 49 min 58 sec
Threads: 10 Questions: 2403760 Slow queries: 127 Opens: 1556 Flush tables: 1 Open tables: 1555 Queries per second avg: 52.032
9) The vB that I am trying to optimize is the biggest thing on the server. I have one other smaller vB on the server that gets
maybe 50 users posting to it in a day. There are also some other scripts that are very minimally used... maybe a half dozen users
per day. In total, there are probably about 2 dozen active sites on the server.
10) Averages about 800-1000 concurrent users (timeout 1800). The peak of record is 1467 a couple months ago, but the server can't
get to that now. Once it gets to 900 and approaches 1000, things fall apart. At around 800, the server load is 10, and it climbs
rapidly up from there. The last couple of weeks, it goes to 20-25 at 900 users, and over 30 by the time it gets to 1000... which
at that point people are getting frustrated and going elsewhere.
11) pdaphonehome.com/phpinfo.php
12)
KeepAlive off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 7
MaxClients 580
MaxRequestsPerChild 0
13)
14)
uname -a
Linux server1.srvsys.com 2.4.21-27.0.1.ELsmp #1 SMP Mon Dec 20 18:47:45 EST 2004 i686 i686 i386 GNU/Linux
ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) 4
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 7168
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 9
cpu MHz : 2799.268
cache size : 512 KB
physical id : 0
siblings : 2
runqueue : 0
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht
tm
bogomips : 5583.66
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 9
cpu MHz : 2799.268
cache size : 512 KB
physical id : 0
siblings : 2
runqueue : 0
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht
tm
bogomips : 5596.77
processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 9
cpu MHz : 2799.268
cache size : 512 KB
physical id : 6
siblings : 2
runqueue : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht
tm
bogomips : 5596.77
processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 9
cpu MHz : 2799.268
cache size : 512 KB
physical id : 6
siblings : 2
runqueue : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht
tm
bogomips : 5596.77
Current box - Dual Xenon 2.8Ghz w/2G memory, 73GB EIDE drives
Options for new box and delta expense.... I'd obviously like to keep an eye on costs. I don't anticipate significant new traffic... we seem to have a cyclical pattern that goes up and down, but tends to hover around 1000 active users... +/- 300.
-$30/month
Conroe 3060 Dual Core 2.4GHz, 2GB RAM, 146GB 10K RPM SAS Drives, RedHat Enterprise 4 Linux
Net Same/month
Woodcrest 5130 Dual Core 2.0GHz, 2GB RAM, 146GB 10K RPM SAS, RedHat Enterprise 4 Linux
+100/month
Dual Woodcrest 5130 Dual Core 2.0GHz, 2GB RAM, 146GB 10K RPM SAS, RedHat Enterprise 4 Linux
Here are the answers to optimization questions...
1) Dedicated, although I have a couple dozen very small sites also running on the server.
2) Server Specs,
CPU/CPU Speed: Dual Xenon 2.8Mhz
RAM Installed: 2G
Disk Installed: Dual 73GB EIDE
OS Installed: RedHat Enterprise 3 i686
Apache Version: 1.3.37
PHP Version: 4.4.7
MySQL Version: 4.1.22-standard
3) vBulletin 3.6.5
4) I am not aware of any innodb stuff
5) I'm running Cpanel, so I believe its part of that.
6) Top Stats
Members - 61,166
Threads - 82,386
Posts - 644,726
Newest member to the forums [jayzay]
Record online users 1467 (06-20-2007 09:50 PM)
Top poster [Marctronixx - 17070]
Most replied to thread [Samsung i760 - Release/Rumor Thread] - 5,844 replies
Most viewed thread [Samsung i760 - Release/Rumor Thread] - 665,343 views
Most popular forum [PPC-6700 / XV6700] - 10,608 threads, 103,631 posts
16:04:20 up 11 days, 13:57, 2 users, load average: 7.07, 8.42, 11.24
344 processes: 340 sleeping, 2 running, 1 zombie, 1 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 59.4% 0.0% 3.8% 0.2% 3.5% 1.9% 30.9%
cpu00 52.8% 0.0% 2.8% 0.0% 12.5% 0.0% 31.7%
cpu01 58.6% 0.0% 6.7% 0.0% 0.0% 0.0% 34.6%
cpu02 57.6% 0.0% 3.8% 0.9% 0.0% 3.8% 33.6%
cpu03 68.5% 0.0% 1.9% 0.0% 1.9% 3.8% 23.8%
Mem: 2054944k av, 1889900k used, 165044k free, 0k shrd, 20228k buff
1437404k actv, 170048k in_d, 24604k in_c
Swap: 1052248k av, 277080k used, 775168k free 785468k cached
7) mysql.cnf
------ start my.cnf -------
[mysqld]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-innodb
old-passwords=1
max_connections = 2000
key_buffer = 64M
myisam_sort_buffer_size = 128M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 2048
thread_cache_size = 256
wait_timeout = 100
connect_timeout = 20
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
thread_concurrency = 4
[mysql.server]
user=mysql
basedir=/var/lib/mysql
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[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
[mysqlhotcopy]
interactive-timeout
8) MySQL Stats from ssh telnet as root user, type:
+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| connect_timeout | 20 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 2093056 |
| key_buffer_size | 67108864 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 16776192 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 2000 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 134217728 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 10000 |
| pid_file | /var/lib/mysql/server1.srvsys.com.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 2093056 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 4194296 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | CDT |
| table_cache | 2048 |
| table_type | MyISAM |
| thread_cache_size | 256 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.22-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 100 |
+---------------------------------+------------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 426 |
| Aborted_connects | 3338 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 456119869 |
| Bytes_sent | 3866579738 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 114 |
| Com_change_db | 351740 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 114 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 3108 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 80 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 55184 |
| Com_insert_select | 13 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 512 |
| 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 | 5628 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 385834 |
| Com_set_option | 1330 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 933 |
| Com_show_databases | 40 |
| Com_show_errors | 0 |
| Com_show_fields | 973 |
| Com_show_grants | 69 |
| 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 | 192 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 39 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 1046 |
| Com_show_variables | 51 |
| 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 | 520 |
| Com_update | 164183 |
| Com_update_multi | 54 |
| Connections | 345156 |
| Created_tmp_disk_tables | 2871 |
| Created_tmp_files | 500 |
| Created_tmp_tables | 34210 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 1 |
| Delayed_writes | 86 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 18997 |
| Handler_discover | 0 |
| Handler_read_first | 31727 |
| Handler_read_key | 21413906 |
| Handler_read_next | 39372481 |
| Handler_read_prev | 733360 |
| Handler_read_rnd | 7627070 |
| Handler_read_rnd_next | 186280905 |
| Handler_rollback | 0 |
| Handler_update | 299176 |
| Handler_write | 8108847 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 1901 |
| Key_blocks_used | 56089 |
| Key_read_requests | 114258467 |
| Key_reads | 56643 |
| Key_write_requests | 84788 |
| Key_writes | 66900 |
| Max_used_connections | 140 |
| Not_flushed_delayed_rows | 1 |
| Open_files | 2439 |
| Open_streams | 0 |
| Open_tables | 1555 |
| Opened_tables | 1556 |
| Qcache_free_blocks | 2391 |
| Qcache_free_memory | 17036928 |
| Qcache_hits | 1090709 |
| Qcache_inserts | 380121 |
| Qcache_lowmem_prunes | 27936 |
| Qcache_not_cached | 4043 |
| Qcache_queries_in_cache | 3706 |
| Qcache_total_blocks | 11527 |
| Questions | 2403753 |
| Rpl_status | NULL |
| Select_full_join | 546 |
| Select_full_range_join | 19 |
| Select_range | 141827 |
| Select_range_check | 0 |
| Select_scan | 77026 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 127 |
| Sort_merge_passes | 250 |
| Sort_range | 74910 |
| Sort_rows | 22552710 |
| Sort_scan | 27862 |
| Table_locks_immediate | 1108121 |
| Table_locks_waited | 3336 |
| Threads_cached | 131 |
| Threads_connected | 10 |
| Threads_created | 140 |
| Threads_running | 2 |
| Uptime | 46198 |
+----------------------------+------------+
Uptime: 46198 Threads: 10 Questions: 2403759 Slow queries: 127 Opens: 1556 Flush tables: 1 Open tables: 1555 Queries per second avg: 52.032
mysqladmin Ver 8.41 Distrib 4.1.22, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.22-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 12 hours 49 min 58 sec
Threads: 10 Questions: 2403760 Slow queries: 127 Opens: 1556 Flush tables: 1 Open tables: 1555 Queries per second avg: 52.032
9) The vB that I am trying to optimize is the biggest thing on the server. I have one other smaller vB on the server that gets
maybe 50 users posting to it in a day. There are also some other scripts that are very minimally used... maybe a half dozen users
per day. In total, there are probably about 2 dozen active sites on the server.
10) Averages about 800-1000 concurrent users (timeout 1800). The peak of record is 1467 a couple months ago, but the server can't
get to that now. Once it gets to 900 and approaches 1000, things fall apart. At around 800, the server load is 10, and it climbs
rapidly up from there. The last couple of weeks, it goes to 20-25 at 900 users, and over 30 by the time it gets to 1000... which
at that point people are getting frustrated and going elsewhere.
11) pdaphonehome.com/phpinfo.php
12)
KeepAlive off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 7
MaxClients 580
MaxRequestsPerChild 0
13)
14)
uname -a
Linux server1.srvsys.com 2.4.21-27.0.1.ELsmp #1 SMP Mon Dec 20 18:47:45 EST 2004 i686 i686 i386 GNU/Linux
ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) 4
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 7168
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 9
cpu MHz : 2799.268
cache size : 512 KB
physical id : 0
siblings : 2
runqueue : 0
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht
tm
bogomips : 5583.66
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 9
cpu MHz : 2799.268
cache size : 512 KB
physical id : 0
siblings : 2
runqueue : 0
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht
tm
bogomips : 5596.77
processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 9
cpu MHz : 2799.268
cache size : 512 KB
physical id : 6
siblings : 2
runqueue : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht
tm
bogomips : 5596.77
processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 9
cpu MHz : 2799.268
cache size : 512 KB
physical id : 6
siblings : 2
runqueue : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht
tm
bogomips : 5596.77