BUFFERPOOL question
A user asked what practical difference there is between BUFFERPOOL settings lru_min_dirty=1/lru_max_dirty=2 and 51/52 when the gap stays the same. Answers explained that the values are thresholds at which cleaner threads start and stop flushing, so high values leave far more dirty pages in the pool at checkpoint time -- meaning longer, blocking checkpoints (pre-IDS 11) and slower crash recovery. Low values (even fractional, e.g. 0.5) suit OLTP; higher values suit batch/DSS work. John Miller and Art Kagel noted that IDS 11.50's non-blocking checkpoints reduce the impact, so somewhat higher settings become acceptable, with a developerWorks article cited. The poster's follow-up about setting extremely tiny values is not answered in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all,
I have a basic question but it make me confuse. What's the different between
BUFFERPOOL below ?
BUFFERPOOL size=2K,buffers=1000000,lrus=64,lru_min_dirty=1,lru_max_dirty=2
and
BUFFERPOOL size=2K,buffers=1000000,lrus=64,lru_min_dirty=51,lru_max_dirty=52
Best Regards,
Jakkrit A.
Hi,
Informix has a very good documentation explaining these (and other) parameters.
The short answer to your question is:
With the second setting the database server may need to flush (write) 1/2
million pages at checkpoint time (blocking the server prior to IDS 11). That
can take some time.
In the first setting the server only needs to flush 20000 pages (in the worst
case). You can even use fractional values (lru_min_dirty=0.5).
Regards
Andreas Kutsche
-------------------------------------------
SPAR Österreichische Warenhandels-AG
Hauptzentrale
A - 5015 Salzburg, Europastrasse 3
FN 34170 a
Tel: +43 662 4470 24223
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich
geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse,
enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die
Informationen in dieser E-Mail sind ausschließlich für den Adressaten
bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir
Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung
zu setzen.
Über das Internet versandte E-Mails können leicht manipuliert oder unter
fremdem Namen erstellt werden. Daher schließen wir die rechtliche
Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der
Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich
bestätigt und gezeichnet wird.
Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung
von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl.
hieraus entstehende Schäden.
Wir danken für Ihr Verständnis.
Important notice: The contents of this e-mail may contain confidential and
legally protected information that is in particular related to operational and
trade secrets, which the recipient is obliged to treat as confidential. The
information in this e-mail is made available exclusively for use by the
addressee. In the event that the e-mail may have been sent to you in error, we
would ask you to kindly delete this communication from your system and to
contact us.
E-mails sent via the Internet can be easily manipulated or sent out under
someone else's name. We therefore do not accept legal liability for the
information contained in this communication. The contents of the e-mail are
only legally binding if they have been confirmed and signed by us in writing.
If, in spite of our using Antivirus protection software, a virus may have
penetrated your system through the sending of this e-mail, we do not accept
liability for any damage that may possibly arise as a result of this.
We trust that you appreciate our position.
-------------------------------------------
-----Ursprüngliche Nachricht-----
Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von jak aka
Gesendet: Donnerstag, 12. Februar 2009 08:32
An: ids@iiug.org
Betreff: BUFFERPOOL question [14854]
Hi all,
I have a basic question but it make me confuse. What's the different between
BUFFERPOOL below ?
BUFFERPOOL size=2K,buffers=1000000,lrus=64,lru_min_dirty=1,lru_max_dirty=2
and
BUFFERPOOL size=2K,buffers=1000000,lrus=64,lru_min_dirty=51,lru_max_dirty=52
Best Regards,
Jakkrit A.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Trick question?
The LRU flush parameters, lru_max_dirty and lru_min_dirty are different.
If you want more than that, look in the Administrator's Reference manual for
the meaning, purpose, and setting of the lru flush parameters in the
BUFFERPOOL section. You may also find more information in the Tuning Guide
or by searching the archives of this list for lru_min_dirty, lru_max_dirty,
and lru_min/max_dirty (I sometimes refer to both together that way. Meet us
half way Jak, do some research before you post a question.
(*&^ but I'm cranky today.
Art
On Thu, Feb 12, 2009 at 2:31 AM, jak aka <jakkritakeng@yahoo.com> wrote:
> Hi all,
>
> I have a basic question but it make me confuse. What's the different
> between
> BUFFERPOOL below ?>
> BUFFERPOOL size=2K,buffers=1000000,lrus=64,lru_min_dirty=1,lru_max_dirty=2
> and
> BUFFERPOOL
> size=2K,buffers=1000000,lrus=64,lru_min_dirty=51,lru_max_dirty=52>
> Best Regards,
> Jakkrit A.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--001485f91dd2b220b50462b71e68
Hi Jak,
The LRU values are different.
Now as to a deeper philosophical question, I suppose there is nothing really
different with the BUFFERPOOLS below, only in how the identical BUFFERPOOLS
are managed.
________________________________
From: jak aka <jakkritakeng@yahoo.com>
To: ids@iiug.org
Sent: Thursday, February 12, 2009 1:31:47 AM
Subject: BUFFERPOOL question [14854]
Hi all,
I have a basic question but it make me confuse. What's the different between
BUFFERPOOL below ?
BUFFERPOOL size=2K,buffers=1000000,lrus=64,lru_min_dirty=1,lru_max_dirty=2
and
BUFFERPOOL size=2K,buffers=1000000,lrus=64,lru_min_dirty=51,lru_max_dirty=52
Best Regards,
Jakkrit A.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Sorry , I may ask you not clear. I know what's the meaning of lru_max and
lru_min and I 've also read many book but I still not clear. I don't want to
waste your time if you feel uncomfortable to answer please do nothing.
Anyway , my question is if I remain the gap 1 (lru_max - lrun_min ,2-1 ,
52-51) . What 's the different ?
What will be effect, checkpoint ,... ?
Sorry again Art.
________________________________
From: Art Kagel <art.kagel@gmail.com>
To: ids@iiug.org
Sent: Thursday, February 12, 2009 6:35:43 PM
Subject: Re: BUFFERPOOL question [14857]
Trick question?
The LRU flush parameters, lru_max_dirty and lru_min_dirty are different.
If you want more than that, look in the Administrator's Reference manual for
the meaning, purpose, and setting of the lru flush parameters in the
BUFFERPOOL section. You may also find more information in the Tuning Guide
or by searching the archives of this list for lru_min_dirty, lru_max_dirty,
and lru_min/max_dirty (I sometimes refer to both together that way. Meet us
half way Jak, do some research before you post a question.
(*&^ but I'm cranky today.
Art
On Thu, Feb 12, 2009 at 2:31 AM, jak aka <jakkritakeng@yahoo.com> wrote:
> Hi all,
>
> I have a basic question but it make me confuse. What's the different
> between
> BUFFERPOOL below ?>
> BUFFERPOOL size=2K,buffers=1000000,lrus=64,lru_min_dirty=1,lru_max_dirty=2
> and
> BUFFERPOOL
> size=2K,buffers=1000000,lrus=64,lru_min_dirty=51,lru_max_dirty=52>
> Best Regards,
> Jakkrit A.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--001485f91dd2b220b50462b71e68
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Informix have two different forms of flush dirty page from buffer to disck.
The first one is online (not lock) and if made by the cleaners theards. The
second one if in the checkpoint proccess (locking updates).
The form of work of the cleaners depends of the lru_min_dirty and
lru_max_dirty parameters. Lru_max_dirty parameter specify when the cleaners
began to work and lru_min_dirty when cleaners stop to work. By example, If
we have a have a configuration of 51,50 (max_dirty and min_dirty). The
cleaners not began to work until the 51% of the buffers are dirty. In your
second example if we have 2,1, the cleaners began to work when the 2% of the
buffers are dirty. Is clear than with a configuration of 2,1 we go to have
minus dirty pages when the checkpoint process start and the duration of
checkpoints will be smaller.
This sound fine, but you also must to have in consideration than flush dirty
page is more efficient in checkpoint process than will cleaners (checkpoint
don´t need to worry about locks).
So, in sumary if you have interative operations in your database your must
to have small values for lry_min_dirty and lry_max_dirty and a small
checkpoint interval in order to not lock interatives users. But if your
database only have batch operation don't worry about checkpoint times
because don't lock anyone and put high values for this parameters.
I hope this clarify you
Juan Jorge Cruces Fernández
Accelya
----- Original Message -----
From: "jak aka" <jakkritakeng@yahoo.com>
To: <ids@iiug.org>
Sent: Monday, February 16, 2009 6:27 AM
Subject: Re: BUFFERPOOL question [14893]
> Sorry , I may ask you not clear. I know what's the meaning of lru_max and
> lru_min and I 've also read many book but I still not clear. I don't want
> to
> waste your time if you feel uncomfortable to answer please do nothing.
>
> Anyway , my question is if I remain the gap 1 (lru_max - lrun_min ,2-1 ,
> 52-51) . What 's the different ?
> What will be effect, checkpoint ,... ?
>
> Sorry again Art.
>
> ________________________________
> From: Art Kagel <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Thursday, February 12, 2009 6:35:43 PM
> Subject: Re: BUFFERPOOL question [14857]
>
> Trick question?
>
> The LRU flush parameters, lru_max_dirty and lru_min_dirty are different.
>
> If you want more than that, look in the Administrator's Reference manual
> for
> the meaning, purpose, and setting of the lru flush parameters in the
> BUFFERPOOL section. You may also find more information in the Tuning Guide
> or by searching the archives of this list for lru_min_dirty,
> lru_max_dirty,
> and lru_min/max_dirty (I sometimes refer to both together that way. Meet
> us
> half way Jak, do some research before you post a question.
>
> (*&^ but I'm cranky today.
>
> Art
>
> On Thu, Feb 12, 2009 at 2:31 AM, jak aka <jakkritakeng@yahoo.com> wrote:
>
>> Hi all,
>>
>> I have a basic question but it make me confuse. What's the different
>> between
>> BUFFERPOOL below ?
>>
>> BUFFERPOOL
>> size=2K,buffers=1000000,lrus=64,lru_min_dirty=1,lru_max_dirty=2>> and
>> BUFFERPOOL
>> size=2K,buffers=1000000,lrus=64,lru_min_dirty=51,lru_max_dirty=52>>
>> Best Regards,
>> Jakkrit A.
>>
>>
>>
>>
>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity
> with which I am affiliated nor those of the entities themselves.
>
> --001485f91dd2b220b50462b71e68
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Este mensaje se dirige exclusivamente a su destinatario, contiene información
CONFIDENCIAL sometida a secreto profesional y su divulgación está prohibida
por ley. Si ha recibido este mensaje por error, su copia y uso están
prohibidos, rogándole que nos lo comunique inmediatamente por esta misma vía y
proceda a su destrucción.
El correo electrónico no garantiza la confidencialidad de los mensajes ni su
integridad o correcta recepción. La compañía ACCELYA no asume responsabilidad
por estas circunstancias.
Si el destinatario de este mensaje no consintiera la utilización del correo
electrónico y la grabación de los mensajes, rogamos nos lo notifique de forma
inmediata.
This message is intended exclusively for its addressee. It contains
information that is CONFIDENTIAL and protected by a professional privilege or
whose disclosure is prohibited by law. If this message has been received in
error, you should know that it is forbidden to copy or use it. Please
immediately notify us via e-mail and delete it.
Internet e-mail neither guarantees the confidentiality nor the integrity or
proper receipt of the messages sent. ACCELYA company assumes no liability or
responsibility for any error caused by matters beyond our control. If the
addressee of this message does not consent to the use of Internet e-mail and
message recording, please notify us immediately.
While this answer is very accurate for IDS releases before version 11. =
In
version 11
there is a new feature called non-blocking checkpoints. In short, it i=
s
generally
beneficial to have lru min and max dirty above 50% in version 11 or hig=
her.
I
would suggest reading the following for more details.
http://www.ibm.com/developerworks/data/library/techarticle/dm-0703lashl=
ey/index.html
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 02/17/2009 09:40:46 AM:
> Informix have two different forms of flush dirty page from buffer to
disck.
> The first one is online (not lock) and if made by the cleaners theard=
s.
The
> second one if in the checkpoint proccess (locking updates).
>
> The form of work of the cleaners depends of the lru_min_dirty and
> lru_max_dirty parameters. Lru_max_dirty parameter specify when the
cleaners
> began to work and lru_min_dirty when cleaners stop to work. By exampl=
e,
If
> we have a have a configuration of 51,50 (max_dirty and min_dirty). Th=
e
> cleaners not began to work until the 51% of the buffers are dirty. In=
your
> second example if we have 2,1, the cleaners began to work when the 2%=
of
the
> buffers are dirty. Is clear than with a configuration of 2,1 we go to=
have
> minus dirty pages when the checkpoint process start and the duration =
of
> checkpoints will be smaller.
>
> This sound fine, but you also must to have in consideration than flus=
h
dirty
> page is more efficient in checkpoint process than will cleaners
(checkpoint
> don=B4t need to worry about locks).
>
> So, in sumary if you have interative operations in your database your=
must
> to have small values for lry_min_dirty and lry_max_dirty and a small
> checkpoint interval in order to not lock interatives users. But if yo=
ur
> database only have batch operation don't worry about checkpoint times=
> because don't lock anyone and put high values for this parameters.
>
> I hope this clarify you
>
> Juan Jorge Cruces Fern=E1ndez
> Accelya
>
> ----- Original Message -----
> From: "jak aka" <jakkritakeng@yahoo.com>
> To: <ids@iiug.org>
> Sent: Monday, February 16, 2009 6:27 AM
> Subject: Re: BUFFERPOOL question [14893]
>
> > Sorry , I may ask you not clear. I know what's the meaning of lru_m=
ax
and
> > lru_min and I 've also read many book but I still not clear. I don'=
t
want
> > to
> > waste your time if you feel uncomfortable to answer please do nothi=
ng.
> >
> > Anyway , my question is if I remain the gap 1 (lru_max -
lrun_min ,2-1 ,
> > 52-51) . What 's the different ?
> > What will be effect, checkpoint ,... ?
> >
> > Sorry again Art.
> >
> > ________________________________
> > From: Art Kagel <art.kagel@gmail.com>
> > To: ids@iiug.org
> > Sent: Thursday, February 12, 2009 6:35:43 PM
> > Subject: Re: BUFFERPOOL question [14857]
> >
> > Trick question?
> >
> > The LRU flush parameters, lru_max_dirty and lru_min_dirty are
different.
> >
> > If you want more than that, look in the Administrator's Reference
manual
> > for
> > the meaning, purpose, and setting of the lru flush parameters in th=
e
> > BUFFERPOOL section. You may also find more information in the Tunin=
g
Guide
> > or by searching the archives of this list for lru_min_dirty,
> > lru_max_dirty,
> > and lru_min/max_dirty (I sometimes refer to both together that way.=
Meet
> > us
> > half way Jak, do some research before you post a question.
> >
> > (*&^ but I'm cranky today.
> >
> > Art
> >
> > On Thu, Feb 12, 2009 at 2:31 AM, jak aka <jakkritakeng@yahoo.com>
wrote:
> >
> >> Hi all,
> >>
> >> I have a basic question but it make me confuse. What's the differe=
nt
> >> between
> >> BUFFERPOOL below ?
> >>
> >> BUFFERPOOL
> >> size=3D2K,buffers=3D1000000,lrus=3D64,lru_min_dirty=3D1,lru_max_di=
rty=3D2> >> and
> >> BUFFERPOOL
> >> size=3D2K,buffers=3D1000000,lrus=3D64,lru_min_dirty=3D51,lru_max_d=
irty=3D52> >>
> >> Best Regards,
> >> Jakkrit A.
> >>
> >>
> >>
> >>
> >
> >
>
***********************************************************************=
********
> >> Forum Note: Use "Reply" to post a response in the discussion forum=
.
> >>
> >>
> >
> > --
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
opinions
> > and
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization
> > with which I am associated either explicitly or implicitly. Neither=
do
> > those opinions reflect those of other individuals affiliated with a=
ny
> > entity
> > with which I am affiliated nor those of the entities themselves.
> >
> > --001485f91dd2b220b50462b71e68
> >
> >
> >
>
***********************************************************************=
********
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
> >
> >
>
***********************************************************************=
********
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
> >
>
> Este mensaje se dirige exclusivamente a su destinatario, contiene
informaci=F3n
> CONFIDENCIAL sometida a secreto profesional y su divulgaci=F3n est=E1=
prohibida
> por ley. Si ha recibido este mensaje por error, su copia y uso est=E1=
n
> prohibidos, rog=E1ndole que nos lo comunique inmediatamente por esta
> misma v=EDa y
> proceda a su destrucci=F3n.
> El correo electr=F3nico no garantiza la confidencialidad de los mensa=
jes ni
su
> integridad o correcta recepci=F3n. La compa=F1=EDa ACCELYA no asume
responsabilidad
> por estas circunstancias.
> Si el destinatario de este mensaje no consintiera la utilizaci=F3n de=
l
correo
> electr=F3nico y la grabaci=F3n de los mensajes, rogamos nos lo notifi=
quede
forma
> inmediata.
> This message is intended exclusively for its addressee. It contains
> information that is CONFIDENTIAL and protected by a professional
privilege or
> whose disclosure is prohibited by law. If this message has been recei=
ved
in
> error, you should know that it is forbidden to copy or use it. Please=
> immediately notify us via e-mail and delete it.
> Internet e-mail neither guarantees the confidentiality nor the integr=
ity
or
> proper receipt of the messages sent. ACCELYA company assumes no liabi=
lity
or
> responsibility for any error caused by matters beyond our control. If=
the
> addressee of this message does not consent to the use of Internet e-m=
ail
and
> message recording, please notify us immediately.
>
>
>
***********************************************************************=
*******
The following applies ONLY for servers that are primarily OLTP style
servers. For DSS and DW servers, you can probably set the lru flush
settings as high as you like, since there are few if any updates to the
database beyond bulk reloads for DW servers and bulk appends for DSS
servers.
OK, the difference is that setting lru_min/max_dirty to 51/52 rather than
2/1 will leave many more buffers dirty at checkpoint time. If you are
running any version of IDS prior to 11.50, which has non-blocking
checkpoints, or have disabled non-blocking checkpoints in 11.50, there will
be effects on your applications. More dirty buffers take longer to flush to
disk at checkpoint time (unless you are using 9.40 through 11.10 which have
a non-flushing form of checkpoint, but that has other problems - later).
Until 11.50's non-blocking checkpoints the checkpoint had to block until all
user sessions had left any critical code sections and had been blocked from
entering new critical sections. The block on user sessions lasts until all
dirty buffers have been flushed safely to disk. More buffers to be flushed
means more time that your applications will be stopped from making changes
to the database.
If you are using the form of checkpointing introduced in 9.40, buffers are
only flushed using LRU writes not during checkpoints. This almost
eliminates the blocks, however, the cost is possibly much longer recovery
times if the server crashes. Again, the more dirty buffers there are at any
given time, the longer it will take to recover the server after a crash.
With non-blocking checkpoints in 11.50, there are no more blocks during a
checkpoint and buffers are flushed during the checkpoint. So, as John
pointed out, the number of dirty buffers is less of an issue than it was in
earlier releases. However, in order to maintain reasonable recovery times,
the engine either has to flush buffers more aggressively or add resources to
the instance to maintain addtional recovery information. So, there is still
an impact from using higher lru_min/max_dirty settings, but far less than in
any previous versions. I would NEVER set them as high as 51/52, but while
in 7.31 I would certainly set them to 1/2 or 2/3 for an OLTP system, and on
9.40 through 11.10 I might even set them as low as 0.1/0.2 if I have a very
large buffer pool (indeed I was the first to request fractional lru flush
settings), on 11.50 I have considered increasing these as high as 10/5. Not
much higher than that certainly to my mind. I still feel most comfortable
when as much of my data as possible is safely on disk and not dependent on
even IDS's legendary recovery capabilities.
Art
On Mon, Feb 16, 2009 at 12:27 AM, jak aka <jakkritakeng@yahoo.com> wrote:
> Sorry , I may ask you not clear. I know what's the meaning of lru_max and
> lru_min and I 've also read many book but I still not clear. I don't want
> to
> waste your time if you feel uncomfortable to answer please do nothing.
>
> Anyway , my question is if I remain the gap 1 (lru_max - lrun_min ,2-1 ,
> 52-51) . What 's the different ?
> What will be effect, checkpoint ,... ?
>
> Sorry again Art.
>
> ________________________________
> From: Art Kagel <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Thursday, February 12, 2009 6:35:43 PM
> Subject: Re: BUFFERPOOL question [14857]
>
> Trick question?
>
> The LRU flush parameters, lru_max_dirty and lru_min_dirty are different.
>
> If you want more than that, look in the Administrator's Reference manual
> for
> the meaning, purpose, and setting of the lru flush parameters in the
> BUFFERPOOL section. You may also find more information in the Tuning Guide
> or by searching the archives of this list for lru_min_dirty, lru_max_dirty,
> and lru_min/max_dirty (I sometimes refer to both together that way. Meet us
> half way Jak, do some research before you post a question.
>
> (*&^ but I'm cranky today.
>
> Art
>
> On Thu, Feb 12, 2009 at 2:31 AM, jak aka <jakkritakeng@yahoo.com> wrote:
>
> > Hi all,
> >
> > I have a basic question but it make me confuse. What's the different
> > between
> > BUFFERPOOL below ?
> >
> > BUFFERPOOL
> size=2K,buffers=1000000,lrus=64,lru_min_dirty=1,lru_max_dirty=2> > and
> > BUFFERPOOL
> > size=2K,buffers=1000000,lrus=64,lru_min_dirty=51,lru_max_dirty=52> >
> > Best Regards,
> > Jakkrit A.
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity
> with which I am affiliated nor those of the entities themselves.
>
> --001485f91dd2b220b50462b71e68
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--0016364ee01cbef6150463268f42
Thank you all.
I have few more question.
Suppose.
buffer = 1000000
case1 lru_max = 51 , lru_min=50
case2 lru_max = 2 , lru_min=1
Dirty pages will be flushed 1% when checkpoint time (1% of 1M = 10000 pages) .
The checkpoint duration must be equal becase both case will flush only 10000
pages. Am I correct ?
- If I want to reduce the checkpoint to the mimimum duration , Can I set
0.0002, 0.0001 (lru_max,lru_min) (i'm not sure how many digit can I set) or
50.0002,50.0001 (0.0001 % of buffer to be flush when normal checkpoint) to
make all dirty page flushed by lru-flush.
If I do so, Any concern would you recommend ?
Best regards,
Jakkrit A.
________________________________
From: John Miller iii <miller3@us.ibm.com>
To: ids@iiug.org
Sent: Wednesday, February 18, 2009 4:03:31 AM
Subject: Re: BUFFERPOOL question [14905]
While this answer is very accurate for IDS releases before version 11. =
In
version 11
there is a new feature called non-blocking checkpoints. In short, it i=
s
generally
beneficial to have lru min and max dirty above 50% in version 11 or hig=
her.
I
would suggest reading the following for more details.
http://www.ibm.com/developerworks/data/library/techarticle/dm-0703lashl=
ey/index.html
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 02/17/2009 09:40:46 AM:
> Informix have two different forms of flush dirty page from buffer to
disck.
> The first one is online (not lock) and if made by the cleaners theard=
s.
The
> second one if in the checkpoint proccess (locking updates).
>
> The form of work of the cleaners depends of the lru_min_dirty and
> lru_max_dirty parameters. Lru_max_dirty parameter specify when the
cleaners
> began to work and lru_min_dirty when cleaners stop to work. By exampl=
e,
If
> we have a have a configuration of 51,50 (max_dirty and min_dirty). Th=
e
> cleaners not began to work until the 51% of the buffers are dirty. In=
your
> second example if we have 2,1, the cleaners began to work when the 2%=
of
the
> buffers are dirty. Is clear than with a configuration of 2,1 we go to=
have
> minus dirty pages when the checkpoint process start and the duration =
of
> checkpoints will be smaller.
>
> This sound fine, but you also must to have in consideration than flus=
h
dirty
> page is more efficient in checkpoint process than will cleaners
(checkpoint
> don=B4t need to worry about locks).
>
> So, in sumary if you have interative operations in your database your=
must
> to have small values for lry_min_dirty and lry_max_dirty and a small
> checkpoint interval in order to not lock interatives users. But if yo=
ur
> database only have batch operation don't worry about checkpoint times=
> because don't lock anyone and put high values for this parameters.
>
> I hope this clarify you
>
> Juan Jorge Cruces Fern=E1ndez
> Accelya
>
> ----- Original Message -----
> From: "jak aka" <jakkritakeng@yahoo.com>
> To: <ids@iiug.org>
> Sent: Monday, February 16, 2009 6:27 AM
> Subject: Re: BUFFERPOOL question [14893]
>
> > Sorry , I may ask you not clear. I know what's the meaning of lru_m=
ax
and
> > lru_min and I 've also read many book but I still not clear. I don'=
t
want
> > to
> > waste your time if you feel uncomfortable to answer please do nothi=
ng.
> >
> > Anyway , my question is if I remain the gap 1 (lru_max -
lrun_min ,2-1 ,
> > 52-51) . What 's the different ?
> > What will be effect, checkpoint ,... ?
> >
> > Sorry again Art.
> >
> > ________________________________
> > From: Art Kagel <art.kagel@gmail.com>
> > To: ids@iiug.org
> > Sent: Thursday, February 12, 2009 6:35:43 PM
> > Subject: Re: BUFFERPOOL question [14857]
> >
> > Trick question?
> >
> > The LRU flush parameters, lru_max_dirty and lru_min_dirty are
different.
> >
> > If you want more than that, look in the Administrator's Reference
manual
> > for
> > the meaning, purpose, and setting of the lru flush parameters in th=
e
> > BUFFERPOOL section. You may also find more information in the Tunin=
g
Guide
> > or by searching the archives of this list for lru_min_dirty,
> > lru_max_dirty,
> > and lru_min/max_dirty (I sometimes refer to both together that way.=
Meet
> > us
> > half way Jak, do some research before you post a question.
> >
> > (*&^ but I'm cranky today.
> >
> > Art
> >
> > On Thu, Feb 12, 2009 at 2:31 AM, jak aka <jakkritakeng@yahoo.com>
wrote:
> >
> >> Hi all,
> >>
> >> I have a basic question but it make me confuse. What's the differe=
nt
> >> between
> >> BUFFERPOOL below ?
> >>
> >> BUFFERPOOL
> >> size=3D2K,buffers=3D1000000,lrus=3D64,lru_min_dirty=3D1,lru_max_di=
rty=3D2> >> and
> >> BUFFERPOOL
> >> size=3D2K,buffers=3D1000000,lrus=3D64,lru_min_dirty=3D51,lru_max_d=
irty=3D52> >>
> >> Best Regards,
> >> Jakkrit A.
> >>
> >>
> >>
> >>
> >
> >
>
***********************************************************************=
********
> >> Forum Note: Use "Reply" to post a response in the discussion forum=
..
> >>
> >>
> >
> > --
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
opinions
> > and
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization
> > with which I am associated either explicitly or implicitly. Neither=
do
> > those opinions reflect those of other individuals affiliated with a=
ny
> > entity
> > with which I am affiliated nor those of the entities themselves.
> >
> > --001485f91dd2b220b50462b71e68
> >
> >
> >
>
***********************************************************************=
********
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
> >
> >
>
***********************************************************************=
********
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
> >
>
> Este mensaje se dirige exclusivamente a su destinatario, contiene
informaci=F3n
> CONFIDENCIAL sometida a secreto profesional y su divulgaci=F3n est=E1=
prohibida
> por ley. Si ha recibido este mensaje por error, su copia y uso est=E1=
n
> prohibidos, rog=E1ndole que nos lo comunique inmediatamente por esta
> misma v=EDa y
> proceda a su destrucci=F3n.
> El correo electr=F3nico no garantiza la confidencialidad de los mensa=
jes ni
su
> integridad o correcta recepci=F3n. La compa=F1=EDa ACCELYA no asume
responsabilidad
> por estas circunstancias.
> Si el destinatario de este mensaje no consintiera la utilizaci=F3n de=
l
correo
> electr=F3nico y la grabaci=F3n de los mensajes, rogamos nos lo notifi=
que
No!
The lru_min/max_dirty parameters control how many buffers are flushed
BETWEEN checkpoints, not at checkpiont time. So:
case1 lru_max = 51 , lru_min=50
This means that when any lru is 51% dirty flush it clean to 50%. But at
checkpoint time ALL of the dirty buffers, which in this case will average
50.5% of all buffers (or 505,000 buffers) will have to be flushed making the
checkpoint VERY long and before 11.50 blocking all new transactions and new
portions of existing transactions from continuing.
case2 lru_max = 2 , lru_min=1
This means that when any lru is 2% dirty flush it clean to 1%. At
checkpoint time all of the dirty buffers, which in this case will average
0.5% of all buffers (or 5,000 buffers) will have to be flushed making the
checkpoint rather short.
Another upshot of leaving 50% of your buffers dirty is the possibility that
at peak load times your applications will not be able to find a clean buffer
into which to place a new page from disk or a completely new page and will
have to perform what's known as a Foreground Write. This means that the
engine thread running your query will have to stop to write out a page on
its own. This is very bad and will severely reduce server performance. If
onstat -F shows more than a VERY small number (some say only zero isacceptable) of foreground writes either your buffer cache is way too small
or the lru flush parameters are too high.
Art
On Tue, Feb 17, 2009 at 10:48 PM, jak aka <jakkritakeng@yahoo.com> wrote:
> Thank you all.
>
> I have few more question.
>
> Suppose.
> buffer = 1000000
>
> case1 lru_max = 51 , lru_min=50
> case2 lru_max = 2 , lru_min=1
>
> Dirty pages will be flushed 1% when checkpoint time (1% of 1M = 10000
> pages) .
> The checkpoint duration must be equal becase both case will flush only
> 10000
> pages. Am I correct ?
>
> - If I want to reduce the checkpoint to the mimimum duration , Can I set
> 0.0002, 0.0001 (lru_max,lru_min) (i'm not sure how many digit can I set) or
> 50.0002,50.0001 (0.0001 % of buffer to be flush when normal checkpoint) to
> make all dirty page flushed by lru-flush.
> If I do so, Any concern would you recommend ?
>
> Best regards,
> Jakkrit A.
>
> ________________________________
> From: John Miller iii <miller3@us.ibm.com>
> To: ids@iiug.org
> Sent: Wednesday, February 18, 2009 4:03:31 AM
> Subject: Re: BUFFERPOOL question [14905]
>
> While this answer is very accurate for IDS releases before version 11. =
> In
> version 11
> there is a new feature called non-blocking checkpoints. In short, it i=
> s
> generally
> beneficial to have lru min and max dirty above 50% in version 11 or hig=
> her.
> I
> would suggest reading the following for more details.
>
> http://www.ibm.com/developerworks/data/library/techarticle/dm-0703lashl=
> ey/index.html
>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 02/17/2009 09:40:46 AM:
>
> > Informix have two different forms of flush dirty page from buffer to
> disck.
> > The first one is online (not lock) and if made by the cleaners theard=
> s.
> The
> > second one if in the checkpoint proccess (locking updates).
> >
> > The form of work of the cleaners depends of the lru_min_dirty and
> > lru_max_dirty parameters. Lru_max_dirty parameter specify when the
> cleaners
> > began to work and lru_min_dirty when cleaners stop to work. By exampl=
> e,
> If
> > we have a have a configuration of 51,50 (max_dirty and min_dirty). Th=
> e
> > cleaners not began to work until the 51% of the buffers are dirty. In=
>
> your
> > second example if we have 2,1, the cleaners began to work when the 2%=
> of
> the
> > buffers are dirty. Is clear than with a configuration of 2,1 we go to=
>
> have
> > minus dirty pages when the checkpoint process start and the duration =
> of
> > checkpoints will be smaller.
> >
> > This sound fine, but you also must to have in consideration than flus=
> h
> dirty
> > page is more efficient in checkpoint process than will cleaners
> (checkpoint
> > don=B4t need to worry about locks).
> >
> > So, in sumary if you have interative operations in your database your=
>
> must
> > to have small values for lry_min_dirty and lry_max_dirty and a small
> > checkpoint interval in order to not lock interatives users. But if yo=
> ur
> > database only have batch operation don't worry about checkpoint times=
>
> > because don't lock anyone and put high values for this parameters.
> >
> > I hope this clarify you
> >
> > Juan Jorge Cruces Fern=E1ndez
> > Accelya
> >
> > ----- Original Message -----
> > From: "jak aka" <jakkritakeng@yahoo.com>
> > To: <ids@iiug.org>
> > Sent: Monday, February 16, 2009 6:27 AM
> > Subject: Re: BUFFERPOOL question [14893]
> >
> > > Sorry , I may ask you not clear. I know what's the meaning of lru_m=
> ax
> and
> > > lru_min and I 've also read many book but I still not clear. I don'=
> t
> want
> > > to
> > > waste your time if you feel uncomfortable to answer please do nothi=
> ng.
> > >
> > > Anyway , my question is if I remain the gap 1 (lru_max -
> lrun_min ,2-1 ,
> > > 52-51) . What 's the different ?
> > > What will be effect, checkpoint ,... ?
> > >
> > > Sorry again Art.
> > >
> > > ________________________________
> > > From: Art Kagel <art.kagel@gmail.com>
> > > To: ids@iiug.org
> > > Sent: Thursday, February 12, 2009 6:35:43 PM
> > > Subject: Re: BUFFERPOOL question [14857]
> > >
> > > Trick question?
> > >
> > > The LRU flush parameters, lru_max_dirty and lru_min_dirty are
> different.
> > >
> > > If you want more than that, look in the Administrator's Reference
> manual
> > > for
> > > the meaning, purpose, and setting of the lru flush parameters in th=
> e
> > > BUFFERPOOL section. You may also find more information in the Tunin=
> g
> Guide
> > > or by searching the archives of this list for lru_min_dirty,
> > > lru_max_dirty,
> > > and lru_min/max_dirty (I sometimes refer to both together that way.=
>
> Meet
> > > us
> > > half way Jak, do some research before you post a question.
> > >
> > > (*&^ but I'm cranky today.
> > >
> > > Art
> > >
> > > On Thu, Feb 12, 2009 at 2:31 AM, jak aka <jakkritakeng@yahoo.com>
> wrote:
> > >
> > >> Hi all,
> > >>
> > >> I have a basic question but it make me confuse. What's the differe=
> nt
> > >> between
> > >> BUFFERPOOL below ?
> > >>
> > >> BUFFERPOOL
> > >> size=3D2K,buffers=3D1000000,lrus=3D64,lru_min_dirty=3D1,lru_max_di=
> rty=3D2> > >> and
> > >> BUFFERPOOL
> > >> size=3D2K,buffers=3D1000000,lrus=3D64,lru_min_dirty=3D51,lru_max_d=
> irty=3D52> > >>
> > >> Best Regards,
> > >> Jakkrit A.
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> >
> ***********************************************************************=
> ********
>
> > >> Forum Note: Use @@
Thank you very much Art.
________________________________
From: Art Kagel <art.kagel@gmail.com>
To: ids@iiug.org
Sent: Wednesday, February 18, 2009 11:06:35 AM
Subject: Re: BUFFERPOOL question [14920]
No!
The lru_min/max_dirty parameters control how many buffers are flushed
BETWEEN checkpoints, not at checkpiont time. So:
case1 lru_max = 51 , lru_min=50
This means that when any lru is 51% dirty flush it clean to 50%. But at
checkpoint time ALL of the dirty buffers, which in this case will average
50.5% of all buffers (or 505,000 buffers) will have to be flushed making the
checkpoint VERY long and before 11.50 blocking all new transactions and new
portions of existing transactions from continuing.
case2 lru_max = 2 , lru_min=1
This means that when any lru is 2% dirty flush it clean to 1%. At
checkpoint time all of the dirty buffers, which in this case will average
0.5% of all buffers (or 5,000 buffers) will have to be flushed making the
checkpoint rather short.
Another upshot of leaving 50% of your buffers dirty is the possibility that
at peak load times your applications will not be able to find a clean buffer
into which to place a new page from disk or a completely new page and will
have to perform what's known as a Foreground Write. This means that the
engine thread running your query will have to stop to write out a page on
its own. This is very bad and will severely reduce server performance. If
onstat -F shows more than a VERY small number (some say only zero isacceptable) of foreground writes either your buffer cache is way too small
or the lru flush parameters are too high.
Art
On Tue, Feb 17, 2009 at 10:48 PM, jak aka <jakkritakeng@yahoo.com> wrote:
> Thank you all.
>
> I have few more question.
>
> Suppose.
> buffer = 1000000
>
> case1 lru_max = 51 , lru_min=50
> case2 lru_max = 2 , lru_min=1
>
> Dirty pages will be flushed 1% when checkpoint time (1% of 1M = 10000
> pages) .
> The checkpoint duration must be equal becase both case will flush only
> 10000
> pages. Am I correct ?
>
> - If I want to reduce the checkpoint to the mimimum duration , Can I set
> 0.0002, 0.0001 (lru_max,lru_min) (i'm not sure how many digit can I set) or
> 50.0002,50.0001 (0.0001 % of buffer to be flush when normal checkpoint) to
> make all dirty page flushed by lru-flush.
> If I do so, Any concern would you recommend ?
>
> Best regards,
> Jakkrit A.
>
> ________________________________
> From: John Miller iii <miller3@us.ibm.com>
> To: ids@iiug.org
> Sent: Wednesday, February 18, 2009 4:03:31 AM
> Subject: Re: BUFFERPOOL question [14905]
>
> While this answer is very accurate for IDS releases before version 11. =
> In
> version 11
> there is a new feature called non-blocking checkpoints. In short, it i=
> s
> generally
> beneficial to have lru min and max dirty above 50% in version 11 or hig=
> her.
> I
> would suggest reading the following for more details.
>
> http://www.ibm.com/developerworks/data/library/techarticle/dm-0703lashl=
> ey/index.html
>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 02/17/2009 09:40:46 AM:
>
> > Informix have two different forms of flush dirty page from buffer to
> disck.
> > The first one is online (not lock) and if made by the cleaners theard=
> s.
> The
> > second one if in the checkpoint proccess (locking updates).
> >
> > The form of work of the cleaners depends of the lru_min_dirty and
> > lru_max_dirty parameters. Lru_max_dirty parameter specify when the
> cleaners
> > began to work and lru_min_dirty when cleaners stop to work. By exampl=
> e,
> If
> > we have a have a configuration of 51,50 (max_dirty and min_dirty). Th=
> e
> > cleaners not began to work until the 51% of the buffers are dirty. In=
>
> your
> > second example if we have 2,1, the cleaners began to work when the 2%=
> of
> the
> > buffers are dirty. Is clear than with a configuration of 2,1 we go to=
>
> have
> > minus dirty pages when the checkpoint process start and the duration =
> of
> > checkpoints will be smaller.
> >
> > This sound fine, but you also must to have in consideration than flus=
> h
> dirty
> > page is more efficient in checkpoint process than will cleaners
> (checkpoint
> > don=B4t need to worry about locks).
> >
> > So, in sumary if you have interative operations in your database your=
>
> must
> > to have small values for lry_min_dirty and lry_max_dirty and a small
> > checkpoint interval in order to not lock interatives users. But if yo=
> ur
> > database only have batch operation don't worry about checkpoint times=
>
> > because don't lock anyone and put high values for this parameters.
> >
> > I hope this clarify you
> >
> > Juan Jorge Cruces Fern=E1ndez
> > Accelya
> >
> > ----- Original Message -----
> > From: "jak aka" <jakkritakeng@yahoo.com>
> > To: <ids@iiug.org>
> > Sent: Monday, February 16, 2009 6:27 AM
> > Subject: Re: BUFFERPOOL question [14893]
> >
> > > Sorry , I may ask you not clear. I know what's the meaning of lru_m=
> ax
> and
> > > lru_min and I 've also read many book but I still not clear. I don'=
> t
> want
> > > to
> > > waste your time if you feel uncomfortable to answer please do nothi=
> ng.
> > >
> > > Anyway , my question is if I remain the gap 1 (lru_max -
> lrun_min ,2-1 ,
> > > 52-51) . What 's the different ?
> > > What will be effect, checkpoint ,... ?
> > >
> > > Sorry again Art.
> > >
> > > ________________________________
> > > From: Art Kagel <art.kagel@gmail.com>
> > > To: ids@iiug.org
> > > Sent: Thursday, February 12, 2009 6:35:43 PM
> > > Subject: Re: BUFFERPOOL question [14857]
> > >
> > > Trick question?
> > >
> > > The LRU flush parameters, lru_max_dirty and lru_min_dirty are
> different.
> > >
> > > If you want more than that, look in the Administrator's Reference
> manual
> > > for
> > > the meaning, purpose, and setting of the lru flush parameters in th=
> e
> > > BUFFERPOOL section. You may also find more information in the Tunin=
> g
> Guide
> > > or by searching the archives of this list for lru_min_dirty,
> > > lru_max_dirty,
> > > and lru_min/max_dirty (I sometimes refer to both together that way.=
>
> Meet
> > > us
> > > half way Jak, do some research before you post a question.
> > >
> > > (*&^ but I'm cranky today.
> > >
> > > Art
> > >
> > > On Thu, Feb 12, 2009 at 2:31 AM, jak aka <jakkritakeng@yahoo.com>
> wrote:
> > >
> > >> Hi all,
> > >>
> > >> I have a basic question but it make me confuse. What's the differe=
> nt
> > >> between
> > >> BUFFERPOOL below ?
> > >>
> > >> BUFFERPOOL
> > >> size=3D2K,buffers=3D1000000,lrus=3D64,lru_min_dirty=3D1,lru_max_di=
> rty=3D2> > >> and
> > >> BUFFERPOOL
> > >> size=3D2K,buffers=3D1000000,lrus=3D64,lru_min_dirty=3D51,lru_max_d=
> irty=3D52> > >>
> > >> Best R