How do I routinely kill MySQL queries that have been alive for "too long"?
我如何定期杀死已经存活“太久”的MySQL查询?
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"
编辑:从“查杀连接”到“查询查询”的更新问题
4 个解决方案
#1
Install the RubyGem mysql_manager (sudo gem install mysql_manager) and then run a command like this:
安装RubyGem mysql_manager(sudo gem install mysql_manager),然后运行如下命令:
mysql-manager --kill --kill:user api --kill:max-query-time 30 --log:level DEBUG
For more options, run mysql-manager --help.
有关更多选项,请运行mysql-manager --help。
You might need to specify an alternative --db:dsn, --db:username, or --db:password.
您可能需要指定替代方法--db:dsn, - db:username或--db:password。
Read more about it here: https://github.com/osterman/mysql_manager
在这里阅读更多相关信息:https://github.com/osterman/mysql_manager
#2
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.)
但是,您不应该只是终止连接,因为这可能会导致数据完整性问题。相反,您应该使用流程列表输出作为在源头纠正问题之前突出显示潜在问题的方法。 (在这个意义上,它是一个(非常)穷人的MySQL企业监控器。)
#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 5.0.x仅支持“SHOW FULL PROCESSLIST”命令。没有能力查询和过滤进程列表,因为它是一个SQL表,例如SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST。 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
MySQL有一个KILL命令可以杀死查询或整个连接。 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.
但是,您需要一个运行“SHOW FULL PROCESSLIST”的Ruby或Perl脚本,识别哪些查询运行“太长”,然后发出相应的KILL命令。
You can also do this from the command line, e.g.
您也可以从命令行执行此操作,例如
mysqladmin processlist
mysqladmin kill
#4
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;"
命令“mysqladmin processlist”将显示当前连接,“时间”列指示自上次活动以来的时间。您可以使用SQL命令“SHOW FULL PROCESSLIST;”执行相同的操作。
#1
Install the RubyGem mysql_manager (sudo gem install mysql_manager) and then run a command like this:
安装RubyGem mysql_manager(sudo gem install mysql_manager),然后运行如下命令:
mysql-manager --kill --kill:user api --kill:max-query-time 30 --log:level DEBUG
For more options, run mysql-manager --help.
有关更多选项,请运行mysql-manager --help。
You might need to specify an alternative --db:dsn, --db:username, or --db:password.
您可能需要指定替代方法--db:dsn, - db:username或--db:password。
Read more about it here: https://github.com/osterman/mysql_manager
在这里阅读更多相关信息:https://github.com/osterman/mysql_manager
#2
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.)
但是,您不应该只是终止连接,因为这可能会导致数据完整性问题。相反,您应该使用流程列表输出作为在源头纠正问题之前突出显示潜在问题的方法。 (在这个意义上,它是一个(非常)穷人的MySQL企业监控器。)
#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 5.0.x仅支持“SHOW FULL PROCESSLIST”命令。没有能力查询和过滤进程列表,因为它是一个SQL表,例如SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST。 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
MySQL有一个KILL命令可以杀死查询或整个连接。 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.
但是,您需要一个运行“SHOW FULL PROCESSLIST”的Ruby或Perl脚本,识别哪些查询运行“太长”,然后发出相应的KILL命令。
You can also do this from the command line, e.g.
您也可以从命令行执行此操作,例如
mysqladmin processlist
mysqladmin kill
#4
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;"
命令“mysqladmin processlist”将显示当前连接,“时间”列指示自上次活动以来的时间。您可以使用SQL命令“SHOW FULL PROCESSLIST;”执行相同的操作。