Journal size/frequency

We have a journalled Ingres 2.0 database (we're on extended support) which writes
journal files infrequently.

Journal block size : 16384
Initial journal size : 4
Target journal size : 32

I've reduced the target journal size to 32 in an attempt to force more frequent writes
but nothing is written during the working day. Here's an extract from the end of last week:

-rwx------ 1 ingres system 2277888 Sep 25 01:29 j0003260.jnl
-rwx------ 1 ingres system 1376768 Sep 25 03:07 j0003261.jnl
-rwx------ 1 ingres system 1262080 Sep 25 07:35 j0003262.jnl
-rwx------ 1 ingres system 197120 Sep 25 21:43 j0003263.jnl
-rwx------ 1 ingres system 2277888 Sep 26 01:26 j0003264.jnl
-rwx------ 1 ingres system 1376768 Sep 26 03:06 j0003265.jnl
-rwx------ 1 ingres system 4801024 Sep 26 07:35 j0003266.jnl
-rwx------ 1 ingres system 1327616 Sep 26 07:35 j0003267.jnl
-rwx------ 1 ingres system 410112 Sep 26 21:43 j0003268.jnl

The 21:43 relates to the nightly checkpoint, the other night times relate to nightly
jobs repopulating tables. The daytime activity appears insufficient to force journal
file writes, so I'm worried that a disk crash late in the afternoon would cause us to
lose the day's transactions.

Can anyone suggest the best course of action? Should I try reducing the journal block size?

Alan

On Sep 29, 2008, at 5:52 AM, A.C.P.Crawshaw wrote:

> We have a journalled Ingres 2.0 database (we're on extended
> support) which writes
> journal files infrequently.
> ... The daytime activity appears insufficient to force journal
> file writes, so I'm worried that a disk crash late in the afternoon
> would cause us to
> lose the day's transactions.
>

First, check the archiver_interval configuration parameter to
make sure it's 1. Also double check your consistency point
interval (cp_interval) to make sure that it's not too big.
A rough guideline for CP interval would be a percentage that
works out to 50 Mb of transaction log, and plus or minus
25 Mb or so is fine. (Avoid CP intervals that are more
than about 20% of the transaction log, you can get into
logfull issues that way unless you do extremely little
updating.)

If the configuration is OK, you might simply not be updating
much during the day. You can issue periodic DM1314 trace
points to force an archiver cycle every hour or so if you
like. You might want to precede the DM1314 with a DM1305
to also force a consistency point (or, use the cp_timer
config parameter of the recovery server); if the installation
really is that light on updating, the extra CP's won't hurt,
and will afford a little extra safety against massive disk
failure.

Karl

On 29 Záø, 11:52, "A.C.P.Crawshaw" wrote:
> We have a journalled Ingres 2.0 database (we're on extended support) which writes
> journal files infrequently.
>
> Journal block size : * *16384
> Initial journal size : * * *4
> Target journal size : * * *32
>
> I've reduced the target journal size to 32 in an attempt to force more frequent writes
> but nothing is written during the working day. Here's an extract from theend of last week:
>
> -rwx------ * 1 ingres * system * 2277888 Sep 25 01:29 j0003260.jnl
> -rwx------ * 1 ingres * system * 1376768 Sep 25 03:07 j0003261.jnl
> -rwx------ * 1 ingres * system * 1262080 Sep 25 07:35 j0003262.jnl
> -rwx------ * 1 ingres * system * *197120 Sep 25 21:43 j0003263.jnl
> -rwx------ * 1 ingres * system * 2277888 Sep 26 01:26 j0003264.jnl
> -rwx------ * 1 ingres * system * 1376768 Sep 26 03:06 j0003265.jnl
> -rwx------ * 1 ingres * system * 4801024 Sep 26 07:35 j0003266.jnl
> -rwx------ * 1 ingres * system * 1327616 Sep 26 07:35 j0003267.jnl
> -rwx------ * 1 ingres * system * *410112 Sep 26 21:43 j0003268.jnl
>
> The 21:43 relates to the nightly checkpoint, the other night times relateto nightly
> jobs repopulating tables. The daytime activity appears insufficient to force journal
> file writes, so I'm worried that a disk crash late in the afternoon wouldcause us to
> lose the day's transactions.
>
> Can anyone suggest the best course of action? Should I try reducing the journal block size?
>
> Alan

I think you should reduce archiver_interval and cp_interval in cbf.
Btw if you run ckpdb and then nightly jobs, during rollforwarddb
ingres will process all these journal files and you will wait.

Vaclav Dohnal wrote:

> I think you should reduce archiver_interval and cp_interval in cbf.
> Btw if you run ckpdb and then nightly jobs, during rollforwarddb
> ingres will process all these journal files and you will wait.

Thanks everyone for your replies, archiver_interval was set to 7, I've now reduced it to
1 and will check what effect this has before trying the other suggestions. cp_interval
is 5, i.e. 5% of 1Gb which tallies with Karl's figure.

The ckpdb is timed to catch the nightly unix file backup to tape, which kicks in at
22:00, though it may be worth our considering a second checkpoint at the end of the night.

Alan