1. Is this on dedicated or shared virual server. If shared, how many sites share this server
Dedicated (4 boxes with memcached server)
2. your server specs. For example:
Database (1 server): freeBSD
Server Dell 2950 MLK III series
Quad Core Intel® Xeon® E5430, 2x6MB Cache, 2.66GHz, 1333MHz FSB
Memory 24 Gig
Webserver (2 servers): freeBSD with Litespeed
Server Dell 1950 MLK III series
Quad Core Intel® Xeon® E5420, 2x6MB Cache, 2.33GHz, 1333MHz FSB (4x1024)
Memory 8 Gig
Memcache (1 server): freeBSD
Server Dell 1950 MLK III series
Quad Core Intel® Xeon® E5420, 2x6MB Cache, 2.33GHz, 1333MHz FSB (4x1024)
Memory 8 Gig
hard drive type/configuration: RAID
PHP version: 5.2.6
MySQL version: 4.1.22-log3. what version of vB are you running ? 3.7.2
4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type
databases/tables on your server ?
No
5. if possible how mysql was compiled/installed
I dont know
6. your top stats (DATABASE SERVER)
last pid: 1682; load averages: 44.88, 34.27, 27.03 up 0+06:45:49 05:48:45
526 processes: 20 running, 506 sleeping
CPU states: 89.2% user, 0.0% nice, 10.4% system, 0.4% interrupt, 0.0% idle
Mem: 1142M Active, 5074M Inact, 307M Wired, 76K Cache, 214M Buf, 17G Free
Swap: 4096M Total, 4096M Free
PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
883 mysql 510 4 0 1853M 1149M sbwait 1 905:58 213.77% mysqld
1619 root 1 96 0 8680K 3304K CPU0 0 0:02 0.00% top
1586 root 1 96 0 32936K 4520K select 0 0:00 0.00% sshd
665 root 1 8 0 5736K 1396K nanslp 0 0:00 0.00% cron
507 root 1 96 0 4684K 1352K select 0 0:00 0.00% syslogd
1590 root 1 20 0 9624K 2792K pause 1 0:00 0.00% csh
860 root 1 8 0 6212K 1844K wait 1 0:00 0.00% sh
658 root 1 96 0 20960K 3544K select 0 0:00 0.00% sshd
710 root 1 5 0 4668K 1200K ttyin 3 0:00 0.00% getty
708 root 1 5 0 4668K 1200K ttyin 0 0:00 0.00% getty
711 root 1 5 0 4668K 1200K ttyin 0 0:00 0.00% getty
714 root 1 5 0 4668K 1200K ttyin 1 0:00 0.00% getty
709 root 1 5 0 4668K 1200K ttyin 0 0:00 0.00% getty
712 root 1 5 0 4668K 1200K ttyin 0 0:00 0.00% getty
707 root 1 5 0 4668K 1200K ttyin 1 0:00 0.00% getty
713 root 1 5 0 4668K 1200K ttyin 3 0:00 0.00% getty
461 root 1 96 0 1616K 628K select 1 0:00 0.00% devd7. 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).
[mysqld]
# datadir=/var/lib/mysql
socket=/tmp/mysql.sock
back_log=50
skip-innodb
wait_timeout=20
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=1
# auto_increment_increment=10
# auto_increment_offset=1
# log-bin=mysql-bin
log-bin=mysql-db1-bin
#log-slave-updates
max_user_connections=650
max_connections=650
connect_timeout=20
max_connect_errors=20
sort_buffer_size=8M
read_buffer_size=8M
read_rnd_buffer_size=8M
join_buffer_size=8M
key_buffer=1024M
max_allowed_packet=8M
table_cache=1M
query_cache_size=384M
query_cache_type=1
Performance - MoodleDocs::
Nov 7, 2008 Important: You must make backups of your database before attempting to change any MySQL server configuration. After any change to the my.cnf
http://docs.moodle.org/en/PerformanceHOME |
query_cache_limit=5MB
myisam_sort_buffer_size=64M
thread_concurrency=8 ## Number of CPUs x 2
thread_cache_size=500
[mysql.server]
user=mysql
#basedir=/var/lib
[mysqld_safe]
open_files_limit=8192
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid8. MySQL stats from ssh telnet as root user type:
+---------------------------------+----------------------------------------+
Variable_name Value
+---------------------------------+----------------------------------------+
back_log 50
basedir /usr/local/mysql/
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/local/mysql/share/mysql/charsets/
collation_connection latin1_swedish_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
concurrent_insert ON
connect_timeout 20
datadir /usr/local/mysql/var/
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
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 NO
have_blackhole_engine NO
have_compress YES
have_crypt YES
have_csv NO
have_example_engine NO
have_geometry YES
have_innodb DISABLED
have_isam NO
have_merge_engine YES
have_ndbcluster NO
have_openssl NO
have_query_cache YES
have_raid NO
have_rtree_keys YES
have_symlink YES
init_connect
init_file
init_slave
innodb_additional_mem_pool_size 1048576
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_data_file_path
innodb_data_home_dir
innodb_fast_shutdown ON
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_table_locks ON
innodb_thread_concurrency 8
interactive_timeout 28800
join_buffer_size 8384512
key_buffer_size 1073741824
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language /usr/local/mysql/share/mysql/english/
large_files_support ON
lc_time_names en_US
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_bin ON
log_error
MySQL 5 optimizing - D-talk::
You can't just reload your server look at the performance (emulated or live) change your /etc/my.cnf and repeat. The best thing you can do is take a
http://blog.dynom.nl/archives/MySQL-5-optimizing_20071009_10.htmlHOME |
Bobcares Blog » Mysql Server Optimization for DBA::
Sep 18, 2008 The MySQL server configuration file should be named my.cnf and is Each database server will be running different applications in it and
http://bobcares.com/index.php/blog/?p=115HOME |
log_slave_updates OFF
log_slow_queries OFF
log_update 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 8387584
max_binlog_cache_size 18446744073709551615
max_binlog_size 1073741824
max_connect_errors 20
max_connections 650
max_delayed_threads 20
max_error_count 64
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_tmp_tables 32
max_user_connections 650
max_write_lock_count 18446744073709551615
myisam_data_pointer_size 4
myisam_max_extra_sort_file_size 2147483648
myisam_max_sort_file_size 9223372036854775807
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 1000000
net_write_timeout 60
new OFF
old_passwords ON
open_files_limit 11095
pid_file /usr/local/mysql/var/db.kazhi.us.pid
port 3306
preload_buffer_size 32768
prepared_stmt_count 0
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 5242880
query_cache_min_res_unit 4096
query_cache_size 402653184
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 8384512
read_only OFF
read_rnd_buffer_size 8384512
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_net_timeout 3600
slave_transaction_retries 0
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 8388600
sql_mode
sort_buffer_size 8388600
sql_mode
sql_notes ON
sql_warnings ON
storage_engine MyISAM
sync_binlog 0
sync_frm ON
sync_replication 0
sync_replication_slave_id 0
sync_replication_timeout 0
system_time_zone UTC
table_cache 5217
table_type MyISAM
thread_cache_size 500
thread_stack 196608
time_format %H:%i:%s
time_zone SYSTEM
tmp_table_size 33554432
tmpdir
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
version 4.1.22-log
version_comment Source distribution
version_compile_machine x86_64
version_compile_os unknown-freebsd7.0
wait_timeout 20
+---------------------------------+----------------------------------------+
+----------------------------+--------------+
Variable_name Value
+----------------------------+--------------+
Aborted_clients 37546
Aborted_connects 0
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 3735900282
Bytes_sent 172978797578
Com_admin_commands 22026
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 1803281
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 2
Com_dealloc_sql 0
Com_delete 83717
Com_delete_multi 6
Com_do 0
Com_drop_db 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 2
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 909207
Com_insert_select 2
Com_kill 0
Com_load 0
Com_load_master_data 0
[#AAC-833] we need changes in our my.cnf ASAFP - ASAP - The Aptana ::
we need access to my.cnf so we can configure our db. right now our service is after some time (maybe 1-2 days), analysis the slow.log and optimize the
http://support.aptana.com/asap/browse/AAC-833HOME |
Com_load_master_table 0
Com_lock_tables 19448
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 1
Com_replace 28193
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 3794458
Com_set_option 416264
Com_show_binlog_events 0
Com_show_binlogs 1
Com_show_charsets 5
Com_show_collations 5
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 0
Com_show_databases 4
Com_show_errors 0
Com_show_fields 522
Com_show_grants 2
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 2
Com_show_privileges 0
Com_show_processlist 2
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 7
Com_show_storage_engines 0
Com_show_tables 10
Com_show_variables 43
Com_show_warnings 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_prepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 3
Com_unlock_tables 19448
Com_update 956455
Com_update_multi 2
Connections 1781258
Created_tmp_disk_tables 12864
Created_tmp_files 226
Created_tmp_tables 71959
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 38989
Handler_discover 0
Handler_read_first 1123452
Handler_read_key 82603346
Handler_read_next 702459784
Handler_read_prev 23386613
Handler_read_rnd 7986571
Handler_read_rnd_next 52997766962
Handler_rollback 0
Handler_update 2272448
Handler_write 1698108
Key_blocks_not_flushed 9
Key_blocks_unused 424513
Key_blocks_used 432855
Key_read_requests 341729940
Key_reads 433396
Key_write_requests 3772163
Key_writes 2981026
Max_used_connections 595
Not_flushed_delayed_rows 0
Open_files 3673
Open_streams 0
Open_tables 3702
Opened_tables 4004
Qcache_free_blocks 25246
Qcache_free_memory 289392976
Qcache_hits 2380395
Qcache_inserts 3563936
Qcache_hits 2380395
Qcache_inserts 3563936
Qcache_lowmem_prunes 0
Qcache_not_cached 211438
Qcache_queries_in_cache 59084
Qcache_total_blocks 144524
Questions 12174337
Rpl_status NULL
Select_full_join 15
Select_full_range_join 0
Select_range 991047
Select_range_check 0
Select_scan 480508
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 71
Sort_merge_passes 113
Sort_range 822340
Sort_rows 15532418
Sort_scan 34119
Table_locks_immediate 9485765
Table_locks_waited 1735105
Threads_cached 273
Threads_connected 269
Threads_created 663
Threads_running 119
Uptime 23707
+----------------------------+--------------+
Uptime: 23707 Threads: 272 Questions: 12174341 Slow queries: 71 Opens: 4004 Flush tables: 1 Open tables: 3702 Queries per second avg: 513.534
mysqladmin Ver 8.41 Distrib 4.1.22, for portbld-freebsd7.0 on amd64
(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 4.1.22-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 6 hours 35 min 7 sec
Threads: 271 Questions: 12174348 Slow queries: 71 Opens: 4004 Flush tables: 1 Open tables: 3702 Queries per second avg: 513.534
+---------+----------------------+-----------------+------------------+---------+------+------------------+-----------------------------------------------------------------$
Id User Host db Command Time State Info $
+---------+----------------------+-----------------+------------------+---------+------+------------------+-----------------------------------------------------------------$
1777753 kazhi_kazhi 10.10.1.2:55001 kazhi_vbulletin Sleep 20 $
1778987 kazhi_kazhi 10.10.1.3:53402 kazhi_vbulletin Sleep 20 $
1779205 kazhi_kazhi 10.10.1.2:55997 kazhi_vbulletin Sleep 5 $
1779336 kazhi_kazhi 10.10.1.2:56097 kazhi_vbulletin Sleep 19 $
1779354 kazhi_kazhi 10.10.1.2:56115 kazhi_vbulletin Sleep 19 $
1779478 kazhi_kazhi 10.10.1.3:53559 kazhi_vbulletin Sleep 19 $
1779483 kazhi_kazhi 10.10.1.2:56201 kazhi_vbulletin Sleep 18 $
1779594 kazhi_kazhi 10.10.1.2:56297 kazhi_vbulletin Sleep 1 $
1779666 kazhi_kazhi 10.10.1.2:56347 kazhi_vbulletin Query 7 Sending data SELECT COUNT(*) AS total FROM post_thanks WHERE userid = 333728 $
1779742 kazhi_kazhi 10.10.1.2:56402 kazhi_vbulletin Sleep 14 $
1779762 kazhi_kazhi 10.10.1.2:56420 kazhi_vbulletin Sleep 14 $
1779742 kazhi_kazhi 10.10.1.2:56402 kazhi_vbulletin Sleep 14 $
1779762 kazhi_kazhi 10.10.1.2:56420 kazhi_vbulletin Sleep 14 $
1779860 kazhi_kazhi 10.10.1.2:56496 kazhi_vbulletin Query 7 Sending data SELECT COUNT(*) AS total FROM post_thanks WHERE userid = 203358 $
1779966 kazhi_kazhi 10.10.1.3:53707 kazhi_vbulletin Sleep 10 $
1779989 kazhi_kazhi 10.10.1.2:56559 kazhi_vbulletin Sleep 10 $
1780065 kazhi_kazhi 10.10.1.3:53748 kazhi_vbulletin Query 7 Sending data SELECT COUNT(*) AS total FROM post_thanks WHERE userid = 398508 $
1780089 kazhi_kazhi 10.10.1.2:56606 kazhi_vbulletin Sleep 10 $
1780103 kazhi_kazhi 10.10.1.3:53779 kazhi_vbulletin Sleep 10 $
1780149 kazhi_kazhi 10.10.1.3:53807 kazhi_vbulletin Sleep 10 $
1780168 kazhi_kazhi 10.10.1.3:53818 kazhi_vbulletin Query 4 Opening tables UPDATE session
SET lastactivity = 1215701837, location = '/showthread.php?t=906361', inthread =
1780175 kazhi_kazhi 10.10.1.3:53822 kazhi_vbulletin Query 6 Sending data SELECT COUNT(*) AS total FROM post_thanks WHERE userid = 125825 $
1780201 kazhi_kazhi 10.10.1.3:53839 kazhi_vbulletin Sleep 9 $
1780214 kazhi_kazhi 10.10.1.2:56657 kazhi_vbulletin Sleep 9 $
1780242 kazhi_kazhi 10.10.1.2:56681 kazhi_vbulletin Query 1 Locked UPDATE user
SET lastactivity = 1215701837
WHERE userid = 421220
1780277 kazhi_kazhi 10.10.1.2:56714 kazhi_vbulletin Sleep 8 $
1780308 kazhi_kazhi 10.10.1.2:56745 kazhi_vbulletin Query 4 Sending data SELECT COUNT(*) AS total FROM post_thanks WHERE userid = 78673 A$
1780297 kazhi_kazhi 10.10.1.2:56734 kazhi_vbulletin Sleep 8 $
1780326 kazhi_kazhi 10.10.1.2:56762 kazhi_vbulletin Sleep 8 $
1780335 kazhi_kazhi 10.10.1.3:53850 kazhi_vbulletin Sleep 1 $
1780336 kazhi_kazhi 10.10.1.2:56771 kazhi_vbulletin Query 0 Locked UPDATE user
SET lastactivity = 1215701838
WHERE userid = 394207
1780405 kazhi_kazhi 10.10.1.2:56814 kazhi_vbulletin Sleep 8 $
1780412 kazhi_kazhi 10.10.1.3:53881 kazhi_vbulletin Sleep 7 $
1780421 kazhi_kazhi 10.10.1.3:53890 kazhi_vbulletin Sleep 7 $
1780423 kazhi_kazhi 10.10.1.2:56818 kazhi_vbulletin Query 2 Sending data SELECT COUNT(*) AS total FROM post_thanks WHERE userid = 308610 $
1780466 kazhi_kazhi 10.10.1.2:56845 kazhi_vbulletin Sleep 6 $
1780504 kazhi_kazhi 10.10.1.2:56955 kazhi_vbulletin Query 3 Sending data SELECT COUNT(*) AS total FROM post_thanks WHERE userid = 477217 $
1780565 kazhi_kazhi 10.10.1.3:53984 kazhi_vbulletin Query 1 Locked UPDATE user
SET lastactivity = 1215701840
WHERE userid = 27473
1780569 kazhi_kazhi 10.10.1.2:57012 kazhi_vbulletin Query 1 Locked UPDATE user
SET lastactivity = 1215701840
WHERE userid = 501440
1780592 kazhi_kazhi 10.10.1.2:57023 kazhi_vbulletin Sleep 6 $
1780587 kazhi_kazhi 10.10.1.2:57018 kazhi_vbulletin Sleep 6 $
1780603 kazhi_kazhi 10.10.1.3:54006 kazhi_vbulletin Query 3 Sending data SELECT COUNT(*) AS total FROM post_thanks WHERE userid = 501377 $
1780619 kazhi_kazhi 10.10.1.2:57038 kazhi_vbulletin Query 2 Sending data SELECT COUNT(*) AS total FROM post_thanks WHERE userid = 129423 $
1780611 kazhi_kazhi 10.10.1.2:57030 kazhi_vbulletin Sleep 5 $
1780657 kazhi_kazhi 10.10.1.2:57067 kazhi_vbulletin Sleep 5 $
1780654 kazhi_kazhi 10.10.1.2:57064 kazhi_vbulletin Sleep 1 $
1780646 kazhi_kazhi 10.10.1.3:54017 kazhi_vbulletin Query 0 Locked UPDATE user
SET lastactivity = 1215701841
WHERE userid = 273891
1780682 kazhi_kazhi 10.10.1.2:57088 kazhi_vbulletin Sleep 5 $
1780719 kazhi_kazhi 10.10.1.3:54037 kazhi_vbulletin Query 0 Locked UPDATE user
SET lastactivity = 1215701841
WHERE userid = 318766
1780728 kazhi_kazhi 10.10.1.3:54044 kazhi_vbulletin Sleep 5 $
1780748 kazhi_kazhi 10.10.1.2:57129 kazhi_vbulletin Sleep 5 $
1780753 kazhi_kazhi 10.10.1.2:57131 kazhi_vbulletin Sleep 5 $
1780766 kazhi_kazhi 10.10.1.3:54059 kazhi_vbulletin Sleep 5 $
1781158 kazhi_kazhi 10.10.1.3:54207 kazhi_vbulletin Query 1 Locked SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS
1781157 kazhi_kazhi 10.10.1.2:57389 kazhi_vbulletin Sleep 0 $
1781156 kazhi_kazhi 10.10.1.2:57388 kazhi_vbulletin Query 0 Locked SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS
1781151 kazhi_kazhi 10.10.1.2:57383 kazhi_vbulletin Sleep 0 $
1781149 kazhi_kazhi 10.10.1.2:57382 kazhi_vbulletin Query 0 Locked SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF
1781148 kazhi_kazhi 10.10.1.2:57381 kazhi_vbulletin Sleep 0 $
1781146 kazhi_kazhi 10.10.1.2:57379 kazhi_vbulletin Query 0 Locked SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF
1781145 kazhi_kazhi 10.10.1.2:57378 kazhi_vbulletin Query 1 Locked SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF
1781144 kazhi_kazhi 10.10.1.3:54205 kazhi_vbulletin Query 1 Locked SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF
1781143 kazhi_kazhi 10.10.1.2:57377 kazhi_vbulletin Sleep 0 $
1781136 kazhi_kazhi 10.10.1.2:57371 kazhi_vbulletin Query 0 Locked SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF
1781133 kazhi_kazhi 10.10.1.2:57368 kazhi_vbulletin Query 0 Locked SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF
1781130 kazhi_kazhi 10.10.1.2:57365 kazhi_vbulletin Query 0 Locked SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroup
1781129 kazhi_kazhi 10.10.1.2:57364 kazhi_vbulletin Query 0 Locked SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF
1781128 kazhi_kazhi 10.10.1.2:57363 kazhi_vbulletin Sleep 0 $
1781127 kazhi_kazhi 10.10.1.2:57362 kazhi_vbulletin Query 0 Locked SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS
1781126 kazhi_kazhi 10.10.1.2:57361 kazhi_vbulletin Query 0 Locked SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF
1781124 kazhi_kazhi 10.10.1.3:54202 kazhi_vbulletin Query 1 Locked SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF
1781169 kazhi_kazhi 10.10.1.3:54211 kazhi_vbulletin Sleep 1 $
1781166 kazhi_kazhi 10.10.1.3:54209 kazhi_vbulletin Query 1 Locked SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF
1781170 kazhi_kazhi 10.10.1.3:54212 kazhi_vbulletin Sleep 1 $
1781173 kazhi_kazhi 10.10.1.3:54214 kazhi_vbulletin Sleep 1 $
1781172 kazhi_kazhi 10.10.1.3:54213 kazhi_vbulletin Sleep 1 $
1781171 kazhi_kazhi 10.10.1.2:57397 kazhi_vbulletin Sleep 1 $
1781174 kazhi_kazhi 10.10.1.2:57398 kazhi_vbulletin Sleep 1 $
1781177 kazhi_kazhi 10.10.1.3:54216 kazhi_vbulletin Sleep 1 $
1781175 kazhi_kazhi 10.10.1.2:57399 kazhi_vbulletin Query 0 Locked SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
Yes
10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
Average 7000 users Max 12.000, Cookie timeout 2000
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://119.110.77.2/php.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 :
Not Apache, it's Litespeed
Try the following in this exact order. You can ignore any of the suggestions that you have already done.
1. Edit mysql server's /etc/my.cnf or c:my.ini for windows and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards. Make sure to restart mysql server everytime you make changes to your my.cnf for the changes to take effect.
If mysql doesn't restart properly after my.cnf changes and you're on VPS server, make sure skip-innodb entry is removed or commented out from below my.cnf
[mysqld]
socket=/tmp/mysql.sock
skip-name-resolve
safe-show-database
#old_passwords
back_log = 50
skip-innodb
max_connections = 650
key_buffer_size = 768M
myisam_sort_buffer_size = 256M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 16M
table_cache = 5000
thread_cache_size = 768
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 16
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 3M
bulk_insert_buffer_size = 64M
query_cache_limit = 9M
query_cache_size = 384M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 16
[mysql.server]
user=mysql
#basedir=/var/lib
[mysqld_safe]
open_files_limit=8192
log-error=/var/log/mysqld.log
nice = -10
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 64M
[myisamchk]
key_buffer = 1024M
sort_buffer = 256M
read_buffer = 64M
write_buffer = 64M
If you get mysql server gone away error messages, then keep increasing wait_timeout value in my.cnf by 60 second increments, then restart mysql after my.cnf changes for it to take effect. Test for a few days and see if you get less or eliminate that error message. If it still occurs, then keep repeating the 60 second increment until the message goes away. Each vB forum and server will have different optimal wait_timeout values depending on your vB forum traffic patterns and server hardware specifications.
2. Remove Eaccelerator or APC Cache if they're installed (check phpinfo.php url of yours to see) and instead install Xcache v1.2.1 final release or Xcache v1.2.2 http://xcache.lighttpd.net/wiki/Release-1.2.2 which seems to be a bit faster than APC Cache - http://www.vbulletin.com/forum/showthread.php?t=213267. Xcache site http://xcache.lighttpd.net/, documentation http://trac.lighttpd.net/trac/wiki/Docs and forums http://forum.lighttpd.net/forum/4
Remember to set in php.ini the values for xcache.size to 32M or 64M and for xcache.count to a value of equal to number of processor cores you have so single core cpu = 1 or single dual core cpu = 2 or single quad core cpu = 4 or dual cpus each with dual core = 4 or dual cpus each with quad core = 8.
3. Upgrade to vB 3.0.17 http://www.vbulletin.com/forum/showthread.php?t=209720 if you're on vB 3.0.xx or upgrade to vB 3.5.8 http://www.vbulletin.com/forum/showthread.php?t=221903 if you're on vB 3.5.x. Or if on vB 3.6.x, upgrade to vB 3.6.10 PL3 http://www.vbulletin.com/forum/showthread.php?t=277945.
But ultimately, the latest stable vB 3.7.2 PL1 is highly recommended http://www.vbulletin.com/forum/showthread.php?postid=1585047. You can use my method of upgrading outlined at http://www.vbulletin.com/forum/showthread.php?t=187770 which is essentially same in that you make a copy of your live database and import it into a new empty database and point vB 3.7.0 config.php to that new imported database name, so you essentially do an upgrade on a copy of your database, leaving original database intact in case of any problems. This method also allows you to run the original database on a different directory so to run both original forum/database along side the upgraded forum/database so you can easily revert all changed templates on upgraded forum and then using old forum/database transfer or port your custom style/images etc to the new upgrade database.
Read each versions listed thread to understand the changes that have occured etc.
4. If you just upgraded to vB 3.5.x/3.6.x try to disable these 4 options:
Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?
Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums
Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread
Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search
Or relevant sections in vB 3.7.x
5. 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.
6. If you have split web + db servers, ensure web server has dual gigabit 1000Mbps speed network cards as outlined at http://www.vbulletin.org/forum/showthread.php?t=111191
 Where's The Advantage In Windows Genuine Advantage?
 Stocks Bounce After S&P Joins Bear Market |
Optimize the Database Server my.cnf please