Server Optimization Please

[luck777jojo]

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

This is a dedicated server only running the vbb and a few domains that redirect to the vbb


2. your server specs.

cpu speed/type single or dual cpus: single AMD Opteron 148
how much memory installed: 2GB RAM
hard drive type/configuration: 2 x 160GB RAID1
linux distributor or windows version: SuSe 9.3 Linux kernel 2.6.11.4
apache/IIS version: Apache v2.0.53
PHP version: PHP 4.3.10
MySQL version: MySQL 4.1.10a

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

default install with SuSe 9.3


5. your top stats


top - 20:43:18 up 5:50, 2 users, load average: 2.46, 3.18, 3.37
Tasks: 203 total, 2 running, 201 sleeping, 0 stopped, 0 zombie
Cpu(s): 47.7% us, 3.3% sy, 0.0% ni, 46.0% id, 1.0% wa, 1.3% hi, 0.7% si
Mem: 2076532k total, 1846748k used, 229784k free, 81768k buffers
Swap: 1052248k total, 0k used, 1052248k free, 915528k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6860 wwwrun 15 0 25992 15m 5904 S 11.0 0.8 0:01.15 httpd2-prefork
6647 wwwrun 15 0 26200 15m 5904 S 8.3 0.8 0:02.26 httpd2-prefork
6410 wwwrun 15 0 26092 15m 5908 S 8.0 0.8 0:01.94 httpd2-prefork
6979 wwwrun 15 0 26128 15m 5904 S 6.0 0.8 0:00.83 httpd2-prefork
7242 wwwrun 15 0 25744 15m 5888 S 5.7 0.8 0:00.17 httpd2-prefork
5600 mysql 15 0 113m 39m 4420 S 5.0 1.9 18:07.76 mysqld
6509 wwwrun 15 0 26228 15m 5900 S 4.7 0.8 0:01.11 httpd2-prefork
4518 wwwrun 15 0 26436 15m 5924 S 0.7 0.8 0:04.40 httpd2-prefork
5692 wwwrun 15 0 28748 18m 5972 S 0.7 0.9 0:03.16 httpd2-prefork
4622 wwwrun 15 0 26132 15m 5912 S 0.3 0.8 0:06.29 httpd2-prefork
6051 wwwrun 15 0 26136 15m 5912 S 0.3 0.8 0:01.58 httpd2-prefork
6289 wwwrun 15 0 26008 15m 5912 S 0.3 0.8 0:03.02 httpd2-prefork
6295 wwwrun 15 0 27620 17m 5956 S 0.3 0.9 0:02.14 httpd2-prefork
6491 wwwrun 15 0 26040 15m 5900 S 0.3 0.8 0:00.62 httpd2-prefork
7180 wwwrun 15 0 26228 15m 5896 S 0.3 0.8 0:01.29 httpd2-prefork
7193 wwwrun 15 0 26232 15m 5900 S 0.3 0.8 0:00.33 httpd2-prefork
7394 wwwrun 15 0 24020 13m 5856 S 0.3 0.7 0:00.08 httpd2-prefork



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


[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
server-id = 1

[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout



7. MySQL stats from ssh telnet as root user type:
Quote:
mysqladmin -u root -p var ext stat ver


copy and paste output here


+---------------------------------+----------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------+
| back_log | 50 |
| basedir | /usr/ |
| 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_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | YES |
| have_ndbcluster | NO |
| 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 | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| 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 | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| 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_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 |
| net_buffer_length | 8192 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| pid_file | /var/lib/mysql/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| 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 | 258048 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| relay_log_purge | ON |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 524280 |
| sql_mode | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| sync_frm | ON |
| system_time_zone | CEST |
| 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.10a |
| version_comment | SUSE MySQL RPM |
| version_compile_machine | i686 |
| version_compile_os | suse-linux |
| wait_timeout | 28800 |
+---------------------------------+----------------------------+
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 32 |
| Aborted_connects | 12 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 246446314 |
| Bytes_sent | 1768496233 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 50205 |
| 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 | 4288 |
| 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 | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 31709 |
| Com_insert_select | 93 |
| 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 | 3185 |
| 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 | 533175 |
| Com_set_option | 137 |
| 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 | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_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 | 7 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 5 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 59254 |
| Com_update_multi | 0 |
| Connections | 50322 |
| Created_tmp_disk_tables | 6596 |
| Created_tmp_files | 1797 |
| Created_tmp_tables | 37410 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 11706 |
| Handler_discover | 0 |
| Handler_read_first | 6495 |
| Handler_read_key | 43377028 |
| Handler_read_next | 49921564 |
| Handler_read_prev | 235576 |
| Handler_read_rnd | 26721489 |
| Handler_read_rnd_next | 140104294 |
| Handler_rollback | 138 |
| Handler_update | 92381 |
| Handler_write | 25906950 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 14497 |
| Key_read_requests | 131621453 |
| Key_reads | 672777 |
| Key_write_requests | 111983 |
| Key_writes | 49883 |
| Max_used_connections | 39 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 93 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 9135 |
| 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 | 732343 |
| Rpl_status | NULL |
| Select_full_join | 51 |
| Select_full_range_join | 41 |
| Select_range | 191494 |
| Select_range_check | 0 |
| Select_scan | 97743 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 301 |
| Slow_queries | 31 |
| Sort_merge_passes | 1009 |
| Sort_range | 88714 |
| Sort_rows | 30739746 |
| Sort_scan | 41287 |
| Table_locks_immediate | 972051 |
| Table_locks_waited | 4924 |
| Threads_cached | 0 |
| Threads_connected | 4 |
| Threads_created | 50321 |
| Threads_running | 1 |
| Uptime | 21743 |
+--------------------------+------------+
Uptime: 21743 Threads: 5 Questions: 732346 Slow queries: 31 Opens: 9135 Flush tables: 1 Open tables: 64 Queries per second avg: 33.682
mysqladmin Ver 8.41 Distrib 4.1.10a, for suse-linux 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.10a
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 6 hours 2 min 23 sec
Threads: 5 Questions: 732346 Slow queries: 31 Opens: 9135 Flush tables: 1 Open tables: 64 Queries per second avg: 33.682



8. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?

vB is the only thing on the server that uses php or mysql, the redirects use .htaccess for 301 redirecting

9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?


Average is about 700 users
Max is about 980 users
Cookie timeout is 1800 seconds


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.mpcforum.com/l7j/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.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 files above 2GB are found

14. Post output from these 3 commands

uname -a


Linux h750426 2.6.11.4-21.11-default #1 Thu Feb 2 20:54:26 UTC 2006 i686 athlon 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) 16383
virtual memory (kbytes, -v) unlimited


cat /proc/cpuinfo

processor : 0
vendor_id : AuthenticAMD
cpu family : 15
model : 39
model name : AMD Opteron(tm) Processor 148
stepping : 1
cpu MHz : 2195.999
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 1
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 pni syscall nx mmxext fxsr_opt lm 3dnowext 3dnow pni lahf_lm
bogomips : 4341.76



Thanks in advance for helping out :)

[Odysseus]

You don't user the built-in mysql query cache, and you don't have a php encoder installed.

Enable the query cache by adding this line to /et/my.cnf:

query_cache_size = 96M

Also, you should remove these Zend programs (if you don't really, really, really need them for a goot reason) and instead of them all, install APC Cache:

http://www.vbulletin.com/forum/showthread.php?t=165367

This will reduce server load dramatically.


The third problem is your PHP version, 4.3.10. If this is a "true" 4.3.10, then it contains a "bug", which affects vBulletin and makes it slower than it should be. Update to 4.4.2. (If this is a SuSE package installed via Yast, the bug is not contained in it).


However, if your forum has an avergage of 700 users browsing (at this timeout), your machine might be underpowered. A dual CPU or dual core system would be useful here.

[Zachery]

Hmm, thats ALOT of cacheing for querys, 32mb should be more than enough if not 24mb.

You are in need of some upgrades though, namely php mysql and apache all are out of date.

You should update to in this order
Apache 2.0.55
Then upgrade mysql to 4.1.18
Now upgrade php to 4.4.2 and compile with mysql as well as your systems apache version.

[eva2000]

not really that much for query caching but best to install and run mysqlreport http://www.vbulletin.com/forum/showthread.php?t=175177 to get a better picture of mysql memory usage and stats etc

1. Upgrade MySQL server to 4.1.18 http://www.vbulletin.com/forum/showthread.php?t=175163
2. Upgrade PHP to 4.4.2 if you use phpadsnew make sure to update to phpadsnew 2.0.7 or higher to work with PHP 4.4.1/4.4.2
3. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards


[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
safe-show-database
back_log = 50
skip-innodb
max_connections = 500
key_buffer = 32M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 60
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 96M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM

[mysqld_safe]
#nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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

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

[luck777jojo]

Thanks for the help, the suggestions did help a bit, still getting a pretty high server load (about 1.5 - 2 average) but at least the pages load more or less at an acceptible rate which is good.

I also figured out that I should never update Plesk ever :x
This was the first time I tried even bothering with updating plesk as I usually just update the components manually and never bother with the stupid control panels that the hosting company installs.
Just keep em around because I need to use their automated backup feature otherwise I cannot easily backup to the backup FTP the hosting company provides. They have the backup FTP behind a firewall and disconnected from the net so I need to manually figure out all the ports and **** if I don't use the software provided.

In any case it seems that plesk installed 2 copies of qmail (similar to sendmail) when it was upgraded (even though I never used qmail, but noone asked me) and that was killing the server by running them both at once trying to figure out who gets which email.

So yeah in conclusion if anyone is running plesk 7.4 don't update it to 7.5 :x

[eva2000]

get rid of plesk and use something like whm/cpanel :)

[luck777jojo]

actually I'm thinking about getting rid of any control pannels at all, I've been having nothing but problems with them. A clean linux install would be alot better and plus I mean it's not that hard to configure it manually, I don't even know why people bother with these control panels, they're just extra overhead

[Scott MacVicar]

I use a plain debian box, I think control panels are geared towards those who host sites. I know how to edit the config files and add mail accounts. It's pretty simple.

I know that all the control panels add extra overheads and mess about with configurations, they do however perform the task of providing easy management interfaces well.