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.pr...@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.richa...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
