Re: [Info-Ingres] Journal size/frequency

[Database Administrator]

Hi Alan,

For Ingres 2.0 I ran a script from cron each 10 min without any performance
impact.
Something like this:

sql iidbdb < set trace point dm1305 \g
\q
eof

With 2.6 I discovered the script was nolonger required. I was able to set
cp_timer = 600 (10 min)

Now (ingres2006) I'm using "alterdb dbname -next_jnl_file" to force a
journal every 10 min for the purpose of replication to another server.



Paul
-----Original Message-----
From: info-ingres-bounces@kettleriverconsulting.com
[mailto:info-ingres-bounces@kettleriverconsulting.com]On Behalf Of
A.C.P.Crawshaw
Sent: Monday, 29 September 2008 7:52 PM
To: info-ingres@kettleriverconsulting.com
Subject: [Info-Ingres] 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
_______________________________________________
Info-Ingres mailing list
Info-Ingres@kettleriverconsulting.com
http://www.kettleriverconsulting.com...fo/info-ingres

[Database Administrator]

Paul White wrote:
> Hi Alan,
>
> For Ingres 2.0 I ran a script from cron each 10 min without any performance
> impact.
> Something like this:
>
> sql iidbdb < > set trace point dm1305 \g
> \q
> eof
>
> With 2.6 I discovered the script was nolonger required. I was able to set
> cp_timer = 600 (10 min)
>
> Now (ingres2006) I'm using "alterdb dbname -next_jnl_file" to force a
> journal every 10 min for the purpose of replication to another server.

I finally got round to trying this out on a test database. It behaved differerently from
what I expected. After "set trace point dm1305" in the test database I ran

update buildings_work set object_status = 'A

which wrote:

-rwx------ 1 ingres system 4358656 Nov 13 09:58 j0000027.jnl

then repeated the update command which wrote:

-rwx------ 1 ingres system 4375040 Nov 13 10:07 j0000027.jnl

and again...

-rwx------ 1 ingres system 4391424 Nov 13 10:11 j0000027.jnl

I only issued the "set trace point dm1305" once, at the beginning.
Another strange thing... I issued "set notrace point dm1305" but this had no effect,
updates continued to generate updated j0000027.jnl files
--
----------------------
Alan Crawshaw Tel: 01248 383248
Database Administrator Fax: 01248 383826
Bangor University
Adeilad Deiniol
Deiniol Road
Bangor
Gwynedd
LL57 2UX

[Database Administrator]

2008/11/13 A.C.P.Crawshaw

> Paul White wrote:
> > Hi Alan,
> >
> > For Ingres 2.0 I ran a script from cron each 10 min without any
> performance
> > impact.
> > Something like this:
> >
> > sql iidbdb < > > set trace point dm1305 \g
> > \q
> > eof
> >
> > With 2.6 I discovered the script was nolonger required. I was able to set
> > cp_timer = 600 (10 min)
> >
> > Now (ingres2006) I'm using "alterdb dbname -next_jnl_file" to force a
> > journal every 10 min for the purpose of replication to another server.
>
> I finally got round to trying this out on a test database. It behaved
> differerently from
> what I expected. After "set trace point dm1305" in the test database I ran
>
> update buildings_work set object_status = 'A
>
> which wrote:
>
> -rwx------ 1 ingres system 4358656 Nov 13 09:58 j0000027.jnl
>
> then repeated the update command which wrote:
>
> -rwx------ 1 ingres system 4375040 Nov 13 10:07 j0000027.jnl
>
> and again...
>
> -rwx------ 1 ingres system 4391424 Nov 13 10:11 j0000027.jnl
>
> I only issued the "set trace point dm1305" once, at the beginning.
> Another strange thing... I issued "set notrace point dm1305" but this had
> no effect,
> updates continued to generate updated j0000027.jnl files
> --


Trace point dm1305 is one of those trace points that takes an immediate
action (force a consistency point) rather than a change in status - so "set
notrace point dm1305" whilst valid syntax, has no real meaning, and as you
discovered, no effect.

I did once read a technical explanation of why if you force a cp with dm1305
it doesn't count towards your archiver interval, so if your intent is to
force a new journal then it won't do that. (I think this is still true but
I'm open to being corrected)

To force a new journal you can use alterdb as Paul said. Also
archiver_refresh, which is under configure logging system in CBF, in effect
sets the maximum size of a journal file, as expressed as a percentage of the
logfile.

HTH
--
Paul Mason

[Database Administrator]

2008/11/13 Paul Mason

>
>
> 2008/11/13 A.C.P.Crawshaw
>
> Paul White wrote:
>> > Hi Alan,
>> >
>> > For Ingres 2.0 I ran a script from cron each 10 min without any
>> performance
>> > impact.
>> > Something like this:
>> >
>> > sql iidbdb < >> > set trace point dm1305 \g
>> > \q
>> > eof
>> >
>> > With 2.6 I discovered the script was nolonger required. I was able to
>> set
>> > cp_timer = 600 (10 min)
>> >
>> > Now (ingres2006) I'm using "alterdb dbname -next_jnl_file" to force a
>> > journal every 10 min for the purpose of replication to another server.
>>
>> I finally got round to trying this out on a test database. It behaved
>> differerently from
>> what I expected. After "set trace point dm1305" in the test database I ran
>>
>> update buildings_work set object_status = 'A
>>
>> which wrote:
>>
>> -rwx------ 1 ingres system 4358656 Nov 13 09:58 j0000027.jnl
>>
>> then repeated the update command which wrote:
>>
>> -rwx------ 1 ingres system 4375040 Nov 13 10:07 j0000027.jnl
>>
>> and again...
>>
>> -rwx------ 1 ingres system 4391424 Nov 13 10:11 j0000027.jnl
>>
>> I only issued the "set trace point dm1305" once, at the beginning.
>> Another strange thing... I issued "set notrace point dm1305" but this had
>> no effect,
>> updates continued to generate updated j0000027.jnl files
>> --
>
>
> Trace point dm1305 is one of those trace points that takes an immediate
> action (force a consistency point) rather than a change in status - so "set
> notrace point dm1305" whilst valid syntax, has no real meaning, and as you
> discovered, no effect.
>
> I did once read a technical explanation of why if you force a cp with
> dm1305 it doesn't count towards your archiver interval, so if your intent is
> to force a new journal then it won't do that. (I think this is still true
> but I'm open to being corrected)
>
> To force a new journal you can use alterdb as Paul said. Also
> archiver_refresh, which is under configure logging system in CBF, in effect
> sets the maximum size of a journal file, as expressed as a percentage of the
> logfile.
>
> HTH
>

I've just noticed that you're on 2.0 so the alterdb thing won't work for
you.

--
Paul Mason

[Database Administrator]

Paul Mason wrote:
>
>
> 2008/11/13 A.C.P.Crawshaw >
>
> Paul White wrote:
> > Hi Alan,
> >
> > For Ingres 2.0 I ran a script from cron each 10 min without any
> performance
> > impact.
> > Something like this:
> >
> > sql iidbdb < > > set trace point dm1305 \g
> > \q
> > eof
> >
> > With 2.6 I discovered the script was nolonger required. I was
> able to set
> > cp_timer = 600 (10 min)
> >
> > Now (ingres2006) I'm using "alterdb dbname -next_jnl_file" to force a
> > journal every 10 min for the purpose of replication to another
> server.
>
> I finally got round to trying this out on a test database. It
> behaved differerently from
> what I expected. After "set trace point dm1305" in the test database
> I ran
>
> update buildings_work set object_status = 'A
>
> which wrote:
>
> -rwx------ 1 ingres system 4358656 Nov 13 09:58 j0000027.jnl
>
> then repeated the update command which wrote:
>
> -rwx------ 1 ingres system 4375040 Nov 13 10:07 j0000027.jnl
>
> and again...
>
> -rwx------ 1 ingres system 4391424 Nov 13 10:11 j0000027.jnl
>
> I only issued the "set trace point dm1305" once, at the beginning.
> Another strange thing... I issued "set notrace point dm1305" but
> this had no effect,
> updates continued to generate updated j0000027.jnl files
> --
>
>
> Trace point dm1305 is one of those trace points that takes an immediate
> action (force a consistency point) rather than a change in status - so
> "set notrace point dm1305" whilst valid syntax, has no real meaning, and
> as you discovered, no effect.
>
> I did once read a technical explanation of why if you force a cp with
> dm1305 it doesn't count towards your archiver interval, so if your
> intent is to force a new journal then it won't do that. (I think this is
> still true but I'm open to being corrected)
>
> To force a new journal you can use alterdb as Paul said. Also
> archiver_refresh, which is under configure logging system in CBF, in
> effect sets the maximum size of a journal file, as expressed as a
> percentage of the logfile.
>
> HTH
> --
> Paul Mason

Thanks for the fast response Paul. From both yours and Paul White's responses I expected
the "set trace point dm1305" to have a one-off effect. Can you explain why additional
updates result in the jnl files being overwritten by bigger versions, i.e. it keeps up
to date? I tried a rollforward and all the updates were applied correctly but I'd feel
more comfortable using this on the production database if I understood what was
happening a little more clearly.

Alan

[Database Administrator]

2008/11/13 A.C.P.Crawshaw

>
> Thanks for the fast response Paul. From both yours and Paul White's
> responses I expected
> the "set trace point dm1305" to have a one-off effect. Can you explain why
> additional
> updates result in the jnl files being overwritten by bigger versions, i.e.
> it keeps up
> to date?


