Variable's Day Out #4: innodb_buffer_pool_size

Properties:

Applicable To	InnoDB
Server Startup Option	--innodb_buffer_pool_size=<value>
Scope	Global
Dynamic	No
Possible Values	Integer Range: 1048576 (1MB) - 4294967295 (4GB) (more for 64 bit machines)
Default Value	8388608 (8MB)
Category	Performance

Description:

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. :)

Optimal Setting:

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.

Read more:

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