Request for optimize my server :)

[unnpro]

Dedicated server
FreeBSD 6.0-RELEASE #28
PHP Version 5.2.2
Apache v2.2.4
MySQL 5.0.27
--
2 GB RAM
Dual Intel(R) Xeon(TM) CPU 3.00GHz
HD 110 GB

vBulletin 3.6.7 PL1

top
last pid: 36867; load averages: 5.91, 5.81, 6.98 up 7+18:20:14 15:33:12
97 processes: 15 running, 82 sleeping
CPU states: 69.5% user, 0.0% nice, 14.8% system, 0.4% interrupt, 15.3% idle
Mem: 342M Active, 529M Inact, 227M Wired, 240K Cache, 112M Buf, 903M Free
Swap: 4096M Total, 129M Used, 3966M Free, 3% Inuse

PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
36775 www 1 80 0 29736K 20468K RUN 3 0:04 7.47% httpd
36758 www 1 81 0 30000K 20756K RUN 0 0:05 6.74% httpd
36767 www 1 79 0 30252K 20984K select 2 0:04 6.74% httpd
36660 www 1 81 0 29764K 20500K select 0 0:07 6.01% httpd
36762 www 1 81 0 29728K 20460K select 0 0:04 6.01% httpd
36832 www 1 80 0 29672K 20428K select 0 0:01 6.01% httpd
36654 www 1 4 0 46036K 36716K sbwait 0 0:06 5.22% httpd
36607 www 1 4 0 43860K 34632K sbwait 0 0:08 4.49% httpd
36815 www 1 79 0 34728K 25464K select 0 0:02 4.49% httpd
36819 www 1 78 0 29728K 20460K select 0 0:01 4.49% httpd
36608 www 1 77 0 29856K 20560K select 2 0:09 3.71% httpd
36671 www 1 4 0 43908K 34664K sbwait 0 0:08 3.71% httpd
36759 www 1 78 0 31720K 22444K RUN 2 0:04 3.71% httpd
36734 www 1 77 0 36620K 27332K CPU2 2 0:03 3.71% httpd
36807 www 1 79 0 29700K 20400K select 0 0:01 3.71% httpd
36844 www 1 81 0 29688K 20412K select 0 0:01 3.71% httpd
36687 www 1 79 0 29688K 20432K select 1 0:05 2.98% httpd
36769 www 1 76 0 29740K 20472K select 2 0:03 2.98% httpd
36575 www 1 76 0 30236K 20976K select 2 0:10 2.25% httpd
36683 www 1 77 0 29720K 20488K select 1 0:06 2.25% httpd
36613 www 1 77 0 42604K 33344K RUN 3 0:05 2.25% httpd
36764 www 1 4 0 43784K 34512K sbwait 3 0:04 2.25% httpd
36813 www 1 77 0 29776K 20532K select 0 0:02 2.25% httpd
36821 www 1 4 0 43348K 34084K sbwait 3 0:01 2.25% httpd
36763 www 1 78 0 29740K 20464K select 2 0:01 2.25% httpd
36509 www 1 77 0 29712K 20428K select 3 0:10 1.46% httpd
36765 www 1 20 0 29672K 20416K lockf 0 0:04 1.46% httpd
36790 www 1 20 0 29760K 20512K lockf 1 0:03 1.46% httpd
36816 www 1 76 0 29792K 20520K select 0 0:01 1.46% httpd
36843 www 1 77 0 30260K 20964K select 0 0:01 1.46% httpd
36861 www 1 4 0 43640K 34332K sbwait 2 0:00 1.46% httpd
36205 www 1 76 0 42388K 33044K CPU3 3 0:18 0.73% httpd
36701 www 1 76 0 29776K 20512K select 3 0:04 0.73% httpd
36789 www 1 76 0 29752K 20484K select 0 0:03 0.73% httpd
36791 www 1 76 0 30212K 20956K select 0 0:02 0.73% httpd
36818 www 1 76 0 29728K 20440K select 0 0:02 0.73% httpd
36812 www 1 76 0 29688K 20416K select 3 0:01 0.73% httpd
90356 mysql 18 20 0 114M 55588K kserel 1 20.5H 0.00% mysqld
50464 root 1 8 0 376K 176K nanslp 1 2:04 0.00% urchind


/etc/my.cnf :
[mysqld]
safe-show-database
old_passwords
back_log = 75
skip-innodb
max_connections = 250
key_buffer = 80M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 3000
thread_cache_size = 384
wait_timeout = 35
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 2
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 3M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 131072
query_alloc_block_size = 65536
default-storage-engine = MyISAM

[mysqld_safe]
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

mysqladmin -u root -p var ext ver > stats.txt
+---------------------------------+---------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/db/mysql/ |
| bdb_log_buffer_size | 32768 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /var/tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| datadir | /var/db/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | 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_ndbcluster | NO |
| have_merge_engine | YES |
| 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 | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_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 | 131072 |
| key_buffer_size | 8388600 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/share/mysql/italian/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 33553408 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 150 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| 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 | OFF |
| open_files_limit | 11095 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/db/mysql/teamworld.it.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| 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_compressed_protocol | OFF |
| slave_load_tmpdir | /var/tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 2097144 |
| 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 | CEST |
| table_cache | 64 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /var/tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.24-log |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (July 27, 2006) |
| version_comment | FreeBSD port: mysql-server-5.0.24 |
| version_compile_machine | i386 |
| version_compile_os | portbld-freebsd6.0 |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------------------------------+
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 14 |
| Aborted_connects | 1209 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 3987466963 |
| Bytes_sent | 1954464083 |
| 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 | 1363938 |
| 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 | 164 |
| Com_dealloc_sql | 0 |
| Com_delete | 241841 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 164 |
| 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 | 802208 |
| Com_insert_select | 164 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 45504 |
| Com_optimize | 3517 |
| 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 | 111787 |
| 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 | 13960837 |
| Com_set_option | 168 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 3 |
| Com_show_charsets | 42 |
| Com_show_collations | 42 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 1 |
| Com_show_databases | 15 |
| Com_show_errors | 0 |
| Com_show_fields | 28 |
| Com_show_grants | 26 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 24 |
| 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 | 4 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 60 |
| Com_show_triggers | 0 |
| Com_show_variables | 136 |
| 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 | 164 |
| Com_unlock_tables | 45504 |
| Com_update | 2447981 |
| Com_update_multi | 164 |
| 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 | 1365060 |
| Created_tmp_disk_tables | 20306 |
| Created_tmp_files | 125 |
| Created_tmp_tables | 1077261 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 434719 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1286974 |
| Handler_read_key | 686767903 |
| Handler_read_next | 362812941 |
| Handler_read_prev | 7201219 |
| Handler_read_rnd | 182807982 |
| Handler_read_rnd_next | 3945342123 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 3188700 |
| Handler_write | 176832181 |
| Innodb_buffer_pool_pages_data | 19 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 493 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 77 |
| Innodb_buffer_pool_reads | 12 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2494464 |
| Innodb_data_reads | 25 |
| Innodb_data_writes | 3 |
| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 19 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 7248 |
| Key_read_requests | 1338084336 |
| Key_reads | 14127932 |
| Key_write_requests | 5749121 |
| Key_writes | 4442030 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 151 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 101 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 1286838 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 20388414 |
| Rpl_status | NULL |
| Select_full_join | 213 |
| Select_full_range_join | 0 |
| Select_range | 5657025 |
| Select_range_check | 0 |
| Select_scan | 1837220 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 4 |
| Slow_queries | 866 |
| Sort_merge_passes | 41 |
| Sort_range | 2132991 |
| Sort_rows | 334749576 |
| Sort_scan | 683990 |
| 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 | 29943497 |
| Table_locks_waited | 340473 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 5 |
| Threads_created | 1365059 |
| Threads_running | 5 |
| Uptime | 589725 |
+-----------------------------------+------------+
mysqladmin Ver 8.41 Distrib 5.0.24, for portbld-freebsd6.0 on i386
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 5.0.24-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 6 days 19 hours 48 min 45 sec

Threads: 5 Questions: 20388415 Slow queries: 866 Opens: 1286838 Flush tables: 1 Open tables: 64 Queries per second avg: 34.573


max concurrent users 300 up to 700


KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 5
MinSpareServers 10
MaxSpareServers 15
MaxClients 1300
MaxRequestsPerChild 0
StartServers 20

Thanks in advance!

[eva2000]

You're above my.cnf settings don't match what your mysql server is actually using from mysqladmin variables output.. so ensure you have only one my.cnf on your server at /etc/my.cnf and it has the settings in it and mysql restarted for settings to take effect

Try the following in this exact order. You can ignore any of the suggestions that you have already done.

1. Upgrade to MySQL 5.0.27 or 5.0.41 as you're on 5.0.24a right now and upgrade PHP to 5.2.3 or if you have problems install PHP 4.4.7 (in either case install as ISAPI module and NO CGI)
2. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards


[mysqld]
back_log = 50
skip-innodb
max_connections = 650
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 3M
table_cache = 2000
thread_cache_size = 384
wait_timeout = 35
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 4
concurrent_insert = 2
table_lock_wait_timeout = 35
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 64M
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


3. 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 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

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

5. 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

6. then after a few days, 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.

[unnpro]

Thanks eva2000!
I've updated my httpd.conf and my.cnf settings and restarted Apache/MySQL.
Tomorrow I'll try to install APC Cache or Xcache (which one is the best? Xcache?).

[eva2000]

k... let us know how you do :)

[unnpro]

APC Cache installed!

[unnpro]

6. then after a few days, 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.

last pid: 14736; load averages: 2.83, 3.72, 4.30 up 5+11:51:42 15:02:32
283 processes: 1 starting, 1 running, 281 sleeping
CPU states: 36.0% user, 0.0% nice, 6.7% system, 0.2% interrupt, 57.1% idle
Mem: 752M Active, 1679M Inact, 182M Wired, 119M Cache, 112M Buf, 274M Free
Swap: 4096M Total, 368K Used, 4096M Free

PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
14396 www 1 78 0 74400K 41228K select 0 0:08 3.71% httpd
14540 www 1 79 0 64112K 30412K select 2 0:06 3.71% httpd
14669 www 1 77 0 63804K 25304K select 3 0:02 3.71% httpd
14549 www 1 79 0 63816K 31252K select 0 0:06 2.98% httpd
14667 www 1 76 0 63520K 24208K select 0 0:02 2.98% httpd
14627 www 1 77 0 62276K 25044K select 3 0:03 2.25% httpd
14654 www 1 20 0 63840K 28096K lockf 1 0:03 2.25% httpd
14735 www 1 78 0 62120K 22792K select 3 0:01 2.25% httpd
14365 www 1 20 0 63568K 30252K lockf 1 0:08 1.46% httpd
14460 www 1 77 0 76112K 39948K select 1 0:08 1.46% httpd
14589 www 1 20 0 62200K 24600K lockf 0 0:04 1.46% httpd
14580 www 1 77 0 62136K 22820K select 0 0:02 1.46% httpd
14731 www 1 20 0 62180K 23000K lockf 1 0:01 1.46% httpd
14728 www 1 77 0 62128K 22396K select 2 0:00 1.46% httpd
14623 www 1 76 0 63964K 26344K select 0 0:02 0.73% httpd
14715 www 1 20 0 62136K 22948K lockf 1 0:01 0.73% httpd
14712 www 1 20 0 63508K 27860K lockf 1 0:01 0.73% httpd
14725 www 1 77 0 62112K 22388K select 1 0:01 0.73% httpd
14733 www 1 77 0 62128K 22396K select 0 0:00 0.73% httpd
14727 www 1 76 0 62128K 22240K select 0 0:00 0.73% httpd
1092 mysql 156 20 0 245M 215M kserel 3 692:01 0.00% mysqld
38174 root 1 8 0 61864K 18784K nanslp 0 8:11 0.00% httpd
1123 root 1 8 0 376K 308K nanslp 0 1:30 0.00% urchind
1109 root 1 76 0 25192K 24220K select 0 1:09 0.00% perl5.8.7
931 root 1 76 0 4488K 2544K select 0 0:35 0.00% sendmail
14200 www 1 76 0 63512K 31456K select 0 0:15 0.00% httpd
14289 www 1 76 0 62316K 26636K select 0 0:10 0.00% httpd
869 root 1 76 0 1304K 776K select 2 0:08 0.00% syslogd
14353 www 1 20 0 63860K 30000K lockf 1 0:08 0.00% httpd
1038 clamav 1 4 0 39788K 36968K accept 2 0:08 0.00% clamd
14408 www 1 20 0 64216K 31148K lockf 0 0:08 0.00% httpd
71687 www 1 20 0 62292K 27068K lockf 3 0:07 0.00% httpd
71707 www 1 20 0 63728K 29068K lockf 3 0:05 0.00% httpd
14516 www 1 20 0 63592K 29144K lockf 1 0:05 0.00% httpd
13908 www 1 76 0 74436K 40016K select 2 0:05 0.00% httpd
14440 www 1 20 0 64096K 30316K lockf 1 0:04 0.00% httpd
14614 www 1 76 0 63524K 26208K select 2 0:04 0.00% httpd
82038 www 1 20 0 63484K 26320K lockf 2 0:04 0.00% httpd
48938 www 1 20 0 64024K 30332K lockf 2 0:04 0.00% httpd


