Serious problem - Server load is (seemingly) randomly jumping from under 1 to 75
January 8th, 2009 |I made a post (http://www.vbulletin.com/forum/showthread.php?t=131000) back on February 22nd that outlined the problem(s) I was having with vBulletin and server load. I know that post is long, but reading that post first would probably give more insight into the problems my site has had. Just to paraphrase, at that time, my site was consistently getting high server loads at seemingly completely random times throughout the day. By logging into the Admin CP and monitoring it, most of the time, the first number is under '1' more often than not. However, without warning, that number would jump to very large amounts, such as in the 90's. Please keep in mind that we rarely have more than 150 concurrent users online and almost never over 200. Most of the time, it's between 100 and 150. My host was very aware of the problem, or at least the symptoms of the problem, and eventually, the server load became more stable in that this problem wasn't happening so frequently. It wasn't anything that I did from my end, so I just assumed that he (my host) noticed a problem from their end and fixed whatever it was.
In any event, while this problem became less frequent, it is definitely still happening. I've been monitoring the site pretty heavily the past 48 hours and it has happened at least 4 times that I have noticed. The server load will be completely stable and again, without warning, a user will not be able to connect to the site, and receive an error that message that there was a problem with the database and an email has been dispatched to the webmaster (me). That email gives a mySQL error 0, which I've read is typical when users cannot connect to the the traffic (or connections) being too much for the server. After about 5-10 minutes, the site comes back up and the server load gradually drops from a very high number back to under 1 with little to no change in the amount of users that vB shows as being connected.
I have talked with my host at great length to try to fix this problem. I am running a clean version of the latest vBulletin (3.0.7) and I just had my host upgrade PHP from 4.3.10 to 4.3.11. Additionally, the server has mySQL version 12.22 and I have GZIP turned ON and set at level 1. I have attached a file that has a conversation from yesterday between my host (Joe) and I (Boss-Hog) discussing this problem. From what I could gather, he thinks the problems are a denial of service attack, but I'm not so sure. I just want to get the site consistently running at an acceptable level so that the severe spikes in the server load do not continue to occur, making the site inoperable and driving up our bandwidth costs. If there's any other information you need from me, please let me know. Thank you very much.
Boss
I spoke with my host about the proposed changes. Below, I'm posting the conversation we had (with his permission):
What are your thoughts on the dilemma I am facing? Thanks again for your help!
then install eaccelerator from http://eaccelerator.net/InstallEacceleratorUk read http://www.vbulletin.com/forum/showpost.php?p=805040&postcount=441
for shared hosting 1GB probably isnt enough especially with single 2.4ghz cpu but see how those 2 changes go
Thanks eva, I'll run that by him and keep you posted!
[mysqld]
skip-locking
skip-innodb
max_connections = 650
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 = 10800
connect_timeout = 10
tmp_table_size = 32M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
[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
then install eaccelerator from http://eaccelerator.net/InstallEacceleratorUk read http://www.vbulletin.com/forum/showpost.php?p=805040&postcount=441
for shared hosting 1GB probably isnt enough especially with single 2.4ghz cpu but see how those 2 changes go
Anyway, if you are having anywhere from 50-75 REAL USERS online (not guests) then you are starting to need something dedicated.
Depending on how fast your site is growing will be depandant apon what sort of server you will need for dedicated.
I have posted the answers to those questions below. If there's anything else you need, plase let me know. Thanks!
1). Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)
shared, approx 90 other sites, non mysql users for the most part, nobody that's ever caused a blip before
2). your server specs:
cpu speed/type single or dual cpus): model name: Intel(R) Pentium(R) 4 CPU 2.80GHz with hyperthreading and CentOS
how much memory installed: 1024MB
hard drive type/configuration: Disk /dev/hda: 40.0 GB non raid 15% used.
linux distributor or windows version: redhat linux 9.0
apache/IIS version: apache [root@www12 proc]# httpd -v Server version: Apache/1.3.33 (Unix)
PHP version: PHP 4.3.11
MySQL version: mysql Ver 12.22 Distrib 4.0.16,
3). if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
no
4). if possible how mysql was compiled/installed
my host wasn't sure about the answer to this one
5). your top stats
varies by time of day. Prior to this site, top indicated top usage of .05 or less. Now an average is 1.0 or more - with occasional spikes to 70.0 +
CPU0 states: 18.4% user 2.3% system 0.0% nice 0.0% iowait 78.1% idle
CPU1 states: 18.2% user 3.0% system 0.0% nice 0.0% iowait 78.1% idle
prior to this vb install (from a snapshot taken prior to this domain on this server):
CPU0 states: 3.2% user 0.5% system 0.0% nice 0.0% iowait 95.2% idle
CPU1 states: 1.4% user 0.1% system 0.0% nice 0.0% iowait 98.4% idle
6). your mysql configuration variables located at /etc/my.cnf or c:my.cnf so post the contents inside of my.cnf (minus any passwords of course). If on Windows server if you don't have that file you need to log into telnet and as root user type
Variable_name Value
back_log 50
basedir /
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin1
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert ON
connect_timeout 5
convert_character_set
datadir /var/lib/mysql/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb NO
have_crypt YES
have_innodb YES
have_isam YES
have_raid NO
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
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_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
language /usr/share/mysql/english/
large_files_support ON
local_infile ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings OFF
long_query_time 10
low_priority_updates OFF
lower_case_table_names OFF
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 100
max_connect_errors 10
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 8388608
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 1024
pid_file /var/lib/mysql/www12.hostpc.com.pid
log_error
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_size 0
query_cache_type ON
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 0
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /var/lib/mysql/mysql.sock
sort_buffer_size 2097144
sql_mode 0
table_cache 64
table_type MYISAM
thread_cache_size 0
thread_stack 126976
tx_isolation REPEATABLE-READ
timezone EDT
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
version 4.0.16-standard
wait_timeout 28800
7). your mysql extended-status output which shows entire server's mysql activity status (preferably with a day or 2 worth of mysql uptime for better indication of real activity) either:
Variable_name Value
Aborted_clients 0
Aborted_connects 0
Bytes_received 2252189
Bytes_sent 94110493
Com_admin_commands 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 654
Com_change_master 0
Com_check 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_delete 46
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 149
Com_insert_select 2
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 27
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 5656
Com_set_option 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 0
Com_show_databases 0
Com_show_fields 0
Com_show_grants 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_innodb_status 0
Com_show_tables 0
Com_show_variables 1
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 898
Connections 648
Created_tmp_disk_tables 7
Created_tmp_tables 331
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 23
Handler_read_first 823
Handler_read_key 965117
Handler_read_next 2288444
Handler_read_prev 4409
Handler_read_rnd 62750
Handler_read_rnd_next 1059131
Handler_rollback 0
Handler_update 17013
Handler_write 10360
Key_blocks_used 7793
Key_read_requests 2183927
Key_reads 9658
Key_write_requests 329
Key_writes 304
Max_used_connections 13
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 64
Open_files 85
Open_streams 0
Opened_tables 244
Questions 8082
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 1
Select_full_range_join 0
Select_range 1349
Select_range_check 0
Select_scan 1267
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 3
Sort_merge_passes 0
Sort_range 577
Sort_rows 87353
Sort_scan 567
Table_locks_immediate 11112
Table_locks_waited 50
Threads_cached 0
Threads_created 647
Threads_connected 2
Threads_running 2
Uptime 701
8). is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
No, it's a shared hosting environment. There are other _light_ php/mysql users, but nothing that is drawing this server load other than this forum.
9). how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
We usually average about about 125 users online CONCURRENTLY, if I had to guess. The site is for fans of the Cincinnati Reds baseball teams and during the team's games, we may get as many as 200 concurrently online. However, when it's not during a game, or some other major event like the team trading a player, the number of concurrent users online is generally about 100 or less. The record number of concurrent users is 408 but that is definitely the exception rather than the rule. As far as the number of total users, if I'm using the vBulletin statistics section correctly, the user activity stat shows an average of about 500 users per day for the past month. The highest day was 652 users. We have no limit on the amount of concurrent users. The cookie timeout is 60 minutes.
10). create a file named phpinfo.php and place this code in it and post the url/link to it from your web site
This can be found at: http://www12.hostpc.com/info.php
11). 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 :
Timeout 300
KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 20
StartServers 10
MaxClients 150
MaxRequestsPerChild 1000
12). what version of vB are you running ?
vBulletin 3.0.7
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 exist.
# find / -size +2000000k
[root@www12 log]#
From previous conversation, I'm sure my host is willing to make changes if it will stop the problems we are experiencing. I have yet to hear what changes we might try to make to solve the problem, though. If something could be proposed, I'll gladly run it by my host and we can go from there.
#If you have any other info about this subject , Please add it free.# |
