Re: Best Tune for Online 5.02

Consider making the memory resident. I'd reduce the PHYSBUFF and LOGBUFF sizes dramatically -- to 64 kB or 128 kB each, usually. Allocate the saved memory to BUFFERS. You could verify that this is a good idea by looking at the tbstat output which reports on how those buffers are used (tbstat -F?). The pages per write is likely to be very much smaller than the buffer sizes, so you are not gaining anything by allocating vast amounts of memory to the buffers. Seriously consider lowering LRU_{MIN|MAX}_DIRTY. I'd be inclined to use values such as 10 & 20, if not lower still. This will reduce the checkpoint durations, probably. If your database is a read-mostly database (with the number of reads outweighing the number of writes by more than, say, 20:1, then consider reducing the LRU_{MIN|MAX}_DIRTY values still lower. If checkpoint durations are not currently a problem, then this is not critical. Set LTXHWM and LTXEHWM to 50 & 60 (the new defaults). That gives you better protection against long transactions. Consider placing both physical and logical logs onto other disk drives, preferably using a total of 3 disk drives. That's harder to do, and may not be warranted for a 400 MB database. At a guesstimate, you've got about 32 MB of shared memory in use. That's about right, I'd suggest, for a machine with 128 MB of main memory. You could consider increasing it, but you would have to monitor the overall system to ensure that you aren't incurring swapping (and paging) overhead. You should be monitoring the overall system performance anyway to ensure that it is not the problem, and that the troubles can therefore be fixed by tuning OnLine alone. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }From: inf@dpx20.growela.pt (Serafim Fonseca) }To: informix-list@rmy.emory.edu (Informix List) }X-Informix-List-Id: <list.13815> } }Hello, } }I have an Informix Online 5.02.UC6 installation running on a }RISC6000 with 128MB Ram, with about 30 users. }The production database is about 400MB. }I'm having performance problems. Could someone suggest me some }tunning tips. }The config is as follows: } }# Root Dbspace Configuration } }ROOTNAME rlv02 # Root dbspace name }ROOTPATH /dev/rlv02 # Path for device containing root dbspace }ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) }ROOTSIZE 122880 # Size of root dbspace (Kbytes) } }# Disk Mirroring Configuration } }MIRROR 0 # Mirroring flag (Yes = 1, No = 0) }MIRRORPATH # Path for device containing root dbspace mirror }MIRROROFFSET 0 # Offset into mirror device (Kbytes) } }# Physical Log Configuration } }PHYSDBS rlv02 # Name of dbspace that contains physical log }PHYSFILE 10240 # Physical log file size (Kbytes) } }# Logical Log Configuration } }LOGFILES 8 # Number of logical log files }LOGSIZE 5120 # Size of each logical log file (Kbytes) } }# Message Files } }MSGPATH /usr/informix/online.log # OnLine message log pathname }CONSOLE /dev/console # System console message pathname } }# Archive Tape Device } }TAPEDEV /dev/rmt0 # Archive tape device pathname }TAPEBLK 512 # Archive tape block size (Kbytes) }TAPESIZE 1227776 # Max amount of data to put on tape (Kbytes) } }# Logical Log Backup Tape Device } }LTAPEDEV /dev/null # Logical log tape device pathname }LTAPEBLK 16 # Logical log tape block size (Kbytes) }LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes) } }# Identification Parameters } }SERVERNUM 0 # Unique id associated with this OnLine instance }DBSERVERNAME dpx20 # Unique name of this OnLine instance } }# Shared Memory Parameters } }RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) }USERS 70 # Maximum number of concurrent user processes }TRANSACTIONS 70 # Maximum number of concurrent transactions }LOCKS 50000 # Maximum number of locks }BUFFERS 6144 # Maximum number of shared memory buffers }TBLSPACES 700 # Maximum number of active tblspaces }CHUNKS 8 # Maximum number of chunks }DBSPACES 8 # Maximum number of dbspaces and blobspaces }PHYSBUFF 1536 # Size of physical log buffers (Kbytes) }LOGBUFF 1024 # Size of logical log buffers (Kbytes) }LOGSMAX 12 # Maximum number of logical log files }CLEANERS 3 # Number of page-cleaner processes }SHMBASE 0x30000000 # Shared memory base address }CKPTINTVL 300 # Checkpoint interval (in seconds) }LRUS 8 # Number of LRU queues }LRU_MAX_DIRTY 60 # LRU modified begin-cleaning limit (percent) }LRU_MIN_DIRTY 50 # LRU modified end-cleaning limit (percent) }LTXHWM 80 # Long TX high-water mark (percent) }LTXEHWM 90 # Long TX exclusive high-water mark (percent) } }# Machine- and Product-Specific Parameters } }DYNSHMSZ 0 # Dynamic shared memory size (Kbytes) }GTRID_CMP_SZ 32 # Number of bytes to use in GTRID comparision }DEADLOCK_TIMEOUT 60 # Max time to wait for lock in distributed env. }TXTIMEOUT 300 # Transaction timeout for I-STAR (in seconds) }SPINCNT 0 # No. of times process tries for latch }STAGEBLOB # Reserved for INFORMIX-OnLine/Optical } }# System Page Size } }BUFFSIZE 4096 # Page size (do not change!)