Server Optimization - Need Help - Urgent

[lovelypk]

My forum usually have 50-100 users online anytime and server remains under 1. But once or twice a week i get much traffic due to some events on forum. user onlines goes upto 500. and in that time server loads remain above 30-40 if i keep restarting apache, if i dont restart apache then server goes above 100 and gets un responsive.
I am going to have over 500 users online in 5 hours. So i want help asap so i can prepare the server for it.

1: Dedicated Server
2: cpu speed/type single or dual cpus): Intel(R) Xeon(TM) CPU 2.40GHz how much memory installed: 2 GB ram
hard drive type/configuration: 250 GB non raid
linux distributor or windows version: CentOS 4.5 i686 with Cpanel
apache/IIS version: Apache/1.3.37
PHP version: php 4.4.7
MySQL version: mysql 4.1.21

3: 3.6.5
4. Don't know
5: Cpanel/WHM
6: Non-Peak Time. Server load remains up to 40 in peak time.
top - 13:54:26 up 3:52, 1 user, load average: 0.35, 0.30, 0.72
Tasks: 137 total, 1 running, 135 sleeping, 1 stopped, 0 zombie
Cpu(s): 44.6% us, 5.4% sy, 0.3% ni, 42.6% id, 4.5% wa, 2.6% hi, 0.0% si
Mem: 2073848k total, 1830132k used, 243716k free, 58688k buffers
Swap: 4096564k total, 122224k used, 3974340k free, 1262280k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
30252 nobody 16 0 34648 21m 2900 S 68 1.1 0:28.73 httpd
28136 mysql 10 -5 102m 44m 2640 S 33 2.2 8:26.69 mysqld
31738 root 15 0 3496 900 684 R 2 0.0 0:00.01 top
1 root 16 0 2252 456 424 S 0 0.0 0:09.24 init
2 root RT 0 0 0 0 S 0 0.0 0:00.02 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.72 migration/1
5 root 34 19 0 0 0 S 0 0.0 0:00.04 ksoftirqd/1
6 root RT 0 0 0 0 S 0 0.0 0:01.31 migration/2
7 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/2
8 root RT 0 0 0 0 S 0 0.0 0:00.02 migration/3
9 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/3
10 root 5 -10 0 0 0 S 0 0.0 0:00.14 events/0
11 root 5 -10 0 0 0 S 0 0.0 0:03.85 events/1
12 root 5 -10 0 0 0 S 0 0.0 0:06.64 events/2
13 root 5 -10 0 0 0 S 0 0.0 0:00.15 events/3
14 root 6 -10 0 0 0 S 0 0.0 0:00.01 khelper


7:
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user = mysql
safe-show-database
old_passwords
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 48M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 2000
thread_cache_size = 384
wait_timeout = 35
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 2M
query_cache_size = 24M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM

