MySQL Performance

MySQL & MariaDB Other MySQL® Questions
1

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).

[B]Quote:[/B]
[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

[B]Quote:[/B]
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.

2
[B]Quote:[/B]
Any assistance or advice would be greatly appreciated, thanks.

Well,… its not easy to answer such an unspecific question… Do you have actual performance problems, or do you just want to increase general page load time?

[B]Quote:[/B]

__ 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

That is not to much… a good ratio would be n:1 where n is much bigger than 1… In your case only 42% of the cached statements are reused… Try increasing the cache size,…

[B]Quote:[/B]
__ Threads ____________________________________________________________ _ Running 3 of 7 Cached 121 of 128 %Hit: 99.98 Created 405 0.0/s Slow 11 0.0/s

That is a big cache… I don’t know your Data Access pattern… but the cache is used to buffer the recreation of new threads…
If your typical client access pattern is not to create dozens of new connections, without releasing some in the meantime, you can decrease this cache… But this would be very uncommon for a web application… A “normal” pattern would me more like: 3 new, 1 release, 2 new, 2 release, 2 new, 1 release, 1 new, 2 release…
So you need only your cache to smooth your fluctuations… About 8 threads… or maybe up to 16…

[B]Quote:[/B]
innodb_buffer_pool_size = 1024M
For a machine with 8GB RAM, this is not to much... and since you have more Data than that (Pool 100% used) you could try to increase your buffer_pool to gain some performance...

For any other advice, I do need more information…

3

If you find your system is getting slow, have you tried running PHP with APC compiled in? It’ll make a massive difference, if you configure things properly.