Server Optimization Request
 

1. Is this on dedicated or shared virtual server.

This is a new dedicated server that I split into several VPS with OpenVZ web server and database server were separated as instructed http://www.jasonlitka.com/2006/12/13...s-with-openvz/

2. your server specs.
cpu speed/type single or dual cpus): Core2Quad 6600
how much memory installed: 8GB ram
hard drive type/configuration: 250GB + 160 GB SATA: Centos 5.2
apache/IIS version: apache 2.2.8
linux distributor or windows version: Centos 5.2
PHP version: php 5.2.6
MySQL version: mysql 5.0.58

3. what version of vB are you running ? 3.8.2

4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
5. if possible how mysql was compiled/installed with yum
6. your top stats. So log into your server via ssh telnet as root user and type the command in bold - top
top - 07:42:59 up 1 day, 2:32, 1 user, load average: 0.03, 0.03, 0.00
Mem: 8159420k total, 325944k used, 7833476k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9260 mysql 19 0 133m 18m 4860 S 0.0 0.2 0:03.82 mysqld
5695 apache 18 0 64388 21m 13m S 3.3 0.3 0:01.70 httpd
5647 apache 15 0 64600 19m 11m S 0.0 0.2 0:01.63 httpd
5843 apache 18 0 66524 20m 11m S 0.0 0.3 0:01.10 httpd
9290 root 15 0 9088 1872 792 S 0.0 0.0 0:01.05 sendmail
5885 apache 15 0 63612 18m 11m S 5.3 0.2 0:00.89 httpd
5829 apache 15 0 64968 19m 11m S 0.0 0.2 0:00.88 httpd
1 root 18 0 1984 648 560 S 0.0 0.0 0:00.84 init
5687 apache 18 0 64932 19m 11m S 0.0 0.2 0:00.75 httpd
5681 apache 18 0 64544 18m 11m S 0.0 0.2 0:00.73 httpd
5816 apache 15 0 64748 16m 9360 S 0.0 0.2 0:00.72 httpd
5845 apache 15 0 64980 17m 9.9m S 0.0 0.2 0:00.64 httpd
5838 apache 15 0 62480 15m 9m S 0.0 0.2 0:00.59 httpd
5839 apache 18 0 64656 16m 9632 S 0.0 0.2 0:00.58 httpd
6100 apache 15 0 66176 18m 9824 S 0.0 0.2 0:00.57 httpd
5955 apache 15 0 65420 19m 11m S 0.0 0.2 0:00.56 httpd
5890 apache 15 0 62440 14m 9100 S 0.0 0.2 0:00.51 httpd
5957 apache 15 0 64284 17m 10m S 0.0 0.2 0:00.46 httpd
5939 apache 15 0 62444 14m 9396 S 0.0 0.2 0:00.40 httpd

You'll see stats like the following



Now while top is running hit the number 1 on keyboard to show break down of cpu load over all cpu cores. For example a Q6600 quad core kentsfield processor will have 4 cores. A dual Xeon 53xx/54xx quad core clovertown and harpertown processor based server will have 2x 4 = 8 cores listed.

i.e. after hitting 1

Quote:
top - 07:44:50 up 1 day, 2:34, 1 user, load average: 0.04, 0.03, 0.00
Tasks: 43 total, 1 running, 42 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 0.3%us, 0.0%sy, 0.0%ni, 99.0%id, 0.7%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 1.3%us, 0.3%sy, 0.0%ni, 97.4%id, 1.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8159420k total, 339896k used, 7819524k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached


Best to provide the 2nd example top stats in your threads where possible.

7. 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]
#skip-name-resolve
safe-show-database
#old_passwords
back_log = 50
skip-innodb
max_connections = 500
key_buffer_size = 144M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 8000
thread_cache_size = 384
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 100000
thread_concurrency = 8
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 96M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 4

[mysqld_safe]
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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


8. MySQL stats from ssh telnet as root user type:

Quote:
mysqladmin -u root -p var ext stat ver proc
| Com_help | 0 |
| Com_insert | 733 |
| Com_insert_select | 9 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 311 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 13322 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 24 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 91 |
| Com_show_triggers | 0 |
| Com_show_variables | 3 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 2 |
| Com_unlock_tables | 0 |
| Com_update | 2145 |
| Com_update_multi | 2 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 3741 |
| Created_tmp_disk_tables | 194 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 1160 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 296 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 2969 |
| Handler_read_key | 504955 |
| Handler_read_next | 672122 |
| Handler_read_prev | 18275 |
| Handler_read_rnd | 28402 |
| Handler_read_rnd_next | 2311849 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 67590 |
| Handler_write | 70537 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 118218 |
| Key_blocks_used | 12379 |
| Key_read_requests | 1497315 |
| Key_reads | 12386 |
| Key_write_requests | 2476 |
| Key_writes | 1823 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 57 |
| Ndb_cluster_node_id | 0 |
| Ndb_config_from_host | |
| Ndb_config_from_port | 0 |
| Ndb_number_of_data_nodes | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 529 |
| Open_streams | 0 |
| Open_tables | 274 |
| Opened_tables | 288 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 627 |
| Qcache_free_memory | 83950856 |
| Qcache_hits | 13987 |
| Qcache_inserts | 12876 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 566 |
| Qcache_queries_in_cache | 2814 |
| Qcache_total_blocks | 6549 |
| Questions | 38121 |
| Rpl_status | NULL |
| Select_full_join | 73 |
| Select_full_range_join | 0 |
| Select_range | 3232 |
| Select_range_check | 0 |
| Select_scan | 2200 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 2182 |
| Sort_rows | 215801 |
| Sort_scan | 1095 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 34116 |
| Table_locks_waited | 2 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 56 |
| Threads_connected | 1 |
| Threads_created | 57 |
| Threads_running | 1 |
| Uptime | 4431 |
+-----------------------------------+-----------+
Uptime: 4432 Threads: 1 Questions: 38122 Slow queries: 0 Opens: 288 Flush tables: 1 Open tables: 274 Queries per second avg: 8.602
mysqladmin Ver 8.41 Distrib 5.0.58, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.58
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 1 hour 13 min 53 sec

Threads: 1 Questions: 38122 Slow queries: 0 Opens: 288 Flush tables: 1 Open tables: 274 Queries per second avg: 8.600
+------+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+----+---------+------+-------+------------------+
| 3739 | root | localhost | | Query | 0 | | show processlist |
+------+------+-----------+----+---------+------+-------+------------------+




9. is your vB the only thing on the server? yes

10. how many average -200 and max concurrent users on your vB forum ? 412 and what your cookie timeout is ? 900

11. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site. Please url link only - DO NOT copy and paste entire phpinfo.php page's contents to the thread!

fishyforum.com/phpinfo.php

12. 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 :

Apache 2 users: If you can't find these values in httpd.conf it's because they have moved. You may find the values in /usr/local/apache/conf/extra for http-mpm.conf and http-default.conf

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 4
MinSpareServers 10
MaxSpareServers 20
StartServers 10
MaxClients 256

AND Maxrequestsperchild value = 1000



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

You can use this command to find such files
Quote:
find / -size +2000000k
14. Post output from these 3 commands

uname -a
Linux web.fishyforum.com 2.6.18-92.1.18.el5.028stab060.2 #1 SMP Tue Jan 13 11:38:36 MSK 2009 i686 i686 i386 GNU/Linux

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

cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
stepping : 11
cpu MHz : 2394.053
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 0
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
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 syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips : 4789.99
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
stepping : 11
cpu MHz : 2394.053
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 1
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
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 syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips : 4787.23
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:

processor : 2
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
stepping : 11
cpu MHz : 2394.053
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 2
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
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 syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips : 4787.33
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:

processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
stepping : 11
cpu MHz : 2394.053
cache size : 4096 KB
physical id : 0
siblings : 4
core id : 3
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
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 syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips : 4787.33
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management: