optimal size of innodb buffer pool

Question

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

Answer 1

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

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,

Answer 2

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.