Optimization Help Please

[Cynthia]

1. Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)

Dedicated.

2. your server specs.

cpu speed/type single or dual cpus): Intel(R) Xeon(TM) CPU 3.06GHz
how much memory installed: 6GB
hard drive type/configuration: 4 x 73GB SCA Ultra 160 SCSI Hard Drive
linux distributor or windows version: Red Hat Enterprise Linux ES release 3
apache/IIS version: Apache v2.0.46
PHP version: php 4.3.10
MySQL version: mysql 4.0.23-standard-log


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.

4. if possible how mysql was compiled/installed

?

5. your top stats

23:15:09 up 38 days, 7:31, 1 user, load average: 5.47, 4.95, 4.81
178 processes: 175 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 82.0% 0.0% 4.1% 0.0% 1.0% 0.0% 12.6%
cpu00 82.8% 0.0% 4.9% 0.0% 1.3% 0.0% 10.7%
cpu01 80.4% 0.0% 5.3% 0.0% 0.0% 0.0% 14.1%
cpu02 82.4% 0.0% 3.1% 0.1% 2.3% 0.0% 11.7%
cpu03 82.5% 0.0% 2.9% 0.0% 0.3% 0.0% 14.1%
Mem: 5907384k av, 5825544k used, 81840k free, 0k shrd, 39868k buff
4475208k actv, 925920k in_d, 101216k in_c
Swap: 1052248k av, 25456k used, 1026792k free 5207848k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
18198 apache 16 0 14840 14M 4672 R 4.9 0.2 0:23 1 httpd
18010 apache 16 0 15372 15M 4680 S 4.7 0.2 0:33 0 httpd
18499 apache 15 0 13812 13M 4584 S 4.5 0.2 0:06 1 httpd
18167 apache 16 0 17308 16M 4708 S 4.4 0.2 0:30 3 httpd
18363 apache 15 0 13752 13M 4596 S 3.9 0.2 0:13 0 httpd
18191 apache 15 0 16420 16M 4668 S 3.7 0.2 0:25 1 httpd
18371 apache 15 0 13716 13M 4596 S 3.7 0.2 0:08 1 httpd
18281 apache 16 0 13684 13M 4644 S 3.6 0.2 0:18 1 httpd
18500 apache 15 0 13672 13M 4588 S 3.6 0.2 0:03 2 httpd
18173 apache 15 0 15720 15M 4704 S 3.4 0.2 0:27 3 httpd
18286 apache 16 0 13768 13M 4644 S 3.4 0.2 0:14 0 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).
# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#pid-file=/var/run/mysqld/mysqld.pid
max_connections = 1024
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 10800
connect_timeout = 10
tmp_table_size = 32M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

# RS 20050310
#log-long-format
log-slow-queries=/var/lib/mysql/athena1.mothering.com.slow
log-error=/var/lib/mysql/athena1.mothering.com.err
log-warnings
max_heap_table_size = 64M


[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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


KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 20
StartServers 8
MaxClients 250
MaxRequestsPerChild 1000

# uname -a
Linux athena1.mothering.com 2.4.21-32.0.1.ELsmp #1 SMP Tue May 17 17:52:23 EDT 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) 4
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) 7168
virtual memory (kbytes, -v) unlimited

# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 3.06GHz
stepping : 9
cpu MHz : 3051.297
cache size : 512 KB
physical id : 0
siblings : 2
runqueue : 0
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
bogomips : 6081.74

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 3.06GHz
stepping : 9
cpu MHz : 3051.297
cache size : 512 KB
physical id : 0
siblings : 2
runqueue : 0
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
bogomips : 6094.84

processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 3.06GHz
stepping : 9
cpu MHz : 3051.297
cache size : 512 KB
physical id : 3
siblings : 2
runqueue : 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
bogomips : 6094.84

processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 3.06GHz
stepping : 9
cpu MHz : 3051.297
cache size : 512 KB
physical id : 3
siblings : 2
runqueue : 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
bogomips : 6094.84

7. MySQL stats from ssh telnet as root user (Note: Will install mysqlreport but need direction on how to install it)


