DBサーバが過負荷に陥った時、ボトルネックとなっているクエリを一気に消したいことがあります。

show processlist で確認はできるのですが、そこから一気に消す方法について記録しておきます。
全てを消してしまうとまずいこともあるのでwhereで絞りながら一気に消します。

利用には mysqladmin と　xargs を使っています。

下記のようにして行います。

information_schema.processlist から where を使って select （バージョンによってはperformance_schemaかもしれません）
xargs と mysqladmin kill で全てkill

もちろんですが、ボトルネックの原因となっているクエリ自体を見直さないと問題の解決になりません。

準備

サーバ接続用の環境変数を設定しておきます。

export DB_HOST=mysql.example.com
export DB_USER=username
export DB_PASS=password

一覧の確認

まずはどういったプロセスが動いているか確認します。

$ mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASS} -e "SELECT * FROM information_schema.processlist where command != 'Sleep'\G"

...
*************************** 1. row ***************************
     ID: 123
   USER: username
   HOST: 10.10.10.10:1010
     DB: dbname
COMMAND: Execute
   TIME: 4
  STATE: Sending data
   INFO: SELECT * from users;
...

該当クエリの調整

killしたい対象のプロセスを絞り込むようなWHERE句を書きます。下記ではusersが含まれる実行中のプロセスを抜き出しています。

$ export DB_WHERE="info like '%users%'"

$ mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASS} -e "SELECT * FROM information_schema.processlist where command != 'Sleep' and ${DB_WHERE};"

IDだけをうまく抜き出すようにします。パイプで渡すと出力される枠などはきれいに無くなりxargsに渡せるようになります。
-Nでヘッダーが消えます。

$ mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASS} -N -e "SELECT ID FROM information_schema.processlist where command != 'Sleep' and ${DB_WHERE};"  | cat

kill

xargsを使ってkillします。

$ mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASS} -N -e "SELECT ID FROM information_schema.processlist where command != 'Sleep' and ${DB_WHERE};"  | xargs -n 1 mysqladmin kill -h${DB_HOST} -u${DB_USER} -p${DB_PASS}

良いインフラライフをお送りください。