RE: Onstat -P output

Hi, The processing is OLTP. A SUN 4800 server with 4 CPUs at 750MHZ each. The overall CPU activity is about 65%. The server has a total of 1.8TB (18 disks of 73GB). Out of this 400GB is allocated to the file system and the rest is configured like this: 6 for RAID 10 and 5 for RAID 5. 2 for hot standby and 2 for parity. Memory is 8GB. Well, my worry is the response from the system has slowed down drastically and the users are beginning to complain. we have done index rebuilding, defragmentation on the tables but this has not improved. i have attached the results of onstat -d as well. --- "Konigsberg, Jay" <jaykon@tower.com> wrote: > What, precisely, is it that you are unhappy with? > Are you OLTP, DSS? > How many CPU's? > How many hard drives? > What is your overall CPU activity? > What does you I/O look like? > Etc, etc, etc ... > > > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org] On Behalf Of > Leona Ankrah > Sent: Thursday, August 18, 2005 7:52 AM > To: informix-list@iiug.org > Subject: Onstat -P output > > << File: 77641972-profilereport >> << File: > 1832273169-configuration >> > Hi, > I'm using IDS9.21 UC6 running on a SOlaris 8.0 > Operating system. > The output of the onstat -P is not acceptable and I > would appreciate it if I could be helped get a > better > results. The system is not performing well enough. > Attached is the output of the onstat -p. > I have also attached the onconfig file for the > database configuration. > please help. > > Leona > Ghana Telecom Limited > > > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam > protection around > http://mail.yahoo.com > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com Informix Dynamic Server 2000 Version 9.21.UC6 -- On-Line -- Up 1 days 10:14:29 -- 3063808 Kbytes Dbspaces address number flags fchunk nchunks flags owner name b310d7d0 1 0x1 1 5 N informix rootdbs b3844018 2 0x2001 4 4 N T informix temp1dbs b3844160 3 0x2001 5 4 N T informix temp2dbs b38442a8 4 0x1 6 33 N informix datadbs1r5 b38443f0 5 0x1 36 30 N informix datadbs2r10 b3844538 6 0x1 56 30 N informix datadbs3r5 b3844680 7 0x1 85 30 N informix datadbs4r10 b38447c8 8 0x1 106 34 N informix indexdbs b3844910 9 0x1 126 10 N informix smalldbs 9 active, 2047 maximum Chunks address chk/dbs offset size free bpages flags pathname b310d918 1 1 0 1048575 523224 PO- /dev/online10_1 b314c5f0 2 1 0 1048575 0 PO- /dev/online10_2 b314c760 3 1 0 1048575 54429 PO- /dev/online10_3 b314c8d0 4 2 0 1048575 1046964 PO- /dev/online10_4 b314ca40 5 3 0 1048575 1046204 PO- /dev/online10_5 b314cbb0 6 4 0 1048575 113068 PO- /dev/online5_1 b314cd20 7 4 0 1048575 0 PO- /dev/online5_2 b314ce90 8 4 0 1048575 0 PO- /dev/online5_3 b310da88 9 4 0 1048575 0 PO- /dev/online5_4 b310dbf8 10 4 0 1048575 524286 PO- /dev/online5_5 b310dd68 11 4 0 1048575 0 PO- /dev/online5_6 b313da30 12 4 0 1048575 0 PO- /dev/online5_7 b313dba0 13 4 0 1048575 0 PO- /dev/online5_8 b313dd10 14 4 0 1048575 0 PO- /dev/online5_9 b313de80 15 4 0 1048575 0 PO- /dev/online5_10 b382c018 16 4 0 1048575 0 PO- /dev/online5_11 b382c188 17 4 0 1048575 0 PO- /dev/online5_12 b382c2f8 18 4 0 1048575 0 PO- /dev/online5_13 b382c468 19 4 0 1048575 0 PO- /dev/online5_14 b382c5d8 20 4 0 1048575 0 PO- /dev/online5_15 b382c748 21 4 0 1048575 605928 PO- /dev/online5_16 b382c8b8 22 4 0 1048575 524286 PO- /dev/online5_17 b382ca28 23 4 0 1048575 524286 PO- /dev/online5_18 b382cb98 24 4 0 1048575 0 PO- /dev/online5_19 b382cd08 25 4 0 1048575 524286 PO- /dev/online5_20 b382ce78 26 4 0 1048575 561429 PO- /dev/online5_21 b382d018 27 4 0 1048575 104607 PO- /dev/online5_22 b382d188 28 4 0 1048575 575845 PO- /dev/online5_23 b382d2f8 29 4 0 1048575 0 PO- /dev/online5_24 b382d468 30 4 0 1048575 575845 PO- /dev/online5_25 b382d5d8 31 4 0 1048575 0 PO- /dev/online5_26 b382d748 32 4 0 1048575 1048572 PO- /dev/online5_27 b382d8b8 33 4 0 1048575 1048572 PO- /dev/online5_28 b382da28 34 4 0 1048575 1048572 PO- /dev/online5_29 b382db98 35 4 0 1048575 1048572 PO- /dev/online5_30 b382dd08 36 5 0 1048575 48581 PO- /dev/online10_6 b382de78 37 5 0 1048575 0 PO- /dev/online10_7 b382e018 38 5 0 1048575 0 PO- /dev/online10_8 b382e188 39 5 0 1048575 0 PO- /dev/online10_9 b382e2f8 40 5 0 1048575 99593 PO- /dev/online10_10 b382e468 41 5 0 1048575 0 PO- /dev/online10_11 b382e5d8 42 5 0 1048575 0 PO- /dev/online10_12 b382e748 43 5 0 1048575 0 PO- /dev/online10_13 b382e8b8 44 5 0 1048575 0 PO- /dev/online10_14 b382ea28 45 5 0 1048575 0 PO- /dev/online10_15 b382eb98 46 5 0 1048575 0 PO- /dev/online10_16 b382ed08 47 5 0 1048575 0 PO- /dev/online10_17 b382ee78 48 5 0 1048575 0 PO- /dev/online10_18 b382f018 49 5 0 1048575 0 PO- /dev/online10_19 b382f188 50 5 0 1048575 0 PO- /dev/online10_20 b382f2f8 51 5 0 1048575 524286 PO- /dev/online10_21 b382f468 52 5 0 1048575 524286 PO- /dev/online10_22 b382f5d8 53 5 0 1048575 0 PO- /dev/online10_23 b382f748 54 5 0 1048575 524286 PO- /dev/online10_24 b382f8b8 55 5 0 1048575 561429 PO- /dev/online10_25 b382fa28 56 6 0 1048575 176821 PO- /dev/online5_31 b382fb98 57 6 0 1048575 0 PO- /dev/online5_32 b382fd08 58 6 0 1048575 0

Run onstat -u and find what sessions have the most reads. Run it twice a few seconds apart (or use the qio script in another reply) and run onstat -g ses <session-id> and see what sql that session is doing. Take this sql and if it is a select 1. Run dbaccess against the sessions current database. 2. In dbaccess do Query -> New. 3. Put in SET EXPLAIN ON; and run that. 4. Choose New and put in the sql 5. Run that a wait a few seconds (say 10) and hit in the interrupt key (normally Ctrl-C). 6. Exit dbaccess and look at the last sql in the sqexplain.out file in the current directory. Are there any sequential scans of large tables? If so you need to look at the indexes on that table or run the proper update statistics on that table. If the query is still not using the right index then this is a bug and you need to log a support call with IBM. If indexes are used then are they the right ones for the query. Queries should go from small tables to large ones and use indexes to do the joins. One thing I did see recently was a site where a table contain alarms for people to action. There were 200,000+ alarms in the table! Clearly this table needed to be cleared down. If people have not actioned alarms for 3+ months then they surely they can be cleared down. Let us know the results. You show be able to identify the sessions and sql's that are causing the problem.