Monitor CPU usage by Informix session

We had a situation where an Informix session brought the production Instance down to its knees. We restarted the instance and it happened a second time. Here are the server details: HP-UX uxprod1 B.11.31 U ia64, IBM Informix Dynamic Server Version 11.70.FC5, HDR cluster. The problem details: This information is from using onmonitor/OpenAdmin Tool and top. I identified the cultprit to a session that was running a long-running query which was also a CPU hog. The impatient end-user resubmitted this multiple times (creating multiple identical sessions) and soon we had 3 oninits running at 99-100% CPU. This slowed every other application down. We identified the report and turned it off. The following are my questions: 1. Is there a way to monitor CPU usage by a specific query or Informix session or p.i.d.? 2. Is there a way to limit the CPU allocated to 1 specific query (or session) in terms of %, and allow a query to use, say,5% CPU. 3. Would setting BLOCKTIMEOUT or TXTIMEOUT help?\\\\ Thanks. Murali

On Thu, Nov 15, 2012 at 2:42 PM, MURALI PAZHAYANNUR < pmurali@ftportfolios.com> wrote: > We had a situation where an Informix session brought the production > Instance > down to its knees. We restarted the instance and it happened a second time. > Here are the server details: HP-UX uxprod1 B.11.31 U ia64, IBM Informix > Dynamic Server Version 11.70.FC5, HDR cluster. > Why did you restart the engine? Did you try to kill the sessions (onmode -z SID) or eventually try to kill the client (depending on the architechture and privileges this may not be an option) > > The problem details: This information is from using onmonitor/OpenAdmin > Tool > and top. I identified the cultprit to a session that was running a > long-running query which was also a CPU hog. The impatient end-user > resubmitted this multiple times (creating multiple identical sessions) and > soon we had 3 oninits running at 99-100% CPU. This slowed every other > application down. We identified the report and turned it off. > > The following are my questions: > 1. Is there a way to monitor CPU usage by a specific query or Informix > session > or p.i.d.? > Yes and no... You can activate the SQLTRACE and see the results after query has been run (use OpenAdmin Tool). There are scripts that at a specific moment can show you the sessions there are consuming more CPU. Note that this is a snapshot... I have one of those scripts around here: http://onlinedomus.net/informix/ (go to scripts repository and search for "ixtop") ixtop -h will show you the options (standard disclaimer applies...) But you should find similar scripts (I'd like to think this one is better...) in the IIUG repository > 2. Is there a way to limit the CPU allocated to 1 specific query (or > session) > in terms of %, and allow a query to use, say,5% CPU. > Currently no. > 3. Would setting BLOCKTIMEOUT or TXTIMEOUT help?\\\\ > No. These parameters have nothing to do with your issue. Regards -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7bdc0fa45bd5c904ce8a12c7

Original Post: We had a situation where an Informix session brought the production Instance down to its knees. We restarted the instance and it happened a second time. Here are the server details: HP-UX uxprod1 B.11.31 U ia64, IBM Informix Dynamic Server Version 11.70.FC5, HDR cluster. The problem details: This information is from using onmonitor/OpenAdmin Tool and top. I identified the cultprit to a session that was running a long-running query which was also a CPU hog. The impatient end-user resubmitted this multiple times (creating multiple identical sessions) and soon we had 3 oninits running at 99-100% CPU. This slowed every other application down. We identified the report and turned it off. The following are my questions: 1. Is there a way to monitor CPU usage by a specific query or Informix session or p.i.d.? 2. Is there a way to limit the CPU allocated to 1 specific query (or session) in terms of %, and allow a query to use, say,5% CPU. 3. Would setting BLOCKTIMEOUT or TXTIMEOUT help?\\\\ Thanks. Murali Response: 1) You should be able to use onstat -g cpu to see cpu usage per thread which could then be tracked back to a session (and then a query). 2) No 3) No, those onconfig parameters don't have anything to do with what you are talking about. Jacques Renaut IBM Informix Advanced Support APD Team

This little script may help: 1 dbaccess sysmaster <<EOF 2 SELECT 3 s.sid , 4 s.username , 5 s.uid , 6 s.pid , 7 s.hostname , 8 t.tid, 9 t.name, 10 t.statedesc , 11 t.statedetail, 12 t.cpu_time 13 FROM syssessions s, systcblst t, sysrstcb r 14 WHERE t.tid = r.tid AND s.sid = r.sid 15 order by t.cpu_time desc; 17 EOF Reinhard. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MURALI > PAZHAYANNUR > Sent: Thursday, November 15, 2012 3:43 PM > To: ids@iiug.org > Subject: Monitor CPU usage by Informix session [28837] > > We had a situation where an Informix session brought the production Instance > down to its knees. We restarted the instance and it happened a second time. > Here are the server details: HP-UX uxprod1 B.11.31 U ia64, IBM Informix > Dynamic Server Version 11.70.FC5, HDR cluster. > > The problem details: This information is from using onmonitor/OpenAdmin Tool > and top. I identified the cultprit to a session that was running a long-running query > which was also a CPU hog. The impatient end-user resubmitted this multiple > times (creating multiple identical sessions) and soon we had 3 oninits running at > 99-100% CPU. This slowed every other application down. We identified the report > and turned it off. > > The following are my questions: > 1. Is there a way to monitor CPU usage by a specific query or Informix session or > p.i.d.? > 2. Is there a way to limit the CPU allocated to 1 specific query (or session) in > terms of %, and allow a query to use, say,5% CPU. > 3. Would setting BLOCKTIMEOUT or TXTIMEOUT help?\\\\ > > Thanks. > Murali > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum.