OK? Maybe. Expected? Definitely. Here's the poop:
Chunk writes minimize system overhead and IO channel abuse by the DB server.
LRU writes minimize checkpoint wait times and maximize server throughput.
For an OLTP system 60-80% LRU writes is about the right balance for a
DSS/reporting server maby reverse that to about 60% Chunk writes and for a DW
server 100% Chunk writes. During bulk data loads you definitely want to retune
towards Chunk writes regardless of the server's day-to-day specialization.
Art S. Kagel
----- Original Message -----
From: Bill Marshall <ids@iiug.org>
At: 5/04 15:44:48
Thanks to everyone for our response.
I now understand that having all chuck writes is ok.
Nilesh Ozarkar wrote:
>> I think I should be getting LRU writes.
>>
>
> Why ? does checkpoint takes too long to complete ? or checkpoint duration
> is affecting transaction response time ?
>
> You can monitor LRU queues using 'onstat -R' and check the checkpoint
> duration in message log.
>
> On your HP-UX 32-bit system, default pagesize is 2K.
> You configured 800,000 buffers with lru_max_dirty set to 10%.
> That means when 80,000 buffers are dirty lru queue start flushing dirty
> buffers.
> Onstat -F output show that only 15,558 buffers were flushed during
> checkpoint.
> so there is no way that lru flushing will start.
>
> Regards,
> Nilesh
>
> Wednesday, May 03, 2006 8:29 AM
> To: ids@iiug.org
> cc:
> From: "Bill Marshall" <cwm@stober.com>
> Subject: How do it get writes form the lrus in V10 [6648]
>
> I have a new HP Intel 32 bit chip machine with 2 dual core processors
> and 8 Gb of memory.
>
> This machine runs one production database (Mostly OLTP) and a test
> database that is same thing but very low transactions
>
> When I run on stat -F I have 0 FG writes and 0 LRU writes and Chuunk
> writes of 15558 ( NOte this is after only approx 1 hour of production.
>
> I am running INFORMIX 10.00.UC3 workgroup edition.
>
> I am attaching the onconfig file and output of onstat -d
>
> I think I should be getting LRU writes. If you agree what parameters
> should I change to get them?
>
> ========================================
>
> #**************************************************************************
> #
> # Licensed Material - Property Of IBM
> #
> # "Restricted Materials of IBM"
> #
> # IBM Informix Dynamic Server
> # (c) Copyright IBM Corporation 1996, 2005 All rights reserved.
> #
> # Title: onconfig.std
> # Description: IBM Informix Dynamic Server Configuration Parameters
> #
> #**************************************************************************
>
> # Root Dbspace Configuration
>
> ROOTNAME rootdbs # Root dbspace name
> ROOTPATH /dev/cciss/inforaw1 # Path for device containing root dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
> ROOTSIZE 8000000 # 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 40000 # Physical log file size (Kbytes)
>
> # Logical Log Configuration
>
> LOGFILES 13 # Number of logical log files
> LOGSIZE 8000 # Logical log size (Kbytes)
> LOG_BACKUP_MODE CONT # Logical log backup mode (MANUAL, CONT)
>
> # Security
> # DBCREATE_PERMISSION:
> # By default any user can create a database. Uncomment DBCREATE_PERMISSON
> to
> # limit database creation to a specific user. Add a new DBCREATE_PERMISSION
> # line for each permitted user.
>
> #DBCREATE_PERMISSION informix
>
> # IFX_EXTEND_ROLE:
> # 0 => Disable use of EXTEND role to control who can register
> # external routines. This is the default behaviour.
> # 1 => Enable use of EXTEND role to control who can register
> # external routines.
>
> IFX_EXTEND_ROLE 0 # To control the usage of EXTEND role.
>
> # Tablespace Tablespace Configuration in Root Dbspace
>
> TBLTBLFIRST 0 # First extent size (Kbytes) (0 = default)
> TBLTBLNEXT 0 # Next extent size (Kbytes) (0 = default)
>
> # Diagnostics
>
> MSGPATH /informix10.0.uc3/online1204.log # System message log file path
> CONSOLE /dev/console # System console message path
>
> # To automatically backup logical logs, edit alarmprogram.sh and set
> # BACKUPLOGS=Y
> ALARMPROGRAM /informix10.0.uc3/etc/alarmprogram.sh # Alarm program path
> ALRM_ALL_EVENTS 0 # Triggers ALARMPROGRAM for any event occur
> TBLSPACE_STATS 1 # Maintain tblspace statistics
>
> # System Archive Tape Device
>
> TAPEDEV /dev/st0 # Tape device path
> #TAPEDEV /dev/null # Tape device path
> TAPEBLK 64 # Tape block size (Kbytes)
> TAPESIZE 47201280 # Maximum amount of data to put on tape (Kbytes)
>
> # Log Archive Tape Device
>
> #LTAPEDEV /dev/tapedev # Log tape device path
> LTAPEDEV /dev/null # Log tape device path
> LTAPEBLK 32 # Log tape block size (Kbytes)
> LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes)
>
> # Optical
>
> STAGEBLOB # Informix Dynamic Server staging area
>
> # System Configuration
>
> SERVERNUM 0 # Unique id corresponding to a OnLine instance
> DBSERVERNAME db3v124_shm # Name of default database server
> DBSERVERALIASES db3v124fast # List of alternate dbservernames
> DBSERVERALIASES db3v124link # List of alternate dbservernames
> NETTYPE soctcp,1,100,NET # Configure poll thread(s) for nettype
> NETTYPE ipcshm,1,250,CPU # Configure poll thread(s) for nettype
> DEADLOCK_TIMEOUT 600 # 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 for multi-processor
> NUMCPUVPS 3 # Number of user (cpu) vps
> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one
>
> NOAGE 0 # Process aging
> AFF_SPROC 1 # Affinity start processor
> AFF_NPROCS 3 # Affinity number of processors
>
> # Shared Memory Parameters
>
> LOCKS 200000 # Maximum number of locks
> NUMAIOVPS 16 # Number of IO vps
> PHYSBUFF 128 # Physical log buffer size (Kbytes)
> LOGBUFF 64 # Logical log buffer size (Kbytes)
> CLEANERS 20 # Number of buffer cleaner processes
> SHMBASE 0x44000000L # Shared memory base address
> #SHMVIRTSIZE 8192 # initial virtual shared memory segment size
> SHMVIRTSIZE 131072 # initial virtual shared memory segment size
> SHMADD 32768 # Size of new shared memory segments (Kbytes)
> #SHMADD 64000 # Size of new shared memory segments (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> TXTIMEOUT 300 # Transaction timeout (in sec)
> #TXTIMEOUT 60 # Transaction timeout (in sec)
> #STACKSIZE 32 # Stack size (Kbytes)
> STACKSIZE 128 # Stack size (Kbytes)
>
> # Dynamic Logging
> # DYNAMIC_LOGS:
> # 2 : server automatically add a new logical log when necessary. (ON)
> # 1 : notify DBA to add new logical logs when necessary. (ON)
> # 0 : cannot add logical log on the fly. (OFF)
> #
> # When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM,
> # because the server can add new logical logs during long transaction
> rollback.
> # However, to limit the number of new logical logs being added,
> LTXHWM/LTXEHWM
> # can be set to smaller values.
> #
> # If dynamic logging is off, LTXHWM/LTXEHWM need to be set to smaller
> values
> # to avoid long transaction rollback hanging the server due to lack of
> logical
> # log space, i.e. 50/60 or lower.
> #
> # In case of system configured with CDR, the difference between LTXHWM and
> # LTXEHWM should be atleast 30% so that we could minimize log overrun
> issue.
>
> DYNAMIC_LOGS 2
> LTXHWM 70
> LTXEHWM 80
>
> # System Page Size
> # BUFFSIZE - OnLine no longer supports this configuration parameter.
> # To determine the page size used by OnLine on your platform
> # see the last line of output from the command, 'onstat -b'.
>
> # Recovery Variables
> # OFF_RECVRY_THREADS:
> # Number of parallel worker threads during fast recovery or an offline
> restore.
> # ON_RECVRY_THREADS:
> # Number of parallel worker threads during an online restore.
>
> OFF_RECVRY_THREADS 10 # Default number of offline worker threads
> ON_RECVRY_THREADS 1 # Default number of online worker threads
>
> # Data Replication Variables
> # DRAUTO: 0 manual, 1 retain type, 2 reverse type
> DRAUTO 0 # DR automatic switchover
> DRINTERVAL 30 # DR max time between DR buffer flushes (in sec)
> DRTIMEOUT 30 # DR network timeout (in sec)
> DRLOSTFOUND /informix10.0.uc3/etc/dr.lostfound # DR lost+found file path
> DRIDXAUTO 0 # DR automatic index repair. 0=off, 1=on
>
> # CDR Variables
> CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional)
> CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
> CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes)
> CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max)
> CDR_SERIAL 0 # Serial Column Sequence
> CDR_DBSPACE # dbspace for syscdr database
> CDR_QHDR_DBSPACE # CDR queue dbspace (default same as catalog)
> CDR_QDATA_SBSPACE # List of CDR queue smart blob spaces
>
> # CDR_MAX_DYNAMIC_LOGS
> # -1 => unlimited
> # 0 => disable dynamic log addition
> # >0 => limit the no. of dynamic log additions with the specified value.
> # Max dynamic log requests that CDR can make within one server session.
>
> CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled by default
>
> # Backup/Restore variables
> BAR_ACT_LOG /usr/informix/bar_act.log # ON-Bar Log file - not in /tmp
> please
> BAR_DEBUG_LOG /usr/informix/bar_dbug.log # ON-Bar Debug Log - not in /tmp
> please
> BAR_MAX_BACKUP 0
> BAR_RETRY 1
> BAR_NB_XPORT_COUNT 20
> BAR_XFER_BUF_SIZE 31
> RESTARTABLE_RESTORE ON
> BAR_PROGRESS_FREQ 0
>
> # Informix Storage Manager variables
> ISM_DATA_POOL ISMData
> ISM_LOG_POOL ISMLogs
>
> # Read Ahead Variables
> RA_PAGES 64 # Number of pages to attempt to read ahead
> RA_THRESHOLD 32 # Number of pages left before next group
>
> # DBSPACETEMP:
> # OnLine equivalent of DBTEMP for SE. This is the list of dbspaces
> # that the OnLine SQL Engine will use to create temp tables etc.
> # If specified it must be a colon separated list of dbspaces that exist
> # when the OnLine system is brought online. If not specified, or if
> # all dbspaces specified are invalid, various ad hoc queries will create
> # temporary files in /tmp instead.
>
> DBSPACETEMP tmpchunk # Default temp dbspaces
>
> # DUMP*:
> # The following parameters control the type of diagnostics information
> which
> # is preserved when an unanticipated error condition (assertion failure)
> occurs
> # during OnLine operations.
> # For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.
>
> DUMPDIR /tmp1204 # Preserve diagnostics in this directory
> DUMPSHMEM 1 # Dump a copy of shared memory
> DUMPGCORE 0 # Dump a core image using 'gcore'
> DUMPCORE 0 # Dump a core image (Warning:this aborts OnLine)
> DUMPCNT 1 # Number of shared memory or gcore dumps for
>
> # a single user's session
>
> FILLFACTOR 90 # Fill factor for building indexes
>
> # method for OnLine to use when determining current time
> USEOSTIME 0 # 0: use internal time(fast), 1: get time from OS(slow)
>
> # Parallel Database Queries (pdq)
> MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
> DS_MAX_QUERIES 2 # Maximum number of decision support queries
> DS_TOTAL_MEMORY 256 # Decision support memory (Kbytes)
> DS_MAX_SCANS 1048576 # Maximum number of decision support scans
> DS_NONPDQ_QUERY_MEM 128 # Non PDQ query memory (Kbytes)
> DATASKIP off # List of dbspaces to skip
>
> # OPTCOMPIND
> # 0 => Nested loop joins will be preferred (where
> # possible) over sortmerge joins and hash joins.
> # 1 => If the transaction isolation mode is not
> # "repeatable read", optimizer behaves as in (2)
> # below. Otherwise it behaves as in (0) above.
> # 2 => Use costs regardless of the transaction isolation
> # mode. Nested loop joins are not necessarily
> # preferred. Optimizer bases its decision purely
> # on costs.
> OPTCOMPIND 2 # To hint the optimizer
>
> DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0)
>
> ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAIT
> OPCACHEMAX 0 # Maximum optical cache size (Kbytes)
>
> # HETERO_COMMIT (Gateway participation in distributed transactions)
> # 1 => Heterogeneous Commit is enabled
> # 0 (or any other value) => Heterogeneous Commit is disabled
> HETERO_COMMIT 0
>
> SBSPACENAME # Default smartblob space name - this is where blobs
>
> # go if no sbspace is specified when the smartblob is
>
> # created. It is also used by some datablades as
>
> # the location to put their smartblobs.
> SYSSBSPACENAME # Default smartblob space for use by the Informix
>
> # Server. This is used primarily for Informix Server
>
> # system statistics collection.
>
> BLOCKTIMEOUT 3600 # Default timeout for system block
> SYSALARMPROGRAM /informix10.0.uc3/etc/evidence.sh # System Alarm program
> path
>
> # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
> OPT_GOAL -1
>
> ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or anything but 1)
>
> #Create Index Online Shared Memory usage limitation
> ONLIDX_MAXMEM 5120 # Per pool per index (Kbytes)
>
> #Timeout for client connection request
> LISTEN_TIMEOUT 10 # Timeout (in Seconds)
>
> #Following are the deprecated configuration parameters, instead of these
> #use BUFFERPOOL configuration parameter
> #BUFFERS, LRUS, LRU_MIN_DIRTY, LRU_MAX_DIRTY
> #
> # The following are default settings for enabling Java in the database.
> # Replace all occurrences of /usr/informix with the value of $INFORMIXDIR.
>
> #VPCLASS jvp,num=1 # Number of JVPs to start with
>
> JVPJAVAHOME /informix10.0.uc3/extend/krakatoa/jre # JRE installation root
> directory
> JVPHOME /informix10.0.uc3/extend/krakatoa # Krakatoa installation directory
>
> JVPPROPFILE /informix10.0.uc3/extend/krakatoa/.jvpprops # JVP property file
> JVPLOGFILE /informix10.0.uc3/jvp.log # JVP log file.
>
> JDKVERSION 1.3 # JDK version supported by this server
>
> # The path to the JRE libraries relative to JVPJAVAHOME
> JVPJAVALIB /bin
>
> # The JRE libraries to use for the Java VM
>
> JVPJAVAVM jsig:hpi:jvm:java:net:zip:jpeg
>
> # use JVPARGS to change Java VM configuration
> #To display jni call
> #JVPARGS -verbose:jni
>
> # Classpath to use upon Java VM start-up (use _g version for debugging)
>
> #JVPCLASSPATH
>
>
/usr/informix/extend/krakatoa/krakatoa_g.jar:/usr/informix/extend/krakatoa/jdbc_
g.jar
> #JVPCLASSPATH
>
>
/usr/informix/extend/krakatoa/krakatoa.jar:/usr/informix/extend/krakatoa/jdbc.ja
r
> JVPCLASSPATH
>
>
/informix10.0.uc3/extend/krakatoa/krakatoa.jar:/informix10.0.uc3/extend/krakatoa
/jdbc.jar
>
> # The following parameters are related to the buffer pool
> #BUFFERPOOL
>
default,buffers=800000,lrus=8,lru_min_dirty=20.000000,lru_max_dirty=25.000000
> BUFFERPOOL
> default,buffers=800000,lrus=8,lru_min_dirty=0.000010,lru_max_dirty=25.000000
> #BUFFERPOOL
>
size=2K,buffers=800000,lrus=8,lru_min_dirty=20.000000,lru_max_dirty=25.000000
> BUFFERPOOL
> size=2K,buffers=800000,lrus=8,lru_min_dirty=0.000010,lru_max_dirty=10.000000
>
> --------------- onstat -d gives this result
> IBM Informix Dynamic Server Version 10.00.UC3 -- On-Line -- Up 02:39:37 --
> 1848492 Kbytes
>
> Dbspaces
> address number flags fchunk nchunks pgsize flags owner name
> acbc97e0 1 0x41001 1 1 2048 N B informix rootdbs
> ace9aac8 2 0x40001 2 1 2048 N B informix standard
> ace9ac20 3 0x40001 3 1 2048 N B informix chunke2
> ace9ad78 4 0x40001 4 1 2048 N B informix chunkg7
> ad88f018 5 0x40001 5 1 2048 N B informix chunkg5
> ad88f170 6 0x40001 6 1 2048 N B informix chunkg6
> ad88f2c8 7 0x42001 7 1 2048 N TB informix tmpchunk
> ad88f420 8 0x40001 8 1 2048 N B informix dblog
> 8 active, 2047 maximum
>
> Chunks
> address chunk/dbs offset size free bpages flags pathname
> acbc9938 1 1 0 4000000 2187202 PO-B /dev/cciss/inforaw1
> aceabdd8 2 2 4000000 4000000 2933514 PO-B /dev/cciss/inforaw1
> acbc9b08 3 3 8000000 6000000 3455272 PO-B /dev/cciss/inforaw1
> acbc9c90 4 4 14000000 6000000 3512739 PO-B /dev/cciss/inforaw1
> acbc9e18 5 5 20000000 4000000 2105679 PO-B /dev/cciss/inforaw1
> ace9a630 6 6 24000000 4000000 3082571 PO-B /dev/cciss/inforaw1
> ace9a7b8 7 7 28000000 1000000 998058 PO-B /dev/cciss/inforaw1
> ace9a940 8 8 29000000 250000 24974 PO-B /dev/cciss/inforaw1
> 8 active, 32766 maximum
>
> NOTE: The values in the "size" and "free" columns for DBspace chunks are
>
> displayed in terms of "pgsize" of the DBspace to which they belong.
>
> Expanded chunk capacity mode: always
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
