Checkpoint Question - need feedback

[Madison Pruet]

I've got a question for the user community.

Checkpoints are a real pain because there is a period of time in which
user threads are going to be blocked.

I've been playing around with an idea in which I think I could do
non-blocking checkpoints. The cost of doing this, however, would be
that there would probably need to be a significant increase in the size
of the physical log file.

I know that implementing the fractional LRU min/max has helped reduce
the impact of the checkpoint, but the fractional LRU min/max is not a
guarantee - since it is possible that the LRU page writers won't be able
to keep up with the current activity.

Also - this idea would eliminate fuzzy checkpoints, which is probably a
good thing since fuzzy checkpoints do impact the recovery time of the
server.

So - basic question --- Is the cost of the increased physical log file
(maybe 3-4 times larger in some cases) totally outweigh the benefit of
non-blocking checkpoints?

[Obnoxio The Clown]

Madison,

In my experience, I can honestly say that making the physical log file 4,
5 or even 10 times bigger is no cost at all.

--
Bye now,
Obnoxio

"... no bill is required as no value was provided."
-- Christine Normile

[Madison Pruet]

Obnoxio The Clown wrote:
> Madison Pruet said:
>> I've got a question for the user community.
>>
>>

>> So - basic question --- Is the cost of the increased physical log file
>> (maybe 3-4 times larger in some cases) totally outweigh the benefit of
>> non-blocking checkpoints?
>
> Madison,
>
> In my experience, I can honestly say that making the physical log file 4,
> 5 or even 10 times bigger is no cost at all.
>

Thanks --- I guess I should have mentioned that the amount of physical
logging that this would be using would be roughly the same as v7. Fuzzy
checkpoints reduced the amount of physical logging.

[Obnoxio The Clown]

Madison Pruet said:
> Thanks --- I guess I should have mentioned that the amount of physical
> logging that this would be using would be roughly the same as v7. Fuzzy
> checkpoints reduced the amount of physical logging.

I can't see that changing anyone's mind. Except maybe for the Oracle
haters who loved to have something they could latch onto and go on and on
and on and on and on about...

[Mark Townsend]

Obnoxio The Clown wrote:
> Madison Pruet said:
>> Thanks --- I guess I should have mentioned that the amount of physical
>> logging that this would be using would be roughly the same as v7. Fuzzy
>> checkpoints reduced the amount of physical logging.
>
> I can't see that changing anyone's mind. Except maybe for the Oracle
> haters who loved to have something they could latch onto and go on and on
> and on and on and on about...
>

Don't hate the player. Hate the game.

[Superboer]

Hello Madison,

sorry have not looked for it yet....
what is the max size for the phys log nowadays; it used to be 2 GB
eq the size of a chunk....that is i guess to small.

> So - basic question --- Is the cost of the increased physical log file
> (maybe 3-4 times larger in some cases) totally outweigh the benefit of
> non-blocking checkpoints?

Yes.

however if one is loading a truck load of data, page cleaners may not
keep
up at all. In such a case blocking checkpoint may be welcome.
or generate our own checkpoint whenever the buffer cache is 75% dirty
start a checkpoint which does big buffer writes...

i rather run a checkpoint when the buffer cache is 75 % dirty then when
the phys
log is 75% full; at least resources are used the best way and i have
some control
over it and most important, it's fast...

So do not take them away completely please.

See you

Superboer.

[Simmons, Keith]

Madison

(Particularly if the phys log is in its own dbspace) I see no problem
with increasing the size given the size and cost of modern disks, and
it is relatively easy to move and resize to physlog anyway so nothing
special would be needed for in-place upgrades.

As an aside, following a change to hardware/OS and upgrade from 7.31
to 9.4, I have reduced checkpoints from 13-20 seconds to < 1 second
even with more buffers and a higher LRUMAX/MIN figures.

Keith

-> -----Original Message-----
-> From: Madison Pruet [mailto:mpr...@comcast.net]
-> Sent: Wednesday, July 05, 2006 11:22 PM
-> To: inform...@iiug.org
-> Subject: Checkpoint Question - need feedback
->
->
-> I've got a question for the user community.
->
-> Checkpoints are a real pain because there is a period of
-> time in which
-> user threads are going to be blocked.
->
-> I've been playing around with an idea in which I think I could do
-> non-blocking checkpoints. The cost of doing this, however, would be
-> that there would probably need to be a significant increase
-> in the size
-> of the physical log file.
->
-> I know that implementing the fractional LRU min/max has
-> helped reduce
-> the impact of the checkpoint, but the fractional LRU min/max
-> is not a
-> guarantee - since it is possible that the LRU page writers
-> won't be able
-> to keep up with the current activity.
->
-> Also - this idea would eliminate fuzzy checkpoints, which is
-> probably a
-> good thing since fuzzy checkpoints do impact the recovery
-> time of the
-> server.
->
-> So - basic question --- Is the cost of the increased
-> physical log file
-> (maybe 3-4 times larger in some cases) totally outweigh the
-> benefit of
-> non-blocking checkpoints?
-> _______________________________________________
-> Informix-list mailing list
-> Inform...@iiug.org
-> http://www.iiug.org/mailman/listinfo/informix-list
->

***********************************************************************************************
This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission.

This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems.
***********************************************************************************************

[Art S. Kagel]

I'll trade cheap disk for improved server responsiveness 99 days out of 100!
Go for it Madison.

Art S. Kagel

[Eric Rowell]

>So - basic question --- Is the cost of the increased physical log file
>(maybe 3-4 times larger in some cases) totally outweigh the benefit of
>non-blocking checkpoints?

I would be willing to go this many more times larger then this if needed.

[John Carlson]

On Wed, 05 Jul 2006 17:21:30 -0500, Madison Pruet <mpr...@comcast.net>
wrote:

Sounds reasonable to me . . . . but would it be a conversion/upgrade
requirement, or a feature that would be available to use at a later
date?

JWC

[Superboer]

hello Madison,

one more thing people sometimes use onmode -B followed by onmode -c
to reduce checkpoint times....i guess you know that;
however you may want to look at that too.

Superboer

Superboer schreef:

[John Miller]

Superboer wrote:

> Hello Madison,
>
> sorry have not looked for it yet....
> what is the max size for the phys log nowadays; it used to be 2 GB
> eq the size of a chunk....that is i guess to small.
>
>
>>So - basic question --- Is the cost of the increased physical log file
>>(maybe 3-4 times larger in some cases) totally outweigh the benefit of
>>non-blocking checkpoints?
>
>
> Yes.
>
> however if one is loading a truck load of data, page cleaners may not
> keep
> up at all. In such a case blocking checkpoint may be welcome.

Why not do a flush to disk without doing the check point. You can
use onmode -B. This command uses the same disk flushing code that
the checkpoint code does without doing any user blocking.

> or generate our own checkpoint whenever the buffer cache is 75% dirty
> start a checkpoint which does big buffer writes...
>
> i rather run a checkpoint when the buffer cache is 75 % dirty then when
> the phys
> log is 75% full; at least resources are used the best way and i have
> some control
> over it and most important, it's fast...

If you are in a logging database and you have not dropped a table since
the last checkpoint and you are inserting rows at the end of the table,
no physical logging is done.

FYI,
John

[Superboer]

Thanks John,

i know but if rows are added how does one make sure it is inserted in
uninialized pages
(without rebuilding a table that is...)

i know HPL does in express mode; it adds an extent;
but simple inserts will put data where there is
room i guess.

Superboer.

John Miller schreef:

[da...@smooth1.co.uk]

John Miller wrote:
>
> Why not do a flush to disk without doing the check point. You can
> use onmode -B. This command uses the same disk flushing code that
> the checkpoint code does without doing any user blocking.
>

Because it is undocumented and unsupported?

Or did the support position on that change?

David.