mysql 查询平均时间长,MySQL 管理长时间运行查询

select concat (‘kill ‘,id,‘;‘) from

information_schema.processlist

where time >= 2

-- and user = ‘业务账号‘

and command not in (‘sleep‘,‘Connect‘)

and state not like (‘waiting for table%lock‘);

and info like ‘%Metabase%‘

RDS提供的存储过程：

create event my_long_running_query_monitor

on schedule every 5 minute

starts ‘2015-09-15 11:00:00‘

on completion preserve enable do

begin

declare v_sql varchar(500);

declare no_more_long_running_query integer default 0;

declare c_tid cursor for

select concat (‘kill ‘,id,‘;‘) from

information_schema.processlist

where time >= 3600

and user = substring(current_user(),1,instr(current_user(),‘@‘)-1)

and command not in (‘sleep‘)

and state not like (‘waiting for table%lock‘);

declare continue handler for not found

set no_more_long_running_query=1;

open c_tid;

repeat

fetch c_tid into v_sql;

set @v_sql=v_sql;

prepare stmt from @v_sql;

execute stmt;

deallocate prepare stmt;

until no_more_long_running_query end repeat;

close c_tid;

end;

RDS中的参数

loose_max_statement_time

管理长查询的shell脚本

#!/bin/bash

password=xxxxxx

mysql -uroot -p$password -N -s -e "select concat (‘kill ‘,id,‘;‘) from

information_schema.processlist

where time >= 300

-- and user = ‘业务账号‘

and command not in (‘sleep‘,‘Connect‘)

and state not like (‘waiting for table%lock‘);" > killmysqlsession.txt

#cat killmysqlsession.txt | while read line

#do

#echo $line

#mysql -uroot -p$password -e "$line"

#done

mysql -uroot -p$password < killmysqlsession.txt

#或者登陆实例source killmysqlsession.txt