Innodb buffer pool size

------=_NextPart_000_004C_01CAF800.1B944230
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit

Hi Guys



I just have a quick question.





I have done some research into how to determine the size of your Innodb
buffer pool.



All of the sources I used, specified that the Innodb buffer pool size
should be the same size as your database + 10%.





However, as far as I understand it, the buffer size also relies on that
amount of memory being available. Thus if you increase the buffer size, the
amount of memory used will be increased.



My thinking however, is what happens when the database size grows bigger
than the amount of memory available to the hardware.



Say for instance, a server with MySQL also runs other applications. The
amount of memory on the server is 32Gb and about 31Gb is already in use.



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



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


------=_NextPart_000_004C_01CAF800.1B944230--

Re: Innodb buffer pool size

--0-1495819394-1274341852=:61533
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi, First thing that comes to my mind is that it is probably the best =
time to put your application server and database server on different hosts.=
Having said that, in this case increasing buffer pool size is still advisa=
ble as per my understanding. Your swap consumption will go up in that case =
which is not very good either. But giving only 4 GB to Innodb is even worse=
for the performance. It is subjective though. You should first check if My=
SQL is actually using the allotted 4GB or not. If not, increasing the value=
will not help. Maybe someone can correct me if I'm wrong. :-) Ho=
pe that helps! Regards,=0ANitin =0A_________________ _______=
________=0AFrom: Machiel Richards =0ATo: mysql@lists.my=
sql.com=0ASent: Thu, May 20, 2010 1:07:43 PM=0ASubject: Innodb buffer pool =
size Hi Guys =A0 =A0 I just have a quick question. =0A=
  =A0 I have done some research into how to determine the size o=
f your Innodb=0Abuffer pool. =A0 All of the sources I used, spec=
ified that the Innodb buffer pool size=0Ashould be the same size as your da=
tabase + 10%. =A0 =A0 However, as far as I understand it, =
the buffer size also relies on that=0Aamount of memory being available. Thu=
s if you increase the buffer size, the=0Aamount of memory used will be incr=
eased. =A0 My thinking however, is what happens when the databas=
e size grows bigger=0Athan the amount of memory available to the hardware. =
=A0 Say for instance,=A0 a server with MySQL also runs other ap=
plications. The=0Aamount of memory on the server is 32Gb and about 31Gb is =
already in use. =A0 =A0 The current Innodb buffer pool size is =
at 4Gb for instance, and the=0Ainnodb tables then grow to be about 8Gb in s=
ize. =A0 What would be the appropriate actions for this to ensur=
e the buffers are=0Aset to the size to best suit the database needs? =
=0A
--0-1495819394-1274341852=:61533--

Re: Innodb buffer pool size

--=_79sq7x6gkkg0
Content-Type: text/plain;
charset=ISO-8859-1
Content-Description: Plaintext Version of Message
Content-Disposition: inline
Content-Transfer-Encoding: 7bit



Well, my question is specifically related to in the event that the buffer usage reaches 100%

Quoting Nitin Mehta :

> Hi,
>
> First thing that comes to my mind is that it is probably the best time to put your application server and database server on different hosts. Having said that, in this case increasing buffer pool size is still advisable as per my understanding. Your swap consumption will go up in that case which is not very good either. But giving only 4 GB to Innodb is even worse for the performance. It is subjective though. You should first check if MySQL is actually using the allotted 4GB or not. If not, increasing the value will not help.
>
> Maybe someone can correct me if I'm wrong. :-)
>
> Hope that helps!
>
> Regards,
> Nitin
>
> -------------------------
> FROM: Machiel Richards
> TO: mysql@lists.mysql.com
> SENT: Thu, May 20, 2010 1:07:43 PM
> SUBJECT: Innodb buffer pool size
>
> Hi Guys
>
> I just have a quick question.
>
> I have done some research into how to determine the size of your Innodb
> buffer pool.
>
> All of the sources I used, specified that the Innodb buffer pool size
> should be the same size as your database + 10%.
>
> However, as far as I understand it, the buffer size also relies on that
> amount of memory being available. Thus if you increase the buffer size, the
> amount of memory used will be increased.
>
> My thinking however, is whathappens when the database size grows bigger
> than the amount of memory available to the hardware.
>
> Say for instance, a server with MySQL also runs other applications. The
> amount of memory on the server is 32Gb and about 31Gb is already in use.
>
> The current Innodb buffer pool size is at 4Gb for instance, and the
> innodb tables then grow to be about 8Gb in size.
>
> What would be the appropriate actions for this to ensure the buffers are
> set to the size to best suit the database needs?


--=_79sq7x6gkkg0--

Re: Innodb buffer pool size

In infinite wisdom "Machiel Richards" 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.ht ml)

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Innodb buffer pool size

Machiel,

I'm going to disagree strongly with the previous advice you got. You
should NOT configure the buffer pool to be larger than the amount of
RAM you have. If part of the buffer pool is swapped out, then
swapping it back in is MUCH worse than re-fetching the page. InnoDB
doesn't know the difference between in-memory and swapped out.
(That's the point of virtual memory; it's invisible to the program.)
It assumes that a "memory access" is fast. If it turns out not to
really be a memory access, but instead is a disk access to swap
something in, then everything goes very badly.

If you search for "buffer pool size" on mysqlperformanceblog.com, you
will get good advice. You should also get a copy of High Performance
MySQL, Second Edition. (I'm the lead author.) In short: ignore
advice about ratios, and ignore advice about the size of your data.
Configure the buffer pool to use the amount of memory available,
subtracting what's required for the OS and other things on the
computer to run effectively.

- Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org