DBI mysql slow queries, hanging threads

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Wisest of Monks,

I have a Perl (DBI) program that does a number of very large queries on a mysql server. The typical result set size is in the 100s of MB. The queries take a long time. However, sometimes I have to kill the process before the query completes. Using 'show processlist' I see that there are leftover threads from queries executed from the now killed process. These queries seem to tie up the server until I kill them manually.

Is there a way to have perl DBI automatically kill the sql query thread when an interrupt is detected?

Is there a way to have perl DBI detect a leftover query thread and kill it (or block or warn) before sending a new query?

Any help would be deeply appreciated!

Comment on DBI mysql slow queries, hanging threads

Replies are listed 'Best First'.
Re: DBI mysql slow queries, hanging threads by perrin (Chancellor) on Nov 29, 2007 at 21:20 UTC
You can catch the kill signal in your script and kill the thread on the server with this code: `my $thread_id = $dbh->{thread_id}; my $new_dbh = $dbh->clone(); $new_dbh->{InactiveDestroy} = 0; $new_dbh->do("KILL $thread_id");` [download] (Taken from DBIx::Timeout)	[reply] [d/l]
Re: DBI mysql slow queries, hanging threads by tachyon-II (Chaplain) on Nov 29, 2007 at 23:36 UTC
To catch the signal see Perl FAQ 4.9 `$SIG{'INT'} = 'CLEANUP'; sub CLEANUP { print "\n\nCaught Interrupt (^C), Aborting\n"; exit(1); }` [download] You may also want to catch other signals like KILL, TERM, STOP, QUIT, HUP depending on your OS. I would catch at least INT, KILL and TERM by default. `$SIG{$_} = 'CLEANUP' for qw(INT KILL TERM STOP QUIT);` [download]	[reply] [d/l] [select]