Innodb buffer pool size filling up

------=_NextPart_000_007A_01CA7258.B54B2610
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Good day all



I hope that someone can help me out.



We have a MySQL database where the INNODB_BUFFER_POOL_SIZE
keeps on filling up.

If the database is restarted, the buffer pool is cleared,
however from there it just starts filling up again until 100% full.



We tried increasing the value however the same just happens
over again, it starts getting used more and more until it is 100% full.





Does anybody know how we can get this resolved to first of
all determine why this is happening now and second of all how it can be
fixed / cleared regularly.





Regards

Machiel




------=_NextPart_000_007A_01CA7258.B54B2610--

RE: Innodb buffer pool size filling up

Machiel:

> We have a MySQL database where the
> INNODB_BUFFER_POOL_SIZE
> keeps on filling up.

Are you getting any errors or just noticing the buffer
pool is full?

I saw some error messages about the buffer pool size
becoming a problem if the fscync is slow. Do you see
any more info in the logs?

Neil

--
Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
Host your MySQL database on a CentOS VPS for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


--
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 filling up

There are no errors in the logs at all.

We have a script to calculate the percentages used and free in order
to do daily,weekly and monthly reporting trend analyses and thus we notice
the growth but no errors.



-----Original Message-----
From: Neil Aggarwal [mailto:neil@JAMMConsulting.com]
Sent: 01 December 2009 08:55 AM
To: mysql@lists.mysql.com
Subject: RE: Innodb buffer pool size filling up

Machiel:

> We have a MySQL database where the
> INNODB_BUFFER_POOL_SIZE
> keeps on filling up.

Are you getting any errors or just noticing the buffer
pool is full?

I saw some error messages about the buffer pool size
becoming a problem if the fscync is slow. Do you see
any more info in the logs?

Neil

--
Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
Host your MySQL database on a CentOS VPS for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


--
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 size filling up

--001485eafeb409fd0f0479a8d3d1
Content-Type: text/plain; charset=ISO-8859-1

That is basically its use,
the buffer pool is the collection of all mysql innodb buffers,
and after warm up it goes to keep all cacheable data.
How big is your INNODB_BUFFER_POOL_SIZE ?

Cheers

Claudio


2009/12/1 machiel.richards

> There are no errors in the logs at all.
>
> We have a script to calculate the percentages used and free in order
> to do daily,weekly and monthly reporting trend analyses and thus we notice
> the growth but no errors.
>
>
>
> -----Original Message-----
> From: Neil Aggarwal [mailto:neil@JAMMConsulting.com]
> Sent: 01 December 2009 08:55 AM
> To: mysql@lists.mysql.com
> Subject: RE: Innodb buffer pool size filling up
>
> Machiel:
>
> > We have a MySQL database where the
> > INNODB_BUFFER_POOL_SIZE
> > keeps on filling up.
>
> Are you getting any errors or just noticing the buffer
> pool is full?
>
> I saw some error messages about the buffer pool size
> becoming a problem if the fscync is slow. Do you see
> any more info in the logs?
>
> Neil
>
> --
> Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
> Host your MySQL database on a CentOS VPS for $25/mo
> Unmetered bandwidth = no overage charges, 7 day free trial
>
>
> --
> 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=claudio.nanni@gmail.com
>
>


--
Claudio

--001485eafeb409fd0f0479a8d3d1--

RE: Innodb buffer pool size filling up

------=_NextPart_000_01D8_01CA7288.A29171C0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

The size was at 2Gb and was recently changed to 3Gb in size during the last
week of November (around the 23rd / 24th) and as of this morning was already
sitting at 2.3gb used.



The total database size is about 750Mb.



Regards

Machiel





From: Claudio Nanni [mailto:claudio.nanni@gmail.com]
Sent: 01 December 2009 01:12 PM
To: machiel.richards
Cc: mysql@lists.mysql.com
Subject: Re: Innodb buffer pool size filling up



That is basically its use,
the buffer pool is the collection of all mysql innodb buffers,
and after warm up it goes to keep all cacheable data.
How big is your INNODB_BUFFER_POOL_SIZE ?

Cheers

Claudio



2009/12/1 machiel.richards

There are no errors in the logs at all.

We have a script to calculate the percentages used and free in order
to do daily,weekly and monthly reporting trend analyses and thus we notice
the growth but no errors.




-----Original Message-----
From: Neil Aggarwal [mailto:neil@JAMMConsulting.com]
Sent: 01 December 2009 08:55 AM
To: mysql@lists.mysql.com
Subject: RE: Innodb buffer pool size filling up

Machiel:

> We have a MySQL database where the
> INNODB_BUFFER_POOL_SIZE
> keeps on filling up.

Are you getting any errors or just noticing the buffer
pool is full?

I saw some error messages about the buffer pool size
becoming a problem if the fscync is slow. Do you see
any more info in the logs?

Neil

--
Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
Host your MySQL database on a CentOS VPS for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


--
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=claudio.nanni@gmail.com




--
Claudio


------=_NextPart_000_01D8_01CA7288.A29171C0--

Re: Innodb buffer pool size filling up

--001485e60a0dcc5ecd0479a8fb99
Content-Type: text/plain; charset=ISO-8859-1

The Innodb Buffer Pull usually follow a growth over time that resembles an
horizontal asintot (
http://www.maecla.it/bibliotecaMatematica/go_file/MONE_BESA/ grafico.gif)
This to leverage all its size!
So should not be a problem!

Cheers

Claudio


2009/12/1 machiel.richards

> The size was at 2Gb and was recently changed to 3Gb in size during the
> last week of November (around the 23rd / 24th) and as of this morning was
> already sitting at 2.3gb used.
>
>
>
> The total database size is about 750Mb.
>
>
>
> Regards
>
> Machiel
>
>
>
>
>
> *From:* Claudio Nanni [mailto:claudio.nanni@gmail.com]
> *Sent:* 01 December 2009 01:12 PM
> *To:* machiel.richards
> *Cc:* mysql@lists.mysql.com
> *Subject:* Re: Innodb buffer pool size filling up
>
>
>
> That is basically its use,
> the buffer pool is the collection of all mysql innodb buffers,
> and after warm up it goes to keep all cacheable data.
> How big is your INNODB_BUFFER_POOL_SIZE ?
>
> Cheers
>
> Claudio
>
> 2009/12/1 machiel.richards
>
> There are no errors in the logs at all.
>
> We have a script to calculate the percentages used and free in order
> to do daily,weekly and monthly reporting trend analyses and thus we notice
> the growth but no errors.
>
>
>
>
> -----Original Message-----
> From: Neil Aggarwal [mailto:neil@JAMMConsulting.com]
> Sent: 01 December 2009 08:55 AM
> To: mysql@lists.mysql.com
> Subject: RE: Innodb buffer pool size filling up
>
> Machiel:
>
> > We have a MySQL database where the
> > INNODB_BUFFER_POOL_SIZE
> > keeps on filling up.
>
> Are you getting any errors or just noticing the buffer
> pool is full?
>
> I saw some error messages about the buffer pool size
> becoming a problem if the fscync is slow. Do you see
> any more info in the logs?
>
> Neil
>
> --
> Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
> Host your MySQL database on a CentOS VPS for $25/mo
> Unmetered bandwidth = no overage charges, 7 day free trial
>
>
> --
> 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=claudio.nanni@gmail.com
>
>
>
>
> --
> Claudio
>



--
Claudio

--001485e60a0dcc5ecd0479a8fb99--

RE: Innodb buffer pool size filling up

>-----Original Message-----
>From: machiel.richards [mailto:machiel.richards@gmail.com]
>Sent: Tuesday, December 01, 2009 6:17 AM
>To: 'Claudio Nanni'
>Cc: mysql@lists.mysql.com
>Subject: RE: Innodb buffer pool size filling up
>
>The size was at 2Gb and was recently changed to 3Gb in size during the last
>week of November (around the 23rd / 24th) and as of this morning was already
>sitting at 2.3gb used.
>
>
[JS] At the others have said, the whole purpose of a buffer pool is to hold as
much frequently used data as possible. That lowers the probability of having
to do physical I/O, which is much slower than memory access. The buffer pool
should be full.
>
>The total database size is about 750Mb.
>
>
[JS] It does surprise me that the buffer pool fills up, even though it is
three times the size of your database. My guess is that whatever mechanism is
used to "scavenge" space in the buffer pool isn't triggered until the buffer
pool is full; but that is simply a guess, I really have no idea.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com



>
>Regards
>
>Machiel
>
>
>
>
>
>From: Claudio Nanni [mailto:claudio.nanni@gmail.com]
>Sent: 01 December 2009 01:12 PM
>To: machiel.richards
>Cc: mysql@lists.mysql.com
>Subject: Re: Innodb buffer pool size filling up
>
>
>
>That is basically its use,
>the buffer pool is the collection of all mysql innodb buffers,
>and after warm up it goes to keep all cacheable data.
>How big is your INNODB_BUFFER_POOL_SIZE ?
>
>Cheers
>
>Claudio
>
>
>
>2009/12/1 machiel.richards
>
>There are no errors in the logs at all.
>
> We have a script to calculate the percentages used and free in order
>to do daily,weekly and monthly reporting trend analyses and thus we notice
>the growth but no errors.
>
>
>
>
>-----Original Message-----
>From: Neil Aggarwal [mailto:neil@JAMMConsulting.com]
>Sent: 01 December 2009 08:55 AM
>To: mysql@lists.mysql.com
>Subject: RE: Innodb buffer pool size filling up
>
>Machiel:
>
>> We have a MySQL database where the
>> INNODB_BUFFER_POOL_SIZE
>> keeps on filling up.
>
>Are you getting any errors or just noticing the buffer
>pool is full?
>
>I saw some error messages about the buffer pool size
>becoming a problem if the fscync is slow. Do you see
>any more info in the logs?
>
> Neil
>
>--
>Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
>Host your MySQL database on a CentOS VPS for $25/mo
>Unmetered bandwidth = no overage charges, 7 day free trial
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>
>http://lists.mysql.com/mysql?unsub=machiel.richards@gmail.c om
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>
>
>
>
>--
>Claudio





--
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 filling up

Good Morning all

QUOTE: "We have a MySQL database where the
INNODB_BUFFER_POOL_SIZE keeps on filling up"

I have monitored this issue for the last couple of weeks and even
though the buffer pool usage is increasing slowly, it is still heading to
become 100% full.

It used to vary in the past where it would increase and decrease
each day as required, however this does not seem to be happening anymore.

Does anybody know whether there is a process that is supposed to
manage this or otherwise clear this occasionally that might not be working?

We are getting quite a bit of issues with the client wanting to know
why this is happening, however thus far I haven't been able to find any
conclusive answers, not even through google.

Any assistance would be appreciated.


Thanking everyone in advance.



Regards
Machiel


-----Original Message-----
From: Jerry Schwartz [mailto:jschwartz@the-infoshop.com]
Sent: 01 December 2009 10:04 PM
To: 'machiel.richards'; 'Claudio Nanni'
Cc: mysql@lists.mysql.com
Subject: RE: Innodb buffer pool size filling up

>-----Original Message-----
>From: machiel.richards [mailto:machiel.richards@gmail.com]
>Sent: Tuesday, December 01, 2009 6:17 AM
>To: 'Claudio Nanni'
>Cc: mysql@lists.mysql.com
>Subject: RE: Innodb buffer pool size filling up
>
>The size was at 2Gb and was recently changed to 3Gb in size during the last
>week of November (around the 23rd / 24th) and as of this morning was
already
>sitting at 2.3gb used.
>
>
[JS] At the others have said, the whole purpose of a buffer pool is to hold
as
much frequently used data as possible. That lowers the probability of having

to do physical I/O, which is much slower than memory access. The buffer pool

should be full.
>
>The total database size is about 750Mb.
>
>
[JS] It does surprise me that the buffer pool fills up, even though it is
three times the size of your database. My guess is that whatever mechanism
is
used to "scavenge" space in the buffer pool isn't triggered until the buffer

pool is full; but that is simply a guess, I really have no idea.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com



>
>Regards
>
>Machiel
>
>
>
>
>
>From: Claudio Nanni [mailto:claudio.nanni@gmail.com]
>Sent: 01 December 2009 01:12 PM
>To: machiel.richards
>Cc: mysql@lists.mysql.com
>Subject: Re: Innodb buffer pool size filling up
>
>
>
>That is basically its use,
>the buffer pool is the collection of all mysql innodb buffers,
>and after warm up it goes to keep all cacheable data.
>How big is your INNODB_BUFFER_POOL_SIZE ?
>
>Cheers
>
>Claudio
>
>
>
>2009/12/1 machiel.richards
>
>There are no errors in the logs at all.
>
> We have a script to calculate the percentages used and free in order
>to do daily,weekly and monthly reporting trend analyses and thus we notice
>the growth but no errors.
>
>
>
>
>-----Original Message-----
>From: Neil Aggarwal [mailto:neil@JAMMConsulting.com]
>Sent: 01 December 2009 08:55 AM
>To: mysql@lists.mysql.com
>Subject: RE: Innodb buffer pool size filling up
>
>Machiel:
>
>> We have a MySQL database where the
>> INNODB_BUFFER_POOL_SIZE
>> keeps on filling up.
>
>Are you getting any errors or just noticing the buffer
>pool is full?
>
>I saw some error messages about the buffer pool size
>becoming a problem if the fscync is slow. Do you see
>any more info in the logs?
>
> Neil
>
>--
>Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
>Host your MySQL database on a CentOS VPS for $25/mo
>Unmetered bandwidth = no overage charges, 7 day free trial
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>
>http://lists.mysql.com/mysql?unsub=machiel.richards@gmail.c om
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>
>
>
>
>--
>Claudio





--
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: RE: Innodb buffer pool size filling up

--001485f9aa200aae8b047afc332c
Content-Type: text/plain; charset=ISO-8859-1

Machiel,
That is how it is supposed to work.
You assign a certain amount of memory(RAM) to it and the database engine
then manages it. It is highly desirable that this buffer is fully used, and
if the growing curve is slow it is because it is not undersized. If you
really need more ram for other uses in your system you can lower it a
little, but I think you are using this box only for MySQL and a 'good'
practice is to use 70/80% of system Ram ONLY for innodb buffer pool.

Cheers
Claudio

On 18 dec 2009 06:34, "machiel.richards" wrote:

Good Morning all

QUOTE: "We have a MySQL database where the

INNODB_BUFFER_POOL_SIZE keeps on filling up"
I have monitored this issue for the last couple of weeks and even
though the buffer pool usage is increasing slowly, it is still heading to
become 100% full.

It used to vary in the past where it would increase and decrease
each day as required, however this does not seem to be happening anymore.

Does anybody know whether there is a process that is supposed to
manage this or otherwise clear this occasionally that might not be working?

We are getting quite a bit of issues with the client wanting to know
why this is happening, however thus far I haven't been able to find any
conclusive answers, not even through google.

Any assistance would be appreciated.


Thanking everyone in advance.



Regards
Machiel

-----Original Message----- From: Jerry Schwartz [mailto:
jschwartz@the-infoshop.com] Sent: 01 Dece...
--

MySQL General Mailing List For list archives:
http://lists.mysql.com/mysqlTo unsubscribe: http:/...

--001485f9aa200aae8b047afc332c--

RE: RE: Innodb buffer pool size filling up

------=_NextPart_000_00D0_01CA7FCE.D0B6C570
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Thank you very much.



This now explains a lot.



From: Claudio Nanni [mailto:claudio.nanni@gmail.com]
Sent: 18 December 2009 10:05 AM
To: machiel.richards
Cc: mysql@lists.mysql.com
Subject: Re: RE: Innodb buffer pool size filling up



Machiel,
That is how it is supposed to work.
You assign a certain amount of memory(RAM) to it and the database engine
then manages it. It is highly desirable that this buffer is fully used, and
if the growing curve is slow it is because it is not undersized. If you
really need more ram for other uses in your system you can lower it a
little, but I think you are using this box only for MySQL and a 'good'
practice is to use 70/80% of system Ram ONLY for innodb buffer pool.

Cheers
Claudio

On 18 dec 2009 06:34, "machiel.richards" wrote:

Good Morning all

QUOTE: "We have a MySQL database where the

INNODB_BUFFER_POOL_SIZE keeps on filling up"

I have monitored this issue for the last couple of weeks and even
though the buffer pool usage is increasing slowly, it is still heading to
become 100% full.

It used to vary in the past where it would increase and decrease
each day as required, however this does not seem to be happening anymore.

Does anybody know whether there is a process that is supposed to
manage this or otherwise clear this occasionally that might not be working?

We are getting quite a bit of issues with the client wanting to know
why this is happening, however thus far I haven't been able to find any
conclusive answers, not even through google.

Any assistance would be appreciated.


Thanking everyone in advance.



Regards
Machiel

-----Original Message----- From: Jerry Schwartz
[mailto:jschwartz@the-infoshop.com] Sent: 01 Dece...

--

MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
To unsubscribe: http:/...


------=_NextPart_000_00D0_01CA7FCE.D0B6C570--

RE: Innodb buffer pool size filling up

>-----Original Message-----
>From: machiel.richards [mailto:machiel.richards@gmail.com]
>Sent: Friday, December 18, 2009 12:33 AM
>To: mysql@lists.mysql.com
>Subject: RE: Innodb buffer pool size filling up
>
>Good Morning all
>
> QUOTE: "We have a MySQL database where the
> INNODB_BUFFER_POOL_SIZE keeps on filling up"
>
> I have monitored this issue for the last couple of weeks and even
>though the buffer pool usage is increasing slowly, it is still heading to
>become 100% full.
>
[JS] A buffer pool should become 100% full. That is part and parcel of its
purpose, which is to avoid having to do a physical disk I/O operation.

If a buffer pool never fills up, it is too big.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com







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