如何例行杀死已经存活“太长时间"的MySQL查询? [英] How do I routinely kill MySQL queries that have been alive for "too long"?

问题描述

如何例行终止存活时间过长的MySQL查询?

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?

将问题从终止连接"更新为终止查询"

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

推荐答案

MySQL 5.0.x仅支持"SHOW FULL PROCESSLIST"命令.无法将进程列表视为SQL表来查询和过滤，例如选择* FROM INFORMATION_SCHEMA.PROCESSLIST.此功能已添加到MySQL 5.1 +

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有一个KILL命令，该命令可以终止查询或整个连接. http://dev.mysql.com/doc/refman/5.0/zh/kill.html

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

仍然，您需要一个运行"SHOW FULL PROCESSLIST"的Ruby或Perl脚本，确定哪些查询运行时间过长"，然后发出适当的KILL命令.

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

这篇关于如何例行杀死已经存活“太长时间"的MySQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！