Re: Runaway Informix SQL Processes. Need Help!!

In article <34141D13.2D2A@bloomberg.com>, "Art S. Kagel" <kagel@bloomberg.com> writes >David Williams wrote: >> >> In article <19970906041300.AAA08963@ladder02.news.aol.com>, Paynesys >> <paynesys@aol.com> writes >> > >> > I ran the UNIX vmstat & sar commands and discovered that our >> > system's CPU idle time was at 0%. I then did a ps -ef and >[SNIP] >> > Can someone please explain to me why SQL processes that are >> > aborted via the interrupt key continue to hog CPU time? >> >> The interrupt is only send to the dbaccess process. The sqlexec >> processes ignore them. They will die when they next try to communicate >> with dbaccess (to send query results to dbaccess) and receive an >> error. They reason they hog CPU time is that they are probably trying >> to execute the query and are trying to read every record in a very >> large table since no indexes are being used. > >No. For some reason sometimes when the front-end process, in this case >dbaccess, dies or is killed, the backend sqlexec thread, or sqlturbo, >does not recognize the process has died and spins the CPU trying to >communicate with the front-end. I have seen this happen most frequently >with remote clients from PCs but on UNIX local and remote clients also. > For remote clients, assuming TCP/IP when the sqlexec/sqlturbo tries to write a reply back to the client it will try an send a TCP/IP packet to the client PC. One to two things will happen a) PC is not 'alive' after a suitable timeout value, an error will be returned b) the PC is 'alive', it receives the packet and realises it is not part of a valid connecton (either port number or sequence number is wrong). PC sends a reply back with the RST (reset connection) flag set. sqlexec/turbo receives an error from the write. This means either a) The is a bug in your TCP/IP software (unlikely) b) there is a bug in sqlturbo/sqlexec Since this also happens with local connections I would suspect the bug is in sqlexec/turbo. I've never seen this bug before though. Which version of the engine are you running? Our users do interrupt queries but it is always on queries that take a long time, hence the sqlexec continue to clock up time as it is still processing the query. Next time you see this happening could you try (as root) running truss or trace on the process:- truss -aef -v all -o /tmp/djw1 -p<process id> or trace -o /tmp/djw1 (-p <process id> ?? Not sure for trace). >I would guess that the SQLEXEC thread had just returned a buffer and is >awaiting the front-end to acknowledge the buffer or some such schenario. >It is basically polling and gets stuck. From the 'kill -9' comment I >will assume that this is 5.0x where the problem is MUCH more prevalent. >There is no good solution except to kill of orphaned SQLTURBO processes >periodically and educate users to not use the interrupt key of kill on >Informix front-end processes. > >Art S. Kagel -- David Williams