MySQLの接続状況の確認

運用中のMySQLに過去データを大量投入する必要があったため、接続状況を確認する方法を調べたのでメモ。

概略

1) \s にて、MySQLの稼働状況をチェックできる。 (今回の件とは直接関係なし)
2) show processlistにて、接続プロセス数、接続時間などを確認できる。
 　 接続時間(下記でいうところのTime)は、最終応答からの経過時間。

サンプル

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:          132877
Current database:       sampledb
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.73 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 61 days 17 hours 51 min 29 sec

Threads: 19  Questions: 99114337  Slow queries: 531  Opens: 2600916  Flush tables: 1  Open tables: 64  Queries per second avg: 18.579
--------------

mysql> show processlist;
+--------+-------+----------------------------+-----------+---------+------+-------+------------------+
| Id     | User | Host                        | db        | Command | Time | State | Info             |
+--------+------+-----------------------------+-----------+---------+------+-------+------------------+
| 132709 | app  | location.domain.ne.jp:10486 | sampledb  | Sleep   | 7045 |       | NULL             |
| 132710 | app  | location.domain.ne.jp:10588 | sampledb  | Sleep   | 6952 |       | NULL             |
| 132711 | app  | location.domain.ne.jp:10625 | sampledb  | Sleep   | 6717 |       | NULL             |
| 132712 | app  | location.domain.ne.jp:10840 | sampledb  | Sleep   | 5968 |       | NULL             |
| 132713 | app  | location.domain.ne.jp:10841 | sampledb  | Sleep   | 6169 |       | NULL             |
| 132714 | app  | location.domain.ne.jp:10915 | sampledb  | Sleep   | 6395 |       | NULL             |
| 132715 | app  | location.domain.ne.jp:10006 | sampledb  | Sleep   | 5986 |       | NULL             |
| 132716 | app  | location.domain.ne.jp:10062 | sampledb  | Sleep   | 5666 |       | NULL             |
| 132717 | app  | location.domain.ne.jp:10168 | sampledb  | Sleep   | 5666 |       | NULL             |
| 132718 | app  | location.domain.ne.jp:10429 | sampledb  | Sleep   | 5583 |       | NULL             |
| 132719 | app  | location.domain.ne.jp:10464 | sampledb  | Sleep   | 5577 |       | NULL             |
| 132870 | app  | remote.domain.ne.jp:33063   | NULL      | Sleep   |  475 |       | NULL             |
| 132871 | app  | remote.domain.ne.jp:41295   | sampledb  | Sleep   |  396 |       | NULL             |
| 132872 | app  | remote.domain.ne.jp:38509   | sampledb  | Sleep   |  470 |       | NULL             |
| 132873 | app  | remote.domain.ne.jp:37658   | sampledb  | Sleep   |  420 |       | NULL             |
| 132874 | app  | remote.domain.ne.jp:39027   | sampledb  | Sleep   |  370 |       | NULL             |
| 132875 | app  | location.domain.ne.jp:10031 | sampledb  | Sleep   |  165 |       | NULL             |
| 132876 | app  | location.domain.ne.jp:10032 | sampledb  | Sleep   |  203 |       | NULL             |
| 132877 | root | localhost                   | sampledb  | Query   |    0 | NULL  | show processlist |
+--------+-------+----------------------------+-----------+---------+------+-------+------------------+
19 rows in set (0.00 sec)

mysql>