POLALA.COM
welcome to my space |
HOME  SFN Server Optimization Request |
| SFN Server Optimization Request | | Published by: smith 2009-01-09 |
| I run www.sternFannetwork.com (http://www.sternFannetwork.com) and we've completely outgrown our forums & our servers. I'm been running a heavily modified version of VB2.3 on 2 dual core Xeons. The entire site is VB based. Our traffic is just too much for the setup. I'm setting up an entire new site based on VB3.64 and upgrading our servers. We've added a 3rd server. it's a Quad core Xeon.
The site is still crashing all the time. I'd like to see what we can do to optimize things. I'm hoping we have a bad setting somewhere and you whiz kids will hook me up.
Keep in mind, we are setting things up for Vb3.64 but will be still running VB2.3 until the upgrade is complete.
Thanks in advance.
1. Dedicated
2. Server Specs:
Db2.sternfannetwork.com (DB server):
Dual proc Dual Core Xeon 3.0Ghz
8G memory
2x 73G 15K SCSI drives in Raid 1
MySQL - 5.0.33
Db1.sternfannetwork.com (slave DB server):
Dual Xeon 3.8 Ghz
4G memory
2x 73G 15K SCSI drives in RAID 1
MySQL - 5.0.33
Web.sternfannetwork.com:
Dual Xeon 3.8Ghz
4G Memory
2x 73G 15K SCSI drives in RAID 1
- Hide quoted text -
Apache 2.0.52
PHP 4.4.4
3. VB Version
VB 2.3 & VB3.64. i'm upgrading to 3.64 now but the old board will
be up for awhile as I rewrite the rest of the site.
4. We use Mysql 5. There are no innodb files.
5. Compiled from source with the following flags:
CC="gcc -m64" CXX="g++ -m64" CFLAGS="-L/usr/lib64 -L/lib64
-L/usr/local/lib64" CXXFLAGS="$CFLAGS" LDFLAGS="$CFLAGS" ./configure
--prefix=$pre/usr --sbindir=$pre/usr/sbin --libexecdir=$pre/usr/sbin
--infodir=$pre/usr/share/man --mandir=$pre/usr/share/info
--libdir=$pre/usr/lib64 --enable-shared --enable-static
--enable-thread-safe-client --enable-local-infile
--with-extra-charsets=all --with-gnu-ld --with-pthread
--with-unix-socket-path=/tmp
/mysql.sock --with-mysqld-user=mysql
--without-debug --with-big-tables --with-archive-storage-engine
--with-csv-storage-engine --with-blackhole-storage-engine
--with-federated-storage-engine --with-berkeley-db --without-extra-tools
--with-mysqlmanager=no --with-ndbcluster --without-geometry
5. MySQL Variables from Admin CP
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 100
basedir /usr/
bdb_cache_size 8388600
bdb_home /var/db/mysql/
bdb_log_buffer_size 1048576
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_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/db/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 YES
have_blackhole_engine YES
have_compress YES
have_crypt YES
have_csv YES
have_dynamic_loading YES
have_example_engine NO
have_federated_engine YES
have_geometry NO
have_innodb YES
have_isam NO
have_merge_engine YES
have_ndbcluster DISABLED
have_openssl NO
have_query_cache YES
have_raid NO
have_rtree_keys NO
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 ibdata1:10M:autoextend
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 524283904
key_buffer_size 524288000
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 ON
log_bin_trust_function_creators OFF
log_error
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 0
max_allowed_packet 67107840
max_binlog_cache_size 18446744073709551615
max_binlog_size 1073741824
max_connect_errors 10
max_connections 1000
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_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 18446744073709551615
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 9223372036854775807
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 268435456
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
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old_passwords OFF
open_files_limit 5106
optimizer_prune_level 1
optimizer_search_depth 62
pid_file /var/db/mysql/sfn-db2.cavecreek.net.pid
port 3306
preload_buffer_size 536870912
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 33554432
query_cache_min_res_unit 4096
query_cache_size 536870912
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 2097152
range_alloc_block_size 2048
read_buffer_size 67104768
read_only OFF
read_rnd_buffer_size 520192
relay_log_purge ON
relay_log_space_limit 0
rpl_recovery_rank 0
secure_auth OFF
server_id 1
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 /tmp/mysql.sock
sort_buffer_size 268435448
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 MST
table_cache 2048
table_lock_wait_timeout 50
table_type MyISAM
thread_cache_size 512
thread_stack 524288
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmp_table_size 3145728000
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.33-log
version_bdb Sleepycat Software: Berkeley DB 4.1.24: (January 9,
2007)
version_comment Source distribution
version_compile_machine x86_64
version_compile_os unknown-linux-gnu
wait_timeout 1806. Top Stats
top - 10:13:18 up 6 days, 16:04, 1 user, load average: 2.05, 2.02,
3.23
Tasks: 78 total, 2 running, 76 sleeping, 0 stopped, 0 zombie
Cpu(s): 18.3% us, 7.8% sy, 0.0% ni, 71.5% id, 2.1% wa, 0.1% hi,
0.2% si
Mem: 8165724k total, 4606772k used, 3558952k free, 133932k buffers
Swap: 1020116k total, 10128k used, 1009988k free, 3210984k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8632 mysql 15 0 1685m 828m 5088 S 106 10.4 4512:47 mysqld
1 root 16 0 4752 80 48 S 0 0.0 0:19.38 init
2 root RT 0 0 0 0 S 0 0.0 0:00.38 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.01 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.50 migration/1
5 root 34 19 0 0 0 S 0 0.0 0:00.01 ksoftirqd/1
6 root RT 0 0 0 0 S 0 0.0 0:00.38 migration/2
7 root 34 19 0 0 0 S 0 0.0 0:00.01 ksoftirqd/2
8 root RT 0 0 0 0 S 0 0.0 0:00.39 migration/3
9 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/3
10 root 5 -10 0 0 0 S 0 0.0 0:00.01 events/0
11 root 5 -10 0 0 0 S 0 0.0 0:00.00 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.00 events/3
14 root 6 -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
56 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/0
CNS handbook 1992-1993::
File Format: PDF/Adobe Acrobat - View as HTMLENG MN 732 Combinatorial Optimization and Graph Algorithms account, you should fill out the CNS account request form, which is available in this
http://cns-web.bu.edu/study/CNSStudentHandbook2008-2009HOME |
57 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/1
58 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/2
59 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/3
60 root 15 0 0 0 0 S 0 0.0 0:00.00 khubd
90 root 15 -10 0 0 0 S 0 0.0 0:00.00 aio/0
89 root 15 0 0 0 0 S 0 0.0 1:06.93 kswapd0
91 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/1
92 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/2
93 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/3
237 root 23 0 0 0 0 S 0 0.0 0:00.00 kseriod
355 root 20 0 0 0 0 S 0 0.0 0:00.00 scsi_eh_0
356 root 15 0 0 0 0 S 0 0.0 0:00.00 ahd_dv_0
358 root 19 0 0 0 0 S 0 0.0 0:00.00 scsi_eh_1
359 root 15 0 0 0 0 S 0 0.0 0:00.00 ahd_dv_1
371 root 19 0 0 0 0 S 0 0.0 0:00.00 scsi_eh_2
397 root 15 0 0 0 0 S 0 0.0 0:06.57 kjournald
1662 root 6 -10 3604 4 0 S 0 0.0 0:00.00 udevd
1718 root 22 0 0 0 0 S 0 0.0 0:00.00
shpchpd_event
1974 root 6 -10 0 0 0 S 0 0.0 0:00.00 kauditd
2070 root 15 0 0 0 0 S 0 0.0 0:00.00 kjournald
2071 root 15 0 0 0 0 S 0 0.0 8:35.98 kjournald
2072 root 15 0 0 0 0 S 0 0.0 0:06.49 kjournald
2073 root 15 0 0 0 0 S 0 0.0 1:59.13 kjournald
7. my.cnf
[mysqld]
datadir = /var/db/mysql
safe-show-database
back_log = 100
key_buffer_size = 500M
max_connections = 1000
myisam_sort_buffer_size = 256M
join_buffer_size = 500M
read_buffer_size = 64M
sort_buffer_size = 256M
table_cache = 2048
thread_cache_size = 512
thread_stack=512K
thread_concurrency=16
preload_buffer_size=512M
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 3000M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 32M
query_cache_size = 512M
query_cache_type = 1
query_prealloc_size = 2M
default-storage-engine = MyISAM
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = mutt_sfn
binlog-do-db = sfn
server-id=1
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 64M
[myisamchk]
key_buffer = 400M
sort_buffer = 384M
read_buffer = 128M
write_buffer = 256M
8. MySQL stats from ssh telnet
+---------------------------------+-------------------------------------
-----------------------+
Variable_name Value
+---------------------------------+-------------------------------------
-----------------------+
- Show quoted text -
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 100
basedir /usr/
bdb_cache_size 8388600
bdb_home /var/db/mysql/
bdb_log_buffer_size 1048576
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_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/db/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 YES
have_blackhole_engine YES
have_compress YES
have_crypt YES
have_csv YES
have_dynamic_loading YES
have_example_engine NO
have_federated_engine YES
have_geometry NO
have_innodb YES
have_isam NO
have_merge_engine YES
have_ndbcluster DISABLED
have_openssl NO
have_query_cache YES
have_raid NO
have_rtree_keys NO
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 ibdata1:10M:autoextend
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 524283904
key_buffer_size 524288000
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 ON
log_bin_trust_function_creators OFF
log_error
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 0
max_allowed_packet 67107840
max_binlog_cache_size 18446744073709551615
max_binlog_size 1073741824
max_connect_errors 10
max_connections 1000
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_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 18446744073709551615
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 9223372036854775807
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 268435456
myisam_stats_method nulls_unequal
ndb_autoincrement_prefetch_sz 32
ndb_force_send ON
ndb_use_exact_count ON
ndb_use_transactions OFF
- Show quoted text -
ndb_cache_check_time 0
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old_passwords OFF
open_files_limit 5106
optimizer_prune_level 1
optimizer_search_depth 62
pid_file
/var/db/mysql/sfn-db2.cavecreek.net.pid
port 3306
preload_buffer_size 536870912
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 33554432
query_cache_min_res_unit 4096
query_cache_size 536870912
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 2097152
range_alloc_block_size 2048
read_buffer_size 67104768
read_only OFF
read_rnd_buffer_size 520192
relay_log_purge ON
relay_log_space_limit 0
rpl_recovery_rank 0
secure_auth OFF
server_id 1
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 /tmp/mysql.sock
sort_buffer_size 268435448
Honeywell Acronyms Q-Z::
SFN Smart Field Network SG System Gateway SGC Smart Gas Chromatograph URS User Request Service (part of PCNM Server) US Universal Station
http://hpsweb.honeywell.com/Cultures/en-US/AboutUs/Acronyms/Q-Z/default.htmHOME |
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 MST
table_cache 2048
table_lock_wait_timeout 50
table_type MyISAM
thread_cache_size 512
thread_stack 524288
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmp_table_size 3145728000
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.33-log
version_bdb Sleepycat Software: Berkeley DB
4.1.24: (January 9, 2007)
version_comment Source distribution
version_compile_machine x86_64
version_compile_os unknown-linux-gnu
wait_timeout 180
+---------------------------------+-------------------------------------
-----------------------+
+-----------------------------------+--------------+
Variable_name Value
+-----------------------------------+--------------+
Aborted_clients 398
Aborted_connects 11
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 8149710189
Bytes_sent 197898006301
Com_admin_commands 1
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 1609882
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_create_user 0
Com_dealloc_sql 0
Com_delete 151567
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 266054
Com_insert_select 1737
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 2
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 39831
Com_replace_select 35
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 0
Com_savepoint 0
Com_select 16481445
Com_set_option 191
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 186
Com_show_databases 0
Com_show_errors 0
Com_show_fields 186
Com_show_grants 0
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 0
Com_show_slave_hosts 1
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 2
Com_show_triggers 186
Com_show_variables 2
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 2
Com_update 9052675
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 1610745
Created_tmp_disk_tables 708091
Created_tmp_files 5
Created_tmp_tables 2620547
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 254758
Handler_discover 0
Handler_prepare 0
Handler_read_first 236487
Handler_read_key 45255327261
Handler_read_next 78132356997
Handler_read_prev 23030373
Handler_read_rnd 105019139
Handler_read_rnd_next 18843797258
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 44614224394
Handler_write 296868548
- Show quoted text -
Innodb_buffer_pool_pages_data 19
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 0
Innodb_buffer_pool_pages_free 493
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 0
Innodb_buffer_pool_pages_total 512
Innodb_buffer_pool_read_ahead_rnd 1
Innodb_buffer_pool_read_ahead_seq 0
Innodb_buffer_pool_read_requests 77
Innodb_buffer_pool_reads 12
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 0
Innodb_data_fsyncs 3
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 2494464
Innodb_data_reads 25
Innodb_data_writes 3
Innodb_data_written 1536
Innodb_dblwr_pages_written 0
Innodb_dblwr_writes 0
Innodb_log_waits 0
Innodb_log_write_requests 0
Innodb_log_writes 1
Innodb_os_log_fsyncs 3
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 512
Innodb_page_size 16384
Innodb_pages_created 0
Innodb_pages_read 19
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 177540
Key_blocks_used 242578
Key_read_requests 7770254905
Key_reads 243678
Key_write_requests 3305155
Key_writes 2560689
Last_query_cost 0.000000
Max_used_connections 772
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 1985
Open_streams 0
Open_tables 2048
Opened_tables 5865
Prepared_stmt_count 0
Qcache_free_blocks 51603
Qcache_free_memory 425340272
Qcache_hits 25908556
Qcache_inserts 15403332
Qcache_lowmem_prunes 0
Qcache_not_cached 1077936
Qcache_queries_in_cache 77549
Qcache_total_blocks 206934
Questions 55123251
Rpl_status NULL
Select_full_join 23
Select_full_range_join 0
Select_range 1404101
Select_range_check 0
Select_scan 4083332
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 960
Sort_merge_passes 0
Sort_range 1224712
Sort_rows 6383643193
Sort_scan 1837414
Table_locks_immediate 31947992
Table_locks_waited 1105691
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 494
Threads_connected 23
Threads_created 1281
Threads_running 6
Uptime 170472
+-----------------------------------+--------------+
Uptime: 170472 Threads: 21 Questions: 55123293 Slow queries: 960
Opens: 5865 Flush tables: 1 Open tables: 2048 Queries per second avg:
323.357
mysqladmin Ver 8.41 Distrib 5.0.33, for unknown-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 5.0.33-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 day 23 hours 21 min 12 sec
Threads: 21 Questions: 55123293 Slow queries: 960 Opens: 5865 Flush
tables: 1 Open tables: 2048 Queries per second avg: 323.3578. MySQL Status from Admin CP
Aborted_clients 12
Aborted_connects 0
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 2214
Bytes_sent 210101
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 1
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_create_user 0
Com_dealloc_sql 0
Com_delete 0
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 0
Com_insert_select 0
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 0
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 3
Com_set_option 0
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 0
Com_show_databases 0
Com_show_errors 0
Com_show_fields 0
Com_show_grants 0
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 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 0
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 1
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 16902
Created_tmp_disk_tables 0
Created_tmp_files 5
Created_tmp_tables 1
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 2
Handler_read_key 6
Handler_read_next 1
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 2
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 1
Handler_write 131
Innodb_buffer_pool_pages_data 19
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 0
Innodb_buffer_pool_pages_free 493
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 0
Innodb_buffer_pool_pages_total 512
Innodb_buffer_pool_read_ahead_rnd 1
Innodb_buffer_pool_read_ahead_seq 0
Innodb_buffer_pool_read_requests 77
Innodb_buffer_pool_reads 12
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 0
Innodb_data_fsyncs 3
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 2494464
Innodb_data_reads 25
Innodb_data_writes 3
Innodb_data_written 1536
Innodb_dblwr_pages_written 0
Innodb_dblwr_writes 0
Innodb_log_waits 0
Innodb_log_write_requests 0
Innodb_log_writes 1
Innodb_os_log_fsyncs 3
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 512
Innodb_page_size 16384
Innodb_pages_created 0
Innodb_pages_read 19
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 116463
Key_blocks_used 412422
Key_read_requests 67020455
Key_reads 2777459
Key_write_requests 15205
Key_writes 11355
Last_query_cost 0.000000
Max_used_connections 701
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 802
Open_streams 0
Open_tables 710
Opened_tables 0
Prepared_stmt_count 0
Qcache_free_blocks 605
Qcache_free_memory 532716976
Qcache_hits 223746
Qcache_inserts 120917
Qcache_lowmem_prunes 0
Qcache_not_cached 15952
Qcache_queries_in_cache 1019
Qcache_total_blocks 2789
Questions 482912
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 1
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
Table_locks_immediate 254386
Table_locks_waited 17413
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 426
Threads_connected 95
Threads_created 709
Threads_running 67
Uptime 3133]
9. is your vB the only thing on the server?
I also run Menalto Gallery but it's currently off.
10. average and max concurrent users on your vB forum ?
1000 ave, I don't know what the max is because we crash. the site is
gettign busier 7 busier. we see 1500 now but I'd say it's a safe gues
that we'd break 2000 if we didn't lock up.
cookie timeout : 900
11. PHP Info
http://www.sternfannetwork.com/forum/phpinfo.php
11.
KeepAlive = Off
MaxKeepAliveRequests = 100
KeepAliveTimeout = 15
MinSpareServers = 5
MaxSpareServers = 20
StartServers = 80
MaxClients = 768
Maxrequestsperchild = 0
11. Here is the mysqlreport output:
MySQL 5.0.33-log uptime 2 1:6:54 Thu Feb 15 11:59:54 2007
__ Key __________________________________________________ _______________
Buffer used 238.71M of 500.00M %Used: 47.74
Current 330.06M %Usage: 66.01
Write ratio 0.775
Read ratio 0.000
__ Questions __________________________________________________ _________
Total 57.47M 325.0/s
DMS 27.10M 153.3/s %Total: 47.15
QC Hits 27.02M 152.8/s 47.01
Com_ 1.68M 9.5/s 2.92
COM_QUIT 1.68M 9.5/s 2.92
+Unknown 163 0.0/s 0.00
Slow 984 0.0/s 0.00 %DMS: 0.00
DMS 27.10M 153.3/s 47.15
SELECT 17.18M 97.2/s 29.90 63.41
UPDATE 9.43M 53.4/s 16.42 34.82
INSERT 279.75k 1.6/s 0.49 1.03
DELETE 158.44k 0.9/s 0.28 0.58
REPLACE 41.44k 0.2/s 0.07 0.15
Com_ 1.68M 9.5/s 2.92
change_db 1.68M 9.5/s 2.92
set_option 193 0.0/s 0.00
show_create 186 0.0/s 0.00
__ SELECT and Sort __________________________________________________ ___
Scan 4.26M 24.1/s %SELECT: 24.77
Range 1.47M 8.3/s 8.53
Full join 24 0.0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 1.92M 10.8/s
Sort range 1.28M 7.2/s
Sort mrg pass 0 0/s
__ Query Cache __________________________________________________ _______
Memory usage 94.07M of 512.00M %Used: 18.37
Block Fragmnt 30.41%
Hits 27.02M 152.8/s
Inserts 16.06M 90.8/s
Insrt:Prune 16.06M:1 90.8/s
Hit:Insert 1.68:1
__ Table Locks __________________________________________________ _______
Waited 1.15M 6.5/s %Total: 3.35
Immediate 33.30M 188.4/s
__ Tables __________________________________________________ ____________
Open 2.05k of 2048 %Cache: 100.00
Opened 6.10k 0.0/s
__ Connections __________________________________________________ _______
Max used 772 of 1000 %Max: 77.20
Total 1.68M 9.5/s
__ Created Temp __________________________________________________ ______
Disk table 737.74k 4.2/s
Table 2.73M 15.5/s
File 5 0.0/s
__ Threads __________________________________________________ ___________
Running 5 of 22
Cached 493 of 512 %Hit: 99.92
Created 1.28k 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 418 0.0/s
Connects 15 0.0/s
__ Bytes __________________________________________________ _____________
Sent 206.07G 1.2M/s
Received 8.50G 48.1k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 304.00k of 8.00M %Used: 3.71
Read ratio 0.156
Pages
Free 493 %Total: 96.29
Data 19 3.71 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 77 0.0/s
From file 12 0.0/s 15.58
Ahead Rnd 1 0.0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s
__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 25 0.0/s
Writes 3 0.0/s
fsync 3 0.0/s
Pending
Reads 0
Writes 0
fysnc 0
Pages
Created 0 0/s
Read 19 0.0/s
Written 0 0/s
Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s
12. No large files
13.
Linux sfn-db2.cavecreek.net 2.6.9-42.ELsmp #1 SMP Tue Aug 15 10:35:26
BST 2006 x86_64 x86_64 x86_64 GNU/Linux
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) 71680
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 5160 @ 3.00GHz
stepping : 6
cpu MHz : 3000.141
cache size : 4096 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
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 syscall
nx lm pni monitor ds_cpl est tm2 cx16 xtpr
bogomips : 6006.63
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 5160 @ 3.00GHz
stepping : 6
cpu MHz : 3000.141
cache size : 4096 KB
physical id : 3
siblings : 2
core id : 6
cpu cores : 2
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 syscall
nx lm pni monitor ds_cpl est tm2 cx16 xtpr
bogomips : 5999.39
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:
processor : 2
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 5160 @ 3.00GHz
stepping : 6
cpu MHz : 3000.141
cache size : 4096 KB
physical id : 0
siblings : 2
core id : 1
cpu cores : 2
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 syscall
nx lm pni monitor ds_cpl est tm2 cx16 xtpr
bogomips : 5999.36
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:
processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 5160 @ 3.00GHz
stepping : 6
cpu MHz : 3000.141
cache size : 4096 KB
physical id : 3
siblings : 2
core id : 7
cpu cores : 2
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 syscall
nx lm pni monitor ds_cpl est tm2 cx16 xtpr
bogomips : 5999.41
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:
Good to hear it's improved somewhat.. :)
thanks
the db server gets too many connections and it locks up. I start getting DB connect errors. i'll save one the next time it happens
We made these adjustments & we're doing better today. there have been 1300 for most of the day without issues.
we installed and uninstalled XCache. it was causing the load on the webserver to spike over 40.
What is crashing exactly ? the db servers or web server ? Can you elaborate on the issues and provide a specific example or error messages ? So you're running mysql replication ? got the my.cnf and stats for slave server too ?
I'd upgrade to Apache 2.0.59 as well
Then try the following in this exact order. You can ignore any of the suggestions that you have already done.
1. For main db server, edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards
[mysqld]
datadir = /var/db/mysql
back_log = 150
skip-innodb
max_connections = 1024
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 4M
table_cache = 4000
thread_cache_size = 768
wait_timeout = 35
connect_timeout = 10
tmp_table_size = 768M
max_heap_table_size = 384M
max_allowed_packet = 128M
max_connect_errors = 10
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 16M
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
thread_concurrency=8
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = mutt_sfn
binlog-do-db = sfn
server-id=1
[mysqld_safe]
nice = -10
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 128M
[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M
2. On web server install Xcache which seems to be a bit faster than APC Cache - http://www.vbulletin.com/forum/showthread.php?t=213267
3. You may need to install same mysql version on web server, and recompile PHP with the --with-mysql=/path/to/mysql option to tell PHP to use the latest mysql 5.0.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.
4. 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
5. Edit httpd.conf value on web server from
KeepAlive = Off
MaxKeepAliveRequests = 100
KeepAliveTimeout = 15
MinSpareServers = 5
MaxSpareServers = 20
StartServers = 80
MaxClients = 768
Maxrequestsperchild = 0
to
KeepAlive = On or Off (try both of them and see which is better)
MaxKeepAliveRequests = 100
KeepAliveTimeout = 5
MinSpareServers = 15
MaxSpareServers = 20
StartServers = 40
MaxClients = 512
Maxrequestsperchild = 0
restart apache
 Where's The Advantage In Windows Genuine Advantage?
 Stocks Bounce After S&P Joins Bear Market
|
#If you have any other info about this subject , Please add it free.# | |
|
