Can you shoot me the script ... Thanks ....
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From:
"Art Kagel" <art.kagel@gmail.com>
To:
ids@iiug.org
Date:
11/12/2009 08:31 AM
Subject:
Re: Application slow and data not saved [18074]
Sent by:
ids-bounces@iiug.org
Someone else did a while ago, it's in the Repository as ratios.ksh, but it
hasn't been maintained for a while and may not work with newer servers. I
have an updated script if anyone wants it.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions
and
do not reflect on my employer, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any
entity
with which I am affiliated nor those of the entities themselves.
On Thu, Nov 12, 2009 at 7:25 AM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> Art,
>
> have you scripted the calculation of these values in any of your
> utilities? Didn't want to recreate the wheel ...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From:
> "Art Kagel" <art.kagel@gmail.com>
> To:
> ids@iiug.org
> Date:
> 11/11/2009 10:34 PM
> Subject:
> Re: Application slow and data not saved [18065]
> Sent by:
> ids-bounces@iiug.org
>
> OK, there are three metrics that give one a quick look at the health of
a
> server and suggest quick fixes. They are all calculated from the data in
> the onstat -p output which you have fortunately supplied:
>
> Bufwaits Ratio (BR): (bufwaits / (bufwrites + pagreads)) * 100.00 =
144.94
>
> Buffer Turnover Rate (BTR): ((bufwrites + pagreads) / BUFFERS) / (hours
> since stats) = 13.21
> ReadAhead Utilization Ratio: (RA-pgsused / (ixda-RA + idx-RA + da-RA)) *
> 100.0 = 99.98
>
> OK, interpretation:
>
> BR indicates the percent of data accesses that are experiencing waits
> while
> attempting to access data pages in the cache. BR should be less than 7.0
> ideally. Values between 7 and 10 indicate a server that's is
occassionally
>
> slow and some users may notice. If BR is above 10.00, and you have a BR
of
>
> 144.9, and likely every process accessing the database is running
slowly.
> High BR can indicate that there are not enough buffers but more often it
> indicates that there is contention for access to the LRU queues that
> manage
> the buffer cache and the number of LRUs have to be increased.
>
> BTR indicates the number of times in an hour that the contents of the
> entire
> buffer cache is being replaced by other data pages.BTR should be in
single
>
> digits. Double digit values are indicative of excessive physical IO.
High
> BTR means you are churning the cache and need to increase BUFFERS. Your
> current BTR value, 13.2, means you are turning over the entire buffer
> cache
> every 4.5 minutes (or churning a subset of the cache a lot more
> frequently).
>
> RAU indicates how effectively your IDS instance is using it read-ahead
> pages. Excessive read-ahead can churn the buffer cache unnecessarily.
> Ideally RAU should be as close to 100.0 as possible. Your value of 99.98
> is
> about as good as it gets.
>
> So, what to do? Increase BUFFERS by doubling it from 8000 to at least
> 16000, though if you have the memory to spare I'd go higher, perhaps to
> 25000 or even 100000. I suggest this because your cache ratios are low.
> The read cache hit rate of 78% is abysmal. For an OLTP system you should
> be
> getting a cache hit rate well overf 90%. Your write cahce hit rate of
> 89.6%
> is just within the expected range, so it can improve also, though it's
not
>
> bad.
>
> You currently have LRUS set to 8, I would increase this to the maximum
> value
> of 128. There is little cost to doing this and it will make a HUGE
> difference in performance.
>
> As to the ONCONFIG, I would change the following:
>
> RESIDENT -1 # makes all of the engine's shared memory resident so it
can't
>
> swap out
> AGING 1 # Prevents Solaris from degrading the oninit processes
> priority over time
> CLEANERS 128 # Keep set to the same as LRUS for best performance
> LRUS 128
> BUFFERS 25000
> SINGLE_CPU_VP 1 # Your CPU is too slow for more than one CPU VP, so
enable
>
> this
>
> optimization inside the engine so it can
> reduce internal locking.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity
> with which I am affiliated nor those of the entities themselves.
>
> On Tue, Nov 10, 2009 at 8:28 PM, MUMIN MARJUKI <mumin@akapost.com>
wrote:
>
> > Hi,
> >
> > I have IDS v7.30.UC7 running on Solaris 2.6 with 1024Mb RAM and 1 CPU
> > 400Mhz .
> > There is 2 application db at this IDS. The problem is application will
> > running
> > slow and sometime data cannot be saved when more user used this
> > application.
> > This application already running about 8 years and now it become
slow..
> >
> > this is onstat -p output :
> >
> > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 2 days
> 00:14:22
> > --
> > 50688 Kbytes
> >
> > Profile
> > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> > 30945890 4485340 140820758 78.02 63142 251189 609906 89.65
> >
> > isamtot open start read write rewrite delete commit rollbk
> > 4122988 338018 649682 1248899 81844 10734 911 4852 918
> >
> > 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 39755.92 1429.96 529 1158
> >
> > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> > 7385086 711 3337555672 188 0 57 2095 42989
> >
> > ixda-RA idx-RA da-RA RA-pgsused lchwaits
> > 145303 1137 29458137 29599384 0
> >
> > and this is my onconfig file :
> >
> > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 2 days
> 00:15:09
> > --
> > 50688 Kbytes
> >
> > Configuration File: /home1/informix/etc/onconfig.spplbp
> >
>
#**************************************************************************
>
> > #
> > # INFORMIX SOFTWARE, INC.
> > #
> > # Title: onconfig.std
> > # Description: INFORMIX-OnLine Configuration Parameters
> > #
> >
>
#**************************************************************************
>
> >
> > # Root Dbspace Configuration
> >
> > ROOTNAME rootdbs # Root dbspace name
> > ROOTPATH /home1/dataspace/rootchunk
> >
> > # Path for device containing root dbspace
> > ROOTOFFSET 1024 # Offset of root dbspace into device (Kbytes)
> > ROOTSIZE 2000000 # 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 10000 # Physical log file size (Kbytes)
> >
> > # Logical Log Configuration
> >
> > LOGFILES 20 # Number of logical log files
> > LOGSIZE 20000 # Logical log size (Kbytes)
> >
> > # Diagnostics
> >
> > MSGPATH /home1/informix/online.log # System message log file path
> > CONSOLE /dev/console # System console message path
> > ALARMPROGRAM /home1/informix/etc/no_log.sh # Alarm program path
> >
> > # System Archive Tape Device
> >
> > TAPEDEV /dev/rst4 # Tape device path
> > TAPEBLK 16 # Tape block size (Kbytes)
> > TAPESIZE 12000000 # Maximum amount of data to put on tape (Kbytes)
> > #TAPESIZE 2097152 # Maximum amount of data to put on tape (Kbytes)
> >
> > # Log Archive Tape Device
> >
> > LTAPEDEV /dev/rst5 # Log tape device path
> > LTAPEBLK 16 # Log tape block size (Kbytes)
> > LTAPESIZE 12000000 # Max amount of data to put on log tape (Kbytes)
> >
> > # Optical
> >
> > STAGEBLOB # INFORMIX-OnLine/Optical staging area
> >
> > # System Configuration
> >
> > SERVERNUM 1 # Unique id corresponding to a OnLine instance
> > DBSERVERNAME online_spplbp # Name of default database server
> > DBSERVERALIASES # List of alternate dbservernames
> > NETTYPE tlitcp,1,200,CPU # Override sqlhosts nettype parameters
> > #NETTYPE ipcshm,1,200,CPU # Override sqlhosts nettype parameters
> > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.
> > RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)
> >
> > MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor
> > NUMCPUVPS 1 # 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 0 # Affinity start processor
> > AFF_NPROCS 0 # Affinity number of processors
> >
> > # Shared Memory Parameters
> >
> > LOCKS 10000 # Maximum number of locks
> > BUFFERS 8000 # Maximum number of shared buffers
> > NUMAIOVPS 4 # Number of IO vps
> > PHYSBUFF 128 # Physical log buffer size (Kbytes)
> > LOGBUFF 64 # Logical log buffer size (Kbytes)
> > LOGSMAX 40 # Maximum number of logical log files
> > CLEANERS 4 # Number of buffer cleaner processes
> > SHMBASE 0xa000000 # Shared memory base address
> > SHMVIRTSIZE 8000 # initial virtual shared memory segment size
> > SHMADD 8192 # Size of new shared memory segments (Kbytes)
> > SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> > CKPTINTVL 300 # Check point interval (in sec)
> > LRUS 8 # Number of LRU queues
> > LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
> > LRU_MIN_DIRTY 50 # 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 (Kbytes)
> >
> > # 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 /home1/informix/dr.lostfound # DR lost+found file path
> >
> > # Read Ahead Variables
> > RA_PAGES # Number of pages to attempt to read ahead
> > RA_THRESHOLD # 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 tempchunk1 # 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 /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 OnLine)
> > DUMPCNT 1 # Number of shared memory or gcore dumps for
> >
> > # a single user's session
> >
> > # ADT*
> > # The following parameters control the type and level of secure
auditing
>
> > # present in the OnLine system. By default, ADTMODE is 0 and auditing
> > # is disabled
> >
> > 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)
> >
> > # OFF => 0, LOW => 1, HIGH => 100
> > MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
> > DS_MAX_QUERIES # Maximum number of decision support queries
> > DS_TOTAL_MEMORY # 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 2 # To hint the optimizer
> >
> > ONDBSPACEDOWN 0 # 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)
> > SYSALARMPROGRAM /home1/informix/etc/evidence.sh # System Alarm program
> path
> > TBLSPACE_STATS 1
> > CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes)
> > CDR_EVALTHREADS 1,1 # 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)
> > BAR_ACT_LOG /tmp/bar_act.log
> > BAR_MAX_BACKUP 4
> > BAR_RETRY 1
> > BAR_NB_XPORT_COUNT 10
> > BAR_XFER_BUF_SIZE 31
> > ISM_DATA_POOL ISMData # If the data pool name is changed, be sure to
> > ISM_LOG_POOL ISMLogs
> > HETERO_COMMIT 0
> > OPT_GOAL -1
> > DIRECTIVES 1
> > RESTARTABLE_RESTORE off
> >
> > I think bufwaits little bit high at onstat -p.
> > Does anything wrong with my onconfig file?
> >
> > thanks
> >
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0023545bd76078a7100478243502
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747609af2e14704782c89da
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
