Urgent - IDS 9.4 Taking too much resourcess

Hi to all, I have a big problem on one of our's machines: Recently (3-4 days ago) we upgraded IDS 7.30.UC7 to 9.40.HC4 on HP-UX machine. Machine is rp2450/1 CPU (550MHz)/1GB RAM/Storage is SCSI disks. Old version of IDS was working with simbolic links towards other machine. On the same night we upgraded both machines which had symbolic links between them from 7.3 to 9.4. We are using only raw devices for the db data - no cooked files at all. All chunks are evenly distributed betwen all the disks. The problem is as follows: Before upgrade normal CPU usage was 10-15%. After upgrade: 60%. Much ov it is wio/cpu portion. User portion is about 5%. Before upgrade normal DSK usage was 5 -10%. After upgrade: 40%. Disk are running like crazy all the time! After conversion (7.3 tables to 9.4 tables), which was ok, we did run update statistics drop distributions;update statistics to medium. Same upgrade we did on other machine (upg from IDS 9.2 to 9.4) ant it was ok. Please tell me what info you need (onstat, os info ...) and I will provide that. I need an urgent help on this because I do not know for how much longer the disks will cope with this load! Thanks. Dejan.

Hello, sounds like bad access paths (bad SQL optimization). I assume you have checked all IDS parameters to be OK (BUFFERS, OPTCOMPIND)? You can start with update statistics high on the index fields of your most important tables. Furthermore try to identify the sessions/statements which generate the most load: CPU: select sid,bufreads,pagreads from sysmaster:syssesprof order by bufreads IO: select sid,bufreads,pagreads from sysmaster:syssesprof order by pagreads Look at the SQLs of the sessions (onstat -g sql <SID>). I hope you find SQLs that take some time. Run these SQLs with set explain on. Alternatively: activate dynamic explain for some of the sessions (onmode -Y 1) Regards, Andreas Kutsche > ------------------------------------------- SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastrasse 3 A - 5015 Salzburg Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im > Auftrag von DEJAN STOJC.... > Gesendet: Dienstag, 30. August 2005 10:38 > An: ids@iiug.org > Betreff: Urgent - IDS 9.4 Taking too much resourcess [5676] > > > Hi to all, > I have a big problem on one of our's machines: > Recently (3-4 days ago) we upgraded IDS 7.30.UC7 to 9.40.HC4 > on HP-UX machine. > Machine is rp2450/1 CPU (550MHz)/1GB RAM/Storage is SCSI disks. > Old version of IDS was working with simbolic links towards > other machine. > On the same night we upgraded both machines which had > symbolic links between them from 7.3 to 9.4. > We are using only raw devices for the db data - no cooked > files at all. All chunks are evenly distributed betwen all the disks. > The problem is as follows: > Before upgrade normal CPU usage was 10-15%. After upgrade: > 60%. Much ov it is wio/cpu portion. User portion is about 5%. > Before upgrade normal DSK usage was 5 -10%. After upgrade: > 40%. Disk are running like crazy all the time! > After conversion (7.3 tables to 9.4 tables), which was ok, we > did run update statistics drop distributions;update > statistics to medium. > Same upgrade we did on other machine (upg from IDS 9.2 to > 9.4) ant it was ok. > Please tell me what info you need (onstat, os info ...) and I > will provide that. > I need an urgent help on this because I do not know for how > much longer the disks will cope with this load! > Thanks. > Dejan. > >