+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON |
| connect_timeout | 10 |
| 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 | DISABLED |
| 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_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| 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 | 28800 |
| 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 | ON |
| log_warnings | 2 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 16776192 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 1024 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| max_heap_table_size | 67107840 |
| 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 | 67108864 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 5130 |
| pid_file | /var/lib/mysql/athena1.mothering.com.pid |
| log_error | /var/lib/mysql/athena1.mothering.com.err |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 0 |
| 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 | 1024 |
| table_type | MYISAM |
| thread_cache_size | 64 |
| thread_stack | 126976 |
| tx_isolation | REPEATABLE-READ |
| timezone | EST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.23-standard-log |
| version_comment | Official MySQL RPM |
| version_compile_os | pc-linux |
| wait_timeout | 10800 |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 270 |
| Aborted_connects | 800 |
| Bytes_received | 3120825569 |
| Bytes_sent | 2596860629 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 3493594 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 70521 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 14 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 3336943 |
| Com_insert_select | 197552 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 149 |
| Com_optimize | 266 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 130 |
| Com_replace | 1320555 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 17892743 |
| Com_set_option | 326 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 326 |
| Com_show_databases | 10 |
| Com_show_fields | 356 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 1 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 385 |
| Com_show_variables | 47 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 285 |
| Com_update | 6531627 |
| Connections | 3494291 |
| Created_tmp_disk_tables | 7067 |
| Created_tmp_tables | 111507 |
| Created_tmp_files | 972 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 2924778 |
| Handler_read_first | 492265 |
| Handler_read_key | 293222968 |
| Handler_read_next | 3045828207 |
| Handler_read_prev | 6535122 |
| Handler_read_rnd | 48869677 |
| Handler_read_rnd_next | 1860414195 |
| Handler_rollback | 0 |
| Handler_update | 8384937 |
| Handler_write | 36061721 |
| Key_blocks_used | 15586 |
| Key_read_requests | 1982763088 |
| Key_reads | 46830199 |
| Key_write_requests | 37575390 |
| Key_writes | 37026606 |
| Max_used_connections | 239 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1024 |
| Open_files | 1079 |
| Open_streams | 0 |
| Opened_tables | 7570 |
| Questions | 85409968 |
| Qcache_queries_in_cache | 7376 |
| Qcache_inserts | 14720065 |
| Qcache_hits | 49043373 |
| Qcache_lowmem_prunes | 289070 |
| Qcache_not_cached | 3172678 |
| Qcache_free_memory | 18025056 |
| Qcache_free_blocks | 3114 |
| Qcache_total_blocks | 18157 |
| Rpl_status | NULL |
| Select_full_join | 6291 |
| Select_full_range_join | 204 |
| Select_range | 7789028 |
| Select_range_check | 0 |
| Select_scan | 1428419 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 2 |
| Slow_queries | 539 |
| Sort_merge_passes | 486 |
| Sort_range | 679792 |
| Sort_rows | 47522162 |
| Sort_scan | 68450 |
| Table_locks_immediate | 33295903 |
| Table_locks_waited | 283945 |
| Threads_cached | 60 |
| Threads_created | 1990 |
| Threads_connected | 4 |
| Threads_running | 2 |
| Uptime | 235606 |
+--------------------------+------------+
8. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?

The server carries the mothering.com website as well as ad software that utilises php and mysql


9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
Average - 700-1000 Max - 1374
Cookie timeout - 900

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://www.mothering.com/discussions/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 Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 20
StartServers 8
MaxClients 250
MaxRequestsPerChild 1000

12. what version of vB are you running ?
3.5.3

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

find / -size +2000000k
/var/lib/mysql/motherin_discussions/post.MYD
/var/log/httpd/access_log
/var/log/httpd/access_log.1
/var/log/httpd/access_log.2
/var/log/httpd/access_log.3
/var/log/httpd/access_log.4
/var/spool/mail/junk
/var/www/dump.sql
/root/restore_060217-0022/access_log.3
/root/restore_060217-0022/access_log.4
/home/rack/050711-0232/access_log.1
/home/rack/050711-0232/access_log.2
/home/rack/050711-0232/access_log.3
/home/rack/050711-0232/access_log.4

14. Post output from these 3 commands

uname -a
Linux athena1.mothering.com 2.4.21-32.0.1.ELsmp #1 SMP Tue May 17 17:52:23 EDT 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) 4
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) 7168
virtual memory (kbytes, -v) unlimited

cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 3.06GHz
stepping : 9
cpu MHz : 3051.297
cache size : 512 KB
physical id : 0
siblings : 2
runqueue : 0
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
bogomips : 6081.74

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 3.06GHz
stepping : 9
cpu MHz : 3051.297
cache size : 512 KB
physical id : 0
siblings : 2
runqueue : 0
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
bogomips : 6094.84

processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 3.06GHz
stepping : 9
cpu MHz : 3051.297
cache size : 512 KB
physical id : 3
siblings : 2
runqueue : 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
bogomips : 6094.84

processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 3.06GHz
stepping : 9
cpu MHz : 3051.297
cache size : 512 KB
physical id : 3
siblings : 2
runqueue : 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
bogomips : 6094.84

[eva2000]

Might want to look into upgrade to latest stable 2.6.x smp linux kernel - ask your host about such options

1. Upgrade MySQL server to 4.0.26
2. Upgrade PHP to 4.4.2 if you use phpadsnew make sure to update to phpadsnew 2.0.7 to work with PHP 4.4.1/4.4.2 - ensure you compile PHP to use the option --with-mysql=/usr instead of with shared path so you use latest mysql client libraries as well (phpinfo says you're using mysql 3.23.58 client libraries for PHP)
3. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
safe-show-database
skip-innodb
max_connections = 800
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 1800
thread_cache_size = 512
wait_timeout = 60
connect_timeout = 10
tmp_table_size = 768M
max_heap_table_size = 768M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
nice = -5
err-log=/var/log/mysqld.log
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M

[mysqlhotcopy]
interactive-timeout


4. Install APC Cache lastest version from http://pecl.php.net/package/APC. Read install guide at http://www.vbulletin.com/forum/showthread.php?t=165367 - please remove Zend Optimizer from php.ini before installing APC Cache

5. Upgrade to vB 3.5.4 http://www.vbulletin.com/forum/showthread.php?t=176170 if you're on vB 3.5.x

6. If you just upgraded to vB 3.5.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

[Cynthia]

All optimizations have been implemented. Here are the mysqlreport results:

MySQL 4.0.26-standard uptime 0 12:9:2 Tue Mar 7 07:06:57 2006

__ Key __________________________________________________ _______________
Buffer usage 243.54M of 256.00M %Used: 95.13
Write ratio 0.82
Read ratio 0.00

__ Questions __________________________________________________ _________
Total 10.23M 233.77/s
QC Hits 5.88M 134.52/s %Total: 57.54
DMS 3.43M 78.49/s 33.58
Com_ 452.45k 10.34/s 4.42
COM_QUIT 451.84k 10.33/s 4.42
+Unknown 3.73k 0.09/s 0.04
Slow 35 0.00/s 0.00 %DMS: 0.00
DMS 3.43M 78.49/s 33.58
SELECT 2.16M 49.41/s 21.14 62.95
UPDATE 836.91k 19.13/s 8.18 24.37
INSERT 416.41k 9.52/s 4.07 12.13
DELETE 10.36k 0.24/s 0.10 0.30
REPLACE 8.55k 0.20/s 0.08 0.25
Com_ 452.45k 10.34/s 4.42
change_db 451.71k 10.33/s 4.42
show_tables 171 0.00/s 0.00
show_fields 163 0.00/s 0.00

__ SELECT and Sort __________________________________________________ ___
Scan 196.40k 4.49/s %SELECT: 9.09
Range 769.86k 17.60/s 35.62
Full join 1.39k 0.03/s 0.06
Range check 0 0.00/s 0.00
Full rng join 16 0.00/s 0.00
Sort scan 15.77k 0.36/s
Sort range 141.60k 3.24/s
Sort mrg pass 75 0.00/s

__ Query Cache __________________________________________________ _______
Memory usage 28.37M of 128.00M %Used: 22.16
Block Fragmnt 16.25%
Hits 5.88M 134.5/s
Inserts 1.79M 40.85/s
Prunes 1 0.00/s
Insrt:Prune 1.79M:1 40.85/s
Hit:Insert 3.29:1

__ Table Locks __________________________________________________ _______
Waited 15.55k 0.36/s %Total: 0.37
Immediate 4.18M 95.51/s

__ Tables __________________________________________________ ____________
Open 1.67k of 1800 %Cache: 93.00
Opened 1.70k 0.04/s

__ Connections __________________________________________________ _______
Max used 225 of 800 %Max: 28.12
Total 451.87k 10.33/s

__ Created Temp __________________________________________________ ______
Disk table 1.15k 0.03/s
Table 14.49k 0.33/s
File 150 0.00/s

__ Threads __________________________________________________ ___________
Running 1 of 1
Cache 225 %Hit: 99.95
Created 226 0.01/s
Slow 0 0.00/s

__ Aborted __________________________________________________ ___________
Clients 52 0.00/s
Connects 148 0.00/s

__ Bytes __________________________________________________ _____________
Sent 3.36G 76.77k/s
Received 1.90G 43.45k/s

[Cynthia]

I am thrilled to report these server load averages today!

Server Load Averages1.85 1.98 1.83 | 1,033 Users Online (314 members and 719 guests)

Is that not a great improvement?? :)

