Re: Performance Problem
David Williams wrote:
>
> In article <8k7f77$c96$1@nnrp1.deja.com>, chivaviro1379@my-deja.com
> writes
> >In article <8k6t84$qhn$1@news.xmission.com>,
> > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote:
> >>
> >> From: chivaviro1379@my-deja.com
> >> >
> >> >I have the configuration below running on HP-UX B.11.00 U 9000/800.on
> >> >L100 class which is 64bit machine.
> >> >I have run update statistics but nothing significantly has improved.
HOW do you run update stats? Do you use the recommended suite of commands
for each table? Or do you just UPDATE STATISTICS; at the database level
with no options? Makes a BIG difference.
> >> >Also my backup takes long. Checkpoints are as low as 8 seconds and I
> >> >have more than 200 users working on line. What changes would you
> >> >recommend that I should do to this configuration to improve speed.
> >> >users are now on my neck.
> >>
> >> As *Low* as 8 seconds?
> >*** mean High as 8 not low, sorry
> >>
> >> >Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 1 days
> >> >06:50:31 -- 747528 Kbytes
> >> >
> >> >Configuration File: /u/informix/etc/onconfig.live
> >> ># Root Dbspace Configuration
> >> >
> >> >ROOTNAME rootdbs # Root dbspace name
> >> >ROOTPATH /dev/rootdbs # Path for device containing root> >>
> >> I don't like my informix devices in /dev, personally.
> >>
> >**** This is a link to the device not actual device
> >
> >> ># Physical Log Configuration
> >> >
> >> >PHYSDBS rootdbs # Location (dbspace) of physical log
> >>
> >> Move your logical and physical logs to their own dbspaces on separate
> >> drives.
> >>
> >> >TBLSPACE_STATS 1> >>
> >> Set this to 0.
> >>
> >> >NETTYPE ipcshm,1,500,CPU # Configure poll thread(s) for> >nettype
> >>
> >> Are your users connecting via TCP or SHM? This looks a bit high --
> >try 200
> >> rather than 500.
> >*** Connecting via SHM
> >>
> >> >NETTYPE soctcp,1,5,NET # Configure poll thread(s) for> >> And this is just silly: try 200 instead of 5.
> >>
> >> >RESIDENT 0 # Forced residency flag (Yes = 1, No> >>
> >> Try -1.
> >>
> >> >MULTIPROCESSOR 0 # 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> >>
> >> This is a mess. If you have more than one CPU, use:
> >
> >*** Have 2 CPU's
> >>
> >> MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-
> >> NUMCPUVPS 2 # Number of user (cpu) vps
> >> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu> >>
Also if you have multiple CPUs and will continue to set NUMCPUVPS to 2
then:
NETTYPE ipcshm,2,250,SHM # To load balance the 2 CPU VPS better.
> >> If you have only one CPU, use:
> >>
> >> MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-
> >> NUMCPUVPS 1 # Number of user (cpu) vps
> >> SINGLE_CPU_VP 1 # If non-zero, limit number of cpu
> >>
> >> >LOCKS 400000 # Maximum number of locks
> >> >BUFFERS 200000 # Maximum number of shared buffers> >>
> >> These can be juggled with, but seem reasonable enough. What does
> >onstat -p> >> say?
> >**** onstat -p
> >Profile
> >dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> >12900565 13082123 1485889811 99.13 1605264 2188658 7410289 78.34
> >
> >isamtot open start read write rewrite delete commit
> >rollbk
> >1741872520 12084396 99745410 1397712490 1280831 780543 146068
> >318686 332
> >
> >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 138030.05 3384.42 247 572
> >
> >bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> >2548850 1947 3129828809 28 0 3548 114697 936854
BR = ((2548850 / (13082123 + 7410289)) * 100) = 12.438%
Anything over 7% is trouble and over 10% is performance hell. Increase
LRUS and CLEANERS to maximum, with over 200 users you'll need that. How
long was this running since the stats were cleared? It would be
interesting to see the buffer turnover rate. From CKPTINTVL and ckptwaits
we can infer these stats are for 9H 51M 20S or 591 mins. With 13million
pagereads this means you are turning over the entire buffer cache about
every 9 minutes or about 7 times an hour. This is only a bit high but
the write cache shows the effect. Try increasing BUFFERS to 300000.
Art S. Kagel
> >
>
> seqscans seems high.
>
> run onstat -u and set which sessions have the most reads.
> What do the top 10 sessions with the most reads look like?
>
> Run
>
> onstat -g ses <sessionid>>
> and work out which sql's they run that take more than a few seconds.
> Run these sql's in dbaccess with
>
> SET EXPLAIN ON;>
> before the sql and set what the sqexplain.out file gives.
>
> Are indexes being used? Are these the right indexes?
>
> Go to www.iiug.org and look in the software section for utils_ak2
> Install this and use the dostats from this with
>
> dostats -e -d <datadase>
>
> to run the required update statistics.
>
> >ixda-RA idx-RA da-RA RA-pgsused lchwaits
> >3874088 51539 1174526 5096863 1422889
> >>
> >> >NUMAIOVPS 8 # Number of IO vps> >>
> >> Is KAIO on? Check your release notes for details.
> >>
> >*** KAIO is off
> >
> >> >PHYSBUFF 128 # Physical log buffer size (Kbytes)
> >> >LOGBUFF 128 # Logical log buffer size (Kbytes)> >>
> >> What does onstat -l say?
> >>
> >** onstat -l
> >Physical Logging
> >Buffer bufused bufsize numpages numwrits pages/io
> > P-2 4 64 628828 22523 27.92
> > phybegin physize phypos phyused %used
> > 10003f 125000 80201 1134 0.91
> >
> >Logical Logging
> >Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
> > L-1 1 64 5572358 221257 38607 25.2 5.7
> > Subsystem numrecs Log Space used
> > OLDRSAM 5572358 404160256
> >
>
> Reduce PHYSBUF to 32 and LOGBUFF to 8
>
> >> >CLEANERS 52 # Number of buffer cleaner processes
> >>
> CLEANERS 127>
> >> Hmmm.....we'll revisit this.
> >>
> >> >SHMVIRTSIZE 300000 # initial virtual shared memory> >>
> >> That's pretty big. How much RAM have you got? Sure you're not
> >swapping?
> >
> >**** RAM 2GIG , Not Swapping
> >>
> >> >CKPTINTVL 600 # Check point interval (in sec)> >>
> >> Try 300.
> >>
> >> >LRUS 52 # Number of LRU queues>