WhiteWolf
Tue 20th Jun '06, 9:34pm
1. Is this on dedicated or shared virual server.
A Dedicated Server, running about ~5 websites, large vB and a smaller one.
2. your server specs. For example:
cpu speed/type single or dual cpus): P4 3.06ghz HT
how much memory installed: 2GB DDR
hard drive type/configuration: 160GB EIDE
linux distributor or windows version: Fedora Core 2
apache/IIS version: apache 2.0.51
PHP version: php 4.4.2
MySQL version: mysql 4.0.25
3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
N/A
4. if possible how mysql was compiled/installed
RPM Install
5. your top stats
top - 20:24:18 up 161 days, 13:26, 1 user, load average: 2.00, 2.00, 1.94
Tasks: 108 total, 2 running, 105 sleeping, 0 stopped, 1 zombie
Cpu(s): 0.2% us, 6.6% sy, 46.5% ni, 46.0% id, 0.2% wa, 0.2% hi, 0.3% si
Mem: 2068160k total, 1840856k used, 227304k free, 81392k buffers
Swap: 2048276k total, 212k used, 2048064k free, 1334912k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
28770 apache 19 4 72068 26m 9804 S 22.6 1.3 0:03.08 httpd
28690 apache 19 4 72196 29m 12m S 22.3 1.5 0:03.42 httpd
28807 apache 20 4 71692 26m 9884 S 20.3 1.3 0:01.96 httpd
7982 mysql 21 6 201m 97m 3856 S 11.3 4.8 594:30.61 mysqld
28771 apache 20 4 71580 27m 10m S 11.0 1.3 0:02.66 httpd
28808 apache 20 4 70948 25m 9320 R 7.3 1.2 0:00.22 httpd
28784 apache 20 4 0 0 0 Z 3.0 0.0 0:00.61 httpd <defunct>
28741 apache 19 4 71540 26m 10m S 1.3 1.3 0:02.76 httpd
28787 apache 19 4 71704 26m 9852 S 1.0 1.3 0:03.15 httpd
28640 apache 19 4 72136 27m 10m S 0.7 1.4 0:08.25 httpd
28774 apache 19 4 71588 27m 10m S 0.7 1.4 0:00.84 httpd
28804 root 16 0 1896 920 724 R 0.7 0.0 0:00.05 top
28809 apache 19 4 63988 16m 7604 S 0.7 0.8 0:00.02 httpd
28765 apache 19 4 71908 26m 9856 S 0.3 1.3 0:02.77 httpd
28778 apache 20 4 71876 27m 10m S 0.3 1.4 0:01.88 httpd
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).
[whitewolf@u ~]# sudo cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
#log=/var/log/mysqld.log
socket=/var/lib/mysql/mysql.sock
#skip-locking
skip-bdb
innodb_data_file_path=ibdata1:10M:autoextend
safe-show-database
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=250
interactive_timeout=50
wait_timeout=24
connect_timeout=5
thread_cache_size=100
key_buffer=16M
join_buffer=1M
max_allowed_packet=18M
table_cache=768
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=24M
server-id=1
#long_query_time=8
#log_slow_queries=/var/log/mysql-longqueries.log
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
pidfile= /var/run/mysqld/mysqld.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=18M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer=48M
sort_buffer=48M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=48M
sort_buffer=48M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
7. MySQL stats from ssh telnet as root user type:
| Variable_name | Value |
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin1_de latin2 latin5 sjis swe7 tis620 ujis usa7 win1250 win1251ukr win1251 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| convert_character_set | |
| datadir | /var/lib/mysql/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 4 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| 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_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_table_locks | ON |
| interactive_timeout | 50 |
| join_buffer_size | 1044480 |
| key_buffer_size | 16777216 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_slow_queries | 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 | 18873344 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 250 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_repair_threads | 1 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 25165824 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 1796 |
| pid_file | /var/run/mysqld/mysqld.pid |
| log_error | |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 2093056 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 1 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | 0 |
| table_cache | 768 |
| table_type | MYISAM |
| thread_cache_size | 100 |
| thread_stack | 196608 |
| tx_isolation | REPEATABLE-READ |
| timezone | EDT |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.25 |
| version_comment | Source distribution |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 24 |
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 3566 |
| Aborted_connects | 117 |
| Bytes_received | 2261429445 |
| Bytes_sent | 2011549831 |
| Com_admin_commands | 0 |
| Com_alter_table | 585 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 6585 |
| Com_change_db | 570314 |
| Com_change_master | 0 |
| Com_check | 1 |
| Com_commit | 6585 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 1735 |
| Com_delete | 36768 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 1156 |
| Com_flush | 0 |
| Com_grant | 9 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 207209 |
| Com_insert_select | 1443 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 5475 |
| Com_replace_select | 3 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 3123259 |
| Com_set_option | 4619 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 16 |
| Com_show_create | 425 |
| Com_show_databases | 52 |
| Com_show_fields | 492 |
| Com_show_grants | 2 |
| Com_show_keys | 4705 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1260 |
| Com_show_variables | 210 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 10 |
| Com_unlock_tables | 0 |
| Com_update | 593466 |
| Com_update_multi | 1164 |
| Connections | 780738 |
| Created_tmp_disk_tables | 9666 |
| Created_tmp_tables | 148363 |
| Created_tmp_files | 901 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 3 |
| Handler_commit | 6575 |
| Handler_delete | 67826 |
| Handler_read_first | 185367 |
| Handler_read_key | 299478119 |
| Handler_read_next | 175217059 |
| Handler_read_prev | 841385 |
| Handler_read_rnd | 151279005 |
| Handler_read_rnd_next | 1207326491 |
| Handler_rollback | 3297 |
| Handler_update | 16304146 |
| Handler_write | 71496896 |
| Key_blocks_used | 15586 |
| Key_read_requests | 824816923 |
| Key_reads | 8538875 |
| Key_write_requests | 34772924 |
| Key_writes | 8216753 |
| Max_used_connections | 208 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 768 |
| Open_files | 955 |
| Open_streams | 0 |
| Opened_tables | 6002 |
| Questions | 12652659 |
| Qcache_queries_in_cache | 4672 |
| Qcache_inserts | 2630781 |
| Qcache_hits | 4287192 |
| Qcache_lowmem_prunes | 85583 |
| Qcache_not_cached | 493209 |
| Qcache_free_memory | 6211464 |
| Qcache_free_blocks | 1912 |
| Qcache_total_blocks | 13718 |
| Rpl_status | NULL |
| Select_full_join | 105685 |
| Select_full_range_join | 207 |
| Select_range | 851609 |
| Select_range_check | 0 |
| Select_scan | 754458 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 4 |
| Slow_queries | 89 |
| Sort_merge_passes | 139 |
| Sort_range | 428737 |
| Sort_rows | 165112181 |
| Sort_scan | 361003 |
| Table_locks_immediate | 6939768 |
| Table_locks_waited | 69945 |
| Threads_cached | 94 |
| Threads_created | 231 |
| Threads_connected | 9 |
| Threads_running | 5 |
| Uptime | 328939 |
+--------------------------+------------+
Uptime: 328939 Threads: 9 Questions: 12652660 Slow queries: 89 Opens: 6002 Flush tables: 3 Open tables: 768 Queries per second avg: 38.465
mysqladmin Ver 8.40 Distrib 4.0.25, for redhat-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.0.25
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 3 days 19 hours 22 min 19 sec
Threads: 9 Questions: 12652660 Slow queries: 89 Opens: 6002 Flush tables: 3 Open tables: 768 Queries per second avg: 38.465
8. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
Pretty much, there's some custom website scripts and other small things that hit Php and MySQL
9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
~605 average, (132 members, 473 guests), max is usually 1200
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://whitew0lf.info/tehinfo.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 -- 3
MinSpareServers -- 5
MaxSpareServers -- 8
StartServers -- 5
MaxClients -- 100
AND Maxrequestsperchild value = 4000
12. what version of vB are you running ?
3.5.4
13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log
No such files found
14. Post output from these 3 commands
uname -a
Linux *sniffle* 2.6.11.9-050512a #1 SMP Thu May 12 20:53:02 CEST 2005 i686 i686 i386 GNU/Linux
ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 16319
virtual memory (kbytes, -v) unlimited
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 3.06GHz
stepping : 9
cpu MHz : 3065.156
cache size : 512 KB
physical id : 0
siblings : 2
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 sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips : 6029.31
*repeated for the virtual processor*
Mostly interested in mySQL, PHP and Apache tuning.
eAccelerator is installed
A Dedicated Server, running about ~5 websites, large vB and a smaller one.
2. your server specs. For example:
cpu speed/type single or dual cpus): P4 3.06ghz HT
how much memory installed: 2GB DDR
hard drive type/configuration: 160GB EIDE
linux distributor or windows version: Fedora Core 2
apache/IIS version: apache 2.0.51
PHP version: php 4.4.2
MySQL version: mysql 4.0.25
3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
N/A
4. if possible how mysql was compiled/installed
RPM Install
5. your top stats
top - 20:24:18 up 161 days, 13:26, 1 user, load average: 2.00, 2.00, 1.94
Tasks: 108 total, 2 running, 105 sleeping, 0 stopped, 1 zombie
Cpu(s): 0.2% us, 6.6% sy, 46.5% ni, 46.0% id, 0.2% wa, 0.2% hi, 0.3% si
Mem: 2068160k total, 1840856k used, 227304k free, 81392k buffers
Swap: 2048276k total, 212k used, 2048064k free, 1334912k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
28770 apache 19 4 72068 26m 9804 S 22.6 1.3 0:03.08 httpd
28690 apache 19 4 72196 29m 12m S 22.3 1.5 0:03.42 httpd
28807 apache 20 4 71692 26m 9884 S 20.3 1.3 0:01.96 httpd
7982 mysql 21 6 201m 97m 3856 S 11.3 4.8 594:30.61 mysqld
28771 apache 20 4 71580 27m 10m S 11.0 1.3 0:02.66 httpd
28808 apache 20 4 70948 25m 9320 R 7.3 1.2 0:00.22 httpd
28784 apache 20 4 0 0 0 Z 3.0 0.0 0:00.61 httpd <defunct>
28741 apache 19 4 71540 26m 10m S 1.3 1.3 0:02.76 httpd
28787 apache 19 4 71704 26m 9852 S 1.0 1.3 0:03.15 httpd
28640 apache 19 4 72136 27m 10m S 0.7 1.4 0:08.25 httpd
28774 apache 19 4 71588 27m 10m S 0.7 1.4 0:00.84 httpd
28804 root 16 0 1896 920 724 R 0.7 0.0 0:00.05 top
28809 apache 19 4 63988 16m 7604 S 0.7 0.8 0:00.02 httpd
28765 apache 19 4 71908 26m 9856 S 0.3 1.3 0:02.77 httpd
28778 apache 20 4 71876 27m 10m S 0.3 1.4 0:01.88 httpd
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).
[whitewolf@u ~]# sudo cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
#log=/var/log/mysqld.log
socket=/var/lib/mysql/mysql.sock
#skip-locking
skip-bdb
innodb_data_file_path=ibdata1:10M:autoextend
safe-show-database
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=250
interactive_timeout=50
wait_timeout=24
connect_timeout=5
thread_cache_size=100
key_buffer=16M
join_buffer=1M
max_allowed_packet=18M
table_cache=768
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=24M
server-id=1
#long_query_time=8
#log_slow_queries=/var/log/mysql-longqueries.log
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
pidfile= /var/run/mysqld/mysqld.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=18M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer=48M
sort_buffer=48M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=48M
sort_buffer=48M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
7. MySQL stats from ssh telnet as root user type:
| Variable_name | Value |
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin1_de latin2 latin5 sjis swe7 tis620 ujis usa7 win1250 win1251ukr win1251 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| convert_character_set | |
| datadir | /var/lib/mysql/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 4 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| 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_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_table_locks | ON |
| interactive_timeout | 50 |
| join_buffer_size | 1044480 |
| key_buffer_size | 16777216 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_slow_queries | 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 | 18873344 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 250 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_repair_threads | 1 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 25165824 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 1796 |
| pid_file | /var/run/mysqld/mysqld.pid |
| log_error | |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 2093056 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 1 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | 0 |
| table_cache | 768 |
| table_type | MYISAM |
| thread_cache_size | 100 |
| thread_stack | 196608 |
| tx_isolation | REPEATABLE-READ |
| timezone | EDT |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.25 |
| version_comment | Source distribution |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 24 |
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 3566 |
| Aborted_connects | 117 |
| Bytes_received | 2261429445 |
| Bytes_sent | 2011549831 |
| Com_admin_commands | 0 |
| Com_alter_table | 585 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 6585 |
| Com_change_db | 570314 |
| Com_change_master | 0 |
| Com_check | 1 |
| Com_commit | 6585 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 1735 |
| Com_delete | 36768 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 1156 |
| Com_flush | 0 |
| Com_grant | 9 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 207209 |
| Com_insert_select | 1443 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 5475 |
| Com_replace_select | 3 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 3123259 |
| Com_set_option | 4619 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 16 |
| Com_show_create | 425 |
| Com_show_databases | 52 |
| Com_show_fields | 492 |
| Com_show_grants | 2 |
| Com_show_keys | 4705 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1260 |
| Com_show_variables | 210 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 10 |
| Com_unlock_tables | 0 |
| Com_update | 593466 |
| Com_update_multi | 1164 |
| Connections | 780738 |
| Created_tmp_disk_tables | 9666 |
| Created_tmp_tables | 148363 |
| Created_tmp_files | 901 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 3 |
| Handler_commit | 6575 |
| Handler_delete | 67826 |
| Handler_read_first | 185367 |
| Handler_read_key | 299478119 |
| Handler_read_next | 175217059 |
| Handler_read_prev | 841385 |
| Handler_read_rnd | 151279005 |
| Handler_read_rnd_next | 1207326491 |
| Handler_rollback | 3297 |
| Handler_update | 16304146 |
| Handler_write | 71496896 |
| Key_blocks_used | 15586 |
| Key_read_requests | 824816923 |
| Key_reads | 8538875 |
| Key_write_requests | 34772924 |
| Key_writes | 8216753 |
| Max_used_connections | 208 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 768 |
| Open_files | 955 |
| Open_streams | 0 |
| Opened_tables | 6002 |
| Questions | 12652659 |
| Qcache_queries_in_cache | 4672 |
| Qcache_inserts | 2630781 |
| Qcache_hits | 4287192 |
| Qcache_lowmem_prunes | 85583 |
| Qcache_not_cached | 493209 |
| Qcache_free_memory | 6211464 |
| Qcache_free_blocks | 1912 |
| Qcache_total_blocks | 13718 |
| Rpl_status | NULL |
| Select_full_join | 105685 |
| Select_full_range_join | 207 |
| Select_range | 851609 |
| Select_range_check | 0 |
| Select_scan | 754458 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 4 |
| Slow_queries | 89 |
| Sort_merge_passes | 139 |
| Sort_range | 428737 |
| Sort_rows | 165112181 |
| Sort_scan | 361003 |
| Table_locks_immediate | 6939768 |
| Table_locks_waited | 69945 |
| Threads_cached | 94 |
| Threads_created | 231 |
| Threads_connected | 9 |
| Threads_running | 5 |
| Uptime | 328939 |
+--------------------------+------------+
Uptime: 328939 Threads: 9 Questions: 12652660 Slow queries: 89 Opens: 6002 Flush tables: 3 Open tables: 768 Queries per second avg: 38.465
mysqladmin Ver 8.40 Distrib 4.0.25, for redhat-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.0.25
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 3 days 19 hours 22 min 19 sec
Threads: 9 Questions: 12652660 Slow queries: 89 Opens: 6002 Flush tables: 3 Open tables: 768 Queries per second avg: 38.465
8. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
Pretty much, there's some custom website scripts and other small things that hit Php and MySQL
9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
~605 average, (132 members, 473 guests), max is usually 1200
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://whitew0lf.info/tehinfo.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 -- 3
MinSpareServers -- 5
MaxSpareServers -- 8
StartServers -- 5
MaxClients -- 100
AND Maxrequestsperchild value = 4000
12. what version of vB are you running ?
3.5.4
13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log
No such files found
14. Post output from these 3 commands
uname -a
Linux *sniffle* 2.6.11.9-050512a #1 SMP Thu May 12 20:53:02 CEST 2005 i686 i686 i386 GNU/Linux
ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 16319
virtual memory (kbytes, -v) unlimited
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 3.06GHz
stepping : 9
cpu MHz : 3065.156
cache size : 512 KB
physical id : 0
siblings : 2
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 sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips : 6029.31
*repeated for the virtual processor*
Mostly interested in mySQL, PHP and Apache tuning.
eAccelerator is installed