Re: Checkpoints up to 20 seconds:

Let's change the point of view. Since your main task is to insert about 2 million's rows to a single table everyday, so your database should be tuned to suit the sort-of load task: (1) Don't worry about the checkpoint time You've a lot of modified buffers in the memory so at checkpoint time the cleaner are busy synchronizing between memory and disks. The fastest way to do this is chunk write, that's why: (2) Set LRU_MAX_DIRTY/LRU_MIN_DIRTY to 80/70 to defer the synchronizing effort to checkpoint time to take advange of chunk write and avoid LRU writes or Foreground writes, (make sure your PHYSFILE is big enough) that's why: (3) Maximize the BUFFERS to hold as more pages as possible in memory and then flush them all by chunk write during checkpoint time. You also can: (4) Disable all the constraits on the table and drop the index and after your insert re-enable them. How to speed the index build is a different story but the theory is the same. (5) If possible use High Performance Loader. What kinds of insertion method are you using? Insert cursors is much better than singe insert statement. (6) Turn off the log of your database if possible. (7) Fine tune the PHYSBUFF, 32k maybe too small. HTH Dong >From: jlk62@aol.com32Qfree (JLK62) >Reply-To: jlk62@aol.com32Qfree (JLK62) >To: informix-list@iiug.org >Subject: Checkpoints up to 20 seconds: >Date: 09 Jun 1999 19:51:24 GMT > >I"m hoping someone might be able to give me some pointers why my >checkpoints >are taking so longer. I'm trying to get my company to pay for the >Performance >Tuning Class, but unfortunately the higher ups want to start moving to >Oracle. >I'm running SCO 3.2.5.0.4 and IDS 7.22.UC3. Checkpoints take up to 20+ >seconds: > >Message Log File: /usr/informix/online.log >14:02:40 Checkpoint Completed: duration was 16 seconds. >14:07:59 Checkpoint Completed: duration was 16 seconds. >14:13:17 Checkpoint Completed: duration was 16 seconds. >14:18:38 Checkpoint Completed: duration was 18 seconds. >14:23:59 Checkpoint Completed: duration was 17 seconds. >14:29:19 Checkpoint Completed: duration was 17 seconds. >14:34:39 Checkpoint Completed: duration was 18 seconds. >14:40:00 Checkpoint Completed: duration was 17 seconds. >14:45:20 Checkpoint Completed: duration was 17 seconds. >14:50:40 Checkpoint Completed: duration was 17 seconds. >14:56:00 Checkpoint Completed: duration was 17 seconds. >15:01:21 Checkpoint Completed: duration was 18 seconds. >15:06:39 Checkpoint Completed: duration was 15 seconds. >15:11:59 Checkpoint Completed: duration was 16 seconds. >15:17:17 Checkpoint Completed: duration was 15 seconds. >15:22:35 Checkpoint Completed: duration was 15 seconds. >15:27:54 Checkpoint Completed: duration was 16 seconds. >15:33:12 Checkpoint Completed: duration was 14 seconds. >15:38:28 Checkpoint Completed: duration was 14 seconds. >15:43:45 Checkpoint Completed: duration was 13 seconds.> >I'm inserting into a single table which has roughtly 300 bytes per row, and >3 >composite indexes of 3 columns each. I'm inserting about 1.5-2 million >transactions a day average, and can go up to 4 million transactions. Each >day >is inserted into a new table. > >onstat -p shows (I zero the statistics out every night):> >INFORMIX-OnLine Version 7.22.UC3 -- On-Line -- Up 8 days 15:45:13 -- >121240 >Kbytes > >Profile >dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached >6307783 6496311 37524242 83.19 1559428 2838075 6189176 74.80 > >isamtot open start read write rewrite delete commit >rollbk >20634020 15633 41183 7955238 2411324 36 98895 14768 0 > >ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes >0 0 0 6559.14 2465.00 170 340 > >bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans >200 0 5535304 0 0 310 64106 887 > >ixda-RA idx-RA da-RA RA-pgsused lchwaits >0 0 0 0 30967 > > >and My onconfig looks like: > >#************************************************************************** ># ># INFORMIX SOFTWARE, INC. ># ># Title: onconfig.std ># Description: INFORMIX-OnLine Configuration Parameters ># >#************************************************************************** ># Set NUMAIOVP from 10 to 14 set 1999-05-21 for next restart. ># Restarted 1999-06-01 >#************************************************************************** ># Root Dbspace Configuration >ROOTNAME rootdbs # Root dbspace name >ROOTPATH /dev/rc04d0 #Path for device containing root dbspace >ROOTOFFSET 0 # Offset of root dbspace into device >(Kbytes) >ROOTSIZE 1800000 # 1.8 gig Size of root dbspace (Kbytes)> >#************************************************************************** ># Disk Mirroring Configuration Parameters >MIRROR 0 # Mirroring flag (Yes = 1, No = 0) >MIRRORPATH # Path for device containing mirrored root >MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> >#************************************************************************** ># Physical Log Configuration >PHYSDBS rootdbs # Location (dbspace) of physical log >PHYSFILE 300000 # Physical log file size (Kbytes)> >#************************************************************************** ># Logical Log Configuration >LOGFILES 6 # Number of logical log files >LOGSIZE 50000 # Logical log size (Kbytes)> >#************************************************************************** ># Diagnostics >MSGPATH /usr/informix/online.log # System message log file path >CONSOLE /dev/console # System console message path>ALARMPROGRAM /usr/informix/log_full.sh # Alarm program path > >#************************************************************************** ># System Archive Tape Device >TAPEDEV /dev/null # Tape device path >TAPEBLK 16 # Tape block size (Kbytes) >TAPESIZE 10240 # Maximum amount of data to put on tape >(Kbytes)> >#************************************************************************** ># Log Archive Tape Device >LTAPEDEV /dev/null # Log tape device path >LTAPEBLK 16 # Log tape block size (Kbytes) >LTAPESIZE 10240 # Max amount of data to put on log tape >(Kbytes)> >#************************************************************************** ># Optical >STAGEBLOB # INFORMIX-OnLine/Optical staging area > >#************************************************************************** ># System Configuration >SERVERNUM 0 # Unique id corresponding to a OnLine>instance >DBSERVERNAME onguinness # Name of default database server >DBSERVERALIASES tliguinness # List of alternate dbservernames >NETTYPE ipcshm