MySQL 各クライアントの接続数を簡単に集計する
MySQLのプロセス状況を確認するには、show processlist コマンドで確認することはできるのですが、じゃあ、各接続クライアント毎のサマリを出したい場合はどうすんの?と言ったときに役立つシェルを作ってみました。
結局、show processlistで取得される結果は、information_schema.processlistにあります。このinformation_schema.processlist
の定義はこんな感じになっております、あぁ、テンポラリテーブルなのね。ということでこの部分の説明は割愛です。
[SQL]
CREATE TEMPORARY TABLE PROCESSLIST
(
ID
bigint(4) NOT NULL DEFAULT '0',
USER
varchar(16) NOT NULL DEFAULT '',
HOST
varchar(64) NOT NULL DEFAULT '',
DB
varchar(64) DEFAULT NULL,
COMMAND
varchar(16) NOT NULL DEFAULT '',
TIME
int(7) NOT NULL DEFAULT '0',
STATE
varchar(64) DEFAULT NULL,
INFO
longtext
) ENGINE=MyISAM DEFAULT CHARSET=utf8
[/SQL]
今回の例では、SQLが実行中だかスリープなんだかはあまり関係なく、単純に各クライアントがどれくらいのコネクションを保有しているのか確認したかっただけなので、USER、HOST,DBだけ分かればいいや(^_^)・・なんて感じで作ったのが以下のシェルです。
[SQL] ELECT X.USER, X.HOST, X.DB, COUNT(X.ID) AS CNT FROM ( SELECT USER, REPLACE(HOST,MID(HOST,INSTR(HOST,':'),LENGTH(HOST)),'') AS HOST, DB, ID FROM INFORMATION_SCHEMA.PROCESSLIST ) AS X GROUP BY X.USER, X.HOST, X.DB ORDER BY X.HOST ASC ; [/SQL]
SQL分中の下記箇所は、ポート番号の指定ぽいので、削除してます。(必要な人は必要) [SQL] REPLACE(HOST,MID(HOST,INSTR(HOST,':'),LENGTH(HOST)),'') AS HOST [/SQL]
んで、上記のSQLをSHELLに含めめるとこんな感じになります。
[SHELL]
cat ./mysql_process_summary.sh
!/bin/bash
DATE=date +"%Y%m%d"
HOSTNAME=hostname -a
DB Config
DBUSER=mysqluser DBPASSWD=mypassword
mysql -u ${DBUSER} -p${DBPASSWD} << EOF SELECT X.USER, X.HOST, X.DB, COUNT(X.ID) AS CNT FROM ( SELECT USER, REPLACE(HOST,MID(HOST,INSTR(HOST,':'),LENGTH(HOST)),'') AS HOST, DB, ID FROM INFORMATION_SCHEMA.PROCESSLIST ) AS X GROUP BY X.USER, X.HOST, X.DB ORDER BY X.HOST ASC ; EOF
exit 0 [/SHELL]
でわ、shellを実行してみましょう、以下のような結果になりました。各列は、左から順に、接続ユーザー名、接続元ホスト名、スキーマ名、接続カウント数になります。
[SHELL]
./mysql_process_summary.sh
user host db cnt myuser 192.168.101.XXX db01 2 myuser 192.168.101.XXX db01 1 myuser 192.168.101.XXX db01 2 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 5 myuser 192.168.101.XXX db01 5 slave 192.168.101.XXX NULL 1 myuser 192.168.101.XXX db01 5 myuser 192.168.101.XXX db01 5 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 2 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 5 myuser 192.168.101.XXX db01 5 myuser 192.168.101.XXX db01 2 myuser 192.168.101.XXX db01 2 myuser 192.168.101.XXX db01 2 sysopr localhost NULL 1 [/SHELL]
単純なSQLなんですが、ちょっとした調査にはとても便利!!