List:General Discussion« Previous MessageNext Message »
From:machiel.richards Date:March 16 2010 9:54am
Subject:RE: Innodb buffer pool usage
View as plain text  
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@stripped] 
Sent: 16 March 2010 9:31 AM
To: mysql@stripped
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=1

Thread
Innodb buffer pool usageMachiel Richards16 Mar
  • Re: Innodb buffer pool usageCarlos Proal16 Mar
    • RE: Innodb buffer pool usagemachiel.richards16 Mar
    • RE: Innodb buffer pool usagemachiel.richards16 Mar
  • Re: Innodb buffer pool usageJohn Daisley16 Mar