Server Optimization Request

محروم.كوم · #1 06-17-2009, 01:21 AM

Hello,

I'm looking for help for optimizing my dedicated server. I have two servers one for httpd (Apache) and other for MySQL.

This is the information for optimization.

1. Dedicated
2. HTTP Server:
Dual processor: Intel Xeon-Harpertown 5430-Quadcore 2.66 GHZ
8 GB RAM
2x Western Digital WD Caviar RE 16 SATA II
CentOS 5.2 64 Bits
PHP: 5.1.6
MySQL: 5.0.45

MySQL Server
Same servers specs but with CentOS 5.3 64 Bits

3. vBulletin 3.8.3

4. No
5. Compiled from source with default settings.
6.
Apache: Code:
top - 17:52:41 up 19 days, 22:28, 2 users, load average: 2.99, 6.56, 7.99
Tasks: 379 total, 2 running, 376 sleeping, 0 stopped, 1 zombie
Cpu(s): 17.3%us, 0.9%sy, 0.0%ni, 81.3%id, 0.0%wa, 0.0%hi, 0.4%si, 0.0%st
Mem: 8178308k total, 4873604k used, 3304704k free, 296872k buffers
Swap: 2096472k total, 68832k used, 2027640k free, 1368692k cached

top - 17:55:32 up 19 days, 22:31, 2 users, load average: 5.07, 5.51, 7.31
Tasks: 403 total, 1 running, 402 sleeping, 0 stopped, 0 zombie
Cpu0 : 17.3%us, 1.6%sy, 0.0%ni, 80.8%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 18.6%us, 1.7%sy, 0.0%ni, 78.6%id, 0.9%wa, 0.0%hi, 0.1%si, 0.0%st
Cpu2 : 20.9%us, 1.9%sy, 0.0%ni, 76.9%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 21.3%us, 1.9%sy, 0.0%ni, 76.3%id, 0.4%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 19.5%us, 1.7%sy, 0.0%ni, 78.0%id, 0.7%wa, 0.0%hi, 0.1%si, 0.0%st
Cpu5 : 19.4%us, 1.7%sy, 0.0%ni, 78.6%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 23.8%us, 2.0%sy, 0.0%ni, 73.7%id, 0.4%wa, 0.0%hi, 0.1%si, 0.0%st
Cpu7 : 30.3%us, 3.0%sy, 0.0%ni, 65.0%id, 0.5%wa, 0.1%hi, 1.0%si, 0.0%st
Mem: 8178308k total, 5180644k used, 2997664k free, 297024k buffers
Swap: 2096472k total, 68832k used, 2027640k free, 1368944k cached
MySQL:
Code:
top - 15:53:12 up 1 day, 1:39, 2 users, load average: 1.65, 2.41, 2.92
Tasks: 167 total, 1 running, 166 sleeping, 0 stopped, 0 zombie
Cpu(s): 31.0%us, 2.2%sy, 0.0%ni, 66.7%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 8178252k total, 4297716k used, 3880536k free, 476572k buffers
Swap: 1052248k total, 0k used, 1052248k free, 2539952k cached

top - 15:55:18 up 1 day, 1:41, 2 users, load average: 3.63, 2.99, 3.06
Tasks: 167 total, 1 running, 166 sleeping, 0 stopped, 0 zombie
Cpu0 : 1.3%us, 0.7%sy, 0.0%ni, 98.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 1.0%us, 0.7%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 0.7%us, 0.7%sy, 0.0%ni, 98.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 51.3%us, 3.3%sy, 0.0%ni, 45.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 2.3%us, 1.0%sy, 0.0%ni, 96.7%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st
Cpu5 : 5.4%us, 0.7%sy, 0.0%ni, 93.6%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 11.8%us, 1.6%sy, 0.0%ni, 86.2%id, 0.4%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 9.3%us, 3.3%sy, 0.0%ni, 86.4%id, 0.6%wa, 0.1%hi, 0.4%si, 0.0%st
Mem: 8178252k total, 4296844k used, 3881408k free, 477428k buffers
Swap: 1052248k total, 0k used, 1052248k free, 2540132k cached
7. my.cnf
Code:
[mysqld]
skip-name-resolve
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
safe-show-database
back_log = 50
max_connections = 800
key_buffer_size = 144M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 384
wait_timeout = 90
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 = 1M
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 = 8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 256M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
8.
Code:
#
# Beginning report, 0 0:0:0
#
MySQL 5.0.45 uptime 0 22:0:22 Tue Jun 16 16:07:06 2009

__ Key __________________________________________________ _______________
Buffer used 117.97M of 144.00M %Used: 81.92
Current 144.00M %Usage: 100.00
Write hit 16.49%
Read hit 99.96%

__ Questions __________________________________________________ _________
Total 7.75M 97.8/s
DMS 4.21M 53.2/s %Total: 54.35
QC Hits 2.09M 26.3/s 26.93
Com_ 898.07k 11.3/s 11.59
COM_QUIT 551.60k 7.0/s 7.12
+Unknown 885 0.0/s 0.01
Slow 10 s 772 0.0/s 0.01 %DMS: 0.02 Log: OFF
DMS 4.21M 53.2/s 54.35
SELECT 3.05M 38.5/s 39.37 72.44
UPDATE 561.30k 7.1/s 7.24 13.32
INSERT 483.55k 6.1/s 6.24 11.48
DELETE 97.76k 1.2/s 1.26 2.32
REPLACE 18.42k 0.2/s 0.24 0.44
Com_ 898.07k 11.3/s 11.59
change_db 867.81k 11.0/s 11.20
show_fields 29.84k 0.4/s 0.38
drop_table 149 0.0/s 0.00

__ SELECT and Sort __________________________________________________ ___
Scan 319.58k 4.0/s %SELECT: 10.47
Range 834.84k 10.5/s 27.36
Full join 5 0.0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 101.44k 1.3/s
Sort range 1.25M 15.7/s
Sort mrg pass 135.61k 1.7/s

__ Query Cache __________________________________________________ _______
Memory usage 29.66M of 96.00M %Used: 30.89
Block Fragmnt 21.33%
Hits 2.09M 26.3/s
Inserts 2.94M 37.1/s
Insrt:Prune 17.03:1 34.9/s
Hit:Insert 0.71:1

__ Table Locks __________________________________________________ _______
Waited 546.65k 6.9/s %Total: 6.01
Immediate 8.55M 107.9/s

__ Tables __________________________________________________ ____________
Open 3503 of 4000 %Cache: 87.58
Opened 4.11k 0.1/s

__ Connections __________________________________________________ _______
Max used 390 of 800 %Max: 48.75
Total 551.94k 7.0/s

__ Created Temp __________________________________________________ ______
Disk table 52.87k 0.7/s
Table 213.24k 2.7/s Size: 128.0M
File 262.39k 3.3/s

__ Threads __________________________________________________ ___________
Running 75 of 89
Cached 298 of 384 %Hit: 99.93
Created 390 0.0/s
Slow 0 0/s

__ Aborted __________________________________________________ ___________
Clients 684 0.0/s
Connects 14 0.0/s

__ Bytes __________________________________________________ _____________
Sent 59.87G 755.8k/s
Received 2.27G 28.6k/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 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.45 uptime 0 22:0:32 Tue Jun 16 16:07:16 2009

__ Key __________________________________________________ _______________
Buffer used 117.97M of 144.00M %Used: 81.92
Current 144.00M %Usage: 100.00
Write hit 9.52%
Read hit 99.98%

__ Questions __________________________________________________ _________
Total 1.94k 194.3/s
DMS 1.11k 111.2/s %Total: 57.23
QC Hits 455 45.5/s 23.42
Com_ 219 21.9/s 11.27
COM_QUIT 119 11.9/s 6.12
+Unknown 38 3.8/s 1.96
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 1.11k 111.2/s 57.23
SELECT 763 76.3/s 39.27 68.62
UPDATE 170 17.0/s 8.75 15.29
INSERT 148 14.8/s 7.62 13.31
DELETE 29 2.9/s 1.49 2.61
REPLACE 2 0.2/s 0.10 0.18

Com_ 219 21.9/s 11.27
change_db 204 20.4/s 10.50
show_fields 12 1.2/s 0.62
show_status 1 0.1/s 0.05

__ SELECT and Sort __________________________________________________ ___
Scan 88 8.8/s %SELECT: 11.53
Range 212 21.2/s 27.79
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 44 4.4/s
Sort range 325 32.5/s
Sort mrg pass 40 4.0/s

__ Query Cache __________________________________________________ _______
Memory usage 28.68M of 96.00M %Used: 29.88
Block Fragmnt 21.50%
Hits 455 45.5/s
Inserts 706 70.6/s
Insrt:Prune 706:1 70.5/s
Hit:Insert 0.64:1

__ Table Locks __________________________________________________ _______
Waited 310 31.0/s %Total: 12.91
Immediate 2.09k 209.2/s

__ Tables __________________________________________________ ____________
Open 3503 of 4000 %Cache: 87.58
Opened 0 0/s

__ Connections __________________________________________________ _______
Max used 390 of 800 %Max: 48.75
Total 121 12.1/s

__ Created Temp __________________________________________________ ______
Disk table 22 2.2/s
Table 71 7.1/s Size: 128.0M
File 80 8.0/s

__ Threads __________________________________________________ ___________
Running 27 of 54
Cached 333 of 384 %Hit: 100
Created 0 0/s
Slow 0 0/s

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

__ Bytes __________________________________________________ _____________
Sent 13.22M 1.3M/s
Received 526.99k 52.7k/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 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. vBulletin is the only

10. In average in peak times i have 3500 users online and max concurrent 4500

12.
KeepAlive On
MaxKeepAliveRequests 1000
KeepAliveTimeout 2
MinSpareServers 30
MaxSpareServers 50
StartServers 40
MaxClients 400
MaxRequestsPerChild 10000

14.
[root@mysql ~]# uname -a
Linux mysql.hostname.com 2.6.18-128.1.10.el5 #1 SMP Thu May 7 10:35:59 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux Code:
[root@mysql ~]# 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
Code:
[root@mysql ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5430 @ 2.66GHz
stepping : 6
cpu MHz : 2666.819
cache size : 6144 KB
physical id : 0
siblings : 4
core id : 0
cpu cores : 4
apicid : 0
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 : 5335.63
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5430 @ 2.66GHz
stepping : 6
cpu MHz : 2666.819
cache size : 6144 KB
physical id : 1
siblings : 4
core id : 0
cpu cores : 4
apicid : 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 : 5332.79
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:

processor : 2
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5430 @ 2.66GHz
stepping : 6
cpu MHz : 2666.819
cache size : 6144 KB
physical id : 0
siblings : 4
core id : 1
cpu cores : 4
apicid : 1
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 : 5332.85
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:

processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5430 @ 2.66GHz
stepping : 6
cpu MHz : 2666.819
cache size : 6144 KB
physical id : 1
siblings : 4
core id : 1
cpu cores : 4
apicid : 5
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 : 5332.80
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:

processor : 4
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5430 @ 2.66GHz
stepping : 6
cpu MHz : 2666.819
cache size : 6144 KB
physical id : 0
siblings : 4
core id : 2
cpu cores : 4
apicid : 2
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 : 5332.84
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:

processor : 5
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5430 @ 2.66GHz
stepping : 6
cpu MHz : 2666.819
cache size : 6144 KB
physical id : 1
siblings : 4
core id : 2
cpu cores : 4
apicid : 6
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 : 5398.94
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:

processor : 6
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5430 @ 2.66GHz
stepping : 6
cpu MHz : 2666.819
cache size : 6144 KB
physical id : 0
siblings : 4
core id : 3
cpu cores : 4
apicid : 3
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 : 5332.75
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:

processor : 7
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5430 @ 2.66GHz
stepping : 6
cpu MHz : 2666.819
cache size : 6144 KB
physical id : 1
siblings : 4
core id : 3
cpu cores : 4
apicid : 7
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 : 5332.78
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:

Thanks!!!
__DEFINE_LIKE_SHARE__

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