Hello,
This server has dual intel xeon 5430 cpu, 8GB ram and 2x15k rpm sa-scsi hard drives on raid1. It is a web server and thus apache and mysql run on the same machine for now. We have innodb databases which are several gigabytes big (mediawiki with 12000 articles and some vbulletin forums).
Quote:
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
max_user_connections = 350
max_connections = 500
# interactive_timeout = 10
# wait_timeout = 10
# connect_timeout = 10
thread_cache_size = 128
key_buffer_size = 128M
join_buffer = 64MB
max_allowed_packet = 16M
table_cache = 1024
record_buffer = 1M
innodb_buffer_pool_size = 1024M
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
sort_buffer_size = 8M
myisam_sort_buffer_size = 8M
max_heap_table_size = 128M
tmp_table_size = 128M
max_connect_errors = 10
thread_concurrency = 8
safe-show-database
long_query_time = 1
server-id = 1
#[mysql.server]
#user = mysql
#basedir = /var/lib
[safe_mysqld]
err-log = /var/log/mysqld.log
pid-file = /var/lib/mysql/mysql.pid
open_files_limit = 8192
#[mysqldump]
#quick
#max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
|
Quote:
mysqlreport
__ Key __________________________________________________ __________ _____
Buffer used 96.63M of 128.00M %Used: 75.50
Current 114.92M %Usage: 89.78
Write hit 54.80%
Read hit 99.87%
__ Questions __________________________________________________ _________
Total 29.04M 428.8/s
DMS 11.28M 166.5/s %Total: 38.82
Com_ 11.15M 164.7/s 38.40
QC Hits 3.97M 58.6/s 13.66
COM_QUIT 2.65M 39.1/s 9.12
-Unknown 3.59k 0.1/s 0.01
Slow 1 s 4.00k 0.1/s 0.01 %DMS: 0.04 Log: OFF
DMS 11.28M 166.5/s 38.82
SELECT 9.93M 146.6/s 34.20 88.08
INSERT 1.22M 18.0/s 4.19 10.79
UPDATE 108.28k 1.6/s 0.37 0.96
DELETE 15.42k 0.2/s 0.05 0.14
REPLACE 3.94k 0.1/s 0.01 0.03
Com_ 11.15M 164.7/s 38.40
set_option 4.27M 63.1/s 14.72
change_db 2.65M 39.1/s 9.12
begin 2.60M 38.3/s 8.94
__ SELECT and Sort __________________________________________________ ___
Scan 78.21k 1.2/s %SELECT: 0.79
Range 1.96M 28.9/s 19.69
Full join 1.30k 0.0/s 0.01
Range check 0 0/s 0.00
Full rng join 19 0.0/s 0.00
Sort scan 45.10k 0.7/s
Sort range 47.62k 0.7/s
Sort mrg pass 6 0.0/s
__ Query Cache __________________________________________________ _______
Memory usage 52.94M of 64.00M %Used: 82.72
Block Fragmnt 8.89%
Hits 3.97M 58.6/s
Inserts 9.51M 140.4/s
Insrt:Prune 7.93:1 122.7/s
Hit:Insert 0.42:1
__ Table Locks __________________________________________________ _______
Waited 246 0.0/s %Total: 0.00
Immediate 14.20M 209.7/s
__ Tables __________________________________________________ __________ __
Open 1024 of 1024 %Cache: 100.00
Opened 6.86k 0.1/s
__ Connections __________________________________________________ _______
Max used 405 of 500 %Max: 81.00
Total 2.65M 39.1/s
__ Created Temp __________________________________________________ ______
Disk table 16.63k 0.2/s
Table 49.73k 0.7/s Size: 128.0M
File 17 0.0/s
__ Threads __________________________________________________ __________ _
Running 3 of 7
Cached 121 of 128 %Hit: 99.98
Created 405 0.0/s
Slow 11 0.0/s
__ Aborted __________________________________________________ __________ _
Clients 754 0.0/s
Connects 1.55k 0.0/s
__ Bytes __________________________________________________ __________ ___
Sent 25.75G 380.2k/s
Received 5.00G 73.8k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 1.00G of 1.00G %Used: 100.00
Read hit 99.89%
Pages
Free 0 %Total: 0.00
Data 61.18k 93.35 %Drty: 0.42
Misc 4356 6.65
Latched 1 0.00
Reads 215.45M 3.2k/s
From file 242.12k 3.6/s 0.11
Ahead Rnd 6677 0.1/s
Ahead Sql 472 0.0/s
Writes 24.87M 367.2/s
Flushes 512.00k 7.6/s
Wait Free 0 0/s
__ InnoDB Lock __________________________________________________ _______
Waits 29 0.0/s
Current 0
Time acquiring
Total 52818 ms
Average 1821 ms
Max 51005 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 277.29k 4.1/s
Writes 1.46M 21.5/s
fsync 1.04M 15.3/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 21.70k 0.3/s
Read 597.76k 8.8/s
Written 512.00k 7.6/s
Rows
Deleted 845.75k 12.5/s
Inserted 987.67k 14.6/s
Read 52.30M 772.1/s
Updated 259.29k 3.8/s
|
Any assistance or advice would be greatly appreciated, thanks.
dBforums
>
Mysql Config Tuning
09-27-08, 18:29
Linear Mode
