(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%
  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 ____________________________________________
  Reads        21.51k     0.1/s
  Writes      666.48k     2.0/s
  fsync       324.11k     1.0/s
    Reads           0
    Writes          0
    fsync           0

  Created      84.16k     0.3/s
  Read         59.35k     0.2/s
  Written     597.58k     1.8/s

  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.


1 Answer 1


Are your InnoDB Log Files (ib_logfile0 and ib_logfile1) sized correctly ?

They should be 25% of the InnoDB Buffer Pool
In your case, that would be 224M

You can it them as follows:

  1. Add this setting to /etc/my.cnf : innodb_log_file_size=224M
  2. service mysql stop
  3. rm -f /var/lib/mysql/ib_logfile[01]
  4. service mysql start (ib_logfile0 and ib_logfile1 are recreated)

You should keep the default for innodb_flush_method to insure ACID compliance. Otherwise, you could lose up to one second's worth of transactions.

CAVEAT: If your DB server is dedicated to only to the database, you should reserve 75-80% or RAM for the innodb_buffer_pool.


You will definitely want to change your InnoDB layout !!!!

You need to raise innodb_open_files to 6000
You need to turn on innodb_file_per_table
You need to restructure all InnoDB Data to reside in separate tablespaces

  • Hi Rolando, thanks for that. I actually set the buffer pool to a gig and the log files (accordingly) to 250Mb. After a restart, the buffer pool has already gone to 100% ... what i am not understanding is why it can get this large even though the data+indexes is < 700Mb?
    – DOS
    Mar 29, 2011 at 16:33
  • What are your settings for innodb_data_file_path, innodb_open_files and innodb_file_per_table ? Mar 29, 2011 at 17:17
  • mysql> show variables like "innodb%file%"; +---------------------------+------------------------+ | Variable_name | Value | +---------------------------+------------------------+ | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_log_file_size | 262144000 | | innodb_log_files_in_group | 2 | | innodb_open_files | 300 | +---------------------------+------------------------+
    – DOS
    Mar 30, 2011 at 8:25
  • Sorry, didn't realise that that would not format correctly! The system had already been configured like this prior to this optimisation process..
    – DOS
    Mar 30, 2011 at 8:26
  • I will update my answer with more optimizations Mar 30, 2011 at 15:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .