List:General Discussion« Previous MessageNext Message »
From:Raj Shekhar Date:May 21 2010 1:18pm
Subject:Re: Innodb buffer pool size
View as plain text  
In infinite wisdom "Machiel Richards" <machielr@stripped> wrote:

>     The current Innodb buffer pool size is at 4Gb for instance, and the
> innodb tables then grow to be about 8Gb in size.

InnoDB manages the pool as a list, using a least recently used (LRU) algorithm
incorporating a midpoint insertion strategy. When room is needed to add a new block to
the pool, InnoDB evicts the least recently used block and adds the new block to the
middle of the list.

(see http://dev.mysql.com/doc/refman/5.1/en/innodb-buffer-pool.html)

> 
>    What would be the appropriate actions for this to ensure the buffers are
> set to the size to best suit the database needs?

- Try to set your innodb_buffer_pool to be the same size as your data.
- have monitoring for the innodb_buffer_pool.  "show innodb status"
  prints out stats about the buffer pool as well

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 84966343; in additional pool allocated 1402624
Buffer pool size   3200
Free buffers       110
Database pages     3074
Modified db pages  2674
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 171380, created 51968, written 194688
28.72 reads/s, 20.72 creates/s, 47.55 writes/s
Buffer pool hit rate 999 / 1000

Monitoring free buffers can help in figuring out if the allocated memory
is optimum or not.

-- 
Raj Shekhar
-
If there's anything more important than my ego around, I want it
caught and shot now.


Thread
Innodb buffer pool sizeMachiel Richards20 May
  • Re: Innodb buffer pool sizeNitin Mehta20 May
    • Re: Innodb buffer pool sizemachielr20 May
  • Re: Innodb buffer pool sizeRaj Shekhar21 May
  • Re: Innodb buffer pool sizeBaron Schwartz26 May