Large community optimization request
 

1 - Dedicated Server for WEB and another dedicated server for DATABASE

2 - The two servers are Dell PowerEdge 1950 64 Bits.
Dual Dual Intel(R) Xeon(R) CPU E5410 @ 2.33GHz
8GB of RAM 2 X 160 GB SAS discs for each server

SO: Centos 5.3 64 Bits
Apache 2.2.3-22
PHP 5.2.9 own RPM with only mysqli connector
MySQL 5.0.79,
xCache 1.2.2
All packages are 64bits

3 - vBulletin 3.8.2 + VBSEO

4 - No innodb tables

5 - MySQL was rebuilded from Centos rpm with percona patches the Mysql rpm are the same in every server and i recompile php every time that mysql change.

6 -
Code:
top - 20:00:05 up 7 days, 2:27, 1 user, load average: 1.09, 1.04, 1.00
Tasks: 252 total, 3 running, 248 sleeping, 0 stopped, 1 zombie
Cpu0 : 51.2%us, 3.0%sy, 0.0%ni, 44.2%id, 0.0%wa, 0.0%hi, 1.7%si, 0.0%st
Cpu1 : 19.9%us, 2.0%sy, 0.0%ni, 66.2%id, 11.6%wa, 0.3%hi, 0.0%si, 0.0%st
Cpu2 : 21.3%us, 1.0%sy, 0.0%ni, 77.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 16.3%us, 1.0%sy, 0.0%ni, 82.4%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Mem: 8178672k total, 6435220k used, 1743452k free, 267724k buffers
Swap: 2096472k total, 0k used, 2096472k free, 4088696k cached
7 -
Code:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
socket = /var/lib/mysql/mysql.sock
datadir=/var/lib/mysql
port = 3306
skip-name-resolve
bind-address = 192.168.128.35
safe-show-database
back_log = 50
skip-innodb
max_connections = 650
key_buffer_size = 384M
myisam_sort_buffer_size = 128M
join_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 8M

table_cache = 4096
thread_cache_size = 512

wait_timeout = 120
connect_timeout = 10

tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384

max_connect_errors = 10
thread_concurrency = 8
concurrent_insert = 2

table_lock_wait_timeout = 30
read_rnd_buffer_size = 1M

bulk_insert_buffer_size = 8M

query_cache_limit = 8M
query_cache_size = 288M
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 = 16

[mysqladmin]
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql

[mysqld_safe]
err-log=/var/log/mysqld.log
nice = -10
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 384M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
8 -
Code:
#
# Beginning report, 0 0:0:0
#
MySQL 5.0.79 uptime 7 2:5:44 Wed Jun 3 20:56:25 2009

__ Key __________________________________________________ _______________
Buffer used 315.78M of 384.00M %Used: 82.23
Current 384.00M %Usage: 100.00
Write hit 22.11%
Read hit 99.85%

__ Questions __________________________________________________ _________
Total 33.86M 55.3/s
DMS 19.67M 32.1/s %Total: 58.09
QC Hits 8.77M 14.3/s 25.91
COM_QUIT 2.75M 4.5/s 8.11
Com_ 2.66M 4.3/s 7.87
+Unknown 7.68k 0.0/s 0.02
Slow 10 s 685 0.0/s 0.00 %DMS: 0.00 Log: OFF
DMS 19.67M 32.1/s 58.09
SELECT 14.04M 22.9/s 41.47 71.39
UPDATE 3.21M 5.2/s 9.49 16.33
INSERT 1.98M 3.2/s 5.85 10.06
REPLACE 246.88k 0.4/s 0.73 1.26
DELETE 188.37k 0.3/s 0.56 0.96
Com_ 2.66M 4.3/s 7.87
change_db 2.62M 4.3/s 7.73
unlock_tabl 13.80k 0.0/s 0.04
lock_tables 13.80k 0.0/s 0.04

__ SELECT and Sort __________________________________________________ ___
Scan 1.85M 3.0/s %SELECT: 13.21
Range 4.37M 7.1/s 31.10
Full join 3.07k 0.0/s 0.02
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 158.79k 0.3/s
Sort range 3.71M 6.1/s
Sort mrg pass 3.20k 0.0/s

__ Query Cache __________________________________________________ _______
Memory usage 25.88M of 288.00M %Used: 8.99
Block Fragmnt 23.08%
Hits 8.77M 14.3/s
Inserts 13.96M 22.8/s
Insrt:Prune 13.96M:1 22.8/s
Hit:Insert 0.63:1

__ Table Locks __________________________________________________ _______
Waited 26.88k 0.0/s %Total: 0.06
Immediate 45.57M 74.4/s

__ Tables __________________________________________________ ____________
Open 1676 of 4096 %Cache: 40.92
Opened 30.13k 0.0/s

