RE: Monitor sessions

I think it's great that you're putting together a script to do this sort of thing. I think Paul is absolutely right, you need some commentary so that it doesn't take more than 10 seconds to understand. I would also highly recommend NOT using onstat for this sort of thing. The output from onstat has changed over the years and what you count on being in one place will have moved with the next release. All of the information you need for you script is available in sysmaster - start with the syssesprof table (onstat -g tpf) and work from there with the remaining sysses* tables. I would do this as a stored procedure. cheers j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of mohitanchlia@gmail.com Sent: Wednesday, January 23, 2008 2:20 AM To: informix-list@iiug.org Subject: Re: Monitor sessions On Jan 16, 11:22 am, mohitanch...@gmail.com wrote: > On Jan 16, 10:10 am, mohitanch...@gmail.com wrote: > > > > > On Jan 16, 5:29 am, scottishpoet <drybur...@yahoo.com> wrote: > > > > On Jan 16, 12:43 am, mohitanch...@gmail.com wrote: > > > > > Version: IDS 10 > > > > > I am trying to write a script that will find out those threads or > > > > sessions that are stuck at particular step for more than x mts. It > > > > could be stuck to acquire a lock on a resouce, or waiting for buffer > > > > or checkpoint or using lot of logical logs or may be just running a > > > > particular DB operation like long updates, inserts etc. I was thinking > > > > of using output from onstat -u and parse first flag 1 and run again > > > > after x mts and compare and spit out the details to the user. However, > > > > I am not really sure if it covers all the scenarios. What's the better > > > > way of writing such a script ? > > > > Is upgrading to IDS 11 a possibility? > > > > The OpenAdmin Tool provided with IDS 11 probably provides a lot of the > > > information you are looking to capture. > > > Not at this point.- Hide quoted text - > > > - Show quoted text - > > To start with, I wrote this small script that will report those > processes that are writing most logical logs, this script is an > attempt to identify processes that could potentially lead to long > transactions and also report high usage of logical logs, please > review: > > #Get that process that's writing most logical logs > onstat -g tpf | tr -s " " | sort -nr -k 16 | head > onstat.tpf.out > for i in `cat onstat.tpf.out|cut -d " " -f1`> do > if [ $i -gt 0 ] > then > echo "------ LOGICAL LOG $i START" > user_add=`onstat -g ath|grep $i|tr -s " "|cut -d " " -f4` > if [ `echo "$user_add"|grep -c "[a-z]` -gt 0 ] > then > sess_id=`onstat -u |grep $user_add|cut -d " " -f3` > if [ `echo "$sess_id"|grep -c "[0-9]"` -gt 0 ] > then > u_pid=`onstat -g ses|grep "^$sess_id"|tr -s " "|cut -d " > " -f4` > echo "Process Info " > ps -eaf|grep "$u_pid" > fi > fi > echo "------ LOGICAL LOG $i END" > fi > done Anybody like to comment on this script, if it's ok, bad or whatever ? _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

On Jan 23, 4:03 pm, "Jack Parker" <jack.park...@verizon.net> wrote: > I think it's great that you're putting together a script to do this sort of > thing.  I think Paul is absolutely right, you need some commentary so that > it doesn't take more than 10 seconds to understand.  I would also highly > recommend NOT using onstat for this sort of thing.  The output from onstat > has changed over the years and what you count on being in one place will > have moved with the next release.  All of the information you need for you > script is available in sysmaster - start with the syssesprof table > (onstat -g tpf) and work from there with the remaining sysses* tables.  I > would do this as a stored procedure. > > cheers > j. > > > > -----Original Message----- > From:informix-list-boun...@iiug.org > > [mailto:informix-list-boun...@iiug.org]On Behalf Of > mohitanch...@gmail.com > Sent: Wednesday, January 23, 2008 2:20 AM > To:informix-l...@iiug.org > Subject: Re: Monitor sessions > > On Jan 16, 11:22 am, mohitanch...@gmail.com wrote: > > On Jan 16, 10:10 am, mohitanch...@gmail.com wrote: > > > > On Jan 16, 5:29 am, scottishpoet <drybur...@yahoo.com> wrote: > > > > > On Jan 16, 12:43 am, mohitanch...@gmail.com wrote: > > > > > > Version: IDS 10 > > > > > > I am trying to write a script that will find out those threads or > > > > > sessions that are stuck at particular step for more than x mts. It > > > > > could be stuck to acquire a lock on a resouce, or waiting for buffer > > > > > or checkpoint or using lot of logical logs or may be just running a > > > > > particular DB operation like long updates, inserts etc. I was > thinking > > > > > of using output from onstat -u and parse first flag 1 and run again > > > > > after x mts and compare and spit out the details to the user. > However, > > > > > I am not really sure if it covers all the scenarios. What's the > better > > > > > way of writing such a script ? > > > > > Is upgrading to IDS 11 a possibility? > > > > > The OpenAdmin Tool provided with IDS 11 probably provides a lot of the > > > > information you are looking to capture. > > > > Not at this point.- Hide quoted text - > > > > - Show quoted text - > > > To start with, I wrote this small script that will report those > > processes that are writing most logical logs, this script is an > > attempt to identify processes that could potentially lead to long > > transactions and also report high usage of logical logs, please > > review: > > > #Get that process that's writing most logical logs > > onstat -g tpf | tr -s " " | sort -nr -k 16 | head > onstat.tpf.out > > for i in `cat onstat.tpf.out|cut -d " " -f1`> > do > >    if [ $i -gt 0 ] > >    then > >      echo "------ LOGICAL LOG $i START" > >      user_add=`onstat -g ath|grep $i|tr -s " "|cut -d " " -f4` > >      if [ `echo "$user_add"|grep -c "[a-z]` -gt 0 ] > >      then > >          sess_id=`onstat -u |grep $user_add|cut -d " " -f3` > >          if [ `echo "$sess_id"|grep -c "[0-9]"` -gt 0 ] > >          then > >              u_pid=`onstat -g ses|grep "^$sess_id"|tr -s " "|cut -d " > > " -f4` > >              echo "Process Info " > >              ps -eaf|grep "$u_pid" > >          fi > >      fi > >      echo "------ LOGICAL LOG $i END" > >   fi > > done > > Anybody like to comment on this script, if it's ok, bad or whatever ? > _______________________________________________Informix-list mailing listInformix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text - > > - Show quoted text - Thanks, I'll try that. For my understanding - does "onstat -g tpf | tr -s " " | sort -nr -k 16 | head " give me those transactions that are filling most logical log records at any given time ?

Quothe Mohitan: "For my understanding - does "onstat -g tpf | tr -s " " | sort -nr -k 16 | head" onstat -g tpf - or thread profiles. the tr -s " " (or squeeze out extra spaces) doesn't do anything to help on most systems. sort will generally not care about the amount of whitespace - but your mileage may vary. sort numeric, reverse on field number 16 or lsus (according to Troy Hewitt the amount of logspace in use) would certainly seem to pick out the session with the most log space in use. I have no idea if it is accurate. head will pick off the top 10 in this case, so you will be checking the 10 worst offenders. However, against syssesprof this is a much cleaner query: select * from syssesprof where logrecs > 10 -- or whatever number you like.You also don't have to go through 20 gyrations of onstat to pick off the rest. You want to see the sql these guys are running? select logrecs, sqx_sqlstatement from syssesprof a, syssqexplain b where a.sid=b.sqx_sessionid and logrecs > some_number; If you want to play with some_number and get only the top few, then you might want the highest values for that number: select logrecs, sqx_sqlstatement from syssesprof a, syssqexplain b where a.sid=b.sqx_sessionid and logrecs > (select max(logrecs)*.9 from syssesprof); -- anything thatis greater than 90% of the max. So I ran that and came up emtpy. Backed out, did a little work and: logrecs 449 sqx_sqlstatement select logrecs, sqx_sqlstatement from syssesprof a, syssqexplain b where a.sid=b.sqx_sessionid and logrecs > (select max(logrecs)*.9 from syssesprof) logrecs 480 sqx_sqlstatement create view "informix".syssesprof (sid,lockreqs,locksheld,loc kwts,deadlks,lktouts,logrecs,isreads,iswrites,isrewrites,isde letes,iscommits,isrollbacks,longtxs,bufreads,bufwrites,seqsca ns,pagreads,pagwrites,total_sorts,dsksorts,max_sortdiskspace, logspused,maxlogsp) as select x0.sid ,sum(x0.upf_rqlock ) ,su m(x0.nlocks ) ,sum(x0.upf_wtlock ) ,sum(x0.upf_deadlk ) ,sum( x0.upf_lktouts ) ,sum(x0.upf_lgrecs ) ,sum(x0.upf_isread ) ,s um(x0.upf_iswrite ) ,sum(x0.upf_isrwrite ) ,sum(x0.upf_isdele te ) ,sum(x0.upf_iscommit ) ,sum(x0.upf_isrollback ) ,sum(x0. upf_longtxs ) ,sum(x0.upf_bufreads ) ,sum(x0.upf_bufwrites ) ,sum(x0.upf_seqscans ) ,sum(x0.nreads ) ,sum(x0.nwrites ) ,su m(x0.upf_totsorts ) ,sum(x0.upf_dsksorts ) ,sum(x0.upf_srtspm ax ) ,sum(x0.upf_logspuse ) ,sum(x0.upf_logspmax ) from "info rmix".sysrstcb x0 where (x0.sid > 0 ) group by x0.sid; Figures - my own session and what I happen to be doing at the present. Mind you it does not show what I DID do generate that kind of log activity, but it does show what I am doing. You can of course populate the query with other info, the sessionid, who it is, etc. I am also coming up with this on the fly, I don't presently have a production system to check this against - you may find my suggestions to be totally useless. But then we've all been there. Regards, Jack Parker

On Jan 26, 5:13 pm, "Jack Parker" <jack.park...@verizon.net> wrote: > Quothe Mohitan: > "For my understanding - does "onstat -g tpf | tr -s " " | sort -nr -k 16 | > head" > > onstat -g tpf - or thread profiles.> > the tr -s " " (or squeeze out extra spaces) doesn't do anything to help on > most systems.  sort will generally not care about the amount of whitespace - > but your mileage may vary. > > sort numeric, reverse on field number 16 or lsus (according to Troy Hewitt > the amount of logspace in use) would certainly seem to pick out the session > with the most log space in use.  I have no idea if it is accurate. > > head will pick off the top 10 in this case, so you will be checking the 10 > worst offenders. > > However, against syssesprof this is a much cleaner query: > > select * from syssesprof where logrecs > 10 -- or whatever number you like.> You also don't have to go through 20 gyrations of onstat to pick off the > rest. > > You want to see the sql these guys are running? > > select logrecs, sqx_sqlstatement >   from syssesprof a, syssqexplain b >  where a.sid=b.sqx_sessionid >    and logrecs > some_number;> > If you want to play with some_number and get only the top few, then you > might want the highest values for that number: > > select logrecs, sqx_sqlstatement >   from syssesprof a, syssqexplain b >  where a.sid=b.sqx_sessionid >    and logrecs > (select max(logrecs)*.9 from syssesprof);  -- anything that> is greater than 90% of the max. > > So I ran that and came up emtpy.  Backed out, did a little work and: > > logrecs           449 > sqx_sqlstatement  select logrecs, sqx_sqlstatement >                     from syssesprof a, syssqexplain b >                    where a.sid=b.sqx_sessionid >                      and logrecs > (select max(logrecs)*.9 from syssesprof) > > logrecs           480 > sqx_sqlstatement  create view "informix".syssesprof > (sid,lockreqs,locksheld,loc > > kwts,deadlks,lktouts,logrecs,isreads,iswrites,isrewrites,isde > > letes,iscommits,isrollbacks,longtxs,bufreads,bufwrites,seqsca > > ns,pagreads,pagwrites,total_sorts,dsksorts,max_sortdiskspace, >                   logspused,maxlogsp) as select x0.sid ,sum(x0.upf_rqlock ) > ,su >                   m(x0.nlocks ) ,sum(x0.upf_wtlock ) ,sum(x0.upf_deadlk ) > ,sum( >                   x0.upf_lktouts ) ,sum(x0.upf_lgrecs ) ,sum(x0.upf_isread ) > ,s >                   um(x0.upf_iswrite ) ,sum(x0.upf_isrwrite ) > ,sum(x0.upf_isdele >                   te ) ,sum(x0.upf_iscommit ) ,sum(x0.upf_isrollback ) > ,sum(x0. >                   upf_longtxs ) ,sum(x0.upf_bufreads ) > ,sum(x0.upf_bufwrites ) >                   ,sum(x0.upf_seqscans ) ,sum(x0.nreads ) ,sum(x0.nwrites ) > ,su >                   m(x0.upf_totsorts ) ,sum(x0.upf_dsksorts ) > ,sum(x0.upf_srtspm >                   ax ) ,sum(x0.upf_logspuse ) ,sum(x0.upf_logspmax ) from > "info >                   rmix".sysrstcb x0 where (x0.sid > 0 ) group by x0.sid; > > Figures - my own session and what I happen to be doing at the present.  Mind > you it does not show what I DID do generate that kind of log activity, but > it does show what I am doing.  You can of course populate the query with > other info, the sessionid, who it is, etc. > > I am also coming up with this on the fly, I don't presently have a > production system to check this against - you may find my suggestions to be > totally useless.  But then we've all been there. > > Regards, > Jack Parker So the logrecs column in syssesprof indicates how much logical log records have been written since the process is running ? And what's the difference between logrecs and logspused columns. Which field would tell me how much logical records is currently being used ?

Quothe Mohitan: So the logrecs column in syssesprof indicates how much logical log records have been written since the process is running ? And what's the difference between logrecs and logspused columns. Which field would tell me how much logical records is currently being used ? ----- Without checking (and I do suggest that you do) I would assume that logrecs ("log recs") to be a number of records while logspused ("log sp used") would be log space used - probably in pages. That information should be in the second volume of the Admin Ref Manual (or Guide, not sure). If not there, check the buildsmi scripts in $INFORMIXDIR/etc. cheers j.