(asked on stackoverflow but was suggested to move here!)
I'm having some trouble understanding InnoDB usage - we have a drupal based DB (5:1 read:write) running on mysql (Server 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.