(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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.