 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Ingres Topics / November 2008Journal 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 > We have a journalled Ingres 2.0 database (we're on extended > support) which writes [quoted text clipped - 3 lines] > 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 > We have a journalled Ingres 2.0 database (we're on extended support) which writes > journal files infrequently. [quoted text clipped - 24 lines] > > 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. > 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 Hi Alan, For Ingres 2.0 I ran a script from cron each 10 min without any performance impact. Something like this: sql iidbdb <<eof 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/mailman/listinfo/info-ingres > Hi Alan, > [quoted text clipped - 12 lines] > 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  Signature---------------------- Alan Crawshaw Tel: 01248 383248 Database Administrator Fax: 01248 383826 Bangor University Adeilad Deiniol Deiniol Road Bangor Gwynedd LL57 2UX 2008/11/13 A.C.P.Crawshaw <iss02b@bangor.ac.uk> > > Hi Alan, > > [quoted text clipped - 37 lines] > 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 SignaturePaul Mason > 2008/11/13 A.C.P.Crawshaw <iss02b@bangor.ac.uk <mailto:iss02b@bangor.ac.uk>> > [quoted text clipped - 60 lines] > > HTH 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 2008/11/13 A.C.P.Crawshaw <iss02b@bangor.ac.uk> > Thanks for the fast response Paul. From both yours and Paul White's > responses I expected [quoted text clipped - 3 lines] > 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. SignaturePaul Mason > 2008/11/13 A.C.P.Crawshaw <iss02b@bangor.ac.uk <mailto:iss02b@bangor.ac.uk>> > [quoted text clipped - 11 lines] > 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 <<eof 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 Signature---------------------- Alan Crawshaw Tel: 01248 383248 Database Administrator Fax: 01248 383826 Bangor University Adeilad Deiniol Deiniol Road Bangor Gwynedd LL57 2UX > 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 > 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 [quoted text clipped - 8 lines] > > 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 2008/11/13 A.C.P.Crawshaw <iss02b@bangor.ac.uk> > > 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 [quoted text clipped - 13 lines] > production db but > forgot to change it on the test - it was 7, I've now changed it to 1. Were you the sole user of the test database? If so, and if you exited your session after each update then that would trigger the database being closed, which would trigger an archiver cycle. SignaturePaul Mason > 2008/11/13 A.C.P.Crawshaw <iss02b@bangor.ac.uk <mailto:iss02b@bangor.ac.uk>> > [quoted text clipped - 25 lines] > your session after each update then that would trigger the database > being closed, which would trigger an archiver cycle. Aha... yes, I was. Thanks Paul and Karl for the illuminating replies. Alan 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.