How to identify a run away process in Database
Posted in 2010
A DBA asked how to spot "runaway" or leftover sessions purely from inside Informix (onstat/SQL), since he can't kill processes at the OS level and shell scripts spawning SQL make the client PID hard to trace. Replies distinguished runaway from zombie sessions: use ps -ef at OS level, and within the engine compare nreads/nwrites and CPU/memory in onstat -u and onstat -g ses against expected workload, and match session PIDs to live OS processes to find orphaned sessions; onperf was also suggested for a quick visual. No single definitive check emerged — the advice is heuristic, requiring knowledge of normal values.
Topics: General Discussion
Hi All,
I want to identify what are run away process still running in the database. I
can do "onstat -g ses <sessionid> and get the pid of the session. However, i
can't validate that it is a runaway job.
Is there a way that i can select from a database? or onstat, how would i know,
what are the indication?
Thanks,
If i have this example:
=> onstat -g ses 33351
IBM Informix Dynamic Server Version 7.31.FD8X5 -- On-Line -- Up 6 days
00:48:19 -- 4074880 Kbytes
session #RSAM total used
id user tty pid hostname threads memory memory
33351 user1 - 133652 phiupr06 1 278528 272032
tid name rstcb flags curstk status
344979 sqlexec 7000000855ecf68 ---PR-- 8496 running
Memory pools count 1
name class addr totalsize freesize #allocfrag #freefrag
33351 V 700000084b62028 278528 6496 656 4
name free used name free used
overhead 0 176 scb 0 144
opentable 0 19808 filetable 0 3544
ru 0 280 log 0 4216
temprec 0 1720 keys 0 1512
ralloc 0 191912 gentcb 0 13568
ostcb 0 3408 sqscb 0 12008
rdahead 0 1120 hashfiletab 0 552
osenv 0 2272 sqtcb 0 5104
fragman 0 10384 shmblklist 0 304
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
33351 - users_db DR Not Wait 0 0 7.31
=> onstat -u|grep 33351
7000000855ecf68 ---PR-- 33351 user1 - 0 0 840 4322078 0
It appears you're asking four different questions, each with a different
answer:
1. How to identify a runaway client process, which is an OS question
2. How to identify a runaway DBMS session or thread
3. How to identify a zombie client process
4. How to identify a zombie DBMS session or thread
I'm guessing that by "runaway", you mean threads or processes using far
too much CPU time.
For OS processes, you'll have to use OS commands or tools. You didn't
tell us the platform, so I'll guess. On Linux or Unix, ps -ef is what I
normally use. A defunct process is normally labelled as such, and by
watching the time use, you can usually find the processing chewing up
inordinate time. That does mean you have to understand which processes
normally do use a lot of CPU cycles. The equivalent on Windows servers
is different, and I'm not sure of exactly how to do it.
A runaway thread or session in the DBMS is often found by examining the
nreads and nwrites columns of onstat -u and onstat -g ses for suspect
sessions. Again, you have to know the magnitude of the expected values.
Large reporting queries may to millions of reads for example, and that
would not constitute a runaway session. But looking up a customer's
balance due should take something under 100 reads in all probablilty.
Zombie sessions are trickier. I start by matching up the process id of
each client (from onstat -g ses) with OS processes. If some sessions have
no client process, then that's suspect as a defunct session.
Cheers,
Dick Snoke
IBM ChannelWorks
dsnoke@us.ibm.com
(404) 487-1595
From: "JACK PAPA" <informix2009@gmail.com>
To: ids@iiug.org
Date: 08/20/2010 02:50 AM
Subject: How to identify a run away process in Database [20973]
Sent by: ids-bounces@iiug.org
Hi All,
I want to identify what are run away process still running in the
database. I
can do "onstat -g ses <sessionid> and get the pid of the session. However,
i
can't validate that it is a runaway job.
Is there a way that i can select from a database? or onstat, how would i
know,
what are the indication?
Thanks,
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi,
I've used ps -ef|grep pid, or ps -ef|grep uid on the OS level. I'm asking if
there's any significant (like tty is equal "-" ) command to identify from the
DB. I'm asking this because a shell script can embed multiple shell script
that calls SQLs to run on the DB. Now, since on the OS level, i cannot kill
any process as it is Admin task, I can kill processes inside the DB.
one question, is there a way to identify a zombie or defunct process in DB?.
The user cannot give me the PID (OS Level) as the application was not properly
exited. This left some sessions inside the database.
Example: a ControlM job is calling Multiple scipt shell script and these calls
SQLs.
onstat ?
Hello!
Here, I only monitor the OS processes, grepping for the '?' character,
excluding the services, or users, that
I are regularly (normally runs).
The db sessions of any os process like this "blacklist" is inside de
Informix 4gl program that called it.
So when we kill the OS process that was blocked, the database session(s)
are even dead.
The other trouble is when the connection is external to the database
(.net app for example).
Then there is no OS process running, just the database session.
On that case, I monitor the sessions threads with most cpu time, most
memory used, and so on.
(Of course it´s a linux machine, ok?)
Hope this helps.
Best regards.
Em 20/08/2010 09:12, JACK PAPA escreveu:
> Hi,
>
> I've used ps -ef|grep pid, or ps -ef|grep uid on the OS level. I'm asking if
> there's any significant (like tty is equal "-" ) command to identify from the
> DB. I'm asking this because a shell script can embed multiple shell script
> that calls SQLs to run on the DB. Now, since on the OS level, i cannot kill
> any process as it is Admin task, I can kill processes inside the DB.
>
> one question, is there a way to identify a zombie or defunct process in DB?.
> The user cannot give me the PID (OS Level) as the application was not
properly
> exited. This left some sessions inside the database.
>
> Example: a ControlM job is calling Multiple scipt shell script and these
calls
> SQLs.
>
> onstat ?
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Alexandre Marini
Tecnologia da Informação - DBA
msn: alexandre_marini@hotmail.com
SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg>
IBM Informix Dynamic Server Certified Professional V10 / V11
I user onperf a gui tool that works well this will very quickly show you whats top of the heap very quickly. Have have to have xwindows running though. It is An old tool that i run . Comes standard with informix
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g