Please do check our mysqlreport, George, and advise me if anything more is needed. I want to look into possible server upgrade needs next. Thanks so much! I wish I could put our dancing banana smilie here to celebrate!!

[eva2000]

excellent use of the query cache there :)

one change is in my.cnf change

key_buffer = 256M

to

key_buffer = 384M

restart mysql server (will reset stats) and see how it goes

[Cynthia]

Will do.

In another thread I was asked if we have installed eAccelerator. Do you advise that George?

[eva2000]

post #2, suggestion #4 = APC Cache better than eccelerator :)

[Cynthia]

I'll get back to you once the my.cnf change is complete and I run the report again. :)

[Cynthia]

mysqlreport results after making that change to my.cnf

MySQL 4.0.26-standard uptime 0 0:42:43 Tue Mar 7 14:46:51 2006

__ Key __________________________________________________ _______________
Buffer usage 106.62M of 384.00M %Used: 27.77
Write ratio 0.84
Read ratio 0.00

__ Questions __________________________________________________ _________
Total 1.59M 619.72/s
QC Hits 925.68k 361.17/s %Total: 58.28
DMS 525.35k 204.97/s 33.08
COM_QUIT 68.32k 26.66/s 4.30
Com_ 68.31k 26.65/s 4.30
+Unknown 682 0.27/s 0.04
Slow 2 0.00/s 0.00 %DMS: 0.00
DMS 525.35k 204.97/s 33.08
SELECT 327.96k 127.96/s 20.65 62.43
UPDATE 127.85k 49.88/s 8.05 24.34
INSERT 63.76k 24.88/s 4.01 12.14
REPLACE 4.29k 1.67/s 0.27 0.82
DELETE 1.49k 0.58/s 0.09 0.28
Com_ 68.31k 26.65/s 4.30
change_db 68.30k 26.65/s 4.30
show_proces 3 0.00/s 0.00
show_variab 2 0.00/s 0.00

__ SELECT and Sort __________________________________________________ ___
Scan 35.40k 13.81/s %SELECT: 10.79
Range 117.69k 45.92/s 35.89
Full join 131 0.05/s 0.04
Range check 0 0.00/s 0.00
Full rng join 8 0.00/s 0.00
Sort scan 1.94k 0.76/s
Sort range 19.57k 7.64/s
Sort mrg pass 4 0.00/s

__ Query Cache __________________________________________________ _______
Memory usage 13.59M of 128.00M %Used: 10.62
Block Fragmnt 12.86%
Hits 925.68k 361.1/s
Inserts 265.51k 103.6/s
Prunes 1 0.00/s
Insrt:Prune 265.51k:1 103.6/s
Hit:Insert 3.49:1

__ Table Locks __________________________________________________ _______
Waited 5.21k 2.03/s %Total: 0.81
Immediate 640.12k 249.75/s

__ Tables __________________________________________________ ____________
Open 335 of 1800 %Cache: 18.61
Opened 341 0.13/s

__ Connections __________________________________________________ _______
Max used 38 of 800 %Max: 4.75
Total 68.32k 26.66/s

__ Created Temp __________________________________________________ ______
Disk table 110 0.04/s
Table 1.75k 0.68/s
File 8 0.00/s

__ Threads __________________________________________________ ___________
Running 1 of 2
Cache 37 %Hit: 99.94
Created 39 0.02/s
Slow 0 0.00/s