[mysqld_safe]
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout
8
| read_rnd_buffer_size | 520192 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| 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 | MST |
| table_cache | 2000 |
| table_type | MyISAM |
| thread_cache_size | 384 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 67108864 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.21-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 35 |
+---------------------------------+------------------------------------------+
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 8460 |
| Bytes_sent | 1731580 |
| Com_admin_commands | 1 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 2 |
| 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 | 10 |
| 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 | 1 |
| Com_show_variables | 1 |
| 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 | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Connections | 6 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 0 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_read_first | 6 |
| Handler_read_key | 591 |
| Handler_read_next | 195 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 142 |
| Handler_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 43488 |
| Key_blocks_used | 124 |
| Key_read_requests | 5069 |
| Key_reads | 124 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 2 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 44 |
| Open_streams | 0 |
| Open_tables | 23 |
| Opened_tables | 29 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 24276184 |
| Qcache_hits | 10 |
| Qcache_inserts | 10 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 10 |
| Qcache_total_blocks | 39 |
| Questions | 29 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 3 |
| Select_range_check | 0 |
| Select_scan | 2 |
| 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 | 3 |
| Sort_scan | 1 |
| Table_locks_immediate | 29 |
| Table_locks_waited | 0 |
| Threads_cached | 1 |
| Threads_connected | 1 |
| Threads_created | 2 |
| Threads_running | 1 |
| Uptime | 11 |
+----------------------------+----------+
Uptime: 11 Threads: 1 Questions: 30 Slow queries: 0 Opens: 29 Flush tables: 1 Open tables: 23 Queries per second avg: 2.727
mysqladmin Ver 8.41 Distrib 4.1.21, for pc-linux-gnu on i686
Copyright (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.21-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 11 sec

Threads: 1 Questions: 30 Slow queries: 0 Opens: 29 Flush tables: 1 Open tables: 23 Queries per second avg: 2.727

9: few other sites on server as well but all of them have very low traffic.

10:
average 60-100
Max: 500-600

11: http://www.preattach.com/phpinfo.php
12:

KeepAlive: on
MaxKeepAliveRequests: 150
KeepAliveTimeout: 4
MinSpareServers: 15
MaxSpareServers: 20
StartServers: 15
MaxClients: 400
Maxrequestsperchild value = 1000

14:
uname -a
Linux tx.lovelyhosting.com 2.6.9-42.0.10.ELsmp #1 SMP Tue Feb 27 10:11:19 EST 2007 i686 i686 i386 GNU/Linux

ulimit -aH
core file size (blocks, -c) 1000000
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 4096
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 14335
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.40GHz
stepping : 5
cpu MHz : 2405.859
cache size : 512 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips : 4814.39

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.40GHz
stepping : 5
cpu MHz : 2405.859
cache size : 512 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips : 4810.29

processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.40GHz
stepping : 5
cpu MHz : 2405.859
cache size : 512 KB
physical id : 3
siblings : 2
core id : 3
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips : 4810.26

processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.40GHz
stepping : 5
cpu MHz : 2405.859
cache size : 512 KB
physical id : 3
siblings : 2
core id : 3
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips : 4810.32

[lovelypk]

anyone?

[eva2000]

could you repost output for these 2 commands

top

mysqladmin -u root -p var ext ver > stats.txt

open the stats.txt file to copy and paste it's contents.

reason is the mysql status output you provided is only for 11 seconds uptime.. no where near long enough to guage your server's mysql load

note, don't restart mysql server as that will reset the status output for mysql. Restarting apache is fine but don't restart mysql server.

Ideally you want the stats outputted near peak forum activity time or after a peak period has finished..

[lovelypk]

top - 08:03:27 up 22 min, 1 user, load average: 7.16, 6.73, 5.23
Tasks: 241 total, 1 running, 240 sleeping, 0 stopped, 0 zombie
Cpu(s): 61.7% us, 4.3% sy, 0.0% ni, 33.1% id, 0.2% wa, 0.7% hi, 0.0% si
Mem: 2073848k total, 2056828k used, 17020k free, 21676k buffers
Swap: 4096564k total, 0k used, 4096564k free, 444244k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8588 nobody 17 0 29964 16m 2584 S 44 0.8 0:05.65 httpd
8586 nobody 15 0 31496 18m 2580 S 32 0.9 0:02.57 httpd
8589 nobody 16 0 31472 18m 2672 S 29 0.9 0:01.99 httpd
7188 nobody 15 0 31260 18m 3132 S 29 0.9 0:38.89 httpd
7185 nobody 15 0 31664 18m 2728 S 17 0.9 0:43.12 httpd
8605 nobody 15 0 30268 16m 2604 S 17 0.8 0:02.60 httpd
8250 mysql 10 -5 176m 29m 2576 S 12 1.5 1:14.99 mysqld
7176 nobody 16 0 32452 19m 2712 S 8 0.9 0:33.54 httpd
7239 nobody 15 0 31228 18m 2692 S 7 0.9 0:43.76 httpd
8345 nobody 15 0 31260 17m 2620 S 6 0.9 0:12.00 httpd
7178 nobody 15 0 31652 18m 2696 S 3 0.9 0:42.98 httpd
7229 nobody 15 0 31308 18m 2720 S 3 0.9 0:27.62 httpd
7247 nobody 15 0 31292 18m 2712 S 3 0.9 0:27.35 httpd
7219 nobody 15 0 31308 18m 2712 S 3 0.9 0:41.00 httpd
7211 nobody 15 0 31436 18m 2712 S 3 0.9 0:35.34 httpd
8585 nobody 15 0 21808 8760 2520 S 3 0.4 0:00.47 httpd
8604 nobody 15 0 30532 17m 2584 S 3 0.9 0:02.13 httpd




+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| back_log | 75 |
| basedir | / |
| 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/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| 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 |
| 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_ndbcluster | NO |
| have_merge_engine | YES |
| 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 |
| 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 | 1044480 |
| key_buffer_size | 67108864 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/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 | OFF |
| log_error | |
| 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 | 67107840 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 500 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 67107840 |
| 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_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| 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 | ON |
| open_files_limit | 4510 |
| pid_file | /var/lib/mysql/tx.lovelyhosting.com.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 32768 |
| query_cache_limit | 3145728 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 83886080 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 163840 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| 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 | MST |
| table_cache | 2000 |
| table_type | MyISAM |
| thread_cache_size | 384 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 67108864 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.21-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 35 |
+---------------------------------+------------------------------------------+
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| Aborted_clients | 46 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 6896110 |
| Bytes_sent | 902507417 |
| Com_admin_commands | 1 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 4145 |
| 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 | 120 |
| 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 | 296 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 1 |
| 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 | 96 |
| 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 | 9092 |
| Com_set_option | 3 |
| 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 | 1 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 1 |
| Com_show_variables | 2 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 1 |
| Com_update | 3376 |
| Com_update_multi | 0 |
| Connections | 4122 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 659 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 43 |
| Handler_discover | 0 |
| Handler_read_first | 3045 |
| Handler_read_key | 397796 |
| Handler_read_next | 164823 |
| Handler_read_prev | 8365 |
| Handler_read_rnd | 2287 |
| Handler_read_rnd_next | 6019282 |
| Handler_rollback | 0 |
| Handler_update | 3347 |
| Handler_write | 18160 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 56341 |
| Key_blocks_used | 1649 |
| Key_read_requests | 726554 |
| Key_reads | 1649 |
| Key_write_requests | 348 |
| Key_writes | 282 |
| Max_used_connections | 60 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 318 |
| Open_streams | 0 |
| Open_tables | 177 |
| Opened_tables | 183 |
| Qcache_free_blocks | 224 |
| Qcache_free_memory | 78817432 |
| Qcache_hits | 14913 |
| Qcache_inserts | 8878 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 214 |
| Qcache_queries_in_cache | 627 |
| Qcache_total_blocks | 1655 |
| Questions | 36133 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 1957 |
| Select_range_check | 0 |
| Select_scan | 2483 |
| 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 | 327 |
| Sort_rows | 258699 |
| Sort_scan | 647 |
| Table_locks_immediate | 24153 |
| Table_locks_waited | 87 |
| Threads_cached | 46 |
| Threads_connected | 14 |
| Threads_created | 60 |
| Threads_running | 1 |
| Uptime | 448 |
+----------------------------+-----------+
mysqladmin Ver 8.41 Distrib 4.1.21, for pc-linux-gnu on i686
Copyright (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.21-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 7 min 28 sec

Threads: 14 Questions: 36134 Slow queries: 0 Opens: 183 Flush tables: 1 Open tables: 177 Queries per second avg: 80.656

[eva2000]

448 seconds uptime still not enough to show real mysql statistics or your mysql related loads... from what i can only assume your server isn't able to handle the peak forum periods and may need upgrading the server and/or splitting to a 2nd dual cpu dedicated server to handle mysql by itself i.e. dedicated web/php + dedicated mysql server setup.

[lovelypk]

448 seconds uptime still not enough to show real mysql statistics or your mysql related loads... from what i can only assume your server isn't able to handle the peak forum periods and may need upgrading the server and/or splitting to a 2nd dual cpu dedicated server to handle mysql by itself i.e. dedicated web/php + dedicated mysql server setup.
i will try to get the mysql stats again near peak time. before i had to restart mysql to change it's password from whm.

I don't get that much traffic all the time, so i cant really afford more high end server :(

[lovelypk]

It's not a peak time but around peak time and have 150 users online on forum and maybe another 100 on static html pages.

Top
top - 09:49:13 up 49 min, 1 user, load average: 2.15, 1.69, 1.45
Mem: 2073848k total, 1574804k used, 499044k free, 58812k buffers
Swap: 4096564k total, 0k used, 4096564k free, 686348k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4958 root 16 0 36436 8368 3908 S 0 0.4 41:07.86 httpd
5627 mysql 10 -5 97564 31m 2592 S 22 1.6 4:30.60 mysqld
4897 root 38 19 18204 13m 1732 S 0 0.7 2:21.48 cpanellogd
4404 root 16 0 9800 5480 808 S 0 0.3 1:45.46 lfd
1 root 16 0 3628 548 468 S 0 0.0 0:35.90 init
4766 root 15 0 4596 1436 1156 S 0 0.1 0:33.17 bash
3108 named 18 0 71756 6916 1932 S 0 0.3 0:17.50 named
368 root 15 0 0 0 0 S 2 0.0 0:16.15 kjournald
11559 nobody 16 0 44256 18m 6588 S 1 0.9 0:08.94 httpd
3386 root 16 0 12860 8092 1108 S 0 0.4 0:07.52 chkservd
11576 nobody 16 0 44748 19m 6844 S 0 0.9 0:06.14 httpd
11533 nobody 15 0 44128 17m 5764 S 0 0.9 0:06.02 httpd
11570 nobody 15 0 44192 20m 8700 S 0 1.0 0:05.97 httpd
11535 nobody 15 0 44728 19m 6880 S 19 0.9 0:05.72 httpd
11547 nobody 15 0 43744 17m 6644 S 0 0.9 0:05.70 httpd
11542 nobody 15 0 44032 18m 6560 S 0 0.9 0:05.48 httpd
11566 nobody 16 0 44496 19m 7652 S 0 1.0 0:05.22 httpd
11554 nobody 16 0 47668 23m 7872 S 0 1.1 0:04.84 httpd
11539 nobody 16 0 44544 18m 6488 S 0 0.9 0:04.73 httpd

+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| back_log | 75 |
| basedir | / |
| 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/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| 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 |
| 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_ndbcluster | NO |
| have_merge_engine | YES |
| 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 |
| 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 | 1044480 |
| key_buffer_size | 50331648 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/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 | OFF |
| log_error | |
| 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 | 67107840 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 500 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 67107840 |
| 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_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| 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 | ON |
| open_files_limit | 10000 |
| pid_file | /var/lib/mysql/tx.lovelyhosting.com.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 32768 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 25165824 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 163840 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| 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 | MST |
| table_cache | 2000 |
| table_type | MyISAM |
| thread_cache_size | 384 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 67108864 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.21-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 35 |
+---------------------------------+------------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 92 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 16089964 |
| Bytes_sent | 2496817762 |
| Com_admin_commands | 11 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 5520 |
| 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 | 375 |
| 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 | 609 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 4 |
| 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 | 380 |
| 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 | 19613 |
| Com_set_option | 13 |
| 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 | 9 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 1 |
| Com_show_variables | 3 |
| 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 | 0 |
| Com_unlock_tables | 4 |
| Com_update | 4962 |
| Com_update_multi | 3 |
| Connections | 5495 |
| Created_tmp_disk_tables | 19 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 2263 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 255 |
| Handler_discover | 0 |
| Handler_read_first | 2496 |
| Handler_read_key | 1143244 |
| Handler_read_next | 509562 |
| Handler_read_prev | 4509 |
| Handler_read_rnd | 7883 |
| Handler_read_rnd_next | 20000363 |
| Handler_rollback | 0 |
| Handler_update | 5672 |
| Handler_write | 53687 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 39964 |
| Key_blocks_used | 3648 |
| Key_read_requests | 2205239 |
| Key_reads | 3651 |
| Key_write_requests | 1588 |
| Key_writes | 816 |
| Max_used_connections | 15 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 666 |
| Open_streams | 0 |
| Open_tables | 366 |
| Opened_tables | 372 |
| Qcache_free_blocks | 259 |
| Qcache_free_memory | 16033976 |
| Qcache_hits | 38337 |
| Qcache_inserts | 18804 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 807 |
| Qcache_queries_in_cache | 1577 |
| Qcache_total_blocks | 3683 |
| Questions | 75280 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 5792 |
| Select_range_check | 0 |
| Select_scan | 7933 |
| 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 | 1104 |
| Sort_rows | 708544 |
| Sort_scan | 1826 |
| Table_locks_immediate | 43607 |
| Table_locks_waited | 109 |
| Threads_cached | 11 |
| Threads_connected | 4 |
| Threads_created | 15 |
| Threads_running | 2 |
| Uptime | 2594 |
+----------------------------+------------+
mysqladmin Ver 8.41 Distrib 4.1.21, for pc-linux-gnu on i686
Copyright (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.21-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 43 min 14 sec

Threads: 4 Questions: 75281 Slow queries: 0 Opens: 372 Flush tables: 1 Open tables: 366 Queries per second avg: 29.021

[eva2000]

You're swapping to disk so might need at least another 1GB of additional memory installed on your server. But before you do any upgrades, try the following in this exact order. You can ignore any of the suggestions that you have already done.

1. Upgrade MySQL server to 4.1.22
2. Upgrade PHP to 4.4.7 and/or recompile PHP 4.4.7 after upgrading MySQL (ensure you install as ISAPI and NOT CGI)
3. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards


[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
safe-show-database
old_passwords
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 48M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 2000
thread_cache_size = 384
wait_timeout = 25
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 4
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 2M
query_cache_size = 16M
query_cache_type = 1
query_prealloc_size = 131072
query_alloc_block_size = 65536
default-storage-engine = MyISAM

[mysqld_safe]
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout


4. Install APC Cache lastest version from http://pecl.php.net/package/APC. Read install guide at http://www.vbulletin.com/forum/showthread.php?t=165367 - please remove Zend Optimizer from php.ini before installing APC Cache. OR try the alternative PHP caching software, Xcache which seems to be a bit faster than APC Cache - http://www.vbulletin.com/forum/showthread.php?t=213267

5. Upgrade to vB 3.6.7 http://www.vbulletin.com/forum/showthread.php?t=229950 if you're on vB 3.6.x

6. If you just upgraded to vB 3.5.x/3.6.x try to disable these 4 options:

Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?

Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums

Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread

Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search

7. Edit httpd.conf values to following and restart apache

KeepAlive: On
MaxKeepAliveRequests: 100
KeepAliveTimeout: 1
MinSpareServers: 10
MaxSpareServers: 15
StartServers: 10
MaxClients: 150
MaxRequestsPerChild: 1000