Comments on Rational Relational: How To Perform a Connection Massacre

Thank you very much for this command line :

2008-07-23T12:29:00.000+03:00
Thank you very much for this command line :

for PID in `mysql -u user -ppass -e "show processlist" | grep Sleep | cut -f1`
do
mysql -u user -ppass -e "kill $PID"
done

Before I did all manually, thank you !!

Fatiha

There is a snippet on the Forge site that uses a S...

2008-06-13T20:18:00.000+03:00

There is a snippet on the Forge site that uses a Stored Proc & Cursor to kill long running queries (contributed by Matt Montegomery). To make this a fully automated query killer all you need to do is setup an Event :)

http://forge.mysql.com/tools/tool.php?id=106

was not only ugly, but wrong... corrected:

...

2008-06-13T05:21:00.000+03:00

was not only ugly, but wrong... corrected:

mysql -B -e "SHOW FULL PROCESSLIST;"| tee /tmp/mysqlprocesslist`date "+%Y-%m-%dT%H:%M:%S"` | grep -Ewv "Sleep|INSERT|UPDATE|REPLACE|Binlog Dump|NULL|Command" | grep SELECT | sort -n -r -k 6 | head -n 100 | awk -F \\t '{print $1,$6}' | grep -v " [1-2][0-9]"$ | grep -v " [0-9]"$ | awk '{print $1}' | xargs | sed 's/ /,/g' | xargs mysqladmin kill

It's not pretty and it's only for the '...

2008-06-13T04:36:00.000+03:00

It's not pretty and it's only for the 'nix-minded, but I've found this useful when caught in the precarious position of needing to kill a number of uncooperative selects (in the case below batches of longest running 100, running >=30s)...

mysql -B -e "SHOW FULL PROCESSLIST;"| tee /tmp/mysqlprocesslist`date "+%Y-%m-%dT%H:%M:%S"` | grep -Ewv "Sleep|INSERT|UPDATE|REPLACE|Binlog Dump|NULL|Command" | grep SELECT | sort -n -r -k 6 | head -n 100 | awk -F \\t '{print $1,$6}' | grep -v [1-2][0-9]$ | grep -v " [0-9]"$ | xargs mysqladmin kill

Anonymous: Yes, it actually is a new addition in 5...

2008-06-13T03:01:00.000+03:00

Anonymous: Yes, it actually is a new addition in 5.1. Look at this manual page, in both 5.0 and 5.1 versions:
http://dev.mysql.com/doc/refman/5.1/en/information-schema.html
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
There are a lot more tables in the new soon-to-be-GA-nearly-almost-no-critical-bugs version.

Sheeri: Well, nothing beats a good linux command line :) Unfortunately, If you're working with windows as your client side, most of these tools are not available by default.

Arjen: Yes, I agree. I've added the "Sleep" condition as just an example to emphasize the fact this is a regular table which can be queried against - maybe not the best example at that.

The sleeping connections is just a bad example. It...

2008-06-13T02:35:00.000+03:00

The sleeping connections is just a bad example. It means either persistent connections have been used where they shouldn't have, or the app is behaving silly. Easy MySQL-side workaround for this is
SET GLOBAL wait_timeout=10;

No need to mess with scripts of any kind, anyway ;-)

(that example is for killing queries in the Sleep ...

2008-06-12T23:26:00.001+03:00

(that example is for killing queries in the Sleep mode)

For older systems, you can always use the commandl...

2008-06-12T23:26:00.000+03:00

For older systems, you can always use the commandline, combining mysql -e "show processlist" with grep for filtering and cut for getting the processid. I've done this often, with a shell script like:

for PID in `mysql -u user -ppass -e "show processlist" | grep Sleep | cut -f1`
do
mysql -u user -ppass -e "kill $PID"
done

Is that exclusive to 5.1? I'm not seeing it in 5....

2008-06-12T21:07:00.000+03:00

Is that exclusive to 5.1? I'm not seeing it in 5.0.51a.

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
ERROR 1109 (42S02): Unknown table 'PROCESSLIST' in information_schema