开发者

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!

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