208
182

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

MySQL DBのコネクション数の確認とか

Last updated at Posted at 2012-12-26

mysql クライアント等で以下を実行

現在の接続しているスレッド数

mysql> show status like 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 3     |
+-------------------+-------+
1 row in set (0.00 sec)

あるいは SELECT文 で

> SELECT * FROM information_schema.PROCESSLIST;

プロセスリスト(処理中の接続)を表示する

mysql> show processlist;
+------+------+-----------------+---------------------------+---------+------+-------+------------------+
| Id   | User | Host            | db                        | Command | Time | State | Info             |
+------+------+-----------------+---------------------------+---------+------+-------+------------------+
|   68 | root | localhost:36562 | hogehogehogehogehogehogeh | Sleep   |   30 |       | NULL             |
|   69 | root | localhost:36563 | NULL                      | Sleep   |   40 |       | NULL             |
| 2268 | root | localhost       | hogehogehogehogehogehogeh | Query   |    0 | init  | show processlist |
+------+------+-----------------+---------------------------+---------+------+-------+------------------+
3 rows in set (0.00 sec)

不要なコネクションをkill

mysql> kill 69;
Query OK, 0 rows affected (0.00 sec)

60秒以上経過しているコネクションを抽出

RDS の場合'rdsadmin'は除外

mysql> SELECT * FROM information_schema.PROCESSLIST WHERE time >= 60 and user != 'rdsadmin';

そのIDをカンマ区切りで抽出

mysql> SELECT GROUP_CONCAT(id) FROM information_schema.PROCESSLIST WHERE time >= 60 and user != 'rdsadmin';

まとめて kill

$ mysqladmin kill xx,xx,xx -h host -u user -p

(おまけ)起動してからの累積接続数

mysql> show status like 'Conn%'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 85    |
+---------------+-------+
1 row in set (0.01 sec)

(おまけ)プライマリ/レプリカの判別

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';

プライマリは OFF, レプリカは ON

参考

208
182
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
208
182

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?