+---------------------------------+---------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/db/mysql/ |
| bdb_log_buffer_size | 1024000 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /var/tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/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/db/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | 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 | DISABLED |
| have_isam | NO |
| have_ndbcluster | NO |
| have_merge_engine | YES |
| 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 | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_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 | 1044480 |
| key_buffer_size | 67108864 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 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 | 650 |
| 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 | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 1000000 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 11095 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/db/mysql/teamworld.it.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 65536 |
| query_cache_limit | 4194304 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 131072 |
| 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_compressed_protocol | OFF |
| slave_load_tmpdir | /var/tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 3145720 |
| 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 | CEST |
| table_cache | 2000 |
| table_lock_wait_timeout | 35 |
| table_type | MyISAM |
| thread_cache_size | 384 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 134217728 |
| tmpdir | /var/tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.24-log |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (July 27, 2006) |
| version_comment | FreeBSD port: mysql-server-5.0.24 |
| version_compile_machine | i386 |
| version_compile_os | portbld-freebsd6.0 |
| wait_timeout | 35 |
+---------------------------------+---------------------------------------------------------+
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 42 |
| Aborted_connects | 5 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 3447615169 |
| Bytes_sent | 1084631688 |
| 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 | 1299923 |
| 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 | 133 |
| Com_dealloc_sql | 0 |
| Com_delete | 217323 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 133 |
| 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 | 787071 |
| Com_insert_select | 133 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 31446 |
| Com_optimize | 2346 |
| 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 | 132596 |
| 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 | 7193340 |
| 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 | 6 |
| 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 | 12 |
| Com_show_triggers | 0 |
| Com_show_variables | 35 |
| 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 | 133 |
| Com_unlock_tables | 31446 |
| Com_update | 2218092 |
| Com_update_multi | 133 |
| 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 | 1299924 |
| Created_tmp_disk_tables | 14018 |
| Created_tmp_files | 34 |
| Created_tmp_tables | 177625 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 482494 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 671164 |
| Handler_read_key | 383331713 |
| Handler_read_next | 274350581 |
| Handler_read_prev | 5683712 |
| Handler_read_rnd | 17305747 |
| Handler_read_rnd_next | 3516494836 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 2880968 |
| Handler_write | 11519632 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 57990 |
| Key_read_requests | 810389532 |
| Key_reads | 2922185 |
| Key_write_requests | 6371130 |
| Key_writes | 4392495 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 148 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 447 |
| Open_streams | 0 |
| Open_tables | 562 |
| Opened_tables | 6116 |
| Qcache_free_blocks | 1972 |
| Qcache_free_memory | 28053416 |
| Qcache_hits | 5634043 |
| Qcache_inserts | 7033006 |
| Qcache_lowmem_prunes | 21097 |
| Qcache_not_cached | 129086 |
| Qcache_queries_in_cache | 9587 |
| Qcache_total_blocks | 21902 |
| Questions | 18848254 |
| Rpl_status | NULL |
| Select_full_join | 118 |
| Select_full_range_join | 0 |
| Select_range | 1416861 |
| Select_range_check | 0 |
| Select_scan | 1015464 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 166 |
| Sort_merge_passes | 7 |
| Sort_range | 1578938 |
| Sort_rows | 134623263 |
| Sort_scan | 144462 |
| 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 | 20985046 |
| Table_locks_waited | 79944 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 146 |
| Threads_connected | 2 |
| Threads_created | 148 |
| Threads_running | 1 |
| Uptime | 474805 |
+-----------------------------------+------------+
mysqladmin Ver 8.41 Distrib 5.0.24, for portbld-freebsd6.0 on i386
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 5.0.24-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 5 days 11 hours 53 min 25 sec

