RE: IDS 7.3* - Long long long checkpoint !

Most likely you are waiting on a thread in a critical section. Run the following sql against the sysmaster database while one of these long checkpoints is happening. Then use onstat -g ses SID to see what the user is doing. select username,sid,pid,is_incrit from syssessions where is_incrit = 1; Regards, Bill Dare > -----Original Message----- > From: Laurent [SMTP:collignon.l@wanadoo.fr] > Sent: Tuesday, October 28, 2003 2:14 PM > To: informix-list@iiug.org > Subject: 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. > > 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. sending to informix-list