Re: Checkpoints up to 20 seconds:
Joe
Try decreasing LRU_MIN_DIRTY and LRU_MAX_DIRTY.
HTH
Sujit
jlk62@aol.com32Qfree (JLK62) on 06/09/99 12:51:24 PM
Please respond to jlk62@aol.com32Qfree (JLK62)
To: informix-list@iiug.org
cc: (bcc: Sujit Pal)
Subject: Checkpoints up to 20 seconds:
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 pathALARMPROGRAM /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 OnLineinstance
DBSERVERNAME onguinness # Name of default database server
DBSERVERALIASES tliguinness # List of alternate dbservernames
NETTYPE ipcshm,,, # Configure poll thread(s) for nettype#NETTYPE tlitcp # Configure poll thread(s) for nettype
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed
env.
RESIDENT 1 # Forced residency flag (Yes = 1, No = 0)
MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor
NUMCPUVPS 2 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps toone
NOAGE 0 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors
#**************************************************************************
# Shared Memory Parameters
LOCKS 2000 # Maximum number of locks
BUFFERS 40000 # Maximum number of shared buffers
NUMAIOVPS 36 # Number of IO vps
PHYSBUFF 32 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 12 # Maximum number of logical log files
CLEANERS 36 # Number of buffer cleaner processes
SHMBASE 0x82000000 # Shared
Try this:
Fragment your table (by round robin) into as many as the number of
physical disks you have. Put your indexes for that table in a separate
disk.
The placement of the logical logs is also important. Place them in
their own disk.
Increase Checkpoint interval from 5 mins. to 10 or 15 mins.
LRU_MAX_DIRTY = 2
LRU_MIN_DIRTY = 1PHYSBUFF=128
NUMCPUVPS = number of physical processors available;
Also, try using processor affinity.
Is memory maxed out? 40,000 Buffers on equals 80 MB.
Hope this helps
Zandy
In article <7jreur$ok1$1@news.xmission.com>,
Sujit.Pal@BankAmerica.com wrote:
>
>
> Joe
>
> Try decreasing LRU_MIN_DIRTY and LRU_MAX_DIRTY.
>
> HTH
> Sujit
>
> jlk62@aol.com32Qfree (JLK62) on 06/09/99 12:51:24 PM
>
> Please respond to jlk62@aol.com32Qfree (JLK62)
>
> To: informix-list@iiug.org
> cc: (bcc: Sujit Pal)
> Subject: Checkpoints up to 20 seconds:
>
> 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 rootdbspace
> 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 mirroredroot
> 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 ontape
> (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 logtape
> (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,,, # Configure poll thread(s) for nettype> #NETTYPE tlitcp # Configure poll thread(s) for
nettype
> DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistribute