Optimization Request
 

Hello,

Server: dedicated:
*CPU: Dual Xeon E5420 Quad Core (Harpertown) 2.5Ghz
*RAM: 8GB
*Centos 5.2 x64 2.6.18-92.1.22.el5
*Drive: 2x147 GB SCSI 15,000 RPM
*Apache 2.2
*PHP 5.2.9 (cli) (built: May 25 2009 11:06:50)
Zend Engine v2.2.0
with XCache v1.2.2
with Zend Extension Manager v1.2.2
with Zend Optimizer v3.3.3
*MySQL 5.0.77-community


vBulletin 3.7.5 pl1

Top

Code:
top - 20:59:43 up 2 days, 3:03, 2 users, load average: 7.57, 11.94, 10.93
Tasks: 525 total, 2 running, 521 sleeping, 0 stopped, 2 zombie
Cpu(s): 45.5%us, 3.1%sy, 0.0%ni, 47.0%id, 3.7%wa, 0.1%hi, 0.5%si, 0.0%st
Mem: 8174404k total, 7522464k used, 651940k free, 15844k buffers
Swap: 2040244k total, 32636k used, 2007608k free, 1801868k cached
Code:
top - 21:05:12 up 2 days, 3:08, 2 users, load average: 4.80, 6.96, 8.99
Tasks: 471 total, 4 running, 465 sleeping, 0 stopped, 2 zombie
Cpu0 : 21.9%us, 2.0%sy, 0.0%ni, 70.9%id, 5.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 31.1%us, 2.0%sy, 0.0%ni, 24.5%id, 42.1%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu2 : 41.9%us, 1.0%sy, 0.0%ni, 56.5%id, 0.7%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 47.2%us, 1.7%sy, 0.0%ni, 47.2%id, 4.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 25.9%us, 0.3%sy, 0.0%ni, 73.1%id, 0.7%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 41.5%us, 1.7%sy, 0.0%ni, 53.8%id, 3.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 29.3%us, 1.3%sy, 0.0%ni, 58.3%id, 11.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 44.0%us, 2.6%sy, 0.0%ni, 44.0%id, 5.0%wa, 0.3%hi, 4.0%si, 0.0%st
Mem: 8174404k total, 7578968k used, 595436k free, 21824k buffers
Swap: 2040244k total, 29316k used, 2010928k free, 2350328k cached
My.cnf

Code:
[mysqld]
datadir=/home/mysql
max_connections = 500
safe-show-database
skip-locking
key_buffer = 128M
max_allowed_packet = 16M
table_cache = 4000
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 144M
query_cache_limit = 7M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
thread_concurrency = 4
concurrent_insert = 2
wait_timeout = 35
long_query_time = 5
join_buffer_size = 1M
tmpdir = /home/mysqltemp

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 64M
sort_buffer_size = 64M
read_buffer = 16M
write_buffer = 16M
mysqlreport

Code:
#
# Beginning report, 0 0:0:0
#
MySQL 5.0.77-community uptime 1 8:20:36 Sun Jun 7 21:08:18 2009

__ Key __________________________________________________ _______________
Buffer used 104.66M of 128.00M %Used: 81.76
Current 128.00M %Usage: 100.00
Write hit 19.35%
Read hit 99.85%

__ Questions __________________________________________________ _________
Total 20.77M 178.4/s
DMS 11.88M 102.0/s %Total: 57.19
QC Hits 5.72M 49.2/s 27.55
Com_ 1.64M 14.0/s 7.87
COM_QUIT 1.56M 13.4/s 7.51
-Unknown 25.85k 0.2/s 0.12
Slow 5 s 4.01k 0.0/s 0.02 %DMS: 0.03 Log: OFF
DMS 11.88M 102.0/s 57.19
SELECT 8.66M 74.4/s 41.70 72.92
UPDATE 2.07M 17.8/s 9.96 17.41
INSERT 961.09k 8.3/s 4.63 8.09
DELETE 104.18k 0.9/s 0.50 0.88
REPLACE 83.55k 0.7/s 0.40 0.70
Com_ 1.64M 14.0/s 7.87
change_db 1.56M 13.4/s 7.51
admin_comma 23.88k 0.2/s 0.11
lock_tables 20.92k 0.2/s 0.10

__ SELECT and Sort __________________________________________________ ___
Scan 1.09M 9.4/s %SELECT: 12.63
Range 2.30M 19.8/s 26.58
Full join 5.17k 0.0/s 0.06
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 322.81k 2.8/s
Sort range 3.13M 26.9/s
Sort mrg pass 4.34k 0.0/s

__ Query Cache __________________________________________________ _______
Memory usage 64.92M of 144.00M %Used: 45.09
Block Fragmnt 21.73%
Hits 5.72M 49.2/s
Inserts 7.99M 68.6/s
Insrt:Prune 248.65:1 68.3/s
Hit:Insert 0.72:1

__ Table Locks __________________________________________________ _______
Waited 1.68M 14.5/s %Total: 7.10
Immediate 22.02M 189.1/s

__ Tables __________________________________________________ ____________
Open 4000 of 4000 %Cache: 100.00
Opened 16.59k 0.1/s

__ Connections __________________________________________________ _______
Max used 501 of 500 %Max: 100.20
Total 1.56M 13.4/s

__ Created Temp __________________________________________________ ______
Disk table 9.09k 0.1/s
Table 235.14k 2.0/s Size: 32.0M
File 8.69k 0.1/s

__ Threads __________________________________________________ ___________
Running 14 of 19
Cached 6 of 8 %Hit: 83.99
Created 250.03k 2.1/s
Slow 14 0.0/s

__ Aborted __________________________________________________ ___________
Clients 1.70k 0.0/s
Connects 1.77k 0.0/s

__ Bytes __________________________________________________ _____________
Sent 461.20G 4.0M/s
Received 7.96G 68.4k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 864.00k of 8.00M %Used: 10.55
Read hit 97.62%
Pages
Free 458 %Total: 89.45
Data 54 10.55 %Drty: 0.00
Misc 0 0.00
Latched 0.00
Reads 1.22k 0.0/s
From file 29 0.0/s 2.38
Ahead Rnd 3 0.0/s
Ahead Sql 0 0/s
Writes 1 0.0/s
Flushes 1 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 48 0.0/s
Writes 7 0.0/s
fsync 7 0.0/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 0 0/s
Read 54 0.0/s
Written 1 0.0/s

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

#
# Interval report 1, +0 0:0:10
#
MySQL 5.0.77-community uptime 1 8:20:46 Sun Jun 7 21:08:28 2009

__ Key __________________________________________________ _______________
Buffer used 104.66M of 128.00M %Used: 81.76
Current 128.00M %Usage: 100.00
Write hit 15.77%
Read hit 99.89%

__ Questions __________________________________________________ _________
Total 2.23k 222.8/s
DMS 1.23k 122.8/s %Total: 55.12
QC Hits 646 64.6/s 28.99
Com_ 170 17.0/s 7.63
COM_QUIT 166 16.6/s 7.45
+Unknown 18 1.8/s 0.81
Slow 5 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 1.23k 122.8/s 55.12
SELECT 879 87.9/s 39.45 71.58
UPDATE 234 23.4/s 10.50 19.06
INSERT 92 9.2/s 4.13 7.49
REPLACE 19 1.9/s 0.85 1.55
DELETE 4 0.4/s 0.18 0.33
Com_ 170 17.0/s 7.63
change_db 167 16.7/s 7.50
show_status 1 0.1/s 0.04
set_option 1 0.1/s 0.04

__ SELECT and Sort __________________________________________________ ___
Scan 107 10.7/s %SELECT: 12.17
Range 227 22.7/s 25.82
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 45 4.5/s
Sort range 281 28.1/s
Sort mrg pass 1 0.1/s

__ Query Cache __________________________________________________ _______
Memory usage 65.89M of 144.00M %Used: 45.75
Block Fragmnt 21.40%
Hits 646 64.6/s
Inserts 809 80.9/s
Insrt:Prune 809:1 80.8/s
Hit:Insert 0.80:1

__ Table Locks __________________________________________________ _______
Waited 58 5.8/s %Total: 2.33
Immediate 2.43k 243.3/s

__ Tables __________________________________________________ ____________
Open 4000 of 4000 %Cache: 100.00
Opened 3 0.3/s

__ Connections __________________________________________________ _______
Max used 501 of 500 %Max: 100.20
Total 168 16.8/s

__ Created Temp __________________________________________________ ______
Disk table 10 1.0/s
Table 26 2.6/s Size: 32.0M
File 2 0.2/s

__ Threads __________________________________________________ ___________
Running 2 of 3
Cached 5 of 8 %Hit: 100
Created 0 0/s
Slow 0 0/s

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

__ Bytes __________________________________________________ _____________
Sent 47.46M 4.7M/s
Received 1.51M 150.9k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 864.00k of 8.00M %Used: 10.55
Read hit 0.00%
Pages
Free 458 %Total: 89.45
Data 54 10.55 %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-Not the only site but the most important, other sites are very small.
10- avg: 3200, max: 6000 (15min)
12-
Code:
Keepalive=yes
MinSpareServers 15
MaxSpareServers 25
StartServers 15
ServerLimit 450
MaxClients 400
MaxRequestsPerChild 500
13- none
14-uname
Code:
Linux host...... 2.6.18-92.1.22.el5 #1 SMP Tue Dec 16 11:57:43 EST 2008 x86_64 x86_64 x86_64 GNU/Linux
ulimit

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

Code:
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5420 @ 2.50GHz
stepping : 6
cpu MHz : 2493.820
cache size : 6144 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 : 4989.56
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 E5420 @ 2.50GHz
stepping : 6
cpu MHz : 2493.820
cache size : 6144 KB
physical id : 1
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 : 4986.83
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 E5420 @ 2.50GHz
stepping : 6
cpu MHz : 2493.820
cache size : 6144 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 : 4986.87
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 E5420 @ 2.50GHz
stepping : 6
cpu MHz : 2493.820
cache size : 6144 KB
physical id : 1
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 : 4986.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 E5420 @ 2.50GHz
stepping : 6
cpu MHz : 2493.820
cache size : 6144 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 : 4986.77
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 E5420 @ 2.50GHz
stepping : 6
cpu MHz : 2493.820
cache size : 6144 KB
physical id : 1
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 : 4986.89
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 E5420 @ 2.50GHz
stepping : 6
cpu MHz : 2493.820
cache size : 6144 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 : 4986.79
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 E5420 @ 2.50GHz
stepping : 6
cpu MHz : 2493.820
cache size : 6144 KB
physical id : 1
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 : 4986.88
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:
Thanks a lot !