Re: Article about supposed "murky" future for Oracle

"Mark Townsend" <markbtownsend_at_comcast.net> wrote

> > It virtually eliminates any disk activity
> > except writing to physical once per checkpoint.
>
> Ravi - How expensive are the checkpoints ?

I would be lying if I say it is not. I was never impressed with Informix's implementation of checkpoint. I just can't understand why it has to be a blocked call when no other database blocks during checkpoint. Imagine a database block of 10-15 seconds every 10 minutes (assuming checkpoint interval is set to 10 min).

Before I found out onmode -B, the only way to keep checkpoint times to a reasonable level is to tune LRUS,CLEANERS,LRU_MAX,LRU_MIN very fine and to keep an eye on it. A waste of time for DBA.

> Why would an Informix DBA
> write a perl script to try and predict when the engine is going to do a
> checkpoint ?

I will come to it later.

> Exactly what does onmode -b do in Informix ?

It is onmode -B (upper case B)
onmode -B is a unix command line command to trigger dirty page writes to disk. It is not a blocked call and unlike LRU writes, it writes in chunks, making it order of magnitude faster than LRU writes, which is also a non blocked write, but not a chunk write.

> Is it documented ?

yes from version 9.3. It seems in the version just prior to that (what we are using), someone forgot to document it. It is not there in versions earlier than that.

> Why would an Informix DBA recommend that it be manually
> issued before a 'real' checkpoint ? Does it work all the time ? What are
> the potential recovery problems that can be caused by doing this ?

Aha. Oracle's marketing at work. Trying to create FUD I guess.

onmode -B is 100% safe command. I suspect pretty soon Informix will put that in its checkpoint itself, thus solving the checkpoint problem for good.

Now the issue comes how to trigger onmode -B. Since a scheduled checkpoint is always a predictable event, all one has to do is to monitor the log file and note the timing of last checkpoint. Add checkpoint duration time to it and you will know accurately the next checkpoint tine. What my perl script does is to trigger onmode -B just 10-15 seconds (configurable) before the real checkpoint, so that when informix issues checkpoint it will find lot less dirty pages to write to disk. Checkpoint duration can be reduced from 15 seconds to 1 second. Everyone happy. The script also takes care of any unscheduled checkpoint which can happen sometimes.

However I gave up the script after some usage. There is a better way of doing it. Set the checkpoint time to 15 minutes in config file (when we want checkpoint of 10 min). Set a cronjob to kick in every 10 minutes. The cronjob task will do onmode -B and onmode -c (command line to force a checkpoint manually) with a sleep time between them. The sleep time will just wait for the dirty pages to get reduced by onmode -B. Just 3 lines of shell script. Since cronjob kicks in every 10 minutes and forces a checkpoint, the scheduled checkpoint in the config file never happens because it it set to 15 minutes.

I have been using it for many months now and never seen a checkpoint more than 2 seconds. majority of checkpoint is now 0 or 1 second. It has become a total non issue for me now, as it shud be if Informix had done a better job of it. A sample from today's log file:-

09:17:01 Checkpoint Completed: duration was 1 seconds.

09:27:01 Checkpoint Completed: duration was 1 seconds.

09:37:01 Checkpoint Completed: duration was 1 seconds.

is it safe: 100%.

Does it have any effect in recover: no. Why shud it. All it does it to clear the dirty pages fast.

Is it elegant: Certainly not. IMO depending on an outside process to do such task is not an elegant way of doing it. But between blocking the system during production time for 10-15 seconds and resorting to such unelegant methods, I have my priorities clear. We all work to solve the problem, not to look at aesthetics.

ravi
ps: A DBA friend of mine told me that this approach is Oracleish approach where it is very common to do such things by scripts :-) Received on Thu Apr 01 2004 - 09:48:25 CST