How do I routinely kill MySQL queries that have been alive for "too long"?

Question

How do I routinely kill MySQL queries that have been alive for "too long"?

Is there a system information table of sorts that shows all current queries, and their age?

---

Edit: updated question from "killing connections" to "killing queries"

Answer 1

You can execute...

SHOW FULL PROCESSLIST;

...to show you the currently executing queries.

However, you shouldn't just kill the connections, as this may cause data integrity issues. Instead, you should use the processlist output as a means of highlighting where potential problems may lie prior to correcting the issues at source. (It's sort of a (very) poor man's MySQL Enterprise Monitor in that sense.)

Answer 2

The command "mysqladmin processlist" will show the current connection, and a Time column which indicates the time since last activity. You can do the same with the SQL command "SHOW FULL PROCESSLIST;"

Answer 3

MySQL 5.0.x only supports the “SHOW FULL PROCESSLIST” command. There’s no ability to query and filter the process list as thought it were a SQL table, e.g. SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST. This ability was added in MySQL 5.1+

MySQL has a KILL command that can kill either a query or the entire connection. http://dev.mysql.com/doc/refman/5.0/en/kill.html

Still, you’d need a Ruby or Perl script that runs “SHOW FULL PROCESSLIST”, identifies which queries are running “too long”, then issues the appropriate KILL commands.

You can also do this from the command line, e.g.

mysqladmin processlist
mysqladmin kill