MySQLで、遅い・重いSQL文が実行されていて(CPU100%)、キャンセルしたい時はコマンドラインで接続して、重いSQL文をkillする
参考URL
topコマンドを実行すると、mysqldがcpu100%！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
top top - 02:14:08 up 41 days, 17:47, 0 users, load average: 1.06, 1.06, 0.73 Tasks: 121 total, 1 running, 120 sleeping, 0 stopped, 0 zombie %Cpu(s): 50.5 us, 0.2 sy, 0.0 ni, 49.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 3878644 total, 536884 free, 1885984 used, 1455776 buff/cache KiB Swap: 0 total, 0 free, 0 used. 1721604 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 16360 mysql 20 0 1951076 994036 7984 S 100.3 25.6 688:34.18 mysqld 19674 centos 20 0 951256 30516 17152 S 0.7 0.8 0:06.28 node 8685 root 20 0 525900 98528 14336 S 0.3 2.5 141:46.33 mms 10478 root 20 0 1749528 26932 6236 S 0.3 0.7 279:10.08 aakore 14431 centos 20 0 925304 34744 5564 S 0.3 0.9 6:19.57 node |
コマンドラインからmysql接続。
SHOW PROCESSLIST; で重いSQL文のIDを特定
kill ID番号 で強制終了
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql -u root -p mysql> SHOW PROCESSLIST; +-------+-----------------+-----------------+-------------+---------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+-----------------+-----------------+-------------+---------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 3606519 | Waiting on empty queue | NULL | | 18756 | apilaravel8 | localhost:52540 | apilaravel8 | Execute | 760 | executing | select count(*) as aggregate from `users` where | 18784 | apilaravel8 | localhost:53592 | apilaravel8 | Execute | 352 | Waiting for table metadata lock | drop table | 18785 | apilaravel8 | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | +-------+-----------------+-----------------+-------------+---------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) |
待っているとタイムアウトして自動的にkillされるけど、SQL文が実行されない！って時には試してみるのも良さげ