Re: Runaway Informix SQL Processes. Need Help!!

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. 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