__ Aborted __________________________________________________ ___________
Clients 0 0.00/s
Connects 15 0.01/s

__ Bytes __________________________________________________ _____________
Sent 3.32G 1.30M/s
Received 301.71M 117.72/s

[Cynthia]

Server Load Averages 31.07 10.86 6.50 | 1,335 Users Online (447 members and 888 guests)

Server Load Averages 12.68 6.76 6.01 | 1,400 Users Online (441 members and 959 guests)

Server Load Averages 11.94 8.51 6.79 | 1,452 Users Online (454 members and 998 guests)

Server Load Averages 9.50 8.15 6.81 | 1,456 Users Online (450 members and 1,006 guests)

Is this good? Can you explain that initial 31.07 and if that is a concern still?

[eva2000]

Definitely not good !

first figure is 1min interval, 2nd figure 5min, 3rd value is 15min value

so for past 1 min loads were 31.07 and 12.68 and 11.95 and 9.5

for past 5 mins they were 10.86, 6.76, 8.51 and 8.15

for past 15min intervals they were all >6.00

sounds like you need to split to a 2x dual cpu server setup

what does mysqlreport say ?

[Cynthia]

The mysqlreport after I made the change you requested to my.cnf is listed above. The post before those server load numbers.

[Cynthia]

Or do you mean you want me to run another report now?

[eva2000]

yeah run it again especially after high loads to see if it's mysql related

[Cynthia]

Just so I'm clear - run it right now, when our traffic is minimal? Or wait until we have high traffic again and run it then?

[eva2000]

as long as mysql hasn't been restarted, mysqlreport derives stats from mysql's continued logging of all mysql activity so it doesn't matter when after the high traffic period to run mysqlreport

[Cynthia]

Here it is:

MySQL 4.0.26-standard uptime 1 15:20:11 Thu Mar 9 05:24:19 2006

__ Key __________________________________________________ _______________
Buffer usage 366.26M of 384.00M %Used: 95.38
Write ratio 0.84
Read ratio 0.00

__ Questions __________________________________________________ _________
Total 52.71M 372.24/s
QC Hits 30.63M 216.29/s %Total: 58.11
DMS 17.48M 123.47/s 33.17
Com_ 2.28M 16.13/s 4.33
COM_QUIT 2.28M 16.13/s 4.33
+Unknown 31.72k 0.22/s 0.06
Slow 153 0.00/s 0.00 %DMS: 0.00
DMS 17.48M 123.47/s 33.17
SELECT 10.96M 77.39/s 20.79 62.68
UPDATE 4.28M 30.24/s 8.12 24.49
INSERT 2.12M 14.95/s 4.02 12.11
REPLACE 79.23k 0.56/s 0.15 0.45
DELETE 46.36k 0.33/s 0.09 0.27
Com_ 2.28M 16.13/s 4.33
change_db 2.28M 16.12/s 4.33
show_tables 202 0.00/s 0.00
show_fields 163 0.00/s 0.00

__ SELECT and Sort __________________________________________________ ___
Scan 1.06M 7.48/s %SELECT: 9.66
Range 3.94M 27.81/s 35.94
Full join 4.77k 0.03/s 0.04
Range check 0 0.00/s 0.00
Full rng join 23 0.00/s 0.00
Sort scan 67.05k 0.47/s
Sort range 618.87k 4.37/s
Sort mrg pass 264 0.00/s

__ Query Cache __________________________________________________ _______
Memory usage 53.46M of 128.00M %Used: 41.77
Block Fragmnt 18.73%
Hits 30.63M 216.2/s
Inserts 8.91M 62.94/s
Prunes 1 0.00/s
Insrt:Prune 8.91M:1 62.94/s
Hit:Insert 3.44:1

__ Table Locks __________________________________________________ _______
Waited 91.94k 0.65/s %Total: 0.44
Immediate 21.00M 148.31/s

__ Tables __________________________________________________ ____________
Open 1.40k of 1800 %Cache: 77.89
Opened 1.41k 0.01/s

__ Connections __________________________________________________ _______
Max used 226 of 800 %Max: 28.25
Total 2.28M 16.13/s

__ Created Temp __________________________________________________ ______
Disk table 5.16k 0.04/s
Table 64.13k 0.45/s
File 528 0.00/s

