Need Server Optimization Help Please

محروم.كوم · #1 04-13-2009, 01:00 PM

My server is hitting loads of close to 300 on occasion, so hopefully there is lots that can be done. Thanks for your help!

All of the info you requested is below ...

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

Code:
Dedicated server
2. your server specs. For example:

Code:
cpu speed/type single or dual cpus): 2.0ghz Celeron
how much memory installed: 512mb ram
hard drive type/configuration: 80GB EIDE non raid
linux distributor or windows version: CentOS Linux 4.7
apache/IIS version: apache 2.0.63
PHP version: php 5.1.6
MySQL version: mysql 5.0.68
3. what version of vB are you running ?
Code:
vB 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 ?

Code:
n/a
5. if possible how mysql was compiled/installed
Code:
downloaded pre-compiled rpm from and distributed by centos.org
don't know about any special compile options
6. your top stats. So log into your server via ssh telnet as root user and type the command in bold - top

Code:
top - 02:33:32 up 41 min, 1 user, load average: 0.33, 0.21, 0.19
Tasks: 79 total, 2 running, 77 sleeping, 0 stopped, 0 zombie
Cpu(s): 11.0% us, 0.3% sy, 0.0% ni, 87.7% id, 1.0% wa, 0.0% hi, 0.0% si
Mem: 449840k total, 446396k used, 3444k free, 37556k buffers
Swap: 915696k total, 0k used, 915696k free, 177892k cached
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.

Code:
[root@kcsr conf]# top
top - 03:52:22 up 2:00, 1 user, load average: 0.17, 0.75, 1.00
Tasks: 79 total, 1 running, 78 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.0% us, 0.0% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 449840k total, 447544k used, 2296k free, 18484k buffers
Swap: 915696k total, 144k used, 915552k free, 156572k cached
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).

Code:
[root@kcsr etc]# cat my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
set-variable = key_buffer=100M
set-variable = table_cache=400

# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
connect-string="nodeid=2;host=localhost:1186"

[ndb_mgm]
# connection string for MySQL Cluster management tool
connect-string="host=localhost:1186"
8. MySQL stats from ssh telnet as root user type:

Code:
| Com_help | 0 |
| Com_insert | 906 |
| Com_insert_select | 4 |
| 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 | 134 |
| 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 | 20244 |
| 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 | 1 |
| 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_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 | 0 |
| 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 | 3 |
| Com_unlock_tables | 0 |
| Com_update | 2915 |
| Com_update_multi | 3 |
| 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 | 2706 |
| Created_tmp_disk_tables | 119 |
| Created_tmp_files | 7 |
| Created_tmp_tables | 817 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 2592 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 5599 |
| Handler_read_key | 495328 |
| Handler_read_next | 568357 |
| Handler_read_prev | 13649 |
| Handler_read_rnd | 30117 |
| Handler_read_rnd_next | 578751 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 3175 |
| Handler_write | 26624 |
| Innodb_buffer_pool_pages_data | 19 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 493 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 77 |
| Innodb_buffer_pool_reads | 12 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2494464 |
| Innodb_data_reads | 25 |
| Innodb_data_writes | 3 |

| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 19 |
| 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 | 82602 |
| Key_blocks_used | 8277 |
| Key_read_requests | 1412162 |
| Key_reads | 8306 |
| Key_write_requests | 10511 |
| Key_writes | 3593 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 4 |
| 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 | 285 |
| Open_streams | 0 |
| Open_tables | 160 |
| Opened_tables | 178 |
| Prepared_stmt_count | 0 |
| 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 | 29821 |
| Rpl_status | NULL |
| Select_full_join | 1 |
| Select_full_range_join | 0 |
| Select_range | 7604 |
| Select_range_check | 0 |
| Select_scan | 2404 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 1 |
| Sort_range | 1941 |
| Sort_rows | 61403 |
| Sort_scan | 685 |
| 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 | 37022 |
| Table_locks_waited | 18 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 2705 |
| Threads_running | 1 |
| Uptime | 2395 |
+-----------------------------------+-----------+
Uptime: 2395 Threads: 1 Questions: 29822 Slow queries: 0 Opens: 178 Flush tables: 1 Open tables: 160 Queries per second avg: 12.452
mysqladmin Ver 8.41 Distrib 5.0.68, 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.68
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 39 min 55 sec

Threads: 1 Questions: 29822 Slow queries: 0 Opens: 178 Flush tables: 1 Open tables: 160 Queries per second avg: 12.452
+------+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+----+---------+------+-------+------------------+
| 2705 | root | localhost | | Query | 0 | | show processlist |
+------+------+-----------+----+---------+------+-------+------------------+
Also if you want to better see what's going on install mysqlreport at http://www.vbulletin.com/forum/showthread.php?t=175177 and run it by typing
Quote:
./mysqlreport -r 10 > /statistics.txt
in ssh telnet and then copy and paste the output you see or look into statistics.txt file located at root directory /
Example below

Code:
#
# Beginning report, 0 0:0:0
#
MySQL 5.0.68 uptime 0 040 Mon Apr 13 02:55:35 2009

__ Key __________________________________________________ _______________
Buffer used 8.86M of 100.00M %Used: 8.86
Current 20.11M %Usage: 20.11
Write hit 62.16%
Read hit 99.45%

__ Questions __________________________________________________ _________
Total 39.97k 12.2/s
DMS 32.43k 9.9/s %Total: 81.14
Com_ 3.78k 1.2/s 9.45
COM_QUIT 3.77k 1.2/s 9.44
-Unknown 13 0.0/s 0.03
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 32.43k 9.9/s 81.14
SELECT 27.08k 8.3/s 67.76 83.52
UPDATE 3.80k 1.2/s 9.51 11.72
INSERT 1.14k 0.3/s 2.86 3.53
DELETE 239 0.1/s 0.60 0.74
REPLACE 161 0.0/s 0.40 0.50
Com_ 3.78k 1.2/s 9.45
change_db 3.58k 1.1/s 8.96
show_keys 160 0.0/s 0.40
show_status 7 0.0/s 0.02

__ SELECT and Sort __________________________________________________ ___
Scan 3.35k 1.0/s %SELECT: 12.35
Range 10.33k 3.1/s 38.14
Full join 1 0.0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 924 0.3/s
Sort range 2.62k 0.8/s
Sort mrg pass 2 0.0/s

__ Table Locks __________________________________________________ _______
Waited 22 0.0/s %Total: 0.04
Immediate 49.24k 15.0/s

__ Tables __________________________________________________ ____________
Open 203 of 400 %Cache: 50.75
Opened 221 0.1/s

__ Connections __________________________________________________ _______
Max used 8 of 100 %Max: 8.00
Total 3.78k 1.2/s

__ Created Temp __________________________________________________ ______
Disk table 143 0.0/s
Table 1.24k 0.4/s Size: 32.0M
File 9 0.0/s

__ Threads __________________________________________________ ___________
Running 1 of 1
Cached 0 of 0 %Hit: 0.03
Created 3.78k 1.2/s
Slow 0 0/s

__ Aborted __________________________________________________ ___________
Clients 0 0/s
Connects 9 0.0/s

__ Bytes __________________________________________________ _____________
Sent 1.04G 316.9k/s
Received 11.47M 3.5k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 304.00k of 8.00M %Used: 3.71
Read hit 84.42%
Pages
Free 493 %Total: 96.29
Data 19 3.71 %Drty: 0.00
Misc 0 0.00
Latched 0.00
Reads 77 0.0/s
From file 12 0.0/s 15.58
Ahead Rnd 1 0.0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s

__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 25 0.0/s
Writes 3 0.0/s
fsync 3 0.0/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 0 0/s
Read 19 0.0/s
Written 0 0/s

Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s

#
# Interval report 1, +0 0:0:10
#
MySQL 5.0.68 uptime 0 050 Mon Apr 13 02:55:45 2009

__ Key __________________________________________________ _______________
Buffer used 8.86M of 100.00M %Used: 8.86
Current 100.00M %Usage: 100.00
Write hit 0.00%
Read hit 100.00%

