infreaks
Wed 6th Feb '08, 2:41am
Hi, My forum is getting too slow, and 3 days ago my database is corrupt and cannot be repair, so 2 days forum activity is lost. I provide the details you ask below, I hope you could help me, thanks before :)
1. Is this on dedicated or shared virual server. If shared, how many sites share this server ?
We have 2 Vbulletin forum running in one dedicated web server, while the database is place on master and slave server.
2. your server specs. For example:
Vbulletin forum configuration :
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 7 Webserver : Apache 2.2.6 PHP : php 5.2.0
While the database is place in dedicated server, and using master slave. Below is the configuration :
MYSQL Master server
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 6 MYSQL : mysql 5.0.27
MYSQL Slave server
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4 GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 6 MYSQL : mysql 5.0.27
3. what version of vB are you running ? vBulletin 3.6.7 PL1
4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ? I’m running mysql 5.0.27, I don’t have any innodb type databases or tables in my server.
5. if possible how mysql was compiled/installed ? mysql 5.0.27 version that comes with Fedora 6.
6. your top stats
Total Members: 46,868
Total Threads: 14,438
Total Posts: 1,049,662
Daily Member Logins: 16,879
Most users ever online (concurrently) : 3218
top - 13:26:34 up 105 days, 22:29, 7 users, load average: 3.01, 3.06, 3.18
Tasks: 434 total, 4 running, 429 sleeping, 1 stopped, 0 zombie
Cpu(s): 60.7%us, 2.7%sy, 0.0%ni, 34.7%id, 0.0%wa, 0.1%hi, 1.8%si, 0.0%st
Mem: 4064752k total, 3373188k used, 691564k free, 128560k buffers
Swap: 8385920k total, 8312k used, 8377608k free, 590168k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26253 apache 20 0 216m 22m 4552 S 0 0.6 7:53.95 httpd
26589 apache 20 0 214m 21m 4340 S 0 0.6 7:58.66 httpd
26442 apache 20 0 213m 20m 4360 S 0 0.5 7:52.54 httpd
26283 apache 20 0 211m 18m 4380 S 0 0.5 7:54.74 httpd
26519 apache 20 0 211m 18m 4384 S 4 0.5 7:52.48 httpd
29428 apache 20 0 211m 18m 4360 S 0 0.5 7:19.77 httpd
14583 apache 20 0 211m 18m 4188 S 1 0.5 0:47.33 httpd
26523 apache 20 0 212m 18m 4364 S 5 0.5 8:25.00 httpd
26410 apache 20 0 213m 18m 4468 S 0 0.5 8:33.50 httpd
14574 apache 20 0 211m 18m 4140 S 0 0.5 0:47.57 httpd
29453 apache 20 0 212m 18m 4504 S 0 0.5 6:51.88 httpd
13226 apache 20 0 211m 18m 4220 S 0 0.5 1:22.44 httpd
13298 apache 20 0 211m 17m 4260 S 0 0.5 1:24.09 httpd
14585 apache 20 0 211m 17m 4220 S 4 0.5 0:49.22 httpd
13274 apache 20 0 211m 17m 4264 S 0 0.5 1:18.98 httpd
26147 apache 20 0 211m 17m 4364 S 0 0.4 8:00.78 httpd
13328 apache 20 0 213m 17m 4368 S 0 0.4 1:25.78 httpd
13254 apache 20 0 211m 17m 4224 S 4 0.4 1:22.76 httpd
13253 apache 20 0 211m 17m 4184 S 0 0.4 1:26.32 httpd
14554 apache 20 0 209m 17m 3484 S 0 0.4 0:44.22 httpd
13269 apache 20 0 210m 17m 4124 S 0 0.4 1:19.64 httpd
26505 apache 20 0 209m 17m 4580 S 0 0.4 8:24.23 httpd
26317 apache 20 0 209m 17m 4384 S 0 0.4 7:41.98 httpd
29415 apache 20 0 209m 17m 4352 S 0 0.4 7:02.27 httpd
26480 apache 20 0 209m 17m 4360 S 0 0.4 7:56.62 httpd
13238 apache 20 0 209m 17m 4284 S 1 0.4 1:21.61 httpd
13237 apache 20 0 209m 16m 4336 S 4 0.4 1:19.40 httpd
26294 apache 20 0 209m 16m 4412 S 0 0.4 8:01.72 httpd
26413 apache 20 0 210m 16m 4360 S 6 0.4 8:00.17 httpd
26270 apache 20 0 210m 16m 4472 S 0 0.4 7:55.45 httpd
26482 apache 20 0 208m 16m 4356 S 1 0.4 7:50.29 httpd
26285 apache 20 0 208m 16m 4372 S 0 0.4 8:13.44 httpd
26244 apache 20 0 210m 16m 4540 S 0 0.4 7:57.54 httpd
26100 apache 20 0 210m 16m 4360 S 0 0.4 7:54.40 httpd
13261 apache 20 0 210m 16m 4284 S 0 0.4 1:26.30 httpd
26596 apache 20 0 208m 16m 4392 S 1 0.4 8:26.65 httpd
29393 apache 20 0 209m 16m 4400 S 0 0.4 7:00.81 httpd
29405 apache 20 0 210m 16m 4304 S 0 0.4 7:02.66 httpd
26488 apache 20 0 208m 16m 4312 S 0 0.4 7:58.07 httpd
7. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 300
basedir /usr/
bdb_cache_size 8388600
bdb_home /var/lib/mysql/
bdb_log_buffer_size 2560000
bdb_logdir
bdb_max_lock 10000
bdb_shared_data OFF
bdb_tmpdir /var/log/mytmp/
binlog_cache_size 32768
bulk_insert_buffer_size 33554432
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 2
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 1
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 YES
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 YES
have_isam NO
have_merge_engine YES
have_ndbcluster NO
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 104857600
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 524288000
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path db1:500M;db2:500M;db3:500M:autoextend innodb_data_home_dir /var/lib/ibtablespace/
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 /var/lib/iblogs
innodb_log_archive OFF
innodb_log_buffer_size 8388608
innodb_log_file_size 209715200
innodb_log_files_in_group 3
innodb_log_group_home_dir /var/lib/iblogs
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 4190208
key_buffer_size 402653184
key_cache_age_threshold 300
key_cache_block_size 15872
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 l
og_bin ON
log_bin_trust_function_creators OFF
log_error /var/log/mysqld.err
log_queries_not_using_indexes ON
log_slave_updates OFF
log_slow_queries ON
log_warnings 2
long_query_time 5
low_priority_updates ON
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 536869888
max_binlog_cache_size 10481664
max_binlog_size 419430400
max_connect_errors 999999
max_connections 200
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 429496729
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 256
max_user_connections 300
max_write_lock_count 4294967295
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 2146435072
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 67108864
myisam_stats_method nulls_unequal
net_buffer_length 1048576
net_read_timeout 3600
net_retry_count 10
net_write_timeout 3600
new OFF
old_passwords ON
open_files_limit 10210
optimizer_prune_level 1
optimizer_search_depth 62
pid_file /var/run/mysqld/mysqld.pid
port 3306
preload_buffer_size 32768
prepared_stmt_count 0
protocol_version 10
query_alloc_block_size 65536
query_cache_limit 33554432
query_cache_min_res_unit 8192
query_cache_size 33554432
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 262144
range_alloc_block_size 2048
read_buffer_size 2093056
read_only OFF
read_rnd_buffer_size 782336
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 /var/log/mytmp/
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 4194296
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 WIT
table_cache 5000
table_lock_wait_timeout 50
table_type MyISAM
thread_cache_size 16384
thread_stack 131072
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmp_table_size 33554432
tmpdir /var/log/mytmp
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.27-log
version_bdb Sleepycat Software: Berkeley DB 4.1.24: (October 21, 2006) version_comment Source distribution
version_compile_machine i686
version_compile_os redhat-linux-gnu
wait_timeout 600
8. MySQL stats from ssh telnet as root user type:
Aborted_clients 60
Aborted_connects 3526
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 2223
Bytes_sent 238575
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_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 1227527
Created_tmp_disk_tables 0
Created_tmp_files 30
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 1
Handler_read_key 7
Handler_read_next 1
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 4
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 1
Handler_write 130
Innodb_buffer_pool_pages_data 65
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 0
Innodb_buffer_pool_pages_free 31935
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 0
Innodb_buffer_pool_pages_total 32000
Innodb_buffer_pool_read_ahead_rnd 1
Innodb_buffer_pool_read_ahead_seq 0
Innodb_buffer_pool_read_requests 222
Innodb_buffer_pool_reads 14
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 3248128
Innodb_data_reads 29
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 65
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 0
Key_blocks_used 25164
Key_read_requests 429457817
Key_reads 49514
Key_write_requests 1827973
Key_writes 1174021
Last_query_cost 0.000000
Max_used_connections 201
Not_flushed_delayed_rows 0
Open_files 1361
Open_streams 0
Open_tables 1158
Opened_tables 0
Qcache_free_blocks 765
Qcache_free_memory 13036264
Qcache_hits 3938676
Qcache_inserts 2731057
Qcache_lowmem_prunes 52873
Qcache_not_cached 6558
Qcache_queries_in_cache 5755
Qcache_total_blocks 12402
Questions 10348129
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
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 8399733
Table_locks_waited 18017
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 197
Threads_connected 4
Threads_created 201
Threads_running 2
Uptime 72202
9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql? We have 2 Vbulletin forum running in one dedicated web server, while the database is place on master and slave server.
10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ? Our Average user is : 1568 user. Max Concurrent user was 3218. Cookie Timeout is : unlimited, restart when browser is restart.
11. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site : http://forum.detik.com/info.php
12. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :
KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 128
MaxSpareServers 256
StartServers 512
MaxClients 1800
AND Maxrequestsperchild value 0
13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log ? No "max file size exceeded" error(s) found in apache error log files, the largest apache log file size is 1.1 GB.
14. Post output from these 3 commands
uname –a
Linux kuyak 2.6.22.1-27.fc7 #1 SMP Tue Jul 17 17:19:58 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
ulimit –aH for 2 vBulletin forum in one dedicated server
root@kuyak forumbandung]# ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 38912
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
real-time priority (-r) 0
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 38912
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
ulimit –aH for database master
[root@davinci ~]# ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
max nice (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 65536
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
max rt priority (-r) 0
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 65536
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
ulimit –aH for database slave
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
max nice (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 65536
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
max rt priority (-r) 0
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 65536
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
cat /proc/cpuinfo
Vbulletin forum configuration :
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 7 Webserver : Apache 2.2.6 PHP : php 5.2.0
While the database is place in dedicated server, and using master slave. Below is the configuration :
MYSQL Master server
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 6 MYSQL : mysql 5.0.27
MYSQL Slave server
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4 GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 6 MYSQL : mysql 5.0.27
1. Is this on dedicated or shared virual server. If shared, how many sites share this server ?
We have 2 Vbulletin forum running in one dedicated web server, while the database is place on master and slave server.
2. your server specs. For example:
Vbulletin forum configuration :
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 7 Webserver : Apache 2.2.6 PHP : php 5.2.0
While the database is place in dedicated server, and using master slave. Below is the configuration :
MYSQL Master server
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 6 MYSQL : mysql 5.0.27
MYSQL Slave server
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4 GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 6 MYSQL : mysql 5.0.27
3. what version of vB are you running ? vBulletin 3.6.7 PL1
4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ? I’m running mysql 5.0.27, I don’t have any innodb type databases or tables in my server.
5. if possible how mysql was compiled/installed ? mysql 5.0.27 version that comes with Fedora 6.
6. your top stats
Total Members: 46,868
Total Threads: 14,438
Total Posts: 1,049,662
Daily Member Logins: 16,879
Most users ever online (concurrently) : 3218
top - 13:26:34 up 105 days, 22:29, 7 users, load average: 3.01, 3.06, 3.18
Tasks: 434 total, 4 running, 429 sleeping, 1 stopped, 0 zombie
Cpu(s): 60.7%us, 2.7%sy, 0.0%ni, 34.7%id, 0.0%wa, 0.1%hi, 1.8%si, 0.0%st
Mem: 4064752k total, 3373188k used, 691564k free, 128560k buffers
Swap: 8385920k total, 8312k used, 8377608k free, 590168k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26253 apache 20 0 216m 22m 4552 S 0 0.6 7:53.95 httpd
26589 apache 20 0 214m 21m 4340 S 0 0.6 7:58.66 httpd
26442 apache 20 0 213m 20m 4360 S 0 0.5 7:52.54 httpd
26283 apache 20 0 211m 18m 4380 S 0 0.5 7:54.74 httpd
26519 apache 20 0 211m 18m 4384 S 4 0.5 7:52.48 httpd
29428 apache 20 0 211m 18m 4360 S 0 0.5 7:19.77 httpd
14583 apache 20 0 211m 18m 4188 S 1 0.5 0:47.33 httpd
26523 apache 20 0 212m 18m 4364 S 5 0.5 8:25.00 httpd
26410 apache 20 0 213m 18m 4468 S 0 0.5 8:33.50 httpd
14574 apache 20 0 211m 18m 4140 S 0 0.5 0:47.57 httpd
29453 apache 20 0 212m 18m 4504 S 0 0.5 6:51.88 httpd
13226 apache 20 0 211m 18m 4220 S 0 0.5 1:22.44 httpd
13298 apache 20 0 211m 17m 4260 S 0 0.5 1:24.09 httpd
14585 apache 20 0 211m 17m 4220 S 4 0.5 0:49.22 httpd
13274 apache 20 0 211m 17m 4264 S 0 0.5 1:18.98 httpd
26147 apache 20 0 211m 17m 4364 S 0 0.4 8:00.78 httpd
13328 apache 20 0 213m 17m 4368 S 0 0.4 1:25.78 httpd
13254 apache 20 0 211m 17m 4224 S 4 0.4 1:22.76 httpd
13253 apache 20 0 211m 17m 4184 S 0 0.4 1:26.32 httpd
14554 apache 20 0 209m 17m 3484 S 0 0.4 0:44.22 httpd
13269 apache 20 0 210m 17m 4124 S 0 0.4 1:19.64 httpd
26505 apache 20 0 209m 17m 4580 S 0 0.4 8:24.23 httpd
26317 apache 20 0 209m 17m 4384 S 0 0.4 7:41.98 httpd
29415 apache 20 0 209m 17m 4352 S 0 0.4 7:02.27 httpd
26480 apache 20 0 209m 17m 4360 S 0 0.4 7:56.62 httpd
13238 apache 20 0 209m 17m 4284 S 1 0.4 1:21.61 httpd
13237 apache 20 0 209m 16m 4336 S 4 0.4 1:19.40 httpd
26294 apache 20 0 209m 16m 4412 S 0 0.4 8:01.72 httpd
26413 apache 20 0 210m 16m 4360 S 6 0.4 8:00.17 httpd
26270 apache 20 0 210m 16m 4472 S 0 0.4 7:55.45 httpd
26482 apache 20 0 208m 16m 4356 S 1 0.4 7:50.29 httpd
26285 apache 20 0 208m 16m 4372 S 0 0.4 8:13.44 httpd
26244 apache 20 0 210m 16m 4540 S 0 0.4 7:57.54 httpd
26100 apache 20 0 210m 16m 4360 S 0 0.4 7:54.40 httpd
13261 apache 20 0 210m 16m 4284 S 0 0.4 1:26.30 httpd
26596 apache 20 0 208m 16m 4392 S 1 0.4 8:26.65 httpd
29393 apache 20 0 209m 16m 4400 S 0 0.4 7:00.81 httpd
29405 apache 20 0 210m 16m 4304 S 0 0.4 7:02.66 httpd
26488 apache 20 0 208m 16m 4312 S 0 0.4 7:58.07 httpd
7. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 300
basedir /usr/
bdb_cache_size 8388600
bdb_home /var/lib/mysql/
bdb_log_buffer_size 2560000
bdb_logdir
bdb_max_lock 10000
bdb_shared_data OFF
bdb_tmpdir /var/log/mytmp/
binlog_cache_size 32768
bulk_insert_buffer_size 33554432
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 2
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 1
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 YES
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 YES
have_isam NO
have_merge_engine YES
have_ndbcluster NO
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 104857600
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 524288000
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path db1:500M;db2:500M;db3:500M:autoextend innodb_data_home_dir /var/lib/ibtablespace/
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 /var/lib/iblogs
innodb_log_archive OFF
innodb_log_buffer_size 8388608
innodb_log_file_size 209715200
innodb_log_files_in_group 3
innodb_log_group_home_dir /var/lib/iblogs
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 4190208
key_buffer_size 402653184
key_cache_age_threshold 300
key_cache_block_size 15872
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 l
og_bin ON
log_bin_trust_function_creators OFF
log_error /var/log/mysqld.err
log_queries_not_using_indexes ON
log_slave_updates OFF
log_slow_queries ON
log_warnings 2
long_query_time 5
low_priority_updates ON
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 536869888
max_binlog_cache_size 10481664
max_binlog_size 419430400
max_connect_errors 999999
max_connections 200
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 429496729
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 256
max_user_connections 300
max_write_lock_count 4294967295
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 2146435072
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 67108864
myisam_stats_method nulls_unequal
net_buffer_length 1048576
net_read_timeout 3600
net_retry_count 10
net_write_timeout 3600
new OFF
old_passwords ON
open_files_limit 10210
optimizer_prune_level 1
optimizer_search_depth 62
pid_file /var/run/mysqld/mysqld.pid
port 3306
preload_buffer_size 32768
prepared_stmt_count 0
protocol_version 10
query_alloc_block_size 65536
query_cache_limit 33554432
query_cache_min_res_unit 8192
query_cache_size 33554432
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 262144
range_alloc_block_size 2048
read_buffer_size 2093056
read_only OFF
read_rnd_buffer_size 782336
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 /var/log/mytmp/
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 4194296
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 WIT
table_cache 5000
table_lock_wait_timeout 50
table_type MyISAM
thread_cache_size 16384
thread_stack 131072
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmp_table_size 33554432
tmpdir /var/log/mytmp
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.27-log
version_bdb Sleepycat Software: Berkeley DB 4.1.24: (October 21, 2006) version_comment Source distribution
version_compile_machine i686
version_compile_os redhat-linux-gnu
wait_timeout 600
8. MySQL stats from ssh telnet as root user type:
Aborted_clients 60
Aborted_connects 3526
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 2223
Bytes_sent 238575
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_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 1227527
Created_tmp_disk_tables 0
Created_tmp_files 30
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 1
Handler_read_key 7
Handler_read_next 1
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 4
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 1
Handler_write 130
Innodb_buffer_pool_pages_data 65
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 0
Innodb_buffer_pool_pages_free 31935
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 0
Innodb_buffer_pool_pages_total 32000
Innodb_buffer_pool_read_ahead_rnd 1
Innodb_buffer_pool_read_ahead_seq 0
Innodb_buffer_pool_read_requests 222
Innodb_buffer_pool_reads 14
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 3248128
Innodb_data_reads 29
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 65
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 0
Key_blocks_used 25164
Key_read_requests 429457817
Key_reads 49514
Key_write_requests 1827973
Key_writes 1174021
Last_query_cost 0.000000
Max_used_connections 201
Not_flushed_delayed_rows 0
Open_files 1361
Open_streams 0
Open_tables 1158
Opened_tables 0
Qcache_free_blocks 765
Qcache_free_memory 13036264
Qcache_hits 3938676
Qcache_inserts 2731057
Qcache_lowmem_prunes 52873
Qcache_not_cached 6558
Qcache_queries_in_cache 5755
Qcache_total_blocks 12402
Questions 10348129
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
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 8399733
Table_locks_waited 18017
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 197
Threads_connected 4
Threads_created 201
Threads_running 2
Uptime 72202
9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql? We have 2 Vbulletin forum running in one dedicated web server, while the database is place on master and slave server.
10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ? Our Average user is : 1568 user. Max Concurrent user was 3218. Cookie Timeout is : unlimited, restart when browser is restart.
11. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site : http://forum.detik.com/info.php
12. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :
KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 128
MaxSpareServers 256
StartServers 512
MaxClients 1800
AND Maxrequestsperchild value 0
13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log ? No "max file size exceeded" error(s) found in apache error log files, the largest apache log file size is 1.1 GB.
14. Post output from these 3 commands
uname –a
Linux kuyak 2.6.22.1-27.fc7 #1 SMP Tue Jul 17 17:19:58 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
ulimit –aH for 2 vBulletin forum in one dedicated server
root@kuyak forumbandung]# ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 38912
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
real-time priority (-r) 0
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 38912
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
ulimit –aH for database master
[root@davinci ~]# ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
max nice (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 65536
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
max rt priority (-r) 0
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 65536
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
ulimit –aH for database slave
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
max nice (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 65536
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
max rt priority (-r) 0
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 65536
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
cat /proc/cpuinfo
Vbulletin forum configuration :
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 7 Webserver : Apache 2.2.6 PHP : php 5.2.0
While the database is place in dedicated server, and using master slave. Below is the configuration :
MYSQL Master server
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 6 MYSQL : mysql 5.0.27
MYSQL Slave server
Processor : Intel(R) Xeon(R) CPU 5110 @ 1.60GHz Memory : 4 GB Harddisk : 2 X 146 GB OS : Red Hat Fedora Core 6 MYSQL : mysql 5.0.27