MySQL Crashed > Graceful/Forcefull Reboot Failed > Memory shot up and SLM kicked in
40-60 people active at a time on an 1800 second timeout. Most ever 114 (Christmas eve), Threads: 9,230, Posts: 133,413, Members: 411, Active Members (30 days): 267
I range between 1200-1900 posts a day.
I have optimized based on suggestions from here, and am having zero performance problems.
WHM 11.15.0 cPanel 11.17.0-R19434
CENTOS Enterprise 4.5 i686 on virtuozzo - WHM X v3.1.0
Apache/2.2.6 (Unix) mod_ssl/2.2.6 OpenSSL/0.9.7a mod_auth_passthrough/2.1 mod_bwlimited/1.4 PHP/5.2.4
MySQL: 5.0.45
Ok, here is my series of events.
1. MySQL crashed at 4:53, RAM usage was under 400MB dedicated to apps, under 500mb committed, according to Munin (1152MB SLM VPS).
2. WHM could be accessed and navigated normally, including apache status, vBulletin was failing with DB error "Can't connect to local MySQL server through socket"
3. I attempted a graceful, then after about five or more minutes a forceful reboot (via WHM) - 5:30
4. Processes and RAM quickly started rising ~5:30-5:35
5. Ram apparently exceeded VPS limits, SLM kicked in and Munin stopped recording data.
6. I restarted the VPS via Viruozzo control panel.At the time of the Virtuozzo restart, I had putty open, and it received a "Server Unexpectedly closed network connection". On the screen at that time (and still), was:
Load Average .39, .78, .76
Tasks 111 total, 1 running, 109 sleeping, 0 stopped, 1 zombie
Cpu(s) 0.0% us, .2% sy, 0.0% ni, 92.9% id, 6.9% wa
Mem: 1179648k total, 365900k used, 813748k free.
This seems to be the trigger point, and again, according to Munin, less than half of my Ram was committed at this time, and my server was at about the lowest usage it sees in a 24 hour period.
root@server mysql]# grep ERROR server.mydomain.com.err
080211 4:50:02 [ERROR] Can't create thread to kill server
Apparently SLM started throwing errors like this at some point, but there are no time stamps:
kill_signal(9914.3): task 9fa418c0, thg 858ae980, sig 1
kill_signal(9914.3): task 9711b980, thg 858ae980, sig 1
kill_signal(9914.3): task 60dcb940, thg 858ae980, sig 1
kill_signal(9914.3): task b9364cc0, thg 858ae980, sig 1
kill_signal(9914.3): task 66a95940, thg 858ae980, sig 1
kill_signal(9914.3): task b3adcd00, thg 858ae980, sig 1
kill_signal(9914.3): task b17d7900, thg 858ae980, sig 1
kill_signal(9914.3): task b58a12a0, thg 858ae980, sig 1
kill_signal(9914.3): task 7c5dd2e0, thg 858ae980, sig 1
kill_signal(9914.3): task 347c2100, thg 858ae980, sig 1
kill_signal(9914.3): task 33d94c80, thg 858ae980, sig 1
kill_signal(9914.3): task adf66100, thg 858ae980, sig 1
kill_signal(9914.3): task 5522d2a0, thg 858ae980, sig 1
kill_signal(9914.3): task bcd01320, thg 858ae980, sig 1
kill_signal(9914.3): task 8abe20c0, thg 858ae980, sig 1
kill_signal(9914.3): task ba6c6c80, thg 858ae980, sig 1
kill_signal(9914.3): task 764200c0, thg 858ae980, sig 1
kill_signal(9914.3): task 739546e0, thg 858ae980, sig 1
kill_signal(9914.3): selected 1, signalled 1, queued 1, seq 8, exc 0 2 red 40652 1624
kill_signal(9914.3): selecting to kill, queued 0, seq 9, exc 0 562 goal 0 42290...
kill_signal(9914.3): selected 1, signalled 1, queued 1, seq 9, exc 0 42290 red 40652 1624
kill_signal(9914.3): task a99e4080, thg 858ae980, sig 2
kill_signal(9914.3): task 9ea92080, thg 858ae980, sig 2
kill_signal(9914.3): task 60dcad00, thg 858ae980, sig 2
kill_signal(9914.3): task 3a55f320, thg 858ae980, sig 2
kill_signal(9914.3): task 541586e0, thg 858ae980, sig 2
kill_signal(9914.3): task 23054d40, thg 858ae980, sig 2
kill_signal(9914.3): task 6041f980, thg 858ae980, sig 2
Here are two munin graphs, that show memory and processes seeming normal up until around 5:30 am (and one more graph showing apache processes that would indicate the server was at about it's lowest usage for the day):
http://www.broncos07.com/downloads/server_com-memory-day.png
http://www.broncos07.com/downloads/server_com-processes-day.png
http://www.broncos07.com/downloads/server_com-apache_accesses-day.png
What I am trying to figure out are three things:
1. Based on the munin graphs (and the fact that apache and WHM were working normally 30 minutes after the MySQL crash), is it safe to assume that MySQL did not crash at 4:50 am due to SLM kicking in because my VPS exceeded it's 1152MB of memory?
2. That for some reason the gracefult/forceful reboots via WHM failed and the processes shot up (again, shown on munin graphs around 5:30, before munin stopped tracking stats) and at that point the VPS exceeded its RAM limits and SLM kicked in.
3. What the heck is going on?
The stats on the base server it is hosted on are below, as sent to me by the host. In summary, there are 29 other VPS's on the machine, of those, I have no idea how many other total sites. My VPS can use up to 4 of the 8 cores (400%) at any given time and my CPULimit is 14,651 (which I don't fully understand):
Dell PowerEdge 2900 with Dual Quad-Core Intel X5355 (8 x 2.6Ghz) CPU's, 32 GB of RAM, 2 SA-SCSI disks @ 15k RPM dedicated on a single controller for the OS, and 8 more of the same Seagate disks attached to a secondary controller for the internal RAID-10 data array. Each node also has dual gbit network links and remote DRAC interfaces.
The node itself is running CentOS 32-bit, but that makes no difference to the VPS's, except that they are all 32-bit OS's.
To calculate the amount of CPU, disk and memory allocation you have, login to your VZ PP. You'll see the SLM memory variables listed there, as well as the disk size (that is on the int. array). For the CPU, VZ uses 2 variables, the LIMIT and UNITS. The system can have a total of 800% of a Limit, which is 100% for every single core, so there's 8 cores. The total # of UNITS available on the machine is approx 1063412.
The # of VE's on each node can vary from 20-60, based on the config of each VE. Since you have a higher end package, your grouped amongst the same class as yourself, so your node has only about 29 VE's running on it. Even with 29 VE's running, if you look at the memory output I gave you in the ticket update, you'll see theres well over 13 GB of RAM still free, and the system is not even thinking about touching any swap space. As you are aware we really have no idea how many domains are hosted on each VPS.
2.
Apache/2.2.6
PHP/5.2.4
MySQL: 5.0.45
3. 3.6.8 (patch 2)
4. No
5. MySQL was installed as part of Cpanel/WHM install.
6. Top Stats:
top - 17:52:45 up 2 days, 12:05, 3 users, load average: 0.16, 0.26, 0.26
Tasks: 84 total, 1 running, 83 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 0.2% sy, 0.8% ni, 95.5% id, 3.5% wa, 0.0% hi, 0.0% si
Mem: 1310720k total, 283712k used, 1027008k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
30144 nobody 19 4 35856 15m 8552 S 6 1.2 0:01.35 httpd
17938 nobody 20 4 36780 15m 7792 S 3 1.2 0:01.50 httpd
28563 mysql 22 6 150m 62m 4052 S 1 4.9 6:40.09 mysqld
1 root 15 0 1628 604 524 S 0 0.0 0:00.31 init
23960 root 16 0 1532 528 440 S 0 0.0 0:00.48 syslogd
23981 named 17 0 106m 3240 1860 S 0 0.2 0:01.77 named
24018 root 15 0 1456 368 312 S 0 0.0 0:00.00 courierlogger
24020 root 16 0 1804 592 484 S 0 0.0 0:00.00 authdaemond
24070 root 15 0 1804 356 236 S 0 0.0 0:00.00 authdaemond
24072 root 16 0 1804 356 236 S 0 0.0 0:00.02 authdaemond
24073 root 16 0 1804 356 236 S 0 0.0 0:00.01 authdaemond
24075 root 16 0 1804 356 236 S 0 0.0 0:00.01 authdaemond
24076 root 16 0 1804 356 236 S 0 0.0 0:00.01 authdaemond
1672 root 16 0 4028 1140 844 S 0 0.1 0:00.68 sshd
1696 root 18 0 2084 780 652 S 0 0.1 0:00.00 xinetd
1823 root 16 0 5672 4044 1216 S 0 0.3 0:02.08 chkservd
1842 root 22 0 1460 304 252 S 0 0.0 0:00.00 courierlogger
1843 root 18 0 1568 484 412 S 0 0.0 0:00.00 couriertcpd
1854 root 19 0 1460 304 252 S 0 0.0 0:00.00 courierlogger
1855 root 18 0 1568 484 412 S 0 0.0 0:00.00 couriertcpd
1861 root 15 0 1460 372 312 S 0 0.0 0:00.09 courierlogger
1872 root 15 0 1568 512 440 S 0 0.0 0:00.07 couriertcpd
1879 root 16 0 1460 372 312 S 0 0.0 0:00.00 courierlogger
1880 root 16 0 1568 512 440 S 0 0.0 0:00.01 couriertcpd
1978 mailnull 16 0 7424 1176 776 S 0 0.1 0:00.00 exim
2001 mailnull 15 0 7428 1196 788 S 0 0.1 0:00.01 exim
2017 mailnull 18 0 7416 1144 752 S 0 0.1 0:00.00 exim
2032 root 15 0 3528 1492 640 S 0 0.1 0:01.72 antirelayd
3298 root 20 4 35112 10m 4476 S 0 0.8 0:18.55 httpd
3302 root 24 8 27524 24m 1936 S 0 1.9 0:02.19 spamd
3369 root 15 0 2484 916 524 S 0 0.1 0:01.26 crond
3455 root 16 0 6092 4508 1308 S 0 0.3 0:02.16 munin-node
3544 root 23 8 29892 27m 2040 S 0 2.1 0:01.05 spamd
3553 root 24 8 27780 24m 1480 S 0 1.9 0:00.23 spamd
3745 mailnull 27 12 7340 4692 1604 S 0 0.4 0:00.98 eximstats
3824 root 16 0 9496 6352 1084 S 0 0.5 0:04.45 cphulkd.pl
3879 root 17 0 13724 7860 360 S 0 0.6 0:00.00 cpdavd
3917 root 15 0 5892 4188 1316 S 0 0.3 0:00.12 cpbandwd
3919 root 34 19 12928 10m 1912 S 0 0.8 0:15.37 cpanellogd
3961 nobody 18 0 3784 1736 664 S 0 0.1 0:00.00 entropychat
5185 mailman 24 8 10292 4988 904 S 0 0.4 0:00.01 mailmanctl
5191 mailman 24 8 10080 6656 2580 S 0 0.5 0:14.72 python2.4
5192 mailman 24 8 10080 6664 2580 S 0 0.5 0:15.56 python2.4
5193 mailman 24 8 10056 6664 2580 S 0 0.5 0:14.72 python2.4
5194 mailman 24 8 10116 6656 2580 S 0 0.5 0:14.71 python2.4
5195 mailman 24 8 10064 6680 2580 S 0 0.5 0:14.88 python2.4
5196 mailman 24 8 10080 6724 2580 S 0 0.5 0:15.62 python2.4
5197 mailman 24 8 10080 6652 2580 S 0 0.5 0:15.30 python2.4
5198 mailman 24 8 10080 6652 2580 S 0 0.5 0:00.13 python2.4
7. my.cnf
[mysqld]
safe-show-database
old_passwords
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 2500
thread_cache_size = 128
wait_timeout = 45
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 16
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
old-passwords = 1
log-slow-queries
[mysqld_safe]
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
8. mysqladmin -u root -p var ext stat ver proc
+---------------------------------+----------------------------------------+
Variable_name Value
+---------------------------------+----------------------------------------+
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 75
basedir /
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
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
completion_type 0
concurrent_insert 1
connect_timeout 10
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
div_precision_increment 4
engine_condition_pushdown OFF
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 YES
have_bdb NO
have_blackhole_engine YES
have_compress YES
have_crypt YES
have_csv YES
have_dynamic_loading YES
have_example_engine YES
have_federated_engine YES
have_geometry YES
have_innodb DISABLED
have_isam NO
have_merge_engine YES
have_ndbcluster DISABLED
have_openssl NO
have_ssl NO
have_query_cache YES
have_raid NO
have_rtree_keys YES
have_symlink YES
hostname server.tnedator.com
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_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
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_rollback_on_timeout OFF
innodb_support_xa ON
innodb_sync_spin_loops 20
innodb_table_locks ON
innodb_thread_concurrency 8
innodb_thread_sleep_delay 10000
interactive_timeout 28800
join_buffer_size 1044480
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
large_page_size 0
large_pages OFF
lc_time_names en_US
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_bin OFF
log_bin_trust_function_creators OFF
log_error
log_queries_not_using_indexes OFF
log_slave_updates OFF
log_slow_queries ON
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 67107840
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 500
max_delayed_threads 20
max_error_count 64
max_heap_table_size 67108864
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 4294967295
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 2147483647
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 67108864
myisam_stats_method nulls_unequal
ndb_autoincrement_prefetch_sz 32
ndb_force_send ON
ndb_use_exact_count ON
ndb_use_transactions ON
ndb_cache_check_time 0
ndb_connectstring
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old_passwords ON
open_files_limit 5510
optimizer_prune_level 1
optimizer_search_depth 62
pid_file /var/lib/mysql/server.tnedator.com.pid
port 3306
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
query_alloc_block_size 65536
query_cache_limit 4194304
query_cache_min_res_unit 4096
query_cache_size 67108864
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 262144
range_alloc_block_size 2048
read_buffer_size 1044480
read_only OFF
read_rnd_buffer_size 782336
relay_log_purge ON
relay_log_space_limit 0
rpl_recovery_rank 0
secure_auth OFF
secure_file_priv
server_id 0
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slave_compressed_protocol OFF
slave_load_tmpdir /tmp/
slave_net_timeout 3600
slave_skip_errors OFF
slave_transaction_retries 10
slow_launch_time 2
socket /var/lib/mysql/mysql.sock
sort_buffer_size 2097144
sql_big_selects ON
sql_mode
sql_notes ON
sql_warnings OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine MyISAM
sync_binlog 0
sync_frm ON
system_time_zone CST
table_cache 2500
table_lock_wait_timeout 50
table_type MyISAM
thread_cache_size 128
thread_stack 196608
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmp_table_size 67108864
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.45-community-log
version_comment MySQL Community Edition (GPL)
version_compile_machine i686
version_compile_os pc-linux-gnu
wait_timeout 45
+---------------------------------+----------------------------------------+
+-----------------------------------+------------+
Variable_name Value
+-----------------------------------+------------+
Aborted_clients 952
Aborted_connects 1126
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 175653143
Bytes_sent 1978459768
Com_admin_commands 2
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_call_procedure 0
Com_change_db 39091
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 47
Com_create_user 0
Com_dealloc_sql 0
Com_delete 4926
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 47
Com_drop_user 0
Com_execute_sql 0
Com_flush 10
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 16352
Com_insert_select 47
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 719
Com_optimize 1
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 4326
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 220584
Com_set_option 1642
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 743
Com_show_databases 7
Com_show_errors 0
Com_show_fields 846
Com_show_grants 25
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 283
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 7
Com_show_storage_engines 0
Com_show_tables 20
Com_show_triggers 747
Com_show_variables 10
Com_show_warnings 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_stmt_prepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 47
Com_unlock_tables 722
Com_update 61950
Com_update_multi 79
Com_xa_commit 0
Com_xa_end 0
Com_xa_prepare 0
Com_xa_recover 0
Com_xa_rollback 0
Com_xa_start 0
Compression OFF
Connections 41603
Created_tmp_disk_tables 2332
Created_tmp_files 20
Created_tmp_tables 10286
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 8165
Handler_discover 0
Handler_prepare 0
Handler_read_first 40940
Handler_read_key 4856940
Handler_read_next 11155919
Handler_read_prev 134475
Handler_read_rnd 1080325
Handler_read_rnd_next 6719275
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 98676
Handler_write 449323
Innodb_buffer_pool_pages_data 0
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 0
Innodb_buffer_pool_pages_free 0
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 0
Innodb_buffer_pool_pages_total 0
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead_seq 0
Innodb_buffer_pool_read_requests 0
Innodb_buffer_pool_reads 0
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 0
Innodb_data_fsyncs 0
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 0
Innodb_data_reads 0
Innodb_data_writes 0
Innodb_data_written 0
Innodb_dblwr_pages_written 0
Innodb_dblwr_writes 0
Innodb_log_waits 0
Innodb_log_write_requests 0
Innodb_log_writes 0
Innodb_os_log_fsyncs 0
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 0
Innodb_page_size 0
Innodb_pages_created 0
Innodb_pages_read 0
Innodb_pages_written 0
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 0
Innodb_row_lock_time_avg 0
Innodb_row_lock_time_max 0
Innodb_row_lock_waits 0
Innodb_rows_deleted 0
Innodb_rows_inserted 0
Innodb_rows_read 0
Innodb_rows_updated 0
Key_blocks_not_flushed 0
Key_blocks_unused 37852
Key_blocks_used 20138
Key_read_requests 17089218
Key_reads 21569
Key_write_requests 121509
Key_writes 89491
Last_query_cost 0.000000
Max_used_connections 18
Ndb_cluster_node_id 0
Ndb_config_from_host
Ndb_config_from_port 0
Ndb_number_of_data_nodes 0
Not_flushed_delayed_rows 0
Open_files 945
Open_streams 0
Open_tables 600
Opened_tables 810
Prepared_stmt_count 0
Qcache_free_blocks 1499
Qcache_free_memory 53099160
Qcache_hits 182317
Qcache_inserts 219342
Qcache_lowmem_prunes 0
Qcache_not_cached 637
Qcache_queries_in_cache 4579
Qcache_total_blocks 10921
Questions 576373
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 54128
Select_range_check 0
Select_scan 37654
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 5
Sort_merge_passes 10
Sort_range 51132
Sort_rows 4282490
Sort_scan 19313
Table_locks_immediate 594027
Table_locks_waited 446
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 15
Threads_connected 3
Threads_created 18
Threads_running 1
Uptime 84326
Uptime_since_flush_status 84326
+-----------------------------------+------------+
Uptime: 84326 Threads: 3 Questions: 576374 Slow queries: 5 Opens: 810 Flush tables: 1 Open tables: 600 Queries per second avg: 6.835
mysqladmin Ver 8.41 Distrib 5.0.45, for pc-linux-gnu on i686
(C) 2000-2006 MySQL 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 5.0.45-community-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 23 hours 25 min 26 sec
Threads: 3 Questions: 576374 Slow queries: 5 Opens: 810 Flush tables: 1 Open tables: 600 Queries per second avg: 6.835
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
Id User Host db Command Time State Info
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
41591 eximstats localhost eximstats Sleep 16
41594 cphulkd localhost cphulkd Sleep 12
41602 root localhost Query 0 show processlist
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
9. It's a VPS. My VPS (1152MB SLM RAM) only runs vBulletin/vBadvanced
10. Range between 40-60 active users at most times, sometimes a little higher. Max users to date was 114. 1800 second timeout.
Using apachetop to get a semi-live look at apache requests, using a 180 second window, I typically have 25-40 people making forum requests during any 3 minute window.
11. http://www.tnedator.com/phpinfo.php
12. httpd.conf
You might notice with the phpinfo.php, but I have eaccelerator installed. I know that the recommendation is for Xcache. My VPS provider claims that because I am using the WHMCpanel Apache, I should not add on Xcache as it is incompatible, or could cause problems with WHM/EasyApache. I don't know enough about these things to know how much better Xcache is and whether it outweighs and config issues that might have to be manually fixed.
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 1
MinSpareServers 10
MaxSpareServers 15
MaxClients 150
StartServers 5
MaxRequestsPerChild 0
13. None
14.
uname -a
Linux server.tnedator.com 2.6.9-023stab044.11-enterprise #1 SMP Sun Sep 30 12:15:39 MSD 2007 i686 i686 i386 GNU/Linux
ulimit -aH
core file size (blocks, -c) unlimited
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) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 270336
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X5355 @ 2.66GHz
stepping : 11
cpu MHz : 1329.998
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 0
cpu cores : 4
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
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 pbe nx lm constant_tsc pni monitor ds_cpl est tm2 xtpr
bogomips : 5323.08
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X5355 @ 2.66GHz
stepping : 11
cpu MHz : 1329.998
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 1
cpu cores : 4
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
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 pbe nx lm constant_tsc pni monitor ds_cpl est tm2 xtpr
bogomips : 5320.04
processor : 2
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X5355 @ 2.66GHz
stepping : 11
cpu MHz : 1329.998
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 2
cpu cores : 4
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
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 pbe nx lm constant_tsc pni monitor ds_cpl est tm2 xtpr
bogomips : 5319.99
processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X5355 @ 2.66GHz
stepping : 11
cpu MHz : 1329.998
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 3
cpu cores : 4
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
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 pbe nx lm constant_tsc pni monitor ds_cpl est tm2 xtpr
bogomips : 5319.98
processor : 4
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X5355 @ 2.66GHz
stepping : 11
cpu MHz : 1329.998
cache size : 4096 KB
physical id : 1
siblings : 4
core id : 4
cpu cores : 4
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
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 pbe nx lm constant_tsc pni monitor ds_cpl est tm2 xtpr
bogomips : 5293.29
processor : 5
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X5355 @ 2.66GHz
stepping : 11
cpu MHz : 1329.998
cache size : 4096 KB
physical id : 1
siblings : 4
core id : 5
cpu cores : 4
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
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 pbe nx lm constant_tsc pni monitor ds_cpl est tm2 xtpr
bogomips : 5320.06
processor : 6
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X5355 @ 2.66GHz
stepping : 11
cpu MHz : 1329.998
cache size : 4096 KB
physical id : 1
siblings : 4
core id : 6
cpu cores : 4
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
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 pbe nx lm constant_tsc pni monitor ds_cpl est tm2 xtpr
bogomips : 5320.02
processor : 7
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU X5355 @ 2.66GHz
stepping : 11
cpu MHz : 1329.998
cache size : 4096 KB
physical id : 1
siblings : 4
core id : 7
cpu cores : 4
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
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 pbe nx lm constant_tsc pni monitor ds_cpl est tm2 xtpr
bogomips : 5320.04
Shared module or ISAPI (apache 1.3.x) is what you want and is what you have ATM. Upgrading apache wouldn't hurt
1. Upgrade MySQL 5.0.45 or 5.0.51 if possible and then upgrade PHP to 5.2.5 or if you have problems install PHP 4.4.8 (in either case install as ISAPI module and NOT CGI)
2. Edit mysql server's /etc/my.cnf or c:my.ini for windows and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards. Make sure to restart mysql server everytime you make changes to your my.cnf for the changes to take effect.
If mysql doesn't restart properly after my.cnf changes and you're on VPS server, make sure skip-innodb entry is removed or commented out from below my.cnf
[mysqld]
safe-show-database
old_passwords
back_log = 50
skip-innodb
max_connections = 300
key_buffer_size = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 2000
thread_cache_size = 128
wait_timeout = 15
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 16
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 3M
query_cache_size = 48M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 16
[mysqld_safe]
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
If you get mysql server gone away error messages, then keep increasing wait_timeout value in my.cnf by 60 second increments, then restart mysql after my.cnf changes for it to take effect. Test for a few days and see if you get less or eliminate that error message. If it still occurs, then keep repeating the 60 second increment until the message goes away. Each vB forum and server will have different optimal wait_timeout values depending on your vB forum traffic patterns and server hardware specifications.
3. Xcache works fine with WHM/Cpanel been using it myself for a year or two now. Remove Eaccelerator or APC Cache if they're installed (check phpinfo.php url of yours to see) and instead install Xcache v1.2.1 final release or Xcache v1.2.2 http://xcache.lighttpd.net/wiki/Release-1.2.2 which seems to be a bit faster than APC Cache - http://www.vbulletin.com/forum/showthread.php?t=213267. Xcache site http://xcache.lighttpd.net/, documentation http://trac.lighttpd.net/trac/wiki/Docs and forums http://forum.lighttpd.net/forum/4
4. Upgrade to vB 3.0.17 http://www.vbulletin.com/forum/showthread.php?t=209720 if you're on vB 3.0.xx or upgrade to vB 3.5.8 http://www.vbulletin.com/forum/showthread.php?t=221903 if you're on vB 3.5.x. Upgrade to vB 3.6.8 PL2 http://www.vbulletin.com/forum/showthread.php?t=247739 if you're on vB 3.6.x
5. 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
6. Check phpinfo.php url to see if you have mod_gzip (Apache 1.3.x) or mod_deflate (Apache 2.x) loaded/installed (on phpinfo.php url page in browser you can do CTRL+F to bring up find prompt and type in mod_gzip or mod_deflate to quickly see). If you have either mod_gzip or mod_deflate loaded, then ensure vB admincp gzip compression is disabled since it's the same thing as mod_gzip or mod_deflate and double compression will just increase cpu loads. If you don't have mod_gzip or mod_deflate installed, then DO NOT set vB admincp gzip compression to a level higher than 1. Higher than 1, will only increase cpu loads unncessarily.
7. Edit httpd.conf values to following and restart apache
KeepAlive: On
MaxKeepAliveRequests: 100
KeepAliveTimeout: 1
MinSpareServers: 10
MaxSpareServers: 15
StartServers: 10
MaxClients: 150
MaxRequestsPerChild: 1000
Eva, thanks again for the optimization advice.
I do have a question that I was confused about last time I optimized as well.
I couldn't really find any info on this "in either case install as ISAPI module and NOT CGI". I'm using the default PHP that came with WHM/Cpanel, and then if I remember correctly, I upgraded it via easyapache to the current level (5.2.4). I never saw any options to install as ISAPI vs. CGI.
Can you give me a little more info on how to make sure I install it as ISAPI?
Also, I am on apache 2.2.6, and thought about upgrading to 2.2.8. Do you have any thoughts on whether 2.2.x is the best place to be, or if I should downgrade to 2.x or 1.3.x?
Thanks
#If you have any other info about this subject , Please add it free.# |