Were you expecting it not to grow? Why?

They're not being overwritten it's appending to the current journal file.
Which is what you want. At some point it will place a new journal file.
Archiver_refresh can be used to set a max size for the journals.

--
Paul Mason

[Database Administrator]

Hi.

I'm facing a strange problem here after I migrated form Ingres II 2.6 to Ingres 2006r2 since last week. I share it to you know.
It seems Ingres is considering timezone twice in some queries. I find this problem when using date columns in NOT EXISTS or OUTER JOIN clauses and II_TIMEZONE_NAME=GMT-2.
I did a testcase to send to Ingres service desk and I put it below for who wants to do a test.

Note: I opened an issue with Ingres Corp and a bug was confirmed but it is not solved yet. I have a workaround, but only use when it is identified on our system.
This bug can be having a high impact on our business, maily when considering dates to calculate duties/taxes over late obligations of our students.

Environment:
Sun Fire x4100 2 AMD Dual CPU
Red Hat ES 5
Ingres II 9.1.1 (a64.lnx/103)NPTL + p12941 + p13083
II_TIMEZONE_NAME=GMT-2
II_DATE_FORMAT=MULTINATIONAL4

The testcase - SQL script:

/* Create two tables with a date column in each */
create table td1 (td1_date date);
create table td2 (td2_date date);

/* Insert data into tables */
insert into td1 values ('06/11/2008');
insert into td2 values ('01/11/2008');

/* Select data with a simple select - the row returned is ok */
select td1_date from td1;

/* Select data with not exists clause - the row is NOT ok 2 hours minus */
/* When considering the day, it is the day before */
select td1_date from td1
where not exists (
select 1 from td2
where td1.td1_date = td2.td2_date);

/* The same wrong result in a query with outer join */
select *
from td1 left join td2
on td1.td1_date = td2.td2_date;

/* As an workaround, doing a cast in the where clause the problem does not happen */
select td1_date from td1
where not exists (
select 1 from td2
where date(td1.td1_date) = td2.td2_date);

Regards,

Leandro Fava
Setor de Informática - UNISC
+55 51 3717 7636

[Database Administrator]

> 2008/11/13 A.C.P.Crawshaw >
>
>
> Thanks for the fast response Paul. From both yours and Paul White's
> responses I expected
> the "set trace point dm1305" to have a one-off effect. Can you
> explain why additional
> updates result in the jnl files being overwritten by bigger
> versions, i.e. it keeps up
> to date?
>
>
> Were you expecting it not to grow? Why?
>
> They're not being overwritten it's appending to the current journal
> file. Which is what you want. At some point it will place a new journal
> file. Archiver_refresh can be used to set a max size for the journals.
>

From Paul White's comment:
~~~~
For Ingres 2.0 I ran a script from cron each 10 min without any performance
impact.
Something like this:

sql iidbdb < set trace point dm1305 \g
\q
eof
~~~~
So I was expecting to have to reissue the "set trace point dm1305" at intervals to force
Ingres to write a new journal file. But it appears that issuing it once is sufficient.

Alan
--
----------------------
Alan Crawshaw Tel: 01248 383248
Database Administrator Fax: 01248 383826
Bangor University
Adeilad Deiniol
Deiniol Road
Bangor
Gwynedd
LL57 2UX

[Database Administrator]

On Nov 13, 2008, at 8:34 AM, A.C.P.Crawshaw wrote:
>
> So I was expecting to have to reissue the "set trace point dm1305"
> at intervals to force
> Ingres to write a new journal file. But it appears that issuing it
> once is sufficient.

It would appear that you're getting CP's from somewhere. I would
suspect
a cp_timer setting (for the recovery server). As far as I know,
dm1305 isn't
sticky in any sense ... it just signals CPNEEDED to the logging system.

The archiver runs when a CP takes place, assuming that
archiver_interval is 1.
I think there used to be a bug of some kind that failed to run the
archiver
cycle when a CP was forced with DM1305, but I'm sure that was quite a
while ago.

You can force an archiver cycle with DM1314.

Karl

[Database Administrator]

Karl & Betty Schendel wrote:
>
> It would appear that you're getting CP's from somewhere. I would suspect
> a cp_timer setting (for the recovery server). As far as I know, dm1305
> isn't
> sticky in any sense ... it just signals CPNEEDED to the logging system.
>
> The archiver runs when a CP takes place, assuming that archiver_interval
> is 1.
> I think there used to be a bug of some kind that failed to run the archiver
> cycle when a CP was forced with DM1305, but I'm sure that was quite a
> while ago.
>
> You can force an archiver cycle with DM1314.

Hmmm... cp_timer is set to zero. I'd set archiver_interval on the production db but
forgot to change it on the test - it was 7, I've now changed it to 1.

Alan