Threads: 2 Questions: 18848255 Slow queries: 166 Opens: 6116 Flush tables: 1 Open tables: 562 Queries per second avg: 39.697

Server is stable and fast. Actually we have ~650 users online (max: 888).
Every night we perform a repair on the bbpost table (~400MB).

Do you think that all is OK? :-)

Thank you very much!

[eva2000]

cpu loads a tad high but if your forums perform well then it should be fine.. and no need to perform a repair every night on post table... were you doing a repair when you captured the cpu load stats ? could explain the higher cpu loads :)

[unnpro]

cpu loads a tad high but if your forums perform well then it should be fine.. and no need to perform a repair every night on post table...
Do you think so? Server was crashing many times before I started to repair the table every night ... but maybe it was a misconfig, I don't know :S

were you doing a repair when you captured the cpu load stats ? could explain the higher cpu loads :)
No :P

[eva2000]

mysql corruption would rarely cause the actual server to crash ..

if you suspect table corruption... best thing to do is keep original live db intact (don't delete).. and make a full mysqldump backup of your live database.

Then create new empty database, and import the mysqldump sql backup of your live database. Edit config.php for vB and point to newly created database name and run your forums a few days on the copy of your live database without nightly repairs and see how it goes.

[unnpro]

Hi eva2000 :)
now the server during the afternoon (when we have many people online) is frequently slow in response.

Here are some stats:

last pid: 13632; load averages: 28.75, 20.19, 16.86 up 64+09:10:24 17:27:58
326 processes: 25 running, 301 sleeping
CPU states: 28.5% user, 0.0% nice, 7.5% system, 0.2% interrupt, 63.8% idle
Mem: 714M Active, 1222M Inact, 202M Wired, 94M Cache, 112M Buf, 773M Free
Swap: 4096M Total, 184M Used, 3911M Free, 4% Inuse

PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
58249 mysql 221 20 0 271M 226M kserel 0 84.6H 10.45% mysqld
12965 www 1 4 0 80588K 41600K sbwait 0 0:04 2.25% httpd
13510 www 1 8 0 80336K 39856K wait 0 0:02 1.46% httpd
13589 www 1 78 0 64700K 24596K select 0 0:01 1.46% httpd
13582 www 1 4 0 77316K 37480K sbwait 0 0:01 1.46% httpd
13632 www 1 80 0 3604K 2596K RUN 0 0:00 1.46% sendmail
13631 www 1 8 0 1680K 944K wait 0 0:00 1.46% sh
58486 root 1 8 0 62660K 19132K nanslp 0 48:53 0.00% httpd
1092 root 1 8 0 376K 240K nanslp 0 16:56 0.00% urchind
1082 root 1 76 0 29384K 2448K select 0 13:33 0.00% perl5.8.7
69689 root 1 76 0 4500K 2324K select 0 4:20 0.00% sendmail
855 root 1 76 0 1304K 648K select 0 1:05 0.00% syslogd
1203 root 1 8 0 1324K 748K nanslp 0 0:16 0.00% cron
12825 www 1 76 0 64964K 29932K select 0 0:07 0.00% httpd
12789 www 1 4 0 75784K 42604K sbwait 2 0:07 0.00% httpd
12801 www 1 76 0 64928K 30656K select 0 0:07 0.00% httpd
1198 root 1 76 0 1408K 840K select 0 0:07 0.00% inetd
1 root 1 8 0 728K 40K wait 0 0:07 0.00% init
12673 www 1 76 0 64676K 29244K select 0 0:06 0.00% httpd
1099 root 1 76 0 29384K 2056K select 0 0:06 0.00% perl5.8.7
13005 www 1 76 0 64984K 28112K select 0 0:05 0.00% httpd
13045 www 1 20 0 63032K 30444K lockf 0 0:05 0.00% httpd
12733 www 1 76 0 63124K 26040K select 0 0:04 0.00% httpd
12850 www 1 20 0 63352K 27444K lockf 0 0:04 0.00% httpd
13008 www 1 76 0 64392K 28996K select 0 0:04 0.00% httpd
13251 www 1 77 0 64904K 26760K select 0 0:04 0.00% httpd
13479 www 1 77 0 65416K 26728K select 0 0:04 0.00% httpd
13246 www 1 76 0 64416K 26680K select 0 0:04 0.00% httpd
10165 root 1 76 0 2840K 1880K CPU0 0 0:03 0.00% top
1100 root 1 76 0 29384K 2084K select 0 0:03 0.00% perl5.8.7
12987 www 1 76 0 63228K 26520K select 0 0:03 0.00% httpd
13253 www 1 76 0 65444K 31356K select 0 0:03 0.00% httpd
13247 www 1 76 0 64648K 24792K RUN 0 0:03 0.00% httpd
13046 www 1 76 0 64696K 26104K select 0 0:03 0.00% httpd
13470 www 1 4 0 77256K 40444K sbwait 0 0:03 0.00% httpd
13195 www 1 4 0 66364K 29912K sbwait 0 0:03 0.00% httpd
13427 www 1 76 0 63024K 24676K select 0 0:02 0.00% httpd
13145 www 1 76 0 64408K 25396K select 0 0:02 0.00% httpd
13390 www 1 76 0 64432K 24372K RUN 0 0:02 0.00% httpd

