Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I read a lot of optimal number of innodb_buffer_pool_size and people say it must be larger with 10% than the innodb tables size, what is 60M. Accordyngly this the optimal size of the buffer is about 66M im my case. Should I set this value more than 66M, for example 256M or 1G to achieve better performance? I have enough memory to add more to innodb buffer pool if that improve the performance. Thanks

share|improve this question

2 Answers 2

If you expect your InnoDB data to grow, you should prepare for it by having a larger InnoDB Buffer Pool.

Where does the idea of having additional 10% extra space for the Buffer Pool come from ???

Please note the following diagram of the InnoDB Architecture

InnoDB Architecture

Look in the upper left hand corner where you see the buffer pool. There is a section of it called the "Insert Buffer Part". The purpose of the Insert Buffer is to populate changes to non-unique indexes into the Insert Buffer inside the system tablespace file (a.k.a. ibdata1).

From the diagram, up to 50% of the Buffer Pool can be used to manage the Insert Buffer. That being the case, it would be in your best interests to assign a lot more memory to the Buffer Pool. With a data set of 60M, 256M would be a great place to start,

share|improve this answer
    
Extra 10% of buffer pool is used for control structures. See: dev.mysql.com/doc/refman/5.5/en/… –  user77376 May 7 at 13:17

According to the MySQL manual, the recommended size is "up to 80% of system memory". Or more specifically, most people will recommend 50-80% of memory.

I think you are misquoting the 10% figure for the approximate overhead that will be required on top of whatever you allocate.

share|improve this answer

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.