Server tuneup request March 19th, 2010
MySQL Error : MySQL server has gone away Error Number : 2006
and
PHP Fatal error: Maximum execution time of 90 seconds exceeded in ... (mostly search)
1. Dedicated or shared virtual server?
Dedicated
2. Server specs:
CPU: dual 3.2 GHz Intel Xeons
RAM: 4GB
HD: 73GB SCSI x2 (RAID 1)
OS: Windows Server 2k3
Web: IIS 6.0 (isapi mode)
W3WP: 4 processes recycled every 4 hours or every 10,000 requests
PHP: 5.2.0 (from php.net, not from Zend)
MySQL: 5.0.26 nt-community
vB: 3.6.0
3. Do you have any innodb type databases/tables on your server?
no innodb
4. How was mysql was compiled/installed
Download via MySQL AB and manually installed
5. your top stats
Posts: ~2.1 Million
Threads: ~115,000
Data Usage 2.46 GB
Index Usage 929.31 MB
6. your mysql configuration variables
[mysqld]
port=3306
basedir="C:Mysqlxx"
datadir="C:MysqlxxData"
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 3M
table_cache = 2000
thread_cache_size = 384
wait_timeout = 90
connect_timeout = 90
tmp_table_size = 256M
max_heap_table_size = 128M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 6M
query_cache_size = 80M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
query_cache_min_res_unit = 4096
preload_buffer_size = 32768
default-storage-engine = MyISAM
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION"
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
7. MySQL stats from ssh telnet as root user type:
C:>mysqladmin -u root -p var ext stat ver
Enter ********
+---------------------------------+---------------------------------------------+
Variable_name Value
+---------------------------------+---------------------------------------------+
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 75
basedir C:Mysqlxx
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 C:Mysqlxxsharecharsets
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 C:MysqlxxData
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 1800
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 NO
have_compress YES
have_crypt NO
have_csv NO
have_dynamic_loading YES
have_example_engine NO
have_federated_engine NO
have_geometry YES
have_innodb DISABLED
have_isam NO
have_ndbcluster NO
have_merge_engine YES
have_openssl DISABLED
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_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_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 268435456
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language C:Mysqlxxshareenglish
large_files_support ON
large_page_size 0
large_pages OFF
license GPL
local_infile ON
log OFF
log_bin OFF
log_bin_trust_function_creators OFF
log_error .xxxxxx-64668.err
log_queries_not_using_indexes OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 1
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 134216704
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_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
named_pipe OFF
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old_passwords OFF
open_files_limit 2048
optimizer_prune_level 1
optimizer_search_depth 62
pid_file C:MysqlxxDataxxxxxx-64668.pid
port 3306
preload_buffer_size 32768
prepared_stmt_count 0
protocol_version 10
query_alloc_block_size 131072
query_cache_limit 6291456
query_cache_min_res_unit 4096
query_cache_size 83886080
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 65536
range_alloc_block_size 2048
read_buffer_size 1044480
read_only OFF
read_rnd_buffer_size 520192
relay_log_purge ON
relay_log_space_limit 0
rpl_recovery_rank 0
secure_auth OFF
shared_memory OFF
shared_memory_base_name MYSQL
server_id 0
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slave_compressed_protocol OFF
slave_load_tmpdir C:WINDOWSTEMP
slave_net_timeout 3600
slave_skip_errors OFF
slave_transaction_retries 10
slow_launch_time 2
sort_buffer_size 3145720
sql_big_selects ON
sql_mode STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_E
NGINE_SUBSTITUTION
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 Central Standard Time
table_cache 769
table_lock_wait_timeout 50
table_type MyISAM
thread_cache_size 384
thread_stack 196608
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmp_table_size 268435456
tmpdir C:WINDOWSTEMP
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.24-community-nt
version_comment MySQL Community Edition (GPL)
version_compile_machine ia32
version_compile_os Win32
wait_timeout 20
+---------------------------------+---------------------------------------------+
+-----------------------------------+------------+
Variable_name Value
+-----------------------------------+------------+
Aborted_clients 3066
Aborted_connects 127
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 1980103184
Bytes_sent 1960339711
Com_admin_commands 0
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 1324055
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 3
Com_dealloc_sql 0
Com_delete 68585
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 3
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 568996
Com_insert_select 4256
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
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 294956
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 5310306
Com_set_option 1324202
Com_show_binlog_events 0
Com_show_binlogs 3
Com_show_charsets 60
Com_show_collations 60
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 6
Com_show_databases 0
Com_show_errors 0
Com_show_fields 417
Com_show_grants 13
Com_show_innodb_status 0
Com_show_keys 3
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 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 46
Com_show_triggers 0
Com_show_variables 182
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 0
Com_unlock_tables 0
Com_update 1176155
Com_update_multi 0
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 1324151
Created_tmp_disk_tables 4897
Created_tmp_files 2542
Created_tmp_tables 253102
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 754726
Handler_discover 0
Handler_prepare 0
Handler_read_first 1046284
Handler_read_key 317563496
Handler_read_next 1445630364
Handler_read_prev 13974956
Handler_read_rnd 40956515
Handler_read_rnd_next 3733110383
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 2122318
Handler_write 28335235
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 199318
Key_blocks_used 94083
Key_read_requests 1386204386
Key_reads 14398949
Key_write_requests 3902497
Key_writes 2583042
Last_query_cost 0.000000
Max_used_connections 184
Not_flushed_delayed_rows 0
Open_files 138
Open_streams 0
Open_tables 81
Opened_tables 35424
Qcache_free_blocks 5184
Qcache_free_memory 57010064
Qcache_hits 6238902
Qcache_inserts 5305215
Qcache_lowmem_prunes 166208
Qcache_not_cached 5864
Qcache_queries_in_cache 13317
Qcache_total_blocks 32183
Questions 17633750
Rpl_status NULL
Select_full_join 39597
Select_full_range_join 0
Select_range 1310802
Select_range_check 0
Select_scan 773339
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 95
Sort_merge_passes 1271
Sort_range 1104354
Sort_rows 135988015
Sort_scan 160664
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 14407218
Table_locks_waited 13834
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 180
Threads_connected 4
Threads_created 184
Threads_running 1
Uptime 666789
+-----------------------------------+------------+
Uptime: 666789 Threads: 3 Questions: 17633760 Slow queries: 95 Opens: 35424
Flush tables: 1 Open tables: 81 Queries per second avg: 26.446
mysqladmin Ver 8.41 Distrib 5.0.24, for Win32 on ia32
(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 5.0.24-community-nt
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 7 days 17 hours 13 min 9 sec
Threads: 3 Questions: 17633760 Slow queries: 95 Opens: 35424 Flush tables: 1
Open tables: 81 Queries per second avg: 26.446
C:>
8. is your vB the only thing on the server?
yes
9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
Avg: ~400 users
Max: ~1200
Cookie: 900 sec
10. Post a url/link to phpinfo from your web site
http://tinyurl.com/yzjnfo
11. if you run Apache
n/a
12. what version of vB are you running?
vB 3.6.0
13. check to see if any files hitting 2GB or 4GB max file size limits
no
14. Post output from these 3 commands
n/a
for mysql gone away it's due to wait_timeout value being too low... keep raising the value by 90 second increments in my.cnf and restarting mysql and until the error goes away
Try
1. Upgrade MySQL server to latest MySQL 5.x server = 5.0.27 http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs-5-0-27.html
2. Ensure PHP 5.2.0 is installed as ISAPI module
3. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards
[mysqld]
[mysqld]
port=3306
basedir="C:Mysqlxx"
datadir="C:MysqlxxData"
back_log = 75
skip-innodb
max_connections = 650
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 3M
table_cache = 2500
thread_cache_size = 512
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 384M
max_heap_table_size = 128M
max_allowed_packet = 80M
max_connect_errors = 10
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 80M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
query_cache_min_res_unit = 4096
preload_buffer_size = 32768
default-storage-engine = MyISAM
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION"
[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
4. Install Xcache which seems to be a bit faster than APC Cache - http://www.vbulletin.com/forum/showthread.php?t=213267 and remove APC and eaccelerator
5. Upgrade to vB 3.6.4 http://www.vbulletin.com/forum/showthread.php?t=209717 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
#If you have any other info about this subject , Please add it free.# |
