This is my first post on this forum and unfortunately it's about a problem.
I'm running a medium VB forum on a dedicated server - CentOS Linux distro, with the following configuration:
CPU 4 X Intel Xeon 2.8GHz , 4048Mb memory, 300G hdd
No problems with the forums until a few days ago. I've updated from CentOS 4.4 to CentOS 4.5 and VB from 3.6.5 to 3.6.7 and now I have the following issues:
- After a few hours, the connection to the web server is very slow. I've done several tests and I can see lots of State:Locked threads (ONLY TO VB TABLES) in 'show processeslist' , some of them with more than 1000 seconds time. Also , the mysqlreport shows a high max_connections value (last time 185 from 275) and lots of mysqld linux processses. The only solution I've found: restart mysql
- MySQL is not restarted if running more than a few minutes. I've done a strace on the 'service mysqld stop' and the /etc/init.d/mysqld scripts sends a kill (without parameters -> kill -15) to the pid listed in /var/run/mysql/mysqld.pid (the pid value from mysqld.pid it's a thread pid , not the parent pid). After this kill nothing happens and mysqld stop will fail due to timeout (60 seconds) . I have to run killall -9 mysqld and than restart apache.
It is strange for me to have this problems and I'm sure that the issue is related to VB. I'll be happy to provide more informations if required, currently I'm running:
CentOS 4.5(up to date) with MySQL 5.0.41 (I've tried MySQL 5.0.22, 5.0.27) , Apache 2.0.59 , php 5.1.6 , VB database is myISAM type
my.cnf:
[mysqld]
safe-show-database
skip-innodb
skip-bdb
skip-networking
max_connections = 275
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log=/var/lib/mysql/mysql.log
log-slow-queries=/var/lib/mysql/slow.log
long_query_time = 8
thread_cache_size = 384
thread_concurrency=8
key_buffer = 128M
myisam_sort_buffer_size = 64M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 8M
table_cache = 850
connect_timeout = 10
wait_timeout = 60
max_allowed_packet=16M
#QUERY_CACHE
query_cache_size =512M
query_cache_type = 1
tmp_table_size = 128M
max_heap_table_size = 64M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
[mysql.server]
user=mysql
basedir=/var/lib/mysql
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
httpd.conf:
Timeout 300
KeepAlive On
MaxKeepAliveRequests 300
KeepAliveTimeout 15
StartServers 10
MaxClients 200
MinSpareServers 10
MaxSpareServers 15
MaxRequestsPerChild 100
mysql report after 8 hours running without problems
************************************************** ************************************************** ********
MySQL 5.0.41-log uptime 0 8:3:43 Sat May 26 08:41:51 2007
__ Key __________________________________________________ _______________
Buffer used 15.31M of 128.00M %Used: 11.96
Current 30.05M %Usage: 23.48
Write ratio 0.359
Read ratio 0.003
__ Questions __________________________________________________ _________
Total 293.71k 10.1/s
QC Hits 114.78k 4.0/s %Total: 39.08
DMS 103.54k 3.6/s 35.25
Com_ 44.96k 1.5/s 15.31
COM_QUIT 31.29k 1.1/s 10.65
-Unknown 868 0.0/s 0.30
Slow 0 0/s 0.00 %DMS: 0.00
DMS 103.54k 3.6/s 35.25
SELECT 71.68k 2.5/s 24.40 69.23
UPDATE 21.50k 0.7/s 7.32 20.77
INSERT 4.96k 0.2/s 1.69 4.79
DELETE 4.49k 0.2/s 1.53 4.33
REPLACE 913 0.0/s 0.31 0.88
__ SELECT and Sort __________________________________________________ ___
Scan 10.25k 0.4/s %SELECT: 14.31
Range 15.00k 0.5/s 20.92
Full join 105 0.0/s 0.15
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 2.60k 0.1/s
Sort range 16.42k 0.6/s
Sort mrg pass 0 0/s
__ Query Cache __________________________________________________ _______
Memory usage 98.80M of 512.00M %Used: 19.30
Block Fragmnt 2.23%
Hits 114.78k 4.0/s
Inserts 70.17k 2.4/s
Insrt:Prune 70.17k:1 2.4/s
Hit:Insert 1.64:1
__ Table Locks __________________________________________________ _______
Waited 14 0.0/s %Total: 0.01
Immediate 167.77k 5.8/s
__ Tables __________________________________________________ ____________
Open 541 of 850 %Cache: 63.65
Opened 3.63k 0.1/s
__ Connections __________________________________________________ _______
Max used 22 of 275 %Max: 8.00
Total 33.28k 1.1/s
__ Created Temp __________________________________________________ ______
Disk table 302 0.0/s
Table 3.84k 0.1/s
File 0 0/s
__ Threads __________________________________________________ ___________
Running 2 of 6
Cached 16 of 384 %Hit: 99.93
Created 22 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 3.96k 0.1/s
Connects 2 0.0/s
__ Bytes __________________________________________________ _____________
Sent 2.95G 101.6k/s
Received 78.83M 2.7k/s
************************************************** ************************************************** ********
mysqlreport when having 'tables locked' problem:
MySQL 5.0.41-log uptime 0 10:5:56 Fri May 25 23:50:01 2007
__ Key __________________________________________________ _______________
Buffer used 16.28M of 128.00M %Used: 12.72
Current 30.97M %Usage: 24.20
Write ratio 0.369
Read ratio 0.003
__ Questions __________________________________________________ _________
Total 330.84k 9.1/s
QC Hits 154.77k 4.3/s %Total: 46.78
DMS 108.85k 3.0/s 32.90
Com_ 42.90k 1.2/s 12.97
COM_QUIT 25.27k 0.7/s 7.64
-Unknown 950 0.0/s 0.29
Slow 0 0/s 0.00 %DMS: 0.00
DMS 108.85k 3.0/s 32.90
SELECT 77.12k 2.1/s 23.31 70.85
UPDATE 17.30k 0.5/s 5.23 15.89
INSERT 7.36k 0.2/s 2.23 6.76
DELETE 6.18k 0.2/s 1.87 5.68
REPLACE 898 0.0/s 0.27 0.82
__ SELECT and Sort __________________________________________________ ___
Scan 10.07k 0.3/s %SELECT: 13.06
Range 10.42k 0.3/s 13.52
Full join 105 0.0/s 0.14
Range check 0 0/s 0.00
Full rng join 1 0.0/s 0.00
Sort scan 2.73k 0.1/s
Sort range 17.34k 0.5/s
Sort mrg pass 0 0/s
__ Query Cache __________________________________________________ _______
Memory usage 145.63M of 512.00M %Used: 28.44
Block Fragmnt 0.64%
Hits 154.77k 4.3/s
Inserts 74.17k 2.0/s
Insrt:Prune 74.17k:1 2.0/s
Hit:Insert 2.09:1
__ Table Locks __________________________________________________ _______
Waited 404 0.0/s %Total: 0.25
Immediate 161.81k 4.5/s
__ Tables __________________________________________________ ____________
Open 850 of 850 %Cache: 100.00
Opened 5.21k 0.1/s
__ Connections __________________________________________________ _______
Max used 187 of 275 %Max: 68.00
Total 27.26k 0.7/s
__ Created Temp __________________________________________________ ______
Disk table 712 0.0/s
Table 6.89k 0.2/s
File 0 0/s
__ Threads __________________________________________________ ___________
Running 181 of 182
Cached 5 of 384 %Hit: 99.31
Created 187 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 3.96k 0.1/s
Connects 1 0.0/s
__ Bytes __________________________________________________ _____________
Sent 2.72G 74.7k/s
Received 114.61M 3.2k/s
************************************************** ************************************************** ********
One other remark, when killing mysqld processes I get lots of messages like this in my mysqld.log : [Warning] /usr/libexec/mysqld: Forcing close of thread
mysqlreport:
************************************************** ************************************************** ********
MySQL 5.0.41-log uptime 1 18:22:38 Tue May 29 01:49:06 2007
__ Key __________________________________________________ _______________
Buffer used 23.52M of 128.00M %Used: 18.37
Current 36.54M %Usage: 28.55
Write ratio 0.246
Read ratio 0.000
__ Questions __________________________________________________ _________
Total 1.88M 12.3/s
QC Hits 729.23k 4.8/s %Total: 38.79
DMS 678.72k 4.4/s 36.11
Com_ 307.24k 2.0/s 16.34
COM_QUIT 145.32k 1.0/s 7.73
+Unknown 19.24k 0.1/s 1.02
Slow 15 0.0/s 0.00 %DMS: 0.00
DMS 678.72k 4.4/s 36.11
SELECT 515.65k 3.4/s 27.43 75.97
UPDATE 102.19k 0.7/s 5.44 15.06
INSERT 31.45k 0.2/s 1.67 4.63
DELETE 25.53k 0.2/s 1.36 3.76
REPLACE 3.90k 0.0/s 0.21 0.57
__ SELECT and Sort __________________________________________________ ___
Scan 153.01k 1.0/s %SELECT: 29.67
Range 71.92k 0.5/s 13.95
Full join 6.92k 0.0/s 1.34
Range check 0 0/s 0.00
Full rng join 5 0.0/s 0.00
Sort scan 18.24k 0.1/s
Sort range 88.19k 0.6/s
Sort mrg pass 0 0/s
__ Query Cache __________________________________________________ _______
Memory usage 495.00M of 512.00M %Used: 96.68
Block Fragmnt 3.46%
Hits 729.23k 4.8/s
Inserts 381.33k 2.5/s
Insrt:Prune 29.87:1 2.4/s
Hit:Insert 1.91:1
__ Table Locks __________________________________________________ _______
Waited 177 0.0/s %Total: 0.02
Immediate 908.60k 6.0/s
__ Tables __________________________________________________ ____________
Open 849 of 850 %Cache: 99.88
Opened 64.19k 0.4/s
__ Connections __________________________________________________ _______
Max used 52 of 275 %Max: 18.91
Total 155.79k 1.0/s
__ Created Temp __________________________________________________ ______
Disk table 47.76k 0.3/s
Table 96.40k 0.6/s
File 0 0/s
__ Threads __________________________________________________ ___________
Running 1 of 10
Cached 42 of 384 %Hit: 99.97
Created 52 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 20.95k 0.1/s
Connects 4 0.0/s
__ Bytes __________________________________________________ _____________
Sent 4.00G 26.2k/s
Received 486.27M 3.2k/s
************************************************** ************************************************** ********
************************************************** ************************************************** ********
MySQL 5.0.41-log uptime 0 17:16:21 Sun May 27 02:19:11 2007
__ Key __________________________________________________ _______________
Buffer used 17.18M of 128.00M %Used: 13.42
Current 31.92M %Usage: 24.94
Write ratio 0.298
Read ratio 0.002
__ Questions __________________________________________________ _________
Total 490.23k 7.9/s
QC Hits 164.71k 2.6/s %Total: 33.60
DMS 161.03k 2.6/s 32.85
Com_ 112.40k 1.8/s 22.93
COM_QUIT 42.04k 0.7/s 8.58
+Unknown 10.04k 0.2/s 2.05
Slow 8 0.0/s 0.00 %DMS: 0.00
DMS 161.03k 2.6/s 32.85
SELECT 115.48k 1.9/s 23.56 71.71
UPDATE 28.95k 0.5/s 5.91 17.98
INSERT 8.63k 0.1/s 1.76 5.36
DELETE 6.69k 0.1/s 1.37 4.16
REPLACE 1.28k 0.0/s 0.26 0.80
__ SELECT and Sort __________________________________________________ ___
Scan 53.55k 0.9/s %SELECT: 46.37
Range 24.74k 0.4/s 21.42
Full join 354 0.0/s 0.31
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 3.62k 0.1/s
Sort range 24.09k 0.4/s
Sort mrg pass 0 0/s
__ Query Cache __________________________________________________ _______
Memory usage 131.73M of 512.00M %Used: 25.73
Block Fragmnt 2.56%
Hits 164.71k 2.6/s
Inserts 99.15k 1.6/s
Insrt:Prune 99.15k:1 1.6/s
Hit:Insert 1.66:1
__ Table Locks __________________________________________________ _______
Waited 67 0.0/s %Total: 0.03
Immediate 251.21k 4.0/s
__ Tables __________________________________________________ ____________
Open 850 of 850 %Cache: 100.00
Opened 30.39k 0.5/s
__ Connections __________________________________________________ _______
Max used 38 of 275 %Max: 13.82
Total 45.58k 0.7/s
__ Created Temp __________________________________________________ ______
Disk table 22.70k 0.4/s
Table 38.34k 0.6/s
File 0 0/s
__ Threads __________________________________________________ ___________
Running 1 of 4
Cached 34 of 384 %Hit: 99.92
Created 38 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 7.07k 0.1/s
Connects 2 0.0/s
__ Bytes __________________________________________________ _____________
Sent 1.21G 19.4k/s
Received 115.35M 1.9k/s
************************************************** ************************************************** ********
Please provide the missing information as requested in [Server Optimisation]: Required info for optimisation requests [January 15, 2007] (http://www.vbulletin.com/forum/showthread.php?t=70117)
2. server info
cpu speed: 4XIntel(R) Xeon(TM) CPU 2.80GHz
memory installed: 4096 mb ram
hard drive type/configuration: 300GB
linux distributor or windows version: CentOS 4.5
apache/IIS version: apache 2.0.59
PHP version: php 5.1.6
MySQL version: mysql 5.0.41
3. what version of vB are you running ?
vBulletin 3.6.7
4. Using mysql 5.0.41, no innodb (skip-innodb in my.cnf)
5. mysql installed via yum repository (optimized for CentOS)
6. top stats
Tasks: 176 total, 1 running, 175 sleeping, 0 stopped, 0 zombie
Cpu(s): 2.7% us, 0.7% sy, 0.0% ni, 94.6% id, 2.0% wa, 0.0% hi, 0.0% si
Mem: 4036720k total, 435208k used, 3601512k free, 0k buffers
Swap: 10241428k total, 0k used, 10241428k free, 0k cached
7. my.cnf
[mysqld]
safe-show-database
skip-innodb
skip-bdb
skip-networking
max_connections = 275
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log=/var/lib/mysql/mysql.log
log-slow-queries=/var/lib/mysql/slow.log
long_query_time = 8
thread_cache_size = 384
thread_concurrency=8
key_buffer = 128M
myisam_sort_buffer_size = 64M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 8M
table_cache = 850
connect_timeout = 10
wait_timeout = 60
max_allowed_packet=16M
#QUERY_CACHE
query_cache_size =512M
query_cache_type = 1
tmp_table_size = 128M
max_heap_table_size = 64M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
[mysql.server]
user=mysql
basedir=/var/lib/mysql
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
8. MySQL stats:
mysqladmin -u root -p var ext stat ver:
+---------------------------------+--------------------------------------------------------+
Variable_name Value
+---------------------------------+--------------------------------------------------------+
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 50
basedir /usr/
bdb_cache_size 8388600
bdb_home
bdb_log_buffer_size 0
bdb_logdir
bdb_max_lock 10000
bdb_shared_data OFF
bdb_tmpdir
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 NO
have_bdb DISABLED
have_blackhole_engine NO
have_compress YES
have_crypt YES
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_merge_engine YES
have_ndbcluster NO
have_openssl DISABLED
have_ssl DISABLED
have_query_cache YES
have_raid NO
have_rtree_keys YES
have_symlink YES
hostname site.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 4190208
key_buffer_size 134217728
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 ON
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 8
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 16776192
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 275
max_delayed_threads 20
max_error_count 64
max_heap_table_size 67108864
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
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 /var/run/mysqld/mysqld.pid
port 0
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 536870912
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 4190208
read_only OFF
read_rnd_buffer_size 520192
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 ON
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 8388600
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 CDT
table_cache 850
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 134217728
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.41-log
version_bdb Sleepycat Software: Berkeley DB 4.1.24: (May 2, 2007)
version_comment Source distribution
version_compile_machine i686
version_compile_os redhat-linux-gnu
wait_timeout 60
+---------------------------------+--------------------------------------------------------+
+-----------------------------------+-----------+
Variable_name Value
+-----------------------------------+-----------+
Aborted_clients 500
Aborted_connects 3
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 8719143
Bytes_sent 356307445
Com_admin_commands 157
Com_alter_db 0
Com_alter_table 380
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 3419
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 193
Com_create_user 0
Com_dealloc_sql 0
Com_delete 547
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 193
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 725
Com_insert_select 7
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 190
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 101
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 8764
Com_set_option 39
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 13
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 1
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 32
Com_show_storage_engines 0
Com_show_tables 16
Com_show_triggers 0
Com_show_variables 25
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 3
Com_unlock_tables 190
Com_update 2242
Com_update_multi 3
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 3380
Created_tmp_disk_tables 26
Created_tmp_files 0
Created_tmp_tables 309
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 355
Handler_discover 0
Handler_prepare 0
Handler_read_first 2059
Handler_read_key 176343
Handler_read_next 259752
Handler_read_prev 19348
Handler_read_rnd 27645
Handler_read_rnd_next 585075
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 2536
Handler_write 17578
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 109303
Key_blocks_used 6677
Key_read_requests 534990
Key_reads 6808
Key_write_requests 5321
Key_writes 2074
Last_query_cost 0.000000
Max_used_connections 30
Not_flushed_delayed_rows 0
Open_files 659
Open_streams 0
Open_tables 340
Opened_tables 735
Prepared_stmt_count 0
Qcache_free_blocks 207
Qcache_free_memory 519435736
Qcache_hits 15783
Qcache_inserts 8627
Qcache_lowmem_prunes 0
Qcache_not_cached 206
Qcache_queries_in_cache 3456
Qcache_total_blocks 7423
Questions 35967
Rpl_status NULL
Select_full_join 11
Select_full_range_join 0
Select_range 2155
Select_range_check 0
Select_scan 1338
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 1882
Sort_rows 114324
Sort_scan 276
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 19238
Table_locks_waited 1
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 28
Threads_connected 2
Threads_created 30
Threads_running 1
Uptime 2588
Uptime_since_flush_status 2588
+-----------------------------------+-----------+
Uptime: 2625 Threads: 9 Questions: 36358 Slow queries: 0 Opens: 739 Flush tables: 1 Open tables: 341 Queries per second avg: 13.851
mysqladmin Ver 8.41 Distrib 5.0.41, for redhat-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.41-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 43 min 45 sec
Threads: 9 Questions: 36358 Slow queries: 0 Opens: 739 Flush tables: 1 Open tables: 341 Queries per second avg: 13.851
mysqlreport --all :
************************************************** ************************************************** ********
MySQL 5.0.41-log uptime 0 0:44:50 Sun May 27 08:11:18 2007
__ Key __________________________________________________ _______________
Buffer used 6.59M of 128.00M %Used: 5.15
Current 21.33M %Usage: 16.66
Write ratio 0.391
Read ratio 0.013
__ Questions __________________________________________________ _________
Total 36.84k 13.7/s
QC Hits 16.10k 6.0/s %Total: 43.69
DMS 12.72k 4.7/s 34.52
Com_ 4.98k 1.9/s 13.53
COM_QUIT 3.24k 1.2/s 8.79
-Unknown 195 0.1/s 0.53
Slow 0 0/s 0.00 %DMS: 0.00
DMS 12.72k 4.7/s 34.52
SELECT 8.99k 3.3/s 24.41 70.71
UPDATE 2.32k 0.9/s 6.29 18.22
INSERT 743 0.3/s 2.02 5.84
DELETE 559 0.2/s 1.52 4.40
REPLACE 106 0.0/s 0.29 0.83
__ SELECT and Sort __________________________________________________ ___
Scan 1.38k 0.5/s %SELECT: 15.36
Range 2.24k 0.8/s 24.93
Full join 12 0.0/s 0.13
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 285 0.1/s
Sort range 1.93k 0.7/s
Sort mrg pass 0 0/s
__ Query Cache __________________________________________________ _______
Memory usage 16.80M of 512.00M %Used: 3.28
Block Fragmnt 2.40%
Hits 16.10k 6.0/s
Inserts 8.85k 3.3/s
Insrt:Prune 8.85k:1 3.3/s
Hit:Insert 1.82:1
__ Table Locks __________________________________________________ _______
Waited 1 0.0/s %Total: 0.01
Immediate 19.75k 7.3/s
__ Tables __________________________________________________ ____________
Open 341 of 850 %Cache: 40.12
Opened 739 0.3/s
__ Connections __________________________________________________ _______
Max used 30 of 275 %Max: 10.91
Total 3.50k 1.3/s
__ Created Temp __________________________________________________ ______
Disk table 26 0.0/s
Table 335 0.1/s
File 0 0/s
__ Threads __________________________________________________ ___________
Running 1 of 5
Cached 25 of 384 %Hit: 99.14
Created 30 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 516 0.2/s
Connects 3 0.0/s
__ Bytes __________________________________________________ _____________
Sent 365.78M 136.0k/s
Received 8.89M 3.3k/s
9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
No , we have several other sites (using php/mysql), but having a low traffic.
10. how many average and max concurrent users on your vB forum ?
average users - > 60-70
max concurrent users -> 200
11. phpinfo():
http://www.forums.naturalparenting.com.au/info.php
12.
httpd.conf:
KeepAlive On
MaxKeepAliveRequests 300
KeepAliveTimeout 15
MinSpareServers 10
MaxSpareServers 15
StartServers 10
MaxClients 200
MaxRequestsPerChild 100
13.
No files with size greater than 2000000k
14.
uname -a
Linux site.com 2.6.18.3-RH2112 #3 SMP Thu Nov 23 20:09:53 CST 2006 x86_64 x86_64 x86_64 GNU/Linux
ulimit -aH
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 38912
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 2048
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) 38912
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 1
cpu MHz : 2793.264
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 5590.53
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 1
cpu MHz : 2793.264
cache size : 1024 KB
physical id : 3
siblings : 2
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 5586.41
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:
processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 1
cpu MHz : 2793.264
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 5586.44
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:
processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 1
cpu MHz : 2793.264
cache size : 1024 KB
physical id : 3
siblings : 2
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 5586.41
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:
#If you have any other info about this subject , Please add it free.# |