IDS 7.3* - Long long long checkpoint !

I've noticed very long checkpoints in the online.log file. What does IDS do in a checkpoint that can take so long ? Environnement: IDS running on NT 2000 or Linux (even SCO !) with at least 128MB RAM, monoproc. IDS is usually the only big apps running on the box. "Small" database (about 150 tables for a total of 50 to 500 MB) All databases are buffered logged and programs runs in COMMITTED READ mode with LOCK MODE WAIT 3 secs. Chunks in cooked files or raw devices but never on separate disks 1 dbspace ROOT (sometimes mirrored) 1 dbspace TEMP (temporary dbspace) 1 dbspace for LOG 1 or more dbspaces for databases. Each database always located in a single dbspace.

On Tue, 28 Oct 2003 14:14:02 -0500, Laurent wrote: Crystal typically creates temp tables for its result sets and these are usually logged temp tables written to any 'normal' dbspaces in your DBSPACETEMP list or in ROOTDBS if no DBSPACETEMP or all 'temp' dbspaces are listed. This causes many dirty pages which must be flushed to disk. If you are using default settings then the LRU_MAX/MIN_DIRTY settings may be causing all flushes to hold off for checkpoint time. Try editing the SQL in the Crystal report to add an WITH NO LOG clause to any temp tables. Art S. Kagel > I've noticed very long checkpoints in the online.log file. What does IDS do in > a checkpoint that can take so long ? > > Environnement: IDS running on NT 2000 or Linux (even SCO !) with at least > 128MB RAM, monoproc. > IDS is usually the only big apps running on the box. "Small" database (about > 150 tables for a total of 50 to 500 MB) All databases are buffered logged and > programs runs in COMMITTED READ mode with LOCK MODE WAIT 3 secs. > > Chunks in cooked files or raw devices but never on separate disks 1 dbspace > ROOT (sometimes mirrored) > 1 dbspace TEMP (temporary dbspace) > 1 dbspace for LOG > 1 or more dbspaces for databases. Each database always located in a single > dbspace. > > From 5 to 30 sessions max. > Many more read than insert/update/delete > > Configuration file kept as installed for most variables except: > BUFFERS (2000 to 40000) > LOCKS (>100000)> LOGS: 10 files from 10MB to 20MB each > sometimes LRU_MIN/MAX_DIRTY > > The online.log file shows that checkpoint are usually done in 1 or 2 seconds > during normal activity, that is, mostly readings. Sometimes however, > checkpoint take much more time to complete: from 60 to more than 180 seconds > !!! > > The only big transaction takes place when a program need to prepare datas for > Crystal Reports (reporting tool). These reports never read datas directly in > the "active" tables but in 4 special tables that are filled with preformated > datas. These 4 tables represent 4 levels for grouping as needed. > > First level: TAB_CRG1( > idcr CHAR(20), > crg1 INTEGER, > st01 CHAR(80), > st02 CHAR(80), > ... > st10 CHAR(80), > in01 INTEGER, > in02 INTEGER, > ... > in10 INTEGER, > da01 DATE, > da02 DATE, > ... > da10 DATE, > dc01 DECIMAL(16,4), > dc02 DECIMAL(16,4), > ... > dc10 DECIMAL(16,4) > ) > > Second group: TAB_CRG2( > idcr CHAR(20), > crg1 INTEGER, > crg2 INTEGER, > st01 CHAR(80), > st02 CHAR(80), > ... > st10 CHAR(80), > in01 INTEGER, > ... <same schema as TAB_CRG1> > ) > > Third group: TAB_CRG3, same as TAB_CRG2 + column CRG3 INTEGER Detail: > TAB_CRLG, same as TAB_CRG3 + column CRLG INTEGER > > Each "job" is identified via the column "idcr" as concurrent reports may be > prepared simultaneously. > Datas are INSERTed in 1 to 4 tables, depending on the number of groups it > needs. > Each table has a unique index based on the first columns (IDCR, CRG1 [,CRG2 > [,CRG3, [CRLG]]]) > The biggest table has a data length of 3576 bytes so a single row needs 2 > pages on Unix boxes, 1 page on NT. > > Usually, simple reports represent less than 500 rows but the biggest ones may > grow up to 5000 or 10000 rows. > Preparing such big reports can take up to 15 minutes. Tracing the program > showed that INSERTs were "suspended" from time to time and I wonder if the > long checkpoints explain this reaction. Of course, after the report is closed, > a big DELETE-party begins to free the tables. Side effect: tables are quickly > fragmented in very small parts (8 to 256 pages) but up to 130 fragments !!! > > I've read in Informix Administrator Guide that checkpoint means 'flushing > memory buffers to disk'. OK, our apps are running on small boxes (PC pentium > 500 Mhz to 1Ghz, low disks, few memory) but for such little databases I can't > explain 2 or 3 minutes to write some MB to disk. > > > > What paramaters can I adjust to tune IDS so that checkpoints, INSERTs and > DELETEs won't take so long ? > I've tried PHYSBUF, LRUs, BUFFERS but without understanding the way they work > together. > > PS: I hardly cannot index or even drop/create the working tables, nor use the > EXTENT and NEXT keyword in creating the tables. Even UPDATE STATISTICS would > be hard to handle as my programs as to work on Oracle, Postgres ... > > Feel free to ask me for onstat or oncheck.