Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

(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.

Thanks!

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

UPDATE

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

share|improve this answer
    
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 '11 at 16:33
    
What are your settings for innodb_data_file_path, innodb_open_files and innodb_file_per_table ? –  RolandoMySQLDBA Mar 29 '11 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 '11 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 '11 at 8:26
    
I will update my answer with more optimizations –  RolandoMySQLDBA Mar 30 '11 at 15:37
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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