Consistency Points

From: Roy Hann 
Subject: Re: Consistency Points.

>Consistency points are configured for specific %s of the logfile - are they
>triggered as the logfile fills?


Yup.

>I'm thinking of the following scenario - a number of transactions are 
>running, one of which is a long-running one, they are filling the log-buffers
>and either fill the log-buffer or a timer write occurs. As these buffers fill 
>they are "forced" to the logfile and gradually it fills.
>
>Assuming we do not reach the mlimit would the passing of the consistency
>points be the only trigger for the fast-commit thread (or wb-threads if 
>they exist) to wake up and begin writing dirty pages for those transactions 
>that had commited to the database.

No.  Your write-behind start and end limits control when the write-behind
threads run.   (There seems to be a diversity of opinion of the purpose and
behaviour of the write-behind threads, so I won't be surprised if someone
argues with what I am about to say here.)  In general, when you modify a
page in cache, the odds are very good that you will modify it again very
soon--take for example inserts into an associated data page in a btree.  If
you were to write the page to disk every time it was modified you would soon
thrash the disk subsystem because writing to disk is soooooooo slow.  Ingres
therefore tries to retain recently modified pages in the cache for as long
as is reasonable, hoping to capture multiple updates with a single write to
disk.  Opposing this is the desire to keep the duration of consistency
points to a minimum, because in all but the most recent versions of Ingres,
the server stalls during a consistency point, and in OpenIngres there is
only a single consistency point thread to write the modified pages (in 6.4
the write-behind threads help out too).  You therefore don't want to keep
too many modified pages in the cache.  You need to balance these two
requirements, which you do by setting your wb start and end limits.  The
idea is that wb start specifies your preferred maximum number of modified
pages--the point beyond which the benefit of capturing multiple updates with
a single write is offset by the "hiccup" of a prolonged consistency point.
Ideally the number of modifed pages should oscillate between the two write
behind limits.  I will not go  into what mlimit and flimit are for--this is
already too long.

Now if you buy my explanation above, then you may conclude that it is not
very smart to allow the wbstart and wbend limits to scale up as the cache
size is increased--which is the default.  I say you'd be right; these limits
should be pegged at relatively low values determined by reference to the
bandwidth of the disk subsystem and the desired maximum duration of a
consistency point.  But I ramble.

One other point (and think this what you were asking about) is that it makes
no difference whether an update is part of a committed transaction or not.
If you modify sufficiently many pages they WILL get posted to disk,
regardless.  If the transaction subsequently rolls back, then the
modifications must be reversed on disk too.   Also, a commit does NOT
automatically trigger a write to disk unless you have fast commit turned
off.

>By the same token the long-running transaction would not be flushed from
>cache because part of it would have been flushed to the logfile and part 
>of it would still be processing in cache, this would mean that the logfile 
>% would continue to rise because there is still the possibility that the 
>transaction could be aborted and therefore the after-images
>should not be written to the database.

Almost right, but not exactly for the reasons you suggest.  The proportion
of the log file in use will rise because in the event of a rollback or a
crash, you need the before-images to reverse any updates that may have been
posted to disk.  You also need the after-images in order to post committed
transactions after a crash.

Roy Hann
Rational Commerce Ltd.
"Ingres development, tuning, and operations experts"
Ingres Q & A
Back to William's Home Page
Email William