Hello,
I’m hoping to have our server configuration looked at. We are currently experiencing a peak concurrent users of ~3000 which is causing our web servers to fall over.
After doing some reading about large vBulletin forums it seems we should be able to support many more concurrent users given the hardware we are running on.
Please let me know if there is any configuration we could do to get more out of our servers.
==================================
==================================
In order to help you with server problems and optimization, please provide the following info in your thread (courtesy of eva2000):
1. Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)
Dedicated cluster
2. your server specs. For example:
(5 webservers and 1 DB server)
2x Webservers with the following hardware:
Dual 2.8Ghz Xeon
2GB Memory
1x160GB SATA
2x Webservers with the following hardware:
Dual 3.2Ghz Xeon (64bit)
2GB Memory
2x73GB Ultra 320 SCSI (RAID 1)
1x Webserver with the following hardware:
2x Dual 1.86Ghz Xeon
2GB Memory
2x73GB Ultra 320 SCSI (RAISD 1)
1x Database server with the following hardware:
Dual 3.4Ghz Xeon (64bit)
4GB Memory
4x73GB Ultra 320 SCSI (RAID 1+0)
linux distributor or windows version: redhat linux ES 4
apache: apache 1.3.37
PHP version: php 4.4.4
MySQL version: 4.1.20
3. what version of vB are you running ?
3.6.4
4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
Yes (There’s InnoDB tables in the vBulletin database, such as `administrator` etc)
5. if possible how mysql was compiled/installed
Official RHEL RPM packages
6. your top stats
[root@dp02 ~]# top -n 1 -b
top - 14:12:04 up 573 days, 19:40, 5 users, load average: 0.42, 0.41, 0.38
Tasks: 111 total, 2 running, 109 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.0% us, 0.6% sy, 0.0% ni, 97.9% id, 0.4% wa, 0.0% hi, 0.1% si
Mem: 4045524k total, 3822356k used, 223168k free, 47700k buffers
Swap: 4192956k total, 124k used, 4192832k free, 2961224k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2420 root 16 0 52384 11m 3500 S 6 0.3 0:00.22 php
26594 root 15 0 0 0 0 S 4 0.0 7:04.42 nfsd
26593 root 15 0 0 0 0 S 2 0.0 6:39.71 nfsd
2414 root 16 0 52420 11m 3452 S 2 0.3 0:00.11 php
2552 root 17 0 19460 1856 1292 R 2 0.0 0:00.01 snmpget
1 root 16 0 4748 536 452 S 0 0.0 0:58.86 init
2 root RT 0 0 0 0 S 0 0.0 1:18.40 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:02.26 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 1:02.21 migration/1
5 root 34 19 0 0 0 S 0 0.0 0:00.83 ksoftirqd/1
6 root RT 0 0 0 0 S 0 0.0 0:45.53 migration/2
7 root 34 19 0 0 0 S 0 0.0 0:00.99 ksoftirqd/2
8 root RT 0 0 0 0 S 0 0.0 0:37.20 migration/3
9 root 34 19 0 0 0 S 0 0.0 0:00.71 ksoftirqd/3
10 root 5 -10 0 0 0 S 0 0.0 0:00.05 events/0
11 root 5 -10 0 0 0 S 0 0.0 0:00.01 events/1
12 root 5 -10 0 0 0 S 0 0.0 0:00.01 events/2
13 root 5 -10 0 0 0 S 0 0.0 0:00.03 events/3
14 root 5 -10 0 0 0 S 0 0.0 0:00.00 khelper
15 root 15 -10 0 0 0 S 0 0.0 0:00.00 kacpid
47 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/0
48 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/1
49 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/2
50 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/3
51 root 15 0 0 0 0 S 0 0.0 0:00.00 khubd
68 root 12 -10 0 0 0 S 0 0.0 0:00.00 aio/0
67 root 15 0 0 0 0 S 0 0.0 13:00.02 kswapd0
69 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/1
70 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/2
71 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/3
144 root 15 0 0 0 0 S 0 0.0 0:00.00 kseriod
208 root 19 0 0 0 0 S 0 0.0 0:00.00 scsi_eh_0
217 root 5 -10 0 0 0 S 0 0.0 0:00.00 ata/0
218 root 6 -10 0 0 0 S 0 0.0 0:00.00 ata/1
219 root 5 -10 0 0 0 S 0 0.0 0:00.00 ata/2
220 root 5 -10 0 0 0 S 0 0.0 0:00.00 ata/3
222 root 19 0 0 0 0 S 0 0.0 0:00.00 scsi_eh_1
224 root 19 0 0 0 0 S 0 0.0 0:00.00 scsi_eh_2
229 root 15 0 0 0 0 S 0 0.0 152:45.79 kjournald
1430 root 15 0 0 0 0 S 0 0.0 0:00.02 kjournald
1796 root 16 0 2524 456 388 S 0 0.0 1:05.72 cpuspeed
2031 root 16 0 2528 500 412 S 0 0.0 0:14.10 irqbalance
2048 rpc 16 0 4744 688 560 S 0 0.0 0:00.02 portmap
2067 rpcuser 16 0 5796 784 668 S 0 0.0 0:00.00 rpc.statd
2167 root 18 0 2536 548 460 S 0 0.0 0:00.00 acpid
2309 xfs 16 0 10704 2156 792 S 0 0.1 0:00.01 xfs
2362 root 16 0 27568 1328 1020 S 0 0.0 0:00.00 login
2363 root 17 0 2516 416 352 S 0 0.0 0:00.00 mingetty
2364 root 19 0 2516 416 352 S 0 0.0 0:00.00 mingetty
2365 root 18 0 2516 416 352 S 0 0.0 0:00.00 mingetty
2366 root 18 0 2516 416 352 S 0 0.0 0:00.00 mingetty
32111 root 16 0 92692 2136 1288 S 0 0.1 7:24.18 bacula-fd
23592 root 16 0 14940 1272 1084 S 0 0.0 0:00.76 ntpd
23593 _ntp 15 0 13896 1164 980 S 0 0.0 0:19.59 ntpd
3790 root 15 0 5788 792 616 S 0 0.0 5:48.23 syslogd
3807 root 16 0 2532 492 412 S 0 0.0 0:00.00 klogd
31959 root 16 0 41144 568 496 S 0 0.0 0:00.03 rhnsd
25380 root 6 -10 3604 484 404 S 0 0.0 0:00.00 udevd
27523 root 16 0 8912 748 620 S 0 0.0 0:00.00 atd
27677 dbus 16 0 9640 1256 1092 S 0 0.0 0:00.05 dbus-daemon-1
27990 root 16 0 4172 608 524 S 0 0.0 0:00.00 gpm
28353 root 16 0 18600 6316 1724 S 0 0.2 0:18.55 hald
29097 root 16 0 20920 1576 1208 S 0 0.0 0:00.31 rpc.idmapd
29413 root 16 0 21924 2016 1600 S 0 0.0 21:58.24 sshd
29838 root 16 0 57092 1144 728 S 0 0.0 0:38.05 crond
29868 root 18 0 8712 884 724 S 0 0.0 0:00.00 xinetd
20961 root 16 0 36040 3928 2448 S 0 0.1 2:29.96 sendmail
20969 smmsp 16 0 28836 3216 2140 S 0 0.1 0:00.09 sendmail
iManage: Policy-Driven Self-Management for Enterprise-Scale Systems::
File Format: PDF/Adobe Acrobat - View as HTMLor not robust enough, failing to scale or respond to the full range of an adminis- .. handle 3000 concurrent requests from the clients). The set V
http://www.cc.gatech.edu/~vibhore/papers/mware-2007.pdfHOME |
26588 root 16 0 53216 776 664 S 0 0.0 0:00.00 rpc.rquotad
26592 root 15 0 0 0 0 S 0 0.0 6:45.87 nfsd
26599 root 15 0 0 0 0 S 0 0.0 6:34.26 nfsd
26598 root 15 0 0 0 0 S 0 0.0 6:59.11 nfsd
26595 root 15 0 0 0 0 S 0 0.0 6:59.93 nfsd
26597 root 15 0 0 0 0 S 0 0.0 6:56.74 nfsd
26596 root 15 0 0 0 0 S 0 0.0 7:08.12 nfsd
26600 root 16 0 0 0 0 S 0 0.0 0:00.00 lockd
26601 root 17 0 0 0 0 S 0 0.0 0:00.00 rpciod
26605 root 15 0 6952 2052 736 S 0 0.1 0:05.92 rpc.mountd
10908 root 16 0 37252 2672 2072 S 0 0.1 0:02.39 sshd
10912 root 16 0 53996 1600 1232 S 0 0.0 0:00.13 bash
28645 root 16 0 99m 7448 4992 S 0 0.2 0:00.23 httpd
31206 root 15 0 0 0 0 S 0 0.0 0:37.98 pdflush
31955 root 15 0 0 0 0 S 0 0.0 0:00.61 pdflush
11874 root 16 0 52780 1156 960 S 0 0.0 0:00.00 mysqld_safe
11907 mysql 15 0 259m 116m 4780 S 0 3.0 8:11.09 mysqld
4541 root 16 0 72208 2668 2084 S 0 0.1 0:00.01 cupsd
4576 root 16 0 87696 5944 3020 S 0 0.1 0:06.32 snmpd
28040 apache 16 0 106m 14m 5780 S 0 0.4 0:19.02 httpd
17896 root 16 0 37212 2696 2076 S 0 0.1 0:00.13 sshd
17905 root 15 0 53988 1580 1224 S 0 0.0 0:00.02 bash
18530 root 16 0 19816 2356 1832 S 0 0.1 0:00.05 ssh
24801 root 16 0 53992 1588 1224 S 0 0.0 0:00.03 bash
25407 root 18 0 2524 404 336 S 0 0.0 0:00.00 mingetty
1869 root 16 0 36184 2604 2040 S 0 0.1 0:00.02 sshd
1911 root 16 0 53988 1560 1204 S 0 0.0 0:00.02 bash
9662 apache 16 0 106m 14m 5776 S 0 0.4 0:16.57 httpd
9687 apache 16 0 106m 14m 5780 S 0 0.4 0:16.80 httpd
9693 apache 16 0 106m 14m 5776 S 0 0.4 0:15.95 httpd
9813 apache 16 0 105m 14m 5776 S 0 0.4 0:16.58 httpd
31190 apache 16 0 106m 14m 5776 S 0 0.4 0:16.75 httpd
3864 apache 16 0 106m 14m 5776 S 0 0.4 0:15.30 httpd
3884 apache 16 0 106m 14m 5776 S 0 0.4 0:15.33 httpd
3885 apache 16 0 106m 14m 5776 S 0 0.4 0:14.35 httpd
31443 root 16 0 37252 2672 2072 S 0 0.1 0:00.07 sshd
31452 root 16 0 53996 1592 1224 S 0 0.0 0:00.02 bash
2402 root 16 0 72016 1480 976 S 0 0.0 0:00.00 crond
2404 root 16 0 72016 1480 976 S 0 0.0 0:00.00 crond
2406 root 4 -12 6848 748 616 S 0 0.0 0:00.00 ntpdate
2409 root 21 0 5356 912 776 S 0 0.0 0:00.00 sh
2483 root 16 0 10380 720 584 S 0 0.0 0:00.00 rrdtool
2531 root 15 0 6144 928 696 R 0 0.0 0:00.00 top
7. your mysql configuration variables located at /etc/my.cnf or c:my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).
[root@dp02 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_connections = 900
max_allowed_packet=192M
skip-name-resolv
skip-name-resolve
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
8. MySQL stats from ssh telnet as root user type:
mysqladmin -u root -p var ext stat ver
+---------------------------------+--------------------------------------------------------+
Variable_name Value
+---------------------------------+--------------------------------------------------------+
back_log 50
basedir /usr/
bdb_cache_size 8388600
bdb_home /var/lib/mysql/
bdb_log_buffer_size 32768
bdb_logdir
bdb_max_lock 10000
bdb_shared_data OFF
bdb_tmpdir /tmp/
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 5
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 YES
have_blackhole_engine NO
have_compress YES
have_crypt YES
have_csv NO
have_example_engine NO
have_geometry YES
have_innodb YES
have_isam YES
have_ndbcluster NO
have_openssl YES
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 ibdata1:10M:autoextend
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
HAProxy Reference Manual ------------------- version 1.2.18 willy ::
The maximum number of concurrent sessions on a server will be This makes it easier to disable HTTP keep-alive than the previous 4-rules block.
http://haproxy.1wt.eu/download/1.2/doc/haproxy-en.txtHOME |
Chapter 16. Release Notes::
[JBREM-561] - http chuncked test cases failing under jdk 1.5. ** Feature Request .. This is a major release with over a 160 jira issues resolved (fea.
http://www.jboss.org/jbossremoting/docs/guide/2.2/html/ch16.htmlHOME |
innodb_thread_concurrency 8
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language /usr/share/mysql/english/
large_files_support ON
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 201325568
max_binlog_cache_size 18446744073709551615
max_binlog_size 1073741824
max_connect_errors 10
max_connections 900
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads 20
max_join_size 18446744073709551615
max_length_for_sort_data 1024
max_prepared_stmt_count 16382
max_relay_log_size 0
max_seeks_for_key 18446744073709551615
max_sort_length 1024
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 18446744073709551615
myisam_data_pointer_size 4
myisam_max_extra_sort_file_size 2147483648
myisam_max_sort_file_size 9223372036854775807
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 8388608
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 4510
pid_file /var/run/mysqld/mysqld.pid
port 3306
preload_buffer_size 32768
prepared_stmt_count 0
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 0
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
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 2097144
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 GMT
table_cache 64
table_type MyISAM
thread_cache_size 0
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.20
version_bdb Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006)
version_comment Source distribution
version_compile_machine x86_64
version_compile_os redhat-linux-gnu
wait_timeout 28800
+---------------------------------+--------------------------------------------------------+
+--------------------------------+---------------+
Variable_name Value
+--------------------------------+---------------+
Aborted_clients 1032
Aborted_connects 346
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 10414950181
Bytes_sent 1416623671647
Com_admin_commands 2922
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 3810936
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_dealloc_sql 0
Com_delete 679661
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 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 1619412
Com_insert_select 16745
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 5
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 189950
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 25429268
Com_set_option 480
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 479
Com_show_databases 2
Com_show_errors 0
Com_show_fields 570
Com_show_grants 0
Com_show_innodb_status 32027
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 1601
Com_show_slave_hosts 0
Com_show_slave_status 89670
Com_show_status 89671
Com_show_storage_engines 0
Com_show_tables 519
Com_show_variables 89794
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 12810
Com_unlock_tables 5
Com_update 2996262
Com_update_multi 0
Connections 3899678
Created_tmp_disk_tables 3094
Created_tmp_files 15089
Created_tmp_tables 1855033
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 5
Handler_delete 1288422
Handler_discover 0
Handler_read_first 2912027
Handler_read_key 1283115075
Handler_read_next 2007612713
Handler_read_prev 14451268
Handler_read_rnd 679988148
Handler_read_rnd_next 5457544703
Handler_rollback 3827157
Handler_update 8183034
Handler_write 627818342
Key_blocks_not_flushed 0
Key_blocks_unused 1836
Key_blocks_used 6698
Key_read_requests 3534790292
Key_reads 55359772
Key_write_requests 5592696
Key_writes 4070954
Max_used_connections 556
Not_flushed_delayed_rows 0
Open_files 67
Open_streams 0
Open_tables 64
Opened_tables 1770179
Qcache_free_blocks 0
Qcache_free_memory 0
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0
Qcache_not_cached 0
Qcache_queries_in_cache 0
Qcache_total_blocks 0
Questions 38958574
Rpl_status NULL
Select_full_join 3001
Select_full_range_join 0
Select_range 9188163
Select_range_check 0
Select_scan 2866754
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 611
Sort_merge_passes 7563
Sort_range 3517622
Sort_rows 1076199830
Sort_scan 858626
Ssl_accept_renegotiates 0
Ssl_accepts 0
Ssl_callback_cache_hits 0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects 0
Ssl_connect_renegotiates 0
Ssl_ctx_verify_depth 0
Ssl_ctx_verify_mode 0
Ssl_default_timeout 0
Ssl_finished_accepts 0
Ssl_finished_connects 0
Ssl_session_cache_hits 0
Ssl_session_cache_misses 0
Ssl_session_cache_mode NONE
Ssl_session_cache_overflows 0
Ssl_session_cache_size 0
Ssl_session_cache_timeouts 0
Ssl_sessions_reused 0
Ssl_used_session_cache_entries 0
Ssl_verify_depth 0
Ssl_verify_mode 0
Ssl_version
Table_locks_immediate 46340248
Table_locks_waited 131160
Threads_cached 0
Threads_connected 29
Threads_created 3899677
Threads_running 2
Uptime 384255
+--------------------------------+---------------+
Uptime: 384255 Threads: 29 Questions: 38958575 Slow queries: 611 Opens: 1770179 Flush tables: 1 Open tables: 64 Queries per second avg: 101.387
mysqladmin Ver 8.41 Distrib 4.1.20, for redhat-linux-gnu on x86_64
(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.20
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 4 days 10 hours 44 min 15 sec
Threads: 29 Questions: 38958575 Slow queries: 611 Opens: 1770179 Flush tables: 1 Open tables: 64 Queries per second avg: 101.387
MySQL 4.1.20 uptime 4 10:47:23 Tue Mar 27 14:16:17 2007
__ Key __________________________________________________ _______________
Buffer used 6.54M of 8.00M %Used: 81.76
Current 8.00M %Usage: 100.00
Write ratio 0.728
Read ratio 0.016
__ Questions __________________________________________________ _________
Total 38.98M 101.4/s
DMS 30.95M 80.5/s %Total: 79.40
Com_ 4.13M 10.8/s 10.60
COM_QUIT 3.90M 10.1/s 10.01
-Unknown 3.39k 0.0/s 0.01
Slow 611 0.0/s 0.00 %DMS: 0.00
DMS 30.95M 80.5/s 79.40
SELECT 25.45M 66.2/s 65.27 82.21
UPDATE 3.00M 7.8/s 7.69 9.69
INSERT 1.64M 4.3/s 4.20 5.29
DELETE 680.01k 1.8/s 1.74 2.20
REPLACE 190.06k 0.5/s 0.49 0.61
Com_ 4.13M 10.8/s 10.60
change_db 3.81M 9.9/s 9.78
show_variab 89.84k 0.2/s 0.23
show_status 89.72k 0.2/s 0.23
__ SELECT and Sort __________________________________________________ ___
Scan 2.87M 7.5/s %SELECT: 11.27
Range 9.19M 23.9/s 36.13
Full join 3.01k 0.0/s 0.01
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 859.23k 2.2/s
Sort range 3.52M 9.2/s
Sort mrg pass 7.57k 0.0/s
__ Table Locks __________________________________________________ _______
Waited 131.35k 0.3/s %Total: 0.28
Immediate 46.37M 120.6/s
__ Tables __________________________________________________ ____________
Open 64 of 64 %Cache: 100.00
Opened 1.77M 4.6/s
__ Connections __________________________________________________ _______
Max used 556 of 900 %Max: 61.78
Total 3.90M 10.1/s
__ Created Temp __________________________________________________ ______
Disk table 3.10k 0.0/s
Table 1.86M 4.8/s
File 15.11k 0.0/s
__ Threads __________________________________________________ ___________
Running 1 of 30
Cached 0 of 0 %Hit: 0
Created 3.90M 10.1/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 1.03k 0.0/s
Connects 346 0.0/s
__ Bytes __________________________________________________ _____________
Sent 1.42T 3.7M/s
Received 10.42G 27.1k/s
9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
vB only
10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
Average 1000 max 3000 (and growing)
Cookie lifetime is infinitive or until the browser is closed.
11. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site
(I can PM this if needed)
12. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :
KeepAlive - On
MaxKeepAliveRequests - 100
KeepAliveTimeout - 15
MinSpareServers - 5
MaxSpareServers - 15
StartServers - 5
MaxClients - 300
AND Maxrequestsperchild value = 0
13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log
None
14. Post output from these 3 commands
Linux xxxx.xxxxxx.xxxxxxx.xxx. 2.6.9-11.ELsmp #1 SMP Fri May 20 18:25:30 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux
(identical to all webservers)
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 16383
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
/proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 1
cpu MHz : 3192.149
cache size : 1024 KB
physical id : 0
siblings : 2
fpu : yes
fpu_exception : yes
cpuid level : 5
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 syscall lm pni monitor ds_cpl cid cx16 xtpr
bogomips : 6307.84
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 1
cpu MHz : 3192.149
cache size : 1024 KB
physical id : 3
siblings : 2
fpu : yes
fpu_exception : yes
cpuid level : 5
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 syscall lm pni monitor ds_cpl cid cx16 xtpr
bogomips : 6373.37
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:
processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 1
cpu MHz : 3192.149
cache size : 1024 KB
physical id : 0
siblings : 2
fpu : yes
fpu_exception : yes
cpuid level : 5
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 syscall lm pni monitor ds_cpl cid cx16 xtpr
bogomips : 6373.37
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:
processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 1
cpu MHz : 3192.149
cache size : 1024 KB
physical id : 3
siblings : 2
fpu : yes
fpu_exception : yes
cpuid level : 5
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 syscall lm pni monitor ds_cpl cid cx16 xtpr
bogomips : 6373.37
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:
Hi Longclaw,
Are those 5 web servers load balanced to serve the one vB forum ? how are they connected physically between 5 web servers and 1 db server ? speed of network adaptor cards used ? 100Mb or Gigabit network cards ?
There is room for improvement though via the mysql settings etc. You can ignore any of the suggestions that you have already done.
First off, change all innodb tables back to myisam defaults so you can disable innodb. Then try the following in this exact order
1. Upgrade MySQL server to 4.1.22
2. Upgrade PHP to 4.4.6 and/or recompile PHP 4.4.6 after upgrading MySQL
3. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
safe-show-database
old_passwords
back_log = 150
skip-innodb
max_connections = 800
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 3000
thread_cache_size = 512
wait_timeout = 30
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
nice = -10
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
4. On each web server install APC Cache lastest version from http://pecl.php.net/package/APC. Read install guide at http://www.vbulletin.com/forum/showthread.php?t=165367 - please remove Zend Optimizer from php.ini before installing APC Cache. OR try the alternative PHP caching software, Xcache which seems to be a bit faster than APC Cache - http://www.vbulletin.com/forum/showthread.php?t=213267
5. Upgrade to vB 3.6.5 http://www.vbulletin.com/forum/showthread.php?t=221905 if you're on vB 3.6.x
6. If you just upgraded to vB 3.5.x/3.6.x try to disable these 4 options:
Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?
Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums
Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread
Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search
7. Edit httpd.conf values on each of 5 web servers from
KeepAlive - On
MaxKeepAliveRequests - 100
KeepAliveTimeout - 15
MinSpareServers - 5
MaxSpareServers - 15
StartServers - 5
MaxClients - 300
AND Maxrequestsperchild value = 0
to
KeepAlive - Off
MaxKeepAliveRequests - 100
KeepAliveTimeout - 5
MinSpareServers - 10
MaxSpareServers - 15
StartServers - 10
MaxClients - 150
AND Maxrequestsperchild value = 1000
restart apache
8. You may need to install same mysql version on each of the 5 web servers, and recompile PHP with the --with-mysql=/path/to/mysql option to tell PHP to use the latest mysql 4.1.x client libraries i.e. if mysqldump/mysqlcheck/mysqladmin are located at /usr/bin/ or /usr/local/mysql/bin then you need to compile PHP with --with-mysql=/usr or --with-mysql=/usr/local/mysql - just had a few issues with one vB client have issues with PHP connecting to external mysql server until i did this.
9. If you have split web + db servers, ensure web server has dual network cards as outlined at http://www.vbulletin.org/forum/showthread.php?t=111191 so that they have a dedicated connection to the db server separate from the connection from internet/public.
10. Upgrade the web server's linux kernels all to latest stable 2.6.x smp linux kernels.
 Red Hat's Rough Recovery From CFO Exit
 Windows Live Finds a New, Pre-installed Home |
Servers keep failing over with 3000 concurrent