SEO

Andy and Paul,
You're both wrong in where to start with performance tuning. The first step
must be to learn really well about IDS and how it works. The next step is
to look at the system from ALL angles. Use every source of information you
have available. This includes OS performance information as well as IDS and
application(including query optimizer output).

I always recommend that system administrators should be monitoring
performance for some time before they can start performance tuning.
Otherwise they have little idea of whether or not the changes have been
successful, or whether the improvement they have made in one area has
affected other areas. Ever had a car engine tuned for speed and lost
consumption, or vice versa.

As to the original question I have observed that IDS, in common with many
other multi-user systems, does slow down over time, in particular as tables
increase in size. I have also observed that a factor in this can be user
familiarity with the system. I would start by looking at table sizes -
have they grown more than anticipated. If they have does that affect
decisions about indexing etc. And I would query excessive write activity as
the checkpoints are taking longer. Temp table generation could be a cause
but so could the speed of filling up log buffers. And it could even be some
effect in the OS. Without information gathered before the current situation
it is difficult to know what has changed.

The original question said that checkpoints now take 10 seconds as opposed
to 6 previously. But what about checkpoint frequency? Has that changed?
If it is now 10 seconds every 5 minutes and it used to 6 seconds every 3
minutes is that a significant piece of info?

regards

Malcolm


----- Original Message -----
From: "Andy Kent" <andykent.bristol@virgin.net>
To: <informix-list@iiug.org>
Sent: Thursday, December 11, 2003 9:22 AM
Subject: Re: Perfomance Tuning


> If you follow that logic you'll end up tuning the box to support a
> 1,000,000 row sequential scan that should have been achievable through
> an index lookup.
>
> The reality is usually more complex and more grotesque than that
> example.
>
> Probably the most authoritative and comprehensive performance tuning
> book you can buy (sadly based on A.N. Other database) devotes about
> 75% of its content to SQL, indexing and optimisation.
>
> Admittedly he's talking about checkpoints and therefore write
> activity, but the principle's still the same - all those writes could
> be caused by unnecessary temp table generation.
>
> 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
> > > > segent 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

sending to informix-list

malcolm.iiug wrote:

> Andy and Paul,
> You're both wrong in where to start with performance tuning. The first
> step
> must be to learn really well about IDS and how it works. The next step is
> to look at the system from ALL angles. Use every source of information
> you
> have available. This includes OS performance information as well as IDS
> and application(including query optimizer output).

And you're wrong too. Where you should start is with a comprehensive history
of performance metrics over time and an SLA to define what is acceptable,
along with proof that volumes and user load don't invalidate the SLA.

So there. :-p

--
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

"malcolm.iiug" <malcolm.iiug@btopenworld.com> wrote in message
news:br9mud$kmo$1@terabinaries.xmission.com...
>
> Andy and Paul,
> You're both wrong in where to start with performance tuning. The first
step
> must be to learn really well about IDS and how it works.

Bollocks. Andy's right on this one. Any DBMS will run shite SQL slowly,
for the same reasons. You don't need to know IDS, Oracle or ... er, what's
that other IBM database that is never advertised either called? .... er,
never mind - to understand why a 100 million row sequential scan will not
respond in under a second.