-CP-
Sun 17th Dec '06, 4:59pm
I just got my first dedicated server and I'm sure it could do with some optimisation, any tips would be welcome :)
1. Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)
Dedicated, just my vBulletin is on it.
2. your server specs.
Intel(R) Pentium(R) 4 CPU 2.80GHz
1GB RAM
120GB ATA Hard Drive
CentOS 4.4
Apache 1.3.37
PHP 4.4.4
MySQL 4.1.21-standard
3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
No, don't think so...
4. if possible how mysql was compiled/installed
Not sure, it was already installed on my server.
5. your top stats
top - 20:19:52 up 4:42, 1 user, load average: 1.68, 1.72, 3.32
Tasks: 106 total, 4 running, 102 sleeping, 0 stopped, 0 zombie
Cpu(s): 94.4% us, 5.6% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 1034672k total, 901844k used, 132828k free, 11196k buffers
Swap: 2104504k total, 205844k used, 1898660k free, 154384k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
22876 nobody 17 0 75608 65m 3544 S 56.8 6.5 0:11.44 httpd
22885 nobody 17 0 57772 47m 3084 R 30.6 4.7 0:10.41 httpd
5354 mysql 15 0 157m 17m 2280 S 10.6 1.7 11:10.14 mysqld
2662 named 18 0 38348 2940 1872 S 1.0 0.3 1:11.86 named
22871 nobody 17 0 75116 64m 2960 R 1.0 6.4 0:20.43 httpd
23983 root 16 0 2696 1008 788 R 0.3 0.1 0:00.01 top
1 root 16 0 2824 468 440 S 0.0 0.0 0:00.66 init
2 root 34 19 0 0 0 S 0.0 0.0 0:00.01 ksoftirqd/0
3 root 5 -10 0 0 0 S 0.0 0.0 0:00.03 events/0
4 root 8 -10 0 0 0 S 0.0 0.0 0:00.00 khelper
5 root 15 -10 0 0 0 S 0.0 0.0 0:00.00 kacpid
20 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 kblockd/0
41 root 10 -10 0 0 0 S 0.0 0.0 0:00.00 aio/0
21 root 15 0 0 0 0 S 0.0 0.0 0:00.00 khubd
40 root 16 0 0 0 0 S 0.0 0.0 0:15.43 kswapd0
187 root 25 0 0 0 0 S 0.0 0.0 0:00.00 kseriod
298 root 15 0 0 0 0 S 0.0 0.0 0:00.08 kjournald
6. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).
[mysqld]
set-variable = max_connections=500
safe-show-database
7. MySQL stats from ssh telnet as root user type:
+---------------------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------+
| back_log | 50 |
| 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 | 5 |
| 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 | YES |
| 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 | ibdata1:10M:autoextend |
| 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 | 131072 |
| key_buffer_size | 8388600 |
| 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 | 1048576 |
| 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 | 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_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 | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 2510 |
| pid_file | /var/lib/mysql/server.mysite.net.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_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 | GMT |
| table_cache | 64 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.21-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 12 |
| Aborted_connects | 74 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 11931835 |
| Bytes_sent | 2367178868 |
| 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 | 3372 |
| 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 | 310 |
| 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 | 4 |
| Com_grant | 2 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 936 |
| Com_insert_select | 13 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 79 |
| 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 | 799 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 2 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 24267 |
| Com_set_option | 51 |
| 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 | 3 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 2 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 58 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 10 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 1 |
| Com_show_variables | 7 |
| 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 | 79 |
| Com_update | 1869 |
| Com_update_multi | 0 |
| Connections | 3143 |
| Created_tmp_disk_tables | 6 |
| Created_tmp_files | 4 |
| Created_tmp_tables | 4213 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 941 |
| Handler_discover | 0 |
| Handler_read_first | 5232 |
| Handler_read_key | 43907409 |
| Handler_read_next | 629172 |
| Handler_read_prev | 16871 |
| Handler_read_rnd | 83183 |
| Handler_read_rnd_next | 27993257 |
| Handler_rollback | 0 |
| Handler_update | 3804 |
| Handler_write | 80608 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 3804 |
| Key_blocks_used | 5282 |
| Key_read_requests | 132095248 |
| Key_reads | 14741 |
| Key_write_requests | 6761 |
| Key_writes | 5354 |
| Max_used_connections | 198 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 110 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 3409 |
| 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 | 34763 |
| Rpl_status | NULL |
| Select_full_join | 24 |
| Select_full_range_join | 0 |
| Select_range | 10203 |
| Select_range_check | 0 |
| Select_scan | 5658 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 11 |
| Slow_queries | 272 |
| Sort_merge_passes | 0 |
| Sort_range | 4763 |
| Sort_rows | 25363186 |
| Sort_scan | 3440 |
| Table_locks_immediate | 47134 |
| Table_locks_waited | 694 |
| Threads_cached | 0 |
| Threads_connected | 5 |
| Threads_created | 3142 |
| Threads_running | 1 |
| Uptime | 17294 |
+----------------------------+------------+
Uptime: 17295 Threads: 5 Questions: 34764 Slow queries: 272 Opens: 3409 Flush tables: 1 Open tables: 64 Queries per second avg: 2.010
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: 4 hours 48 min 15 sec
Threads: 5 Questions: 34764 Slow queries: 272 Opens: 3409 Flush tables: 1 Open tables: 64 Queries per second avg: 2.010
8. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
A ton of hacks, main one vBSEO.
9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
Average: 70
Max: 150 (most ever online 321)
Cookie: 1800
10. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site
http://66.79.167.228/~aaa/phpinfo.php
11. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
MaxRequestsPerChild 0
12. what version of vB are you running ?
3.6.1 Patched, planning on upgrading to 3.6.4 next weekend.
13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log
None
14. Post output from these 3 commands
uname -a
Linux server.mysite.net 2.6.9-42.0.3.EL #1 Fri Oct 6 05:59:54 CDT 2006 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) Pentium(R) 4 CPU 2.80GHz
stepping : 9
cpu MHz : 2814.795
cache size : 512 KB
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 : 5630.31
Thanks,
-CP-
[EDIT]
Meant to say my Memory Used goes way up past 90% some time and Server Load sometimes goes way up past 50 which is worrying :(
1. Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)
Dedicated, just my vBulletin is on it.
2. your server specs.
Intel(R) Pentium(R) 4 CPU 2.80GHz
1GB RAM
120GB ATA Hard Drive
CentOS 4.4
Apache 1.3.37
PHP 4.4.4
MySQL 4.1.21-standard
3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
No, don't think so...
4. if possible how mysql was compiled/installed
Not sure, it was already installed on my server.
5. your top stats
top - 20:19:52 up 4:42, 1 user, load average: 1.68, 1.72, 3.32
Tasks: 106 total, 4 running, 102 sleeping, 0 stopped, 0 zombie
Cpu(s): 94.4% us, 5.6% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 1034672k total, 901844k used, 132828k free, 11196k buffers
Swap: 2104504k total, 205844k used, 1898660k free, 154384k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
22876 nobody 17 0 75608 65m 3544 S 56.8 6.5 0:11.44 httpd
22885 nobody 17 0 57772 47m 3084 R 30.6 4.7 0:10.41 httpd
5354 mysql 15 0 157m 17m 2280 S 10.6 1.7 11:10.14 mysqld
2662 named 18 0 38348 2940 1872 S 1.0 0.3 1:11.86 named
22871 nobody 17 0 75116 64m 2960 R 1.0 6.4 0:20.43 httpd
23983 root 16 0 2696 1008 788 R 0.3 0.1 0:00.01 top
1 root 16 0 2824 468 440 S 0.0 0.0 0:00.66 init
2 root 34 19 0 0 0 S 0.0 0.0 0:00.01 ksoftirqd/0
3 root 5 -10 0 0 0 S 0.0 0.0 0:00.03 events/0
4 root 8 -10 0 0 0 S 0.0 0.0 0:00.00 khelper
5 root 15 -10 0 0 0 S 0.0 0.0 0:00.00 kacpid
20 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 kblockd/0
41 root 10 -10 0 0 0 S 0.0 0.0 0:00.00 aio/0
21 root 15 0 0 0 0 S 0.0 0.0 0:00.00 khubd
40 root 16 0 0 0 0 S 0.0 0.0 0:15.43 kswapd0
187 root 25 0 0 0 0 S 0.0 0.0 0:00.00 kseriod
298 root 15 0 0 0 0 S 0.0 0.0 0:00.08 kjournald
6. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).
[mysqld]
set-variable = max_connections=500
safe-show-database
7. MySQL stats from ssh telnet as root user type:
+---------------------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------+
| back_log | 50 |
| 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 | 5 |
| 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 | YES |
| 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 | ibdata1:10M:autoextend |
| 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 | 131072 |
| key_buffer_size | 8388600 |
| 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 | 1048576 |
| 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 | 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_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 | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 2510 |
| pid_file | /var/lib/mysql/server.mysite.net.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_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 | GMT |
| table_cache | 64 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.21-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 12 |
| Aborted_connects | 74 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 11931835 |
| Bytes_sent | 2367178868 |
| 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 | 3372 |
| 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 | 310 |
| 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 | 4 |
| Com_grant | 2 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 936 |
| Com_insert_select | 13 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 79 |
| 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 | 799 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 2 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 24267 |
| Com_set_option | 51 |
| 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 | 3 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 2 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 58 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 10 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 1 |
| Com_show_variables | 7 |
| 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 | 79 |
| Com_update | 1869 |
| Com_update_multi | 0 |
| Connections | 3143 |
| Created_tmp_disk_tables | 6 |
| Created_tmp_files | 4 |
| Created_tmp_tables | 4213 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 941 |
| Handler_discover | 0 |
| Handler_read_first | 5232 |
| Handler_read_key | 43907409 |
| Handler_read_next | 629172 |
| Handler_read_prev | 16871 |
| Handler_read_rnd | 83183 |
| Handler_read_rnd_next | 27993257 |
| Handler_rollback | 0 |
| Handler_update | 3804 |
| Handler_write | 80608 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 3804 |
| Key_blocks_used | 5282 |
| Key_read_requests | 132095248 |
| Key_reads | 14741 |
| Key_write_requests | 6761 |
| Key_writes | 5354 |
| Max_used_connections | 198 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 110 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 3409 |
| 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 | 34763 |
| Rpl_status | NULL |
| Select_full_join | 24 |
| Select_full_range_join | 0 |
| Select_range | 10203 |
| Select_range_check | 0 |
| Select_scan | 5658 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 11 |
| Slow_queries | 272 |
| Sort_merge_passes | 0 |
| Sort_range | 4763 |
| Sort_rows | 25363186 |
| Sort_scan | 3440 |
| Table_locks_immediate | 47134 |
| Table_locks_waited | 694 |
| Threads_cached | 0 |
| Threads_connected | 5 |
| Threads_created | 3142 |
| Threads_running | 1 |
| Uptime | 17294 |
+----------------------------+------------+
Uptime: 17295 Threads: 5 Questions: 34764 Slow queries: 272 Opens: 3409 Flush tables: 1 Open tables: 64 Queries per second avg: 2.010
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: 4 hours 48 min 15 sec
Threads: 5 Questions: 34764 Slow queries: 272 Opens: 3409 Flush tables: 1 Open tables: 64 Queries per second avg: 2.010
8. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
A ton of hacks, main one vBSEO.
9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
Average: 70
Max: 150 (most ever online 321)
Cookie: 1800
10. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site
http://66.79.167.228/~aaa/phpinfo.php
11. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
MaxRequestsPerChild 0
12. what version of vB are you running ?
3.6.1 Patched, planning on upgrading to 3.6.4 next weekend.
13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log
None
14. Post output from these 3 commands
uname -a
Linux server.mysite.net 2.6.9-42.0.3.EL #1 Fri Oct 6 05:59:54 CDT 2006 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) Pentium(R) 4 CPU 2.80GHz
stepping : 9
cpu MHz : 2814.795
cache size : 512 KB
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 : 5630.31
Thanks,
-CP-
[EDIT]
Meant to say my Memory Used goes way up past 90% some time and Server Load sometimes goes way up past 50 which is worrying :(