+---------------------------------+---------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/db/mysql/ |
| bdb_log_buffer_size | 1024000 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /var/tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/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/db/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | 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 | DISABLED |
| have_isam | NO |
| have_ndbcluster | NO |
| have_merge_engine | YES |
| 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 | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_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 | 1044480 |
| key_buffer_size | 67108864 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 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 | 650 |
| 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 | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 1000000 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 11095 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/db/mysql/teamworld.it.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 65536 |
| query_cache_limit | 4194304 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 131072 |
| 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_compressed_protocol | OFF |
| slave_load_tmpdir | /var/tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 3145720 |
| 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 | CEST |
| table_cache | 2000 |
| table_lock_wait_timeout | 35 |
| table_type | MyISAM |
| thread_cache_size | 384 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 134217728 |
| tmpdir | /var/tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.24-log |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (July 27, 2006) |
| version_comment | FreeBSD port: mysql-server-5.0.24 |
| version_compile_machine | i386 |
| version_compile_os | portbld-freebsd6.0 |
| wait_timeout | 35 |
+---------------------------------+---------------------------------------------------------+
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 13268 |
| Aborted_connects | 678 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 3012202012 |
| Bytes_sent | 3008594878 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 1 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 6696429 |
| 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 | 3785 |
| Com_dealloc_sql | 0 |
| Com_delete | 1214955 |
| Com_delete_multi | 942 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 3768 |
| 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 | 3932043 |
| Com_insert_select | 3785 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 460392 |
| Com_optimize | 7784 |
| 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 | 586866 |
| 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 | 37497213 |
| Com_set_option | 120 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 2 |
| Com_show_charsets | 30 |
| Com_show_collations | 30 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 5 |
| Com_show_databases | 45 |
| Com_show_errors | 0 |
| Com_show_fields | 9454 |
| Com_show_grants | 11 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 14 |
| 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 | 104 |
| Com_show_triggers | 0 |
| Com_show_variables | 481 |
| 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 | 3752 |
| Com_unlock_tables | 460383 |
| Com_update | 11862082 |
| Com_update_multi | 3767 |
| 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 | 6697045 |
| Created_tmp_disk_tables | 117026 |
| Created_tmp_files | 199 |
| Created_tmp_tables | 1133075 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 2852549 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 4269213 |
| Handler_read_key | 2185644885 |
| Handler_read_next | 1506415173 |
| Handler_read_prev | 34403234 |
| Handler_read_rnd | 83550435 |
| Handler_read_rnd_next | 1430292079 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 20383376 |
| Handler_write | 56446011 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0