Re: Performance Problem

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. >> >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>> >> 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 > 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>> LRUS 127 >> Hmmm.....we'll revisit this. >> >> ># Read Ahead Variables >> >RA_PAGES # Number of pages to attempt to read>> >ahead >> >RA_THRESHOLD # Number of pages left before next>group >> These should be set, try 16+16 >> >DBSPACETEMP tempdbs # Default temp dbspaces>> >> You should really have at least 3, preferably on separate spindles. >> >> >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>> >> You don't have PDQPRIORITY set, do you? Because on an OLTP system, >you'd be >> gating like mad. > >*** PDQPRIORITY NOT SET >> then set MAXPDQPRIORITY 0 >> >________________________________________________________________________ >> Get Your Private, Free E-mail from MSN Hotmail at >http://www.hotmail.com >> >> > > >Sent via Deja.com http://www.deja.com/ >Before you buy. -- David Williams