Re: Informix just STOPS?

In article <ZbWDK0AxqR90EwSU@smooth1.demon.co.uk>, David Williams <djw@smooth1.demon.co.uk> wrote: >In article <6d1spj$7bf@sjx-ixn3.ix.netcom.com>, Ray Garrison ><apsnet@ix.netcom.com> writes >>In article <34F468E9.189F@bloomberg.com>, >> "Art S. Kagel" <kagel@bloomberg.com> wrote: >> >>>Ray Garrison wrote: >>>> >>>> Running OnLine 7.23.UC1 >>>> HP UX 11.0 >>>> D250 X 2, Gig RAM >>>> >>>> When running, well, just about anything that takes a while >>>> and results in lots of reads (buffer, page, whatever) the >>>> damn engine just stops and sits there. For example, I just >>>> ran an update statistics for table(tabname) from the ISQL >>>> menu. Table is fairly large (120 million rows, about 8 gigs >>>> of space). Process runs for a few hours, then just stops. >>>> ISQL window sits there with Running... displayed. If I >>>> look at onstat -u I see the process, flagged as in a Read >>>> mode. nreads and nwrites never increment. Using HP Glance >>>> I see the system working hard when the process starts, avg >>>> 40% CPU utilization, 75% disk utilization. Then, boom. CPU >>>> activity drops to idle. No indication that anything has hung, >>>> resource run out, I have no clue. Various onstat -g options >>>> indicate the process is running, but nothing happens. This >>>> is a development box, there is no one else logged on, no other >>>> processes running but this one. >>> >>>Update statistics does a LOT of sorting ESPECIALLY for large tables.>>>Most of the sort activity happens in memory and may cause additional >>>virtual segments to be created which takes time. >>> >>>Art S. Kagel >> >> How much time? In one case, just to be sure that the engine was >>truly hung up as opposed to doing something very slooooooowly, I >>left the process up while I was out on vacation. Nine days later >>nothing had changed... process still showed as alive and in a read >>call, nothing incremented, no buffer reads/writes going on, onstat >>reports showed no indication of any problems, but nada happening. >> >>Ray Garrison > > Run onstat - > > I the engine blocked waiting for a checkpoint? > > Has it run out of logical logs? > > Try e-mailing me an onstat -a ... > >-- >David Williams > >Maintainer of the Informix FAQ > Primary site (Beta Version) http://www.smooth1.demon.co.uk > Official site http://www.iiug.org/techinfo/faq/faq_top.html > >I see you standin', Standin' on your own, It's such a lonely place for you, For >you to be If you need a shoulder, Or if you need a friend, I'll be here >standing, Until the bitter end... >So don't chastise me Or think I, I mean you harm... >All I ever wanted Was for you To know that I care The engine is not blocked waiting for a checkpoint. The logical logs are almost 100% available. The database is running without logging (this time, I've tried it with buffered, unbuffered and no logging) I did an onstat -a, the resulting file is 1.8 meg. I will E-Mail it to anyone who is willing to take a look in hopes of helping me with this, don't think posting it to this newsgroup would be received very well. In the output of the onstat -a, I do see the following: Conditions with waiters: cid addr name waiter waittime 177 c08d46e8 notify_MC1 51 52139 If anyone would like me to post any part of the onstat -a output, tell me which sections and I will post. To recap, HP9000 model D370 two processor 512 MEG RAM running the following query: Select month(date2util), trmid, uid, divid, count(*), sum(samt)/100 from stubs, transactions where stubs.trany = transactions.trany and date2util > "6/1/97" group by 1,2,3,4 order by 1,2,3,4 trany is primary key in transactions, foreign key in stubs date2util is indexed stubs has 100,763,524 rows transactions has 96,480,469 rows Query runs for several hours, then just stops and sits there. Engine still up, no other queries will execute. Help help help help help. Thanks, Ray Garrison