|Server Startup Option||--innodb_buffer_pool_size=<value>|
|Possible Values||Integer |
Range: 1048576 (1MB) - 4294967295 (4GB)
(more for 64 bit machines)
|Default Value||8388608 (8MB)|
One of the Peter's "what to tune?" parameters, this innodb buffer pool is a cache for both indexes and data and should be considered as the most important option for InnoDB performance. In case your server is hosting InnoDB heavy database, you can allocate up to 70-80% of the physical memory to this buffer. Anything more than this might cause paging in the operating system. Obviously if your server is not InnoDB heavy and you are using a mix of certain engines, dividing memory can be lot more complicated. This may also be taken as an argument against mixing engine types. :)
As always, there is no magic number and the optimal value for you depends on your requirements. In case your data size is too much, you can always chose to provide as much as possible to this buffer pool. While doing so always remember that InnoDB will eat up some space for data structures related to your buffer pool also. On the other hand, if your projected database size (data+indexes) is small enough to fit in the memory, allocate around 10-15% more than your data-size.
- MySQL manual entry on innodb_buffer_pool_size
- Tuning mysql at mysqlperformanceblog.com
- Choosing the right size for innodb_buffer_pool_size
- InnoDB memory usage
- Using O_DIRECT on Linux and InnoDB to fix swapping
Hope this post was helpful to you. Keep posting your comments.