RE: Load Performance problems
Are you using HPL (High Performance Loader) to do your loads? If so, are
you running in express mode? When you use HPL in express mode, the engine
does "light appends", which bypass the buffers. IIRC, this means that the
physical log will NOT fill up, thus, checkpoints will normally only happen
from CKPTINTVL. You should manually do a checkpoint immediately before and
immediately after the load. And be aware that constraint checks and indexes
are disabled in express mode. If you can live with these restrictions,
check out HPL in express mode, and specifically light appends. You should
be able to find some info on this in the Performance Guide.
HTH,
Paul Mosser
-----Original Message-----
From: Art S. Kagel [mailto:kagel@bloomberg.net]
Sent: Thursday, July 13, 2000 6:53 AM
To: informix-list@iiug.org
Subject: Re: Load Performance problems
The parameters that control this are CKPTINTVL and PHYSFILE. You will
not be able to eliminate checkpoints altogether unless you are willing
to make PHYSFILE 125% of the number of pages that will be updated
during the load which is unreasonble. You could however, make
CKPTINTVL 3600 (1hr) and make PHYSFILE large enough to hold an hours
worth of preimages (about 1/8 the volume of the load). Also change
LRU_MAX_DIRTY & LRU_MIN_DIRTY to 5 and 0 respectively to minimize the
impact of each checkpoint.
Just a hint but if you upgrade to 7.31 which began to implement light
weight checkpoints, and even more so in 9.2x which completed the
process, the physical log is unlikely to trigger early checkpoints AND
checkpoints do NOT flush all dirty buffers as they do in 7.30 and
earlier so the impact of each checkpoint is even smaller (and the
values of LRU_MAX/MIN_DIRTY even more important!)
Art S. Kagel
benlag@aent.com wrote:
>
> I have a IDS 7.30 UC8-1 server running on HP-UX 11.0 system.
>
> The database is a data warehouse and I am trying to configure it so
> that it is optimized for a DSS environment. We process daily data loads
> at night that sometimes run 6 to 8 hours. During the load checkpoints
> are occuring at almost every five minutes (probably because the
> physical buffer is being filled). I want to configure the server so
> that NO checkpoints occur any time during the load. (I will force a
> checkpoint before the load and after the load in my script) I have
> tried changing many parameters (LRUS, PHYSBUFF, CLEANERS) to try and
> make this happen but nothing has worked. I have tried increasing
> PHYSBUFF up to 1024 to no avail...>
> How can I configure my server so that during my load checkpoints do not
> occur??
>
> Here is my onconfig file details:
> #***********************************************************************
> ***
> #
> # INFORMIX SOFTWARE, INC.
> #
> # Title: onconfig.std
> # Description: INFORMIX-OnLine Configuration Parameters
> #
> #***********************************************************************
> ***
>
> # Root Dbspace Configuration
>
> ROOTNAME inf_root # Root dbspace name
> ROOTPATH /dev/vgdata01/rinf_root> # Path for device containing root
> dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device
> (Kbytes)
> ROOTSIZE 512000 # Size of root dbspace (Kbytes)>
> # Disk Mirroring Configuration Parameters
> MIRROR 1 # Mirroring flag (Yes = 1, No = 0)
> MIRRORPATH # Path for device containing mirrored> root
> MIRROROFFSET 0 # Offset into mirrored device (Kbytes)>
> B
> # Physical Log Configuration
>
> PHYSDBS inf_root # Location (dbspace) of physical log
> PHYSFILE 50000 # Physical log file size (Kbytes)>
> # Logical Log Configuration
>
> LOGFILES 12 # Number of logical log files
> LOGSIZE 2000 # Logical log size (Kbytes)>
> # Diagnostics
>
> MSGPATH /usr/informix/online.log # System message log file path
> CONSOLE /dev/console # System console message path> ALARMPROGRAM /users/informix/log_full.sh # Alarm program path
>
> # System Archive Tape Device
> TAPEDEV /dev/rmt/3m # Tape device path
> TAPEBLK 128 # Tape block size (Kbytes)
> TAPESIZE 15000000 # 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 buffet_ipc # Name of default database server> DBSERVERALIASES buffet_tcp,buffet_tcp2 # List of alternate
> dbservernames
> NETTYPE ipcshm,4,300,CPU # Configure poll thread(s) for nettype
> NETTYPE soctcp,4,300,NET # Configure poll thread(s) for nettype
> DEADLOCK_TIMEOUT 60 # Max time to wait of lock in> distributed env.
> RESIDENT 0 # Forced residency flag (Yes = 1, No =
> 0)
>
> MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-> processor
> NUMCPUVPS 4 # Number of user (cpu) vps
> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to one
>
> NOAGE 1 # Process aging
> AFF_SPROC 0 # Affinity start processor
> AFF_NPROCS 0 # Affinity number of processors>
> # Shared Memory Parameters
>
> LOCKS 15000 # Maximum number of locks
> BUFFERS 225000 # Maximum number of shared buffers
> NUMAIOVPS 25 # Number of IO vps
> PHYSBUFF 64 # Physical log buffer size (Kbytes)
> LOGBUFF 64 # Logical log buffer size (Kbytes)> LOGSMAX 25 # Maximum number of logical log files
> CLEANERS 128 # Number of buffer cleaner processes
> SHMBASE 0x0 # Shared memory base address
> SHMVIRTSIZE 250000 # initial virtual shared memory> segment size
> SHMADD 40000 # Size of new shared memory segments
> (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
> 0=>unlimited
> CKPTINTVL 3000 # Check point interval (in sec)
> LRUS 128 # Number of LRU queues
> LRU_MAX_DIRTY 80 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 70 # LRU percent dirty end cleaning limit
> LTXHWM 50 # Long transaction high water mark> percentage
> LTXEHWM 60 # Long transaction high water mark
> (exclusive)
> TXTIMEOUT 0x12c # Transaction timeout (in sec)
> STACKSIZE 32 # Stack size (Kby