Server Optimization Request
 

<div>Dedicated MySQL-only Server. and Dedicated Webserver. Connected to each other via crossover cable.

Processor: Dual Opteron 2344HE (Barcelona)
Memory: 4GB DDR Registered ECC
Hd1: 120GB 7200RPM SATA / 8MB Cache
Hd2: 120GB 7200RPM SATA / 8MB Cache
OS: Linux - CentOS 5 (+64 Bit)
ControlPanel: CPanel / Web Host Manager (+ServerSecure) (+Fantastico/XController)
Bandwidth: 4800GB Monthly Transfer (2400 in + 2400out)

vBulletin 3.7.4 PL1

5.0.67-community MySQL Community Edition (GPL)
MySQL compiled and installed by cPanel / WHM

Code:
top - 21:41:23 up 3 days, 38 min, 4 users, load average: 0.55, 0.81, 1.13
Tasks: 186 total, 1 running, 185 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 7.3%us, 1.3%sy, 0.0%ni, 91.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 8.6%us, 1.7%sy, 0.0%ni, 89.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 25.8%us, 3.3%sy, 0.0%ni, 69.5%id, 0.0%wa, 0.7%hi, 0.7%si, 0.0%st
Cpu4 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 0.3%us, 0.7%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4115716k total, 2404784k used, 1710932k free, 142800k buffers
Swap: 6168952k total, 144k used, 6168808k free, 1576440k cached
Code:
[mysqld]
max_connections = 500
safe-show-database
skip-locking
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 2048
sort_buffer_size = 128M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 32M
thread_cache_size = 8
query_cache_size = 32M
query_cache_type = 1
thread_concurrency = 8
wait_timeout = 60
join_buffer_size = 32M

[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 = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
Code:
mysqlreport is writing relative reports to '/tmp/YsGc3J2Fvl'.
#
# Beginning report, 0 0:0:0
#
MySQL 5.0.67-community uptime 0 4:36:48 Fri May 15 00:17:15 2009

__ Key __________________________________________________ _______________
Buffer used 61.96M of 128.00M %Used: 48.41
Current 85.30M %Usage: 66.64
Write hit 90.51%
Read hit 99.99%

__ Questions __________________________________________________ _________
Total 1.26M 75.6/s
DMS 603.80k 36.4/s %Total: 48.10
QC Hits 486.08k 29.3/s 38.72
COM_QUIT 87.91k 5.3/s 7.00
Com_ 77.47k 4.7/s 6.17
-Unknown 42 0.0/s 0.00
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 603.80k 36.4/s 48.10
SELECT 457.57k 27.6/s 36.45 75.78
UPDATE 104.79k 6.3/s 8.35 17.35
DELETE 30.41k 1.8/s 2.42 5.04
INSERT 7.79k 0.5/s 0.62 1.29
REPLACE 3.25k 0.2/s 0.26 0.54
Com_ 77.47k 4.7/s 6.17
change_db 77.26k 4.7/s 6.15
set_option 58 0.0/s 0.00
show_proces 56 0.0/s 0.00

__ SELECT and Sort __________________________________________________ ___
Scan 136.31k 8.2/s %SELECT: 29.79
Range 44.83k 2.7/s 9.80
Full join 3.09k 0.2/s 0.68
Range check 0 0/s 0.00
Full rng join 2 0.0/s 0.00
Sort scan 89.05k 5.4/s
Sort range 58.69k 3.5/s
Sort mrg pass 0 0/s

__ Query Cache __________________________________________________ _______
Memory usage 22.93M of 32.00M %Used: 71.64
Block Fragmnt 16.99%
Hits 486.08k 29.3/s
Inserts 372.83k 22.4/s
Insrt:Prune 6.07:1 18.8/s
Hit:Insert 1.30:1

__ Table Locks __________________________________________________ _______
Waited 29.53k 1.8/s %Total: 2.94
Immediate 974.71k 58.7/s

__ Tables __________________________________________________ ____________
Open 703 of 2048 %Cache: 34.33
Opened 758 0.0/s

__ Connections __________________________________________________ _______
Max used 36 of 500 %Max: 7.20
Total 87.92k 5.3/s

__ Created Temp __________________________________________________ ______
Disk table 10.91k 0.7/s
Table 57.98k 3.5/s Size: 32.0M
File 5 0.0/s

__ Threads __________________________________________________ ___________
Running 2 of 2
Cached 6 of 8 %Hit: 95.16
Created 4.26k 0.3/s
Slow 0 0/s

__ Aborted __________________________________________________ ___________
Clients 1 0.0/s
Connects 44 0.0/s

__ Bytes __________________________________________________ _____________
Sent 17.36G 1.0M/s
Received 998.19M 60.1k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 320.00k of 8.00M %Used: 3.91
Read hit 92.82%
Pages
Free 492 %Total: 96.09
Data 20 3.91 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 181 0.0/s
From file 13 0.0/s 7.18
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 26 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 20 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.67-community uptime 0 4:36:58 Fri May 15 00:17:25 2009

__ Key __________________________________________________ _______________
Buffer used 61.97M of 128.00M %Used: 48.41
Current 85.31M %Usage: 66.65
Write hit 13.33%
Read hit 99.95%

__ Questions __________________________________________________ _________
Total 836 83.6/s
DMS 358 35.8/s %Total: 42.82
QC Hits 349 34.9/s 41.75
Com_ 69 6.9/s 8.25
COM_QUIT 57 5.7/s 6.82
+Unknown 3 0.3/s 0.36
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 358 35.8/s 42.82
SELECT 255 25.5/s 30.50 71.23
UPDATE 62 6.2/s 7.42 17.32
DELETE 34 3.4/s 4.07 9.50
REPLACE 4 0.4/s 0.48 1.12
INSERT 3 0.3/s 0.36 0.84
Com_ 69 6.9/s 8.25
change_db 53 5.3/s 6.34
set_option 5 0.5/s 0.60
show_databa 4 0.4/s 0.48

__ SELECT and Sort __________________________________________________ ___
Scan 82 8.2/s %SELECT: 32.16
Range 12 1.2/s 4.71
Full join 2 0.2/s 0.78
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 43 4.3/s
Sort range 56 5.6/s
Sort mrg pass 0 0/s

__ Query Cache __________________________________________________ _______
Memory usage 23.08M of 32.00M %Used: 72.12
Block Fragmnt 16.85%
Hits 349 34.9/s
Inserts 214 21.4/s
Insrt:Prune 214:1 21.3/s
Hit:Insert 1.63:1

__ Table Locks __________________________________________________ _______
Waited 2 0.2/s %Total: 0.35
Immediate 563 56.3/s

__ Tables __________________________________________________ ____________
Open 703 of 2048 %Cache: 34.33
Opened 0 0/s

__ Connections __________________________________________________ _______
Max used 36 of 500 %Max: 7.20
Total 59 5.9/s

__ Created Temp __________________________________________________ ______
Disk table 7 0.7/s
Table 33 3.3/s Size: 32.0M
File 0 0/s

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

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

__ Bytes __________________________________________________ _____________
Sent 8.54M 854.0k/s
Received 1.09M 108.7k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 320.00k of 8.00M %Used: 3.91
Read hit 0.00%
Pages
Free 492 %Total: 96.09
Data 20 3.91 %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
<div style="margin:20px; margin-top:5px"> Code:
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| 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 |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| 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 |
| div_precision_increment | 4 |
| keep_files_on_create | OFF |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + ->