Завершить все sleep соединения в MySQL

Из-за плохого кода в php может скопиться довольно большое количество sleep соединений которые нагружают сервер и забьют допустимое число что чревато падению сайта, mysql а может даже и самого сервера по этому, нужно завершить все sleep соединения в MySQL  вовремя.

Смотрим сколько всего соединений:

# mysql -NBe 'show global status like "Threads_connected";' | cut -f2
409

ИЛИ:

# mysql -BNe "select host,count(host) from processlist group by host;" information_schema|wc -l
410

ИЛИ:

# mysql -BNe "show processlist;"|awk '{print $3}'|awk -F":" '{print $1}'|sort|uniq -c
16 172.80.27.16
1 172.80.27.195
184 172.80.27.30
207 172.80.27.53
1 localhost
#

ИЛИ:

# mysql -e "show processlist\G;" | egrep "Host\:" | awk -F: '{ print $2 }' | sort | uniq -c
14 172.80.27.16
1 172.80.27.195
184 172.80.27.30
208 172.80.27.53
1 localhost

Завершить все sleep соединения в MySQL

Можно для начала посмотреть сколько sleep соединений имеется в самом mysql:

# mysql -u [your_user] -p

Тем самым мы подключимся к серверу с mysql, нужно будет ввести пароль для этого.

Теперь, можно выполнить команду которая покажет все запросы:

mysql> show processlist;

Зная ID процесса, можно завершить его:

> kill 370878

Не сильно удобно если их наплодилось МНОГО! Ниже будут приведены готовые, простые решения.

Кстати! Самое простое, НО НЕ ХОРОШЕЕ РЕШЕНИЕ — это перезапустить mysql:

# service mysqld restart

PS: можно 2 команды что выше, объединить в одну:

# mysql -u root -p -e "show full processlist"

Можно записать вывод в файл для дальнейшего использования\анализа:

# mysql -e "show full processlist\G" > /root/processlist.txt

Можно посмотреть sleep-ы и по другому, немного больше параметров:

> select concat ('kill ',ID, ';') from information_schema.processlist where COMMAND='Sleep' and TIME > 50;
> select concat('KILL ',ID,';') from information_schema.processlist where user='root' and time > 200;
> select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep' into outfile '/tmp/process_list.txt';

Завершить все  sleep SELECT запросы можно выполнив команду:

> select concat('KILL ',id,';') from information_schema.processlist where user='root' and INFO like 'SELECT%' into outfile '/tmp/processlist_select_kill_queries.txt'; 
> source /tmp/processlist_select_kill_queries.txt;

Можно написать процедуру и потом ее использовать:

DROP PROCEDURE IF EXISTS  kill_user_processes$$ 

CREATE PROCEDURE `kill_user_processes`(
  IN user_to_kill VARCHAR(255)
)
READS SQL DATA
BEGIN

  DECLARE name_val VARCHAR(255);
  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;

  DECLARE friends_cur CURSOR FOR
    SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE USER=user_to_kill;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

  OPEN friends_cur;
  SELECT FOUND_ROWS() INTO num_rows;

  the_loop: LOOP

    FETCH  friends_cur
    INTO   name_val;

    IF no_more_rows THEN
        CLOSE friends_cur;
        LEAVE the_loop;
    END IF;


 SET @s = name_val;
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT name_val;

    SET loop_cntr = loop_cntr + 1;

  END LOOP the_loop;

  SELECT num_rows, loop_cntr;

END $$
DELIMITER ;

Чтобы ее использовать, используйте:

> kill_user_processes('your_DB_name');

Завершить все:

# mysqladmin pr -u <USERNAME> -p<PASSWORD> | awk '$2~/^[0-9]+/{print $2}' | xargs -i mysqladmin kill {}

Или:

# mysql -h<host> -u<user> -p -e "SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE Command = 'Sleep'" > sleep.txt
# cat sleep.txt | xargs -I% mysql -h<host> -u<user> -p -e "%"

PS: НУЖНО ПРОВЕРИТЬ 2 последних предложения завершить запросы в mysql! Я так не делал никогда!

Не заходя в сам mysql, можно использовать:

# mysql -e "select concat('KILL ',id,';') from information_schema.processlist where Command = 'Sleep' AND user = 'root' into outfile '/tmp/sleep_processes.txt'"
# mysql -e "source /tmp/sleep_processes.txt;"
# rm -rf /tmp/sleep_processes.txt

Вот еще один вариант как убить все заслипаные процессы:

for process_ID in `/usr/bin/mysqladmin processlist | grep -i sleep | awk '{print $2}'`; do /usr/bin/mysqladmin kill $process_ID; done

ИЛИ (для пользователя):

for process_ID in `/usr/bin/mysqladmin processlist -uYour_User_Here -pYour_PassWD_Here | grep -i sleep | awk '{print $2}'`; do /usr/bin/mysqladmin kill $process_ID; done

Вот еще один интересный пример. Эта команда может помочь убить все процессы сна:

# mysqladmin proc | grep Sleep | awk '{print $2}' | xargs -L1 mysqladmin kill

PS: Нашел ее на форуме, еще не успел проверить. Думаю что будет тоже работать.

Bash скрипты с реализацией

1. Вариант

#!/bin/bash

OUTPUT_FILE="/tmp/processlist.txt"
if [ ! -f "$OUTPUT_FILE" ]; then
    touch $OUTPUT_FILE
    chmod 755 $OUTPUT_FILE
else
    cat /dev/null > $OUTPUT_FILE
    chmod 755 $OUTPUT_FILE
fi

MYSQL=`which mysql`
$MYSQL -Bse "show full processlist\G" > $OUTPUT_FILE
Total_processes=$(cat `echo $OUTPUT_FILE`|grep -E "Command:"| cut -d: -f2|wc -l)
Sleep_processes=$(cat `echo $OUTPUT_FILE`|grep -E "Command:"| cut -d: -f2| grep -E "Sleep"|wc -l)

echo "------------------------------------------------------------------------------------";
echo -e "Total_processes=`echo $Total_processes` \nSleep_processes=`echo $Sleep_processes`";
echo "------------------------------------------------------------------------------------";

$MYSQL -e "
select
-- *
id
from information_schema.processlist
where command = 'Sleep'
and time > 1
and host ='localhost'
" | while read id;
do
if [[ "id" == "$id" ]]
then
continue
fi
echo "kill $id";
$MYSQL  -e "kill $id";
done

Вот и все, статья «Завершить все sleep соединения в MySQL» завершена.