Re: Performance Problem/Question

In article <62j7le$85o@cssun.mathcs.emory.edu>, Mark Stannard <mws@appi.ci.in.ameritech.com> writes >All: > >I am currently having a performance problem. This system is primarily >a batch system with most of the updates occuring at night. During the >day reports are pulled on the data loaded at night. If a report is requested >for a one month period of time the performance is acceptable although not >outstanding. However, if you ask for longer than one month then the >time required increases dramatically. A one month report may take a few >minutes while a six month report takes 24-36 hours. I found that the Sounds like a report is sequential scanning a large table. Check what SQL is run in the report (use SET EXPLAIN ON) and get an sqexplain.out file. Either a) indexes are missing. b) indexes need rebuilding after many updates c) Run oncheck -pe and make sure tables you not have > 8 extents. d) When you ask for a siz month report you get a query with an extra table in the from clause. Hence Online tries to do a cross product with that table (every row of that table x the results of the query). >long reports were waiting on buffers, but I don't understand this because >while these reports are waiting other one month reports will still finish >in a normal amount of time. My cache percentages are good and I can't see >anything else that indicates a lack of buffers, but perhaps I am overlooking >something or misinterpreting some of the statistics. > >Anyone with any ideas of what to try or check? > >Any help is appreciated. > > >TIA > >Mark Stannard >Ameritech > > > >Partial output of onstat -u: > Userthreads > address flags sessid user tty wait tout locks nreads nwrites > 4003a014 ---P--D 1 informix - 0 0 0 779 2962 > 4003a454 ---P--F 0 informix - 0 0 0 0 44011 > 4003a894 ---P--F 0 informix - 0 0 0 0 95697 This line :- > 400489d4 B--PR-- 3930 tpmr - 3043da9c 0 1 811773 4400 > 40049694 B--PR-- 8611 tpmr - 3043da9c 0 1 81136 544 and this line:- > 4004d214 ---PR-- 6074 tpmr - 0 0 1 773924 2480 seem silly, far too many reads. > 4004f414 B--PR-- 9638 tpmr - 30346d0c 0 1 3005 48 This line:- > 40050514 ---PR-- 9111 tpmr - 0 0 1 57224 256 may be reasonable for a lot of data. > 39 active, 128 total, 108 maximum concurrent > > > > >Output of onstat -p: > > INFORMIX-OnLine Version 7.23.UC1 -- On-Line -- Up 3 days 12:06:10 -- > 130368 Kbytes > > Profile Up 3 days and 4 BILLION reads, either an index is missing or a report needs rewriting (or possibly database schema redesigning). Get the developer and get them to fix it! > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 12556989 13981606 450358287 97.21 341510 1239267 8368077 95.92 > > isamtot open start read write rewrite delete commit rollbk > 405978035 327559 115904130 65528131 3441601 8847 72315 759 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 63520.79 10761.69 383 766 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 11249654 0 128449184 0 0 746 1983 4207 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 4050080 196202 70260 4274630 150416 > >Output of onstat -R: > > INFORMIX-OnLine Version 7.23.UC1 -- On-Line -- Up 3 days 11:43:50 -- > 130368 Kbytes > > 8 buffer LRU queue pairs > # f/m length % of pair total > 0 f 1829 98.5% 1857 > 1 m 28 1.5% > 2 f 1832 98.4% 1862 > 3 m 30 1.6% > 4 f 1845 98.9% 1866 > 5 m 21 1.1% > 6 f 1914 98.7% 1940 > 7 m 26 1.3% > 8 F 1817 98.2% 1850 > 9 m 33 1.8% > 10 f 1840 98.5% 1868 > 11 m 28 1.5% > 12 f 1836 98.8% 1859 > 13 m 23 1.2% > 14 f 1856 98.7% 1881 > 15 m 25 1.3% > 214 dirty, 14983 queued, 15000 total, 16384 hash buckets, 4096 buffer size > start clean at 10% (of pair total) dirty, or 187 buffs dirty, stop at 5% > > > >My configuration file: > > #************************************************************************** > # > # INFORMIX SOFTWARE, INC. > # > # Title: onconfig.std > # Description: INFORMIX-OnLine Configuration Parameters > # > #************************************************************************** > > # Root Dbspace Configuration > > ROOTNAME rootdbs # Root dbspace name > ROOTPATH /dev/rtchk2 # Path for device containing root dbspace > ROOTOFFSET 4 # Offset of root dbspace into device (Kbytes) > ROOTSIZE 256000 # 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 20000 # Physical log file size (Kbytes)> > # Logical Log Configuration > > LOGFILES 3 # Number of logical log files > LOGSIZE 2000 # Logical log size (Kbytes)> > # Diagnostics > > MSGPATH /usr/informix/online.log # System message log file path > CONSOLE /dev/console # System console message path > ALARMPROGRAM /usr/informix/alarm/alarm.sh # Alarm program path> > # System Archive Tape Device > > #TAPEDEV /netadmin/tpmr/online_backup/online.archive # Tape device > path > TAPEDEV /dev/rmt0.1> > #TAPEDEV /dev/null > TAPEBLK 16 # Tape block size (Kbytes) > TAPESIZE 1024000000 # Maximum amount of data to put on tape > (Kbytes)> > # Log Archive Tape Device > > LTAPEDEV /dev/null # Log tape device path > LTAPEBLK 16 # Log tape block size (Kbytes) > LTAPESIZE 10240 # 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 appi # Name of default database server > DBSERVERALIASES # List of alternate dbservernames>