Hi, your output from onstat -u for session 12 shows: d0f03ea0 ---PR-B 12 informix - 0 0 0 250910180 248240 The flag 'B' in the second column indicates a BTREE cleaner thread. Is it possible that you have a lot of old indexes with a lot of deleted items? If you know that you have such indexes you can think about dropping and recreating them (You know you need an exclusive table lock for that). The new Btree cleaner concept of IDS 9.40 is a little bit mysterious for most people (at least for me). To be sure that the workload/IO comes from the Btree cleaner you can stop it (online), monitor session 12 and afterwards restart the Btree cleaner: onmode -C stop 1 onmode -C start 1You can monitor Btree cleaner activity with onstat -Cbut the output may be quite mysterious to. With onstat -P you can try to identify the indexes which are worked on - I think you will find partitions (indexes) with lots of entries in column 'other' . But that's only a conjecture. Hope that gives some ideas to go on. Regards, Andreas > ------------------------------------------- SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastrasse 3 A - 5015 Salzburg Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------- -----Urspr¨¹ngliche Nachricht----- > Von: Dejan Stojcevski [mailto:dejan.stojcevski@cosmofon.com.mk] > Gesendet: Dienstag, 30. August 2005 14:58 > An: KUTSCHE Andreas (HZ422) > Betreff: RE: Urgent - IDS 9.4 Taking too much resourcess [5676] > > > I identified the session id 12 as a ses that takes much of > the resourcess. > But it is a system session and onstat -g ses 12 does not give > me anything. > Other sessions are performing ok. > Will send you the onconfig also. > At the end it seems IDS is working ok!? > Dejan. > > -----Original Message----- > From: Andreas.KUTSCHE@spar.at [mailto:andreas.kutsche@spar.at] > Sent: §Ó§ä§à§â§ß§Ú§Ü, 30 §Ñ§Ó§Ô§å§ã§ä 2005 14:39 > To: Dejan Stojcevski; ids@iiug.org > Subject: AW: Urgent - IDS 9.4 Taking too much resourcess [5676] > > Hello, > > sounds like bad access paths (bad SQL optimization). I assume you have > checked > all IDS parameters to be OK (BUFFERS, OPTCOMPIND)? You can > start with update > statistics > high on the index fields of your most important tables. > > Furthermore try to identify the sessions/statements which > generate the most > load: > CPU: select sid,bufreads,pagreads from sysmaster:syssesprof order by > bufreads > IO: select sid,bufreads,pagreads from sysmaster:syssesprof order by > pagreads > > Look at the SQLs of the sessions (onstat -g sql <SID>). > I hope you find SQLs that take some time. > Run these SQLs with set explain on. > > Alternatively: activate dynamic explain for some of the > sessions (onmode -Y > 1) > > > Regards, > Andreas Kutsche > > > > > ------------------------------------------- > SPAR Oesterreichische Warenhandels-AG > Hauptzentrale > Europastrasse 3 > A - 5015 Salzburg > > Tel: +43 662 4470 24223 > Mobile: +43 664 6259575 > E-Mail: Andreas.KUTSCHE@spar.at > Internet: http://www.spar.at > ------------------------------------------- > -----Urspr¨¹ngliche Nachricht----- > > > Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im > > Auftrag von DEJAN STOJC.... > > Gesendet: Dienstag, 30. August 2005 10:38 > > An: ids@iiug.org > > Betreff: Urgent - IDS 9.4 Taking too much resourcess [5676] > > > > > > Hi to all, > > I have a big problem on one of our's machines: > > Recently (3-4 days ago) we upgraded IDS 7.30.UC7 to 9.40.HC4 > > on HP-UX machine. > > Machine is rp2450/1 CPU (550MHz)/1GB RAM/Storage is SCSI disks. > > Old version of IDS was working with simbolic links towards > > other machine. > > On the same night we upgraded both machines which had > > symbolic links between them from 7.3 to 9.4. > > We are using only raw devices for the db data - no cooked > > files at all. All chunks are evenly distributed betwen all > the disks. > > The problem is as follows: > > Before upgrade normal CPU usage was 10-15%. After upgrade: > > 60%. Much ov it is wio/cpu portion. User portion is about 5%. > > Before upgrade normal DSK usage was 5 -10%. After upgrade: > > 40%. Disk are running like crazy all the time! > > After conversion (7.3 tables to 9.4 tables), which was ok, we > > did run update statistics drop distributions;update > > statistics to medium. > > Same upgrade we did on other machine (upg from IDS 9.2 to > > 9.4) ant it was ok. > > Please tell me what info you need (onstat, os info ...) and I > > will provide that. > > I need an urgent help on this because I do not know for how > > much longer the disks will cope with this load! > > Thanks. > > Dejan. > > > > > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ > _ _ _ _ _ _ _ > > Scanning of this message and addition of this footer is performed > by COSMOFON's E-mail Filter software in conjunction with > virus detection software. > > COSMOFON - Mobile Telecommunications Services - A.D. Skopje > _______________________________________________________________ > This e-mail (including any attachments) is > confidential and may be protected by legal privilege. > If you are not the intended recipient, you should not copy > it, re-transmit it, use it or disclose its contents, but > should return it to the sender immediately and delete your > copy from your system. Any unauthorized use or > dissemination of this message in whole or in part is strictly > prohibited. Please note that e-mails are susceptible to > change. COSMOFON A.D. Skopje shall not be liable for the > improper or incomplete transmission of the information > contained in this communication nor for any delay in its > receipt or damage to your system. >