__ Threads __________________________________________________ ___________
Running 1 of 1
Cache 226 %Hit: 99.99
Created 227 0.00/s
Slow 0 0.00/s

__ Aborted __________________________________________________ ___________
Clients 136 0.00/s
Connects 500 0.00/s

__ Bytes __________________________________________________ _____________
Sent 3.87G 27.33k/s
Received 1.06G 7.46k/s

[eva2000]

You're using between 372-619 queries/second ! i suspect it's not vB that is doing this for mysql side but probably other mysql using script like your ad software which seems to be using CGI ?

how much traffic does the main non-forum site get compared to vB forum ? what would the ratio main site to forum traffic be ?

try this

1. creating a child style under your existing default vB used style/template and
2. in child style remove all traces of the ad serving code in vB templates and
3. then set this new child style as the main style overriding all member choices for styles and
4. restart mysql server to reset the mysql stats from mysqlreport to 0 and
5. if you can disable or remove ad code on main site too so zero usage of cgi ad banner script that would be better and
6. run this for 2-3 days

then run mysqlreport and see what the stats say as well as over that 2-3 days see what the cpu loads are

[Cynthia]

I'll discuss this with staff and try to get it done asap.

Thanks so much George. You have been a fantastic help! :)

[Cynthia]

Server Load Averages1.63 4.04 4.33 | 961 Users Online (443 members and 518 guests)

Server Load Averages1.59 3.03 3.89 | 950 Users Online (444 members and 506 guests)

Server Load Averages1.71 2.77 3.75 | 947 Users Online (456 members and 491 guests)

I did a test run of removing the ads from the site and got those load averages. Does that look better?

[Cynthia]

These are minutes after returning the ads. I know this is not info mysqlreport can better offer but is it a clear enough indication that the ads are problematic for the server?

Server Load Averages5.90 3.78 3.86 | 964 Users Online (461 members and 503 guests)

Server Load Averages6.20 4.49 4.10 | 942 Users Online (448 members and 494 guests)

Server Load Averages6.98 5.10 4.38 | 935 Users Online (439 members and 496 guests)

Server Load Averages7.01 5.65 4.63 | 926 Users Online (436 members and 490 guests)

Server Load Averages4.86 4.93 4.92 | 1,017 Users Online (453 members and 564 guests)

Server Load Averages4.86 4.93 4.92 | 1,017 Users Online (453 members and 564 guests)

[eva2000]

yup from those stats your culprit is your CGI/MySQL based ad banner stats!!!!! I'd look for an alternative like phpadnews which is php/mysql based with APC or eccelerator caching of PHP would help too

although those load without ad script still kind of high.. probably at a stage were you'd need to split to a 2x dual cpu servers one for web and one for mysql

[Cynthia]

So no need to remove the ads for a few days to look at CPU loads? We're sort of resolved to the fact that we do or soon need to split to two separate servers.

[eva2000]

remove ads from forums too if you only tested without ads from main site and see as well... best to know for sure whether 2nd server is needed :)

[Cynthia]

Oh I did. I removed from the entire site, forums included.

Do you think a wise first step would be to try out a php/mysql ad software before going for the second server? Or do you think it's just a good next step that might as well be done, considering our forum growth?

[Cynthia]

I just noticed the "MySQL 5.0.19 has been released (http://www.vbulletin.com/forum/showthread.php?t=178404)" thread. Will you now be advising everyone to upgrade to that?

[eva2000]

probably wise to try a php/mysql ad script first before upgrading to a 2nd server ... phpadsnew comes to mine

as to mysql 5.0.19 i'd stick with 4.0.26 if you're on 4.0.x or 4.1.18 if you're on 4.1.xx series for now.... only time to upgrade to mysql 5.0.19 right now is if you're already on mysql 5.0.xx series

[Cynthia]

Last night I noticed a sharp rise in load when we had 1000+ users online and disabled all advertising on the forum and the website. This helped but a bit later we had a spike that caused page load time outs. MySQL did not crash though. I asked our host to look into it and this was the response:

We cannot determine with certainty what was causing elevated load once the responsible processes are no longer running. I can see a period of elevated iowait load, which is indicative of heavy disk access. `sar -b` for this period indicates very heavy read access, but not writes to the disk. This disk access is more than likely due to database queries.

This corresponds with the peak traffic time according to the bandwidth graph. If all your optimization avenues have been exhausted, it may be time to consider separate database and web servers, so that the load can be spread out over two machines.


Any thoughts on this? Considering that the ads were disabled through the night so certainly that was not playing a role in the loads, and that later in the evening we had this:

Server Load Averages 35.39 17.15 10.33 | 910 Users Online (288 members and 622 guests

even though the ad software was still completely disabled, it seems to imply that we still have issues that are unrelated to the cgi-based ad software.

What do you suggest? Should I proceed with disabling the ads and restart mysql so that we can get a fresh mysql report in a couple of days?

[eva2000]

Does sound you need to split to 2 dual cpu based servers one for web and one for mysql, the ad script does contribute alot to the higher extreme loads so you would be best to change ad scripts even after splitting to 2 servers.

[Cynthia]

I disabled the ad script yesterday and restarted MySQL. Shall I run a report and post it for your review or do you feel that unnecessary? If unnecessary I'll just return the ads (we really don't want to have them disabled any longer than necessary) and move forward with the server split.

[eva2000]

yeah provide the mysqlreport even though it doesn't change the fact of splitting servers but it's also useful for you to know what your current mysql loads are now to compare with what you get in future :)

[Cynthia]

Here's the latest report:

MySQL 4.0.26-standard uptime 1 11:3:46 Wed Mar 15 05:22:51 2006

__ Key __________________________________________________ _______________
Buffer usage 366.26M of 384.00M %Used: 95.38
Write ratio 0.80
Read ratio 0.00

__ Questions __________________________________________________ _________
Total 5.98M 47.39/s
DMS 3.18M 25.23/s %Total: 53.24
QC Hits 1.97M 15.58/s 32.88
Com_ 415.60k 3.29/s 6.95
COM_QUIT 414.76k 3.29/s 6.93
-Unknown 425 0.00/s 0.01
Slow 127 0.00/s 0.00 %DMS: 0.00
DMS 3.18M 25.23/s 53.24
SELECT 2.17M 17.22/s 36.34 68.26
UPDATE 685.37k 5.43/s 11.46 21.52
INSERT 240.44k 1.90/s 4.02 7.55
DELETE 48.06k 0.38/s 0.80 1.51
REPLACE 37.05k 0.29/s 0.62 1.16
Com_ 415.60k 3.29/s 6.95
change_db 414.36k 3.28/s 6.93
show_tables 315 0.00/s 0.01
set_option 280 0.00/s 0.00

__ SELECT and Sort __________________________________________________ ___
Scan 241.19k 1.91/s %SELECT: 11.10
Range 529.39k 4.19/s 24.35
Full join 4.71k 0.04/s 0.22
Range check 0 0.00/s 0.00
Full rng join 21 0.00/s 0.00
Sort scan 65.69k 0.52/s
Sort range 429.28k 3.40/s
Sort mrg pass 405 0.00/s

__ Query Cache __________________________________________________ _______
Memory usage 46.56M of 128.00M %Used: 36.37
Block Fragmnt 17.39%
Hits 1.97M 15.58/s
Inserts 2.17M 17.18/s
Prunes 1 0.00/s
Insrt:Prune 2.17M:1 17.18/s
Hit:Insert 0.91:1

__ Table Locks __________________________________________________ _______
Waited 18.06k 0.14/s %Total: 0.31
Immediate 5.80M 45.94/s

__ Tables __________________________________________________ ____________
Open 745 of 1800 %Cache: 41.39
Opened 751 0.01/s

__ Connections __________________________________________________ _______
Max used 226 of 800 %Max: 28.25
Total 414.80k 3.29/s

__ Created Temp __________________________________________________ ______
Disk table 4.73k 0.04/s
Table 61.15k 0.48/s
File 810 0.01/s

__ Threads __________________________________________________ ___________
Running 227 of 227
Cache 0 %Hit: 99.95
Created 227 0.00/s
Slow 0 0.00/s

__ Aborted __________________________________________________ ___________
Clients 78 0.00/s
Connects 425 0.00/s

__ Bytes __________________________________________________ _____________
Sent 1.27G 10.10k/s
Received 2.43G 19.26k/s

[eva2000]

mysql looks okay i think its the web end that is overloaded so yes 2nd server is needed