NO! NO! NO!

Andy


Paul Watson <paul@oninit.com> wrote in message news:<3FD75250.9258A064@oninit.com>...
> I'd disagree, I always start at the Unix config and work back to
> the SQL. Highly tuned SQL with a poor engfine config on poorly setup
> server will always be slow.
>
>
> Andy Kent wrote:
> >
> > Always, ALWAYS start performance tuning by looking at the SQL and how
> > the optimiser is running it.
> >
> > Andy
> >
> > bnyaguwa@okzim.co.zw (Bonny) wrote in message news:<699af31f.0312100246.2b806c4@posting.google.c om>...
> > > I have IDS 7.31 running on HP-UX 11.0 and 4 Gig of memory.
> > >
> > > I have a 40 Gig database with on average 260 users and at peak about
> > > 315 users.
> > > I want to tune my database , as it has shown signs of slowing down
> > > recently.
> > > Checkpoints are taking on average 10 seconds,under normal processing
> > > there should be 6 seconds.
> > > I have posted my onconfig file,onstat -p .What parameters should i
> > > consider for tuning both from the Informix side and Unix side.
> > >
> > > Onconfig
> > >
> > > #************************************************* *************************
> > > #
> > > # INFORMIX SOFTWARE, INC.
> > > #
> > > # Title: onconfig.std
> > > # Description: Informix Dynamic Server Configuration Parameters
> > > #
> > > #************************************************* *************************
> > >
> > > # Root Dbspace Configuration
> > >
> > > ROOTNAME rootdbs # Root dbspace name
> > > ROOTPATH /dev/rootdbs # Path for device containing root
> > > dbspace
> > > ROOTOFFSET 0 # Offset of root dbspace into device
> > > (Kbytes)
> > > ROOTSIZE 1000000 # 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 250000 # Physical log file size (Kbytes)
> > >
> > > # Logical Log Configuration
> > >
> > > LOGFILES 30 # Number of logical log files
> > > LOGSIZE 5000 # Logical log size (Kbytes)
> > >
> > > # Diagnostics
> > >
> > > MSGPATH /u/informix/online.log # System message log file path
> > > CONSOLE /dev/console # System console message path
> > > ALARMPROGRAM /u/informix/etc/log_full.sh # Alarm program path
> > > SYSALARMPROGRAM /u/informix/etc/evidence.sh # System Alarm program
> > > path
> > > TBLSPACE_STATS 0
> > >
> > > # System Archive Tape Device
> > >
> > > TAPEDEV /dev/rmt/c8t3d0BEST # Tape device path
> > > TAPEBLK 6144 # Tape block size (Kbytes)
> > > TAPESIZE 80000000 # Maximum amount of data to put on
> > > tape (Kbytes)
> > >
> > > # Log Archive Tape Device
> > >
> > > LTAPEDEV /dev/rmt/1m # Log tape device pat
> > > LTAPEBLK 1024 # Log tape block size (Kbytes)
> > > LTAPESIZE 4000000 # Max amount of data to put on log
> > > tape (Kbytes)
> > >
> > > # Optical
> > >
> > > STAGEBLOB # Informix Dynamic Server/Optical
> > > staging area
> > >
> > > # System Configuration
> > >
> > > SERVERNUM 1 # Unique id corresponding to a Dynamic
> > > Server instance
> > > DBSERVERNAME ok_srvr # Name of default database server
> > > DBSERVERALIASES ok_tcp # List of alternate dbservernames
> > > NETTYPE ipcshm,1,500,CPU # Configure poll thread(s) for
> > > nettype
> > > NETTYPE soctcp,1,5,NET # 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 for
> > > multi-processor
> > > NUMCPUVPS 2 # 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 400000 # Maximum number of locks
> > > BUFFERS 200000 # Maximum number of shared buffers
> > > NUMAIOVPS 8 # Number of IO vps
> > > PHYSBUFF 128 # Physical log buffer size (Kbytes)
> > > LOGBUFF 128 # Logical log buffer size (Kbytes)
> > > LOGSMAX 100 # Maximum number of logical log files
> > > CLEANERS 127 # Number of buffer cleaner processes
> > > SHMBASE 0x0 # Shared memory base address
> > > SHMVIRTSIZE 300000 # initial virtual shared memory
> > > segment size
> > > SHMADD 40000 # Size of new shared memory segments
> > > (Kbytes)
> > > SHMTOTAL 0 # Total shared memory (Kbytes).
> > > 0=>unlimited
> > > CKPTINTVL 360 # Check point interval (in sec)
> > > LRUS 127 # Number of LRU queues
> > > LRU_MAX_DIRTY 1 # LRU percent dirty begin cleaning
> > > limit
> > > LRU_MIN_DIRTY 0 # 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 64 # Stack size (Kbytes)
> > >
> > > # System Page Size
> > > # BUFFSIZE - Dynamic Server no longer supports this configuration
> > > parameter.
> > > # To determine the page size used by Dynamic Server 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 /u/informix/etc/dr.lostfound # DR lost+found file path
> > >
> > > # CDR Variables
> > > CDR_LOGBUFFERS 2048 # size of log reading buffer pool
> > > (Kbytes)
> > > 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_LOGDELTA 30 # % of log space allowed in queue
> > > memory
> > > CDR_NUMCONNECT 400 # Expected connections per server
> > > CDR_NIFRETRY 300 # Connection retry (seconds)
> > > CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0
> > > none, 9 max)
> > >
> > > # Backup/Restore variables
> > > BAR_ACT_LOG /tmp/bar_act.log
> > > BAR_MAX_BACKUP 0
> > > BAR_RETRY 1
> > > BAR_NB_XPORT_COUNT 10
> > > BAR_XFER_BUF_SIZE 31
> > >
> > > # Informix Storage Manager variables
> > > ISM_DATA_POOL ISMData # If the data pool name is changed, be
> > > sure to
> > > # update $INFORMIXDIR/bin/onbar. Change to
> > > # ism_catalog -create_bootstrap -pool <new name>
> > > ISM_LOG_POOL ISMLogs
> > >
> > > # Read Ahead Variables
> > > RA_PAGES # Number of pages to attempt to read
> > > ahead
> > > RA_THRESHOLD # Number of pages left before next
> > > group
> > >
> > > # DBSPACETEMP:
> > > # Dynamic Server equivalent of DBTEMP for SE. This is the list of
> > > dbspaces
> > > # that the Dynamic Server SQL Engine will use to create temp tables
> > > etc.
> > > # If specified it must be a colon separated list of dbspaces that
> > > exist
> > > # when the Dynamic Server 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 tempdbs # 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 Dynamic Server operations.
> > > # For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.
> > >
> > > DUMPDIR /tmp # 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 Dynamic Server)
> > > DUMPCNT 1 # Number of shared memory or gcore
> > > dumps for
> > > # a single user's session
> > >
> > > FILLFACTOR 90 # Fill factor for building indexes
> > >
> > > # method for Dynamic Server to use when determining current time
> > > USEOSTIME 0 # 0: use internal time(fast), 1: get
> > > time from OS(slow)
> > >
> > > MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
> > > DS_MAX_QUERIES 4 # Maximum number of decision support
> > > queries
> > > DS_TOTAL_MEMORY 512 # Decision support memory (Kbytes)
> > > DS_MAX_SCANS 1048576 # Maximum number of decision support
> > > scans
> > > 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 0 # To hint the optimizer
> > >
> > > ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1
> > > = ABORT, 2 = WAIT
> > > LBU_PRESERVE 0 # Preserve last log for log backup
> > > 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
> > >
> > > # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
> > > OPT_GOAL -1
> > >
> > > # Optimizer DIRECTIVES ON (1/Default) or OFF (0)
> > > DIRECTIVES 1
> > >
> > > # Status of restartable restore
> > > RESTARTABLE_RESTORE off
> > >
> > > onstat -p
> > >
> > > Profile
> > >
> > > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> > > 43516129 30668426 1667352873 97.39 2423970 3864252 13071436 81.46
> > >
> > > isamtot open start read write rewrite delete commit
> > > rollbk
> > > 2925597306 23707928 806452050 695971992 1808636 1005737 367626
> > > 662257 64
> > >
> > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> > > 0 0 0 0 0 0 0
> > >
> > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> > > 0 0 0 82474.80 3116.64 221 442
> > >
> > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress
> > > seqscans
> > > 13577857 470 1390483266 4 0 1583 98573
> > > 2061845
> > >
> > > ixda-RA idx-RA da-RA RA-pgsused lchwaits
> > > 17761876 257123 8912823 26921277 873324