Mysql InnoDB optimisation
I'm having some trouble understanding InnoDB usage - we have a drupal based DB (5:1 read:write) running on mysql (Serv开发者_如何学JAVAer version: 5.1.41-3ubuntu12.10-log (Ubuntu)). Our current Innodb data/index sizing is:
Current InnoDB index space = 196 M Current InnoDB data space = 475 M
Looking around on the web and reading books like 'High performance sql' suggest to have 10% increase on data size - i have set the buffer pool to be (data+index)+10% and noticed that the buffer pool was at 100%...even increasing about this to 896Mb still makes it 100% (even though the data + indexes are only ~671Mb?
I've attached the output of the innodb section of mysqlreport below. Pages free of 1 seems to be suggesting a major problem also as well. The innodb_flush_method is set at its default - I will investigate setting this to O_DIRECT but want to sort out this issue before.
__ InnoDB Buffer Pool __________________________________________________
Usage 895.98M of 896.00M %Used: 100.00
Read hit 100.00%
Pages
Free 1 %Total: 0.00
Data 55.96k 97.59 %Drty: 0.01
Misc 1383 2.41
Latched 0 0.00
Reads 405.96M 1.2k/s
From file 15.60k 0.0/s 0.00
Ahead Rnd 211 0.0/s
Ahead Sql 1028 0.0/s
Writes 29.10M 87.3/s
Flushes 597.58k 1.8/s
Wait Free 0 0/s
__ InnoDB Lock _________________________________________________________
Waits 66 0.0/s
Current 0
Time acquiring
Total 3890 ms
Average 58 ms
Max 3377 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 21.51k 0.1/s
Writes 666.48k 2.0/s
fsync 324.11k 1.0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 84.16k 0.3/s
Read 59.35k 0.2/s
Written 597.58k 1.8/s
Rows
Deleted 19.13k 0.1/s
Inserted 6.13M 18.4/s
Read 196.84M 590.6/s
Updated 139.69k 0.4/s
Any help on this would be greatly apprectiated.
Thanks!
精彩评论