__ Connections __________________________________________________ _______
Max used 350 of 650 %Max: 53.85
Total 2.75M 4.5/s

__ Created Temp __________________________________________________ ______
Disk table 45.87k 0.1/s
Table 259.30k 0.4/s Size: 64.0M
File 6.49k 0.0/s

__ Threads __________________________________________________ ___________
Running 1 of 1
Cached 349 of 512 %Hit: 99.99
Created 350 0.0/s
Slow 0 0/s

__ Aborted __________________________________________________ ___________
Clients 275 0.0/s
Connects 5 0.0/s

__ Bytes __________________________________________________ _____________
Sent 283.96G 463.7k/s
Received 20.14G 32.9k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %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

#
# Interval report 1, +0 0:0:10
#
MySQL 5.0.79 uptime 7 2:5:54 Wed Jun 3 20:56:35 2009

__ Key __________________________________________________ _______________
Buffer used 315.78M of 384.00M %Used: 82.23
Current 384.00M %Usage: 100.00
Write hit 1.81%
Read hit 99.99%

__ Questions __________________________________________________ _________
Total 909 90.9/s
DMS 564 56.4/s %Total: 62.05
QC Hits 207 20.7/s 22.77
Com_ 68 6.8/s 7.48
COM_QUIT 67 6.7/s 7.37
+Unknown 3 0.3/s 0.33
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 564 56.4/s 62.05
SELECT 408 40.8/s 44.88 72.34
UPDATE 94 9.4/s 10.34 16.67
INSERT 52 5.2/s 5.72 9.22
DELETE 9 0.9/s 0.99 1.60
REPLACE 1 0.1/s 0.11 0.18
Com_ 68 6.8/s 7.48
change_db 65 6.5/s 7.15
set_option 1 0.1/s 0.11
show_variab 1 0.1/s 0.11

__ SELECT and Sort __________________________________________________ ___
Scan 52 5.2/s %SELECT: 12.75
Range 129 12.9/s 31.62
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 2 0.2/s
Sort range 106 10.6/s
Sort mrg pass 0 0/s

__ Query Cache __________________________________________________ _______
Memory usage 26.55M of 288.00M %Used: 9.22
Block Fragmnt 22.85%
Hits 207 20.7/s
Inserts 405 40.5/s
Insrt:Prune 405:1 40.4/s
Hit:Insert 0.51:1

__ Table Locks __________________________________________________ _______
Waited 0 0/s %Total: 0.00
Immediate 1.31k 130.6/s

__ Tables __________________________________________________ ____________
Open 1676 of 4096 %Cache: 40.92
Opened 0 0/s

__ Connections __________________________________________________ _______
Max used 350 of 650 %Max: 53.85
Total 69 6.9/s

__ Created Temp __________________________________________________ ______
Disk table 1 0.1/s
Table 7 0.7/s Size: 64.0M
File 0 0/s

__ Threads __________________________________________________ ___________
Running 1 of 1
Cached 349 of 512 %Hit: 100
Created 0 0/s
Slow 0 0/s

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

__ Bytes __________________________________________________ _____________
Sent 5.87M 587.4k/s
Received 514.90k 51.5k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %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 - Only vBulletin, vblinks, vBGallery and vBSEO

10 - 2500 concurrent users with 900 cookie timeout

11 - http://www.bmwfaq.com/inf.php

12 -
Code:
Timeout 300
KeepAlive On
MaxKeepAliveRequests 50
KeepAliveTimeout 3


StartServers 15
MinSpareServers 15
MaxSpareServers 25
ServerLimit 384
MaxClients 256
MaxRequestsPerChild 1000

13 - only kcore from bind 8.8GB

14 -
Code:
[root@srv01 daniel]# uname -a
Linux srv01.bmwfaq.com 2.6.18-92.el5 #1 SMP Tue Jun 10 18:51:06 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux
Code:
[root@srv01 daniel]# 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@srv01 daniel]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6
cpu MHz : 1995.025
cache size : 4096 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 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 tm2 cx16 xtpr lahf_lm
bogomips : 3992.98
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) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6
cpu MHz : 1995.025
cache size : 4096 KB
physical id : 3
siblings : 2
core id : 0
cpu cores : 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 tm2 cx16 xtpr lahf_lm
bogomips : 3990.26
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) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6
cpu MHz : 1995.025
cache size : 4096 KB
physical id : 0
siblings : 2
core id : 1
cpu cores : 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 tm2 cx16 xtpr lahf_lm
bogomips : 3990.25
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) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6
cpu MHz : 1995.025
cache size : 4096 KB
physical id : 3
siblings : 2
core id : 1
cpu cores : 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 tm2 cx16 xtpr lahf_lm
bogomips : 3990.22
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management: