httpd-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <graham.rj.spill...@bt.com>
Subject [users@httpd] Killing sessions on Apache with Oracle backend database
Date Mon, 11 Apr 2005 15:14:24 GMT
(Apols for a rather long initial post -
 Hope this hasn't been asked before)

We sometimes need to KILL runaway SIDs in Oracle
but can't find a way of doing it without messing up Apache.
I think it might be a common problem wherever Apache serves Oracle Query results.
So what is the recommended procedure?

I've scanned documentation & googled a bit, but the search words I've used haven't found
anything useful. Closest I got was documentation of the mod_status module on the Apache site.

Environment
-----------
OpSys: Win2K server
Webserver: Apache 1.3
Backend DB: Oracle 8.1.7.4
Users: Several hundred, all in house users - Intranet application
All user connections to the DB run in the same Oracle User.

Problem
-------
Occasionally (but too often) we get very long queries running in the DB.
Users recently unleashed N simultaneous big queries & nobody could then work, 'cos all
CPUs & threads were busy. In such cases we could to kill the culprits, but we have two
issues:

1) We can see the SID in Oracle, but that SID is owned by Apache, and we have no way of knowing
who the user is. The access log will tell us, but only on completion of the query, by which
time it is too late to do any good.

2) If we arbitrarily KILL them (ORAKILL or SQL KILL IMMEDIATE & hang the consequences),
this messes up Apache, 'cos it doesn't know about what we have done. 
Experience (bitter) tells us that an Oracle KILL on an Apache SID also kills Apache in its
entirety, so it has to be re-started and ALL users are affected. If we do this too often,
it will be us/me who is hanged, not the consequences!


So is there a way round this, to do a safe kill?
AND
How can we discover the IP Address responsible for a SID run by Apache?
AND
Given we want to kill a given SID, is there an Apache facility to do this?

Ideally I would like to say to Apache by a "Console command":
   "Tell me the IPAddresses and SIDs of all active SIDs"
and then I would like to say
   "Kill IP xxx.xxx.xxx.xxx's session in SID yy"
I want other Apache sessions to cruise serenely on their way during/after this
and the assassinated thread to be respawned on demand as normal.
(Is this what is meant by a "graceful" restart??)

If the facility doesn't exist, would it be useful to others?
Should it be put to the developers' forum?

I guess all this is a No No, but I'd be very interested if it isn't.


On another, related matter, is there any effective way to break in from an Apache browser
to abort a long running DB query?
- I guess this question reveals the sheer extent of my ignorance of Apache facilities  :o(


Graham

---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
   "   from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org


Mime
View raw message