__ Questions __________________________________________________ _________
Total 29 2.9/s
DMS 20 2.0/s %Total: 68.97
Com_ 6 0.6/s 20.69
COM_QUIT 2 0.2/s 6.90
+Unknown 1 0.1/s 3.45
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 20 2.0/s 68.97
SELECT 18 1.8/s 62.07 90.00
UPDATE 2 0.2/s 6.90 10.00
REPLACE 0 0/s 0.00 0.00
DELETE 0 0/s 0.00 0.00
INSERT 0 0/s 0.00 0.00
Com_ 6 0.6/s 20.69
change_db 3 0.3/s 10.34
show_status 1 0.1/s 3.45
set_option 1 0.1/s 3.45

__ SELECT and Sort __________________________________________________ ___
Scan 4 0.4/s %SELECT: 22.22
Range 6 0.6/s 33.33
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 1 0.1/s
Sort range 1 0.1/s
Sort mrg pass 0 0/s

__ Table Locks __________________________________________________ _______
Waited 0 0/s %Total: 0.00
Immediate 31 3.1/s

__ Tables __________________________________________________ ____________
Open 203 of 400 %Cache: 50.75
Opened 0 0/s

__ Connections __________________________________________________ _______
Max used 8 of 100 %Max: 8.00
Total 4 0.4/s

__ Created Temp __________________________________________________ ______
Disk table 0 0/s
Table 3 0.3/s Size: 32.0M
File 0 0/s

__ Threads __________________________________________________ ___________
Running 1 of 2
Cached 0 of 0 %Hit: 0
Created 4 0.4/s
Slow 0 0/s

__ Aborted __________________________________________________ ___________
Clients 0 0/s
Connects 0 0/s

__ Bytes __________________________________________________ _____________
Sent 644.33k 64.4k/s
Received 6.56k 656.1/s

__ InnoDB Buffer Pool __________________________________________________
Usage 304.00k of 8.00M %Used: 3.71
Read hit 0.00%
Pages
Free 493 %Total: 96.29
Data 19 3.71 %Drty: 0.00
Misc 0 0.00
Latched 0.00
Reads 0 0/s
From file 0 0/s 0.00
Ahead Rnd 0 0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s

__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 0 0/s
Writes 0 0/s
fsync 0 0/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 0 0/s
Read 0 0/s
Written 0 0/s

Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s
9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
Code:
yes, vB is only thing on server.
10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?

Code:
250 avg users
750 max users
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!

Code:
http://www.kcsr.org/PHPBB-root/20090219888/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

Code:
KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 10
MinSpareServers 5
MaxSpareServers 20
StartServers 2
MaxClients 150

Maxrequestsperchild value = 0
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

Code:
find / -size +2000000k

checked and found none
14. Post output from these 3 commands

uname -a

ulimit -aH

cat /proc/cpuinfo

For FreeBSD:

dmesg | grep -i CPU

Code:
[root@kcsr conf]# uname -a
Linux kcsr.org 2.6.9-42.0.3.EL #1 Fri Oct 6 05:59:54 CDT 2006 i686 athlon i386 GNU/Linux
[root@kcsr conf]# ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 7150
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
[root@kcsr conf]# cat /proc/cpuinfo
processor : 0
vendor_id : AuthenticAMD
cpu family : 15
model : 44
model name : AMD Athlon(tm) 64 Processor 3000+
stepping : 2
cpu MHz : 2000.870
cache size : 512 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 apic mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx mmxext lm 3dnowext 3dnow pni
bogomips : 4003.11

[root@kcsr conf]# __DEFINE_LIKE_SHARE__

المواضيع المتشابهه
الموضوع	كاتب الموضوع	المنتدى	مشاركات	آخر مشاركة
Server Optimization Requst - Moved to new server	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	08-02-2009 10:30 AM
Web Server + DB Server Optimization Request	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	07-29-2009 08:30 PM
Web Server + DB Server Optimization Request	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	07-29-2009 08:20 PM
Server Optimization Request. First time optimization	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	06-08-2009 04:40 AM
Server Optimization Request - Please help! Server loads way too high!	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	05-05-2009 02:00 AM