Innodb buffer pool usage

------=_NextPart_000_005D_01CAC4E5.222D9D10
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi all



Maybe someone can assist me with this one.



A while back I requested some information relating to the
MySQL innodb buffer pool size that seems to fill up rather frequently.

The buffer pool is currently set to 3Gb , and it takes about
2-3 weeks after a restart to fill up.



Someone replied and stated that this is preferred to be
running at 100% usage as it means that it is running optimally.



However, the oracle guys in our office disagrees with this
and want to know the following:



.. If the innodb buffer pool is at 100% full, how will we know when
it needs more buffers

o i.e. let's say the database starts getting very busy and needs more
buffers, how will we know that it requires this if the buffer pool usage is
already at 100%.



I am fairly new to database administration so no luck in answering them on
this so I would appreciate the assistance.





Regards

Machiel


------=_NextPart_000_005D_01CAC4E5.222D9D10--

Re: Innodb buffer pool usage

Hi Machiel,

What do you mean with "innodb buffer pool is at 100% full" ?

There are several status variables associated with innodb buffer pool ie:
Innodb_buffer_pool_pages_free is the number of unused data pages.
Innodb_buffer_pool_pages_total is the total number of pages.
Innodb_buffer_pool_pages_data is the total number of used data pages
(clean and dirty).
Innodb_buffer_pool_pages_dirty is the number of dirty data pages. The
number of clean data pages can be calculated from these first two status
variables.
etc..

You can calculate the usage ratio with a basic recipe:
Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total
This is the value that you have to monitor. If its close to 1, then you
set up too much memory for innodb (warning: that this can be due to
frequent flushed to disk, so you have to check that too); if on the
other hand the ratio is too low, then you effectively are running out of
resources and may need to add more memory to innodb. You can imply other
things, with the other variables too.

Obviously you have to do the job and review this values along the time,
maybe there are some actions/effects like running reports or etl
processes, you have to figure out what is going on as a "whole" not just
the values in the formula.

BTW: In Oracle is the same story, one thing is what you reserve
for...and other thing is the actual usage. The latter if you are tuning
Oracle manually, because one important difference in Oracle 10 and 11 is
that the buffers can grow and shrink automatically (if you configure it)
so you can say "use the 100% memory at your convenience" and Oracle can,
for example, reduce the sort buffers and extend the index buffers on the
fly. Obviously this also has advantages and disadvantages, but as a new
DBA is good to get involved in this concepts and comparisons between dbms.

Carlos Proal



On 3/16/2010 12:46 AM, Machiel Richards wrote:
> Hi all
>
>
>
> Maybe someone can assist me with this one.
>
>
>
> A while back I requested some information relating to the
> MySQL innodb buffer pool size that seems to fill up rather frequently.
>
> The buffer pool is currently set to 3Gb , and it takes about
> 2-3 weeks after a restart to fill up.
>
>
>
> Someone replied and stated that this is preferred to be
> running at 100% usage as it means that it is running optimally.
>
>
>
> However, the oracle guys in our office disagrees with this
> and want to know the following:
>
>
>
> . If the innodb buffer pool is at 100% full, how will we know when
> it needs more buffers
>
> o i.e. let's say the database starts getting very busy and needs more
> buffers, how will we know that it requires this if the buffer pool usage is
> already at 100%.
>
>
>
> I am fairly new to database administration so no luck in answering them on
> this so I would appreciate the assistance.
>
>
>
>
>
> Regards
>
> Machiel
>
>
>


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

HI Carlos

We run a script for the monitoring and the output received is as
follows:


InnoDB Buffer Pool __________________________________________________
Usage 3.00G of 3.00G %Used: 100.00
Read hit 100.00%
Pages
Free 0 %Total: 0.00
Data 145.31k 73.91 %Drty: 0.00
Misc 51301 26.09
Latched 0.00
Reads 6.71G 4.0k/s
From file 68.42k 0.0/s 0.00
Ahead Rnd 41 0.0/s
Ahead Sql 1487 0.0/s
Writes 132.01M 79.7/s
Flushes 2.31M 1.4/s
Wait Free 0 0/s


Maybe this will help you out?

Are we perhaps monitoring the wrong thing?

Regards
Machiel





-----Original Message-----
From: Carlos Proal [mailto:carlos.proal@gmail.com]
Sent: 16 March 2010 9:31 AM
To: mysql@lists.mysql.com
Subject: Re: Innodb buffer pool usage


Hi Machiel,

What do you mean with "innodb buffer pool is at 100% full" ?

There are several status variables associated with innodb buffer pool ie:
Innodb_buffer_pool_pages_free is the number of unused data pages.
Innodb_buffer_pool_pages_total is the total number of pages.
Innodb_buffer_pool_pages_data is the total number of used data pages
(clean and dirty).
Innodb_buffer_pool_pages_dirty is the number of dirty data pages. The
number of clean data pages can be calculated from these first two status
variables.
etc..

You can calculate the usage ratio with a basic recipe:
Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total
This is the value that you have to monitor. If its close to 1, then you
set up too much memory for innodb (warning: that this can be due to
frequent flushed to disk, so you have to check that too); if on the
other hand the ratio is too low, then you effectively are running out of
resources and may need to add more memory to innodb. You can imply other
things, with the other variables too.

Obviously you have to do the job and review this values along the time,
maybe there are some actions/effects like running reports or etl
processes, you have to figure out what is going on as a "whole" not just
the values in the formula.

BTW: In Oracle is the same story, one thing is what you reserve
for...and other thing is the actual usage. The latter if you are tuning
Oracle manually, because one important difference in Oracle 10 and 11 is
that the buffers can grow and shrink automatically (if you configure it)
so you can say "use the 100% memory at your convenience" and Oracle can,
for example, reduce the sort buffers and extend the index buffers on the
fly. Obviously this also has advantages and disadvantages, but as a new
DBA is good to get involved in this concepts and comparisons between dbms.

Carlos Proal



On 3/16/2010 12:46 AM, Machiel Richards wrote:
> Hi all
>
>
>
> Maybe someone can assist me with this one.
>
>
>
> A while back I requested some information relating to the
> MySQL innodb buffer pool size that seems to fill up rather frequently.
>
> The buffer pool is currently set to 3Gb , and it takes
about
> 2-3 weeks after a restart to fill up.
>
>
>
> Someone replied and stated that this is preferred to be
> running at 100% usage as it means that it is running optimally.
>
>
>
> However, the oracle guys in our office disagrees with
this
> and want to know the following:
>
>
>
> . If the innodb buffer pool is at 100% full, how will we know when
> it needs more buffers
>
> o i.e. let's say the database starts getting very busy and needs more
> buffers, how will we know that it requires this if the buffer pool usage
is
> already at 100%.
>
>
>
> I am fairly new to database administration so no luck in answering them on
> this so I would appreciate the assistance.
>
>
>
>
>
> Regards
>
> Machiel
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=machiel.richards@gmail.co m


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

Hi Carlos (and all)

I had a look at the script that we use an the following is the
calculations that is used to calculate the innodb buffer usage.




Machiel



-----Original Message-----
From: Carlos Proal [mailto:carlos.proal@gmail.com]
Sent: 16 March 2010 9:31 AM
To: mysql@lists.mysql.com
Subject: Re: Innodb buffer pool usage


Hi Machiel,

What do you mean with "innodb buffer pool is at 100% full" ?

There are several status variables associated with innodb buffer pool ie:
Innodb_buffer_pool_pages_free is the number of unused data pages.
Innodb_buffer_pool_pages_total is the total number of pages.
Innodb_buffer_pool_pages_data is the total number of used data pages
(clean and dirty).
Innodb_buffer_pool_pages_dirty is the number of dirty data pages. The
number of clean data pages can be calculated from these first two status
variables.
etc..

You can calculate the usage ratio with a basic recipe:
Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total
This is the value that you have to monitor. If its close to 1, then you
set up too much memory for innodb (warning: that this can be due to
frequent flushed to disk, so you have to check that too); if on the
other hand the ratio is too low, then you effectively are running out of
resources and may need to add more memory to innodb. You can imply other
things, with the other variables too.

Obviously you have to do the job and review this values along the time,
maybe there are some actions/effects like running reports or etl
processes, you have to figure out what is going on as a "whole" not just
the values in the formula.

BTW: In Oracle is the same story, one thing is what you reserve
for...and other thing is the actual usage. The latter if you are tuning
Oracle manually, because one important difference in Oracle 10 and 11 is
that the buffers can grow and shrink automatically (if you configure it)
so you can say "use the 100% memory at your convenience" and Oracle can,
for example, reduce the sort buffers and extend the index buffers on the
fly. Obviously this also has advantages and disadvantages, but as a new
DBA is good to get involved in this concepts and comparisons between dbms.

Carlos Proal



On 3/16/2010 12:46 AM, Machiel Richards wrote:
> Hi all
>
>
>
> Maybe someone can assist me with this one.
>
>
>
> A while back I requested some information relating to the
> MySQL innodb buffer pool size that seems to fill up rather frequently.
>
> The buffer pool is currently set to 3Gb , and it takes
about
> 2-3 weeks after a restart to fill up.
>
>
>
> Someone replied and stated that this is preferred to be
> running at 100% usage as it means that it is running optimally.
>
>
>
> However, the oracle guys in our office disagrees with
this
> and want to know the following:
>
>
>
> . If the innodb buffer pool is at 100% full, how will we know when
> it needs more buffers
>
> o i.e. let's say the database starts getting very busy and needs more
> buffers, how will we know that it requires this if the buffer pool usage
is
> already at 100%.
>
>
>
> I am fairly new to database administration so no luck in answering them on
> this so I would appreciate the assistance.
>
>
>
>
>
> Regards
>
> Machiel
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=machiel.richards@gmail.co m


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

--0016e6db2dbbe8b74c0481e9efd5
Content-Type: text/plain; charset=ISO-8859-1

There will be an increase in IO and a noticeable decrease in performance if
the buffer pool is too small. Give it all the memory which is not needed
elsewhere. If you can set it a little larger than the size of all your
innodb tablespaces that would be good.

Oracle is a very different animal to MySQL!

Regards
John
On Tue, Mar 16, 2010 at 6:46 AM, Machiel Richards wrote:

> Hi all
>
>
>
> Maybe someone can assist me with this one.
>
>
>
> A while back I requested some information relating to the
> MySQL innodb buffer pool size that seems to fill up rather frequently.
>
> The buffer pool is currently set to 3Gb , and it takes about
> 2-3 weeks after a restart to fill up.
>
>
>
> Someone replied and stated that this is preferred to be
> running at 100% usage as it means that it is running optimally.
>
>
>
> However, the oracle guys in our office disagrees with this
> and want to know the following:
>
>
>
> . If the innodb buffer pool is at 100% full, how will we know when
> it needs more buffers
>
> o i.e. let's say the database starts getting very busy and needs more
> buffers, how will we know that it requires this if the buffer pool usage is
> already at 100%.
>
>
>
> I am fairly new to database administration so no luck in answering them on
> this so I would appreciate the assistance.
>
>
>
>
>
> Regards
>
> Machiel
>
>

--0016e6db2dbbe8b74c0481e9efd5--