MySQLに重いSQLを投げて、帰って来ない時は
重い集計SQLなんかを実行したけど、いつまでたっても帰ってこない時の対処についてです。
まずは、topコマンドで確認します。大抵、以下の例のように、mysqldがリソースを使い切っています。
[root]# top top - 16:20:17 up 15 days, 6:50, 1 user, load average: 5.39, 5.14, 5.04 Tasks: 177 total, 2 running, 175 sleeping, 0 stopped, 0 zombie Cpu(s): 33.7%us, 66.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.3%st Mem: 1048752k total, 826136k used, 222616k free, 194584k buffers Swap: 562264k total, 4492k used, 557772k free, 277856k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2640 mysql 15 0 85704 42m 3664 S 398.6 4.1 2120:30 mysqld 1 root 15 0 2084 656 560 S 0.0 0.1 0:00.03 init 2 root RT -5 0 0 0 S 0.0 0.0 0:00.44 migration/0
そこで、まず、MySQLに接続し「SHOW PROCESSLIST」コマンド結果を確認します。
[root]# mysql --defaults-file=/db/mysql/data/my.cnf -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22802 Server version: 5.1.34-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW PROCESSLIST; +----+------+-----------------+------+---------+-------+----------------------+----------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+-------+----------------------+----------------------------------+ | 10 | root | localhost:46214 | test | Query | 23852 | Copying to tmp table | SELECT COUNT(*) FROM `accesslog` | | 12 | root | localhost:60469 | test | Query | 20332 | Waiting for table | TRUNCATE TABLE `accesslog` |
これは、一つ目の重いSQLが抜けないために、その後のTRUNCATE文がずっと待たされている状態と言えます。なので、この重いSQLをkillします。killコマンドは、「kill (対象SQLの)Id」の構文で実行します。
mysql> kill 10; Query OK, 0 rows affected (0.00 sec) mysql> SHOW PROCESSLIST; +----+------+-----------+------+---------+-------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+-------+-------+------------------+ | 10 | root | localhost | test | Query | 23852 | NULL | SHOW PROCESSLIST |
そうすると、おそらく、待たされていた後続のSQLも流れて、PROCESSLISTには何も出なくなったりすると思います。
（補足）SHOW PROCESSLISTコマンドについて
詳しくは、http://dev.mysql.com/doc/refman/5.1-olh/ja/show-processlist.html を参照してください。
出力カラムは次の意味を持っています。
- Id : 接続識別子。
- User : ステートメントを発行した MySQL ユーザ
- Host : ステートメントを発行しているクライアントのホスト名
- db : デフォルトデータベース
- Command : スレッドが実行しているコマンドのタイプ
- 時刻 : スレッドが現在の状態になってからの秒数
- State : スレッドの状態*1
- Info : スレッドが実行中のステートメント
FULL オプションについて
通常、Info 列に表示されるクエリは１００文字を越えると省略されますが、FULL オプションを指定すると、省略せずクエリの全文が表示されます。その際、表示が崩れるようであれば\G オプションでクエリ結果を垂直表示すると見やすくなります。
mysql> SHOW FULL PROCESSLIST \G;
