Thursday, April 3, 2008

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:

Hope this post was helpful to you. Keep posting your comments.

No comments: