GIGAZINEさまの記事 MySQLの状態がリアルタイムでわかるMySQL用topコマンド「mytop」と「innotop」 に触発されて、mytopを入れてみた。GIGAZINEさまの記事ではソースからコンパイルしているが、なんでもrpmで入れたい派の私は、rpmforgeからパッケージを探すことにした。
■■ パッケージを探す
sh-3.00# yum --enablerepo=rpmforge search mytop
Loading "fastestmirror" plugin
Searching Packages:
Setting up repositories
Loading mirror speeds from cached hostfile
Reading repository metadata in from local files
Excluding Packages in global exclude list
Finished
mytop.noarch 1.4-1.el4.rf rpmforge
Matched from:
mytop
mytop is a console-based (non-gui) tool for monitoring the threads and
overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server. It runs
on most Unix systems (including Mac OS X) which have Perl, DBI, and
Term::ReadKey installed. And with Term::ANSIColor installed you even
get color. If you install Time::HiRes, you'll get good real-time
queries/second stats. As of version 0.7, it even runs on Windows
(somewhat).
http://jeremy.zawodny.com/mysql/mytop/
いつものように、rpmforgeをenableにしてインストールする。
■■ インストール
sh-3.00# yum --enablerepo=rpmforge install mytop
Loading "fastestmirror" plugin
Setting up Install Process
Setting up repositories
Loading mirror speeds from cached hostfile
Reading repository metadata in from local files
Excluding Packages in global exclude list
Finished
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for mytop to pack into transaction set.
mytop-1.4-1.el4.rf.noarch 100% |=========================| 2.9 kB 00:00
---> Package mytop.noarch 0:1.4-1.el4.rf set to be updated
--> Running transaction check
Dependencies Resolved
=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
mytop noarch 1.4-1.el4.rf rpmforge 31 k
Transaction Summary
=============================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)
Total download size: 31 k
Is this ok [y/N]: y
Downloading Packages:
(1/1): mytop-1.4-1.el4.rf 100% |=========================| 31 kB 00:00
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: mytop ######################### [1/1]
Installed: mytop.noarch 0:1.4-1.el4.rf
Complete!
■■ mytopパッケージに含まれるファイルを調べる
sh-3.00$ rpm -ql mytop
/usr/bin/mytop
/usr/share/doc/mytop-1.4
/usr/share/doc/mytop-1.4/Changes
/usr/share/doc/mytop-1.4/INSTALL
/usr/share/doc/mytop-1.4/MANIFEST
/usr/share/doc/mytop-1.4/README
/usr/share/man/man1/mytop.1.gz
■■ まずは使ってみる
sh-3.00$ mytop
Can't locate Term/ReadKey.pm in @INC (@INC contains: /usr/lib/perl5/5.8.5/i386-linux-thread-multi /usr/lib/perl5/5.8.5 /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.2/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.1/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl/5.8.4 /usr/lib/perl5/site_perl/5.8.3 /usr/lib/perl5/site_perl/5.8.2 /usr/lib/perl5/site_perl/5.8.1 /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.2/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.1/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl/5.8.4 /usr/lib/perl5/vendor_perl/5.8.3 /usr/lib/perl5/vendor_perl/5.8.2 /usr/lib/perl5/vendor_perl/5.8.1 /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl .) at /usr/bin/mytop line 165.
おやおや、Term/ReadKey.pm がないって怒られてしまった。というわけで、こいつもrpmforgeから探してインストールする。
sh-3.00# yum --enablerepo=rpmforge search ReadKey
sh-3.00# yum --enablerepo=rpmforge install perl-Term-ReadKey
Loading "fastestmirror" plugin
Setting up Install Process
Setting up repositories
Loading mirror speeds from cached hostfile
Reading repository metadata in from local files
Excluding Packages in global exclude list
Finished
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for perl-Term-ReadKey to pack into transaction set.
perl-Term-ReadKey-2.30-2. 100% |=========================| 3.9 kB 00:00
---> Package perl-Term-ReadKey.i386 0:2.30-2.el4.rf set to be updated
--> Running transaction check
Dependencies Resolved
=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
perl-Term-ReadKey i386 2.30-2.el4.rf rpmforge 31 k
Transaction Summary
=============================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)
Total download size: 31 k
Is this ok [y/N]: y
Downloading Packages:
(1/1): perl-Term-ReadKey- 100% |=========================| 31 kB 00:01
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: perl-Term-ReadKey ######################### [1/1]
Installed: perl-Term-ReadKey.i386 0:2.30-2.el4.rf
Complete!
■■ あらためて使ってみる
sh-3.00$ mytop
Cannot connect to MySQL server. Please check the:
* database you specified "test" (default is "test")
* username you specified "root" (default is "root")
* password you specified "" (default is "")
* hostname you specified "localhost" (default is "localhost")
* port you specified "3306" (default is 3306)
* socket you specified "" (default is "")
The options my be specified on the command-line or in a ~/.mytop
config file. See the manual (perldoc mytop) for details.
Here's the exact error from DBI. It might help you debug:
Access denied for user 'root'@'localhost' (using password: NO)
なるほど、データベース名とユーザ、パスワードを入れなきゃだめなのか。
詳しい使い方を見てみよう
sh-3.00$ perldoc mytop
とりあえず最低限の使い方は
Arguments
mytop handles long and short command-line arguments. Not all options have both long and short formats, however. The
long arguments can start with one or two dashes ‘-’ or ‘--’. They are shown here with just one.
-u or -user username
&n
bsp; Username to use when logging in to the MySQL server. Default: ‘‘root’’.
-p or -pass or -password password
Password to use when logging in to the MySQL server. Default: none.
-h or -host hostname[:port]
Hostname of the MySQL server. The hostname may be followed by an option port number. Note that the port is
specified separate from the host when using a config file. Default: ‘‘localhost’’.
-port or -P port
If you’re running MySQL on a non-standard port, use this to specify the port number. Default: 3306.
-s or -delay seconds
How long between display refreshes. Default: 5
-d or -db or -database database
Use if you’d like mytop to connect to a specific database by default. Default: ‘‘test’’.
このくらいか。
例えば、MySQLのrootユーザになってpostfixの認証データベースを見てみたい場合には
sh-3.00$ mytop -uroot -p<パスワード> -dpostfix -s1
として実行してやれば、postfixのデータベースを1秒おきに見てくれる。
実行結果は以下の通り。下記はあくまで実行時の例である。
====================================================================================================
MySQL on localhost (4.1.20) up 15+12:42:15 [12:46:08]
Queries: 113.8k qps: 0 Slow: 0.0 Se/In/Up/De(%): 91/00/00/00
qps now: 2 Slow qps: 0.0 Threads: 2 ( 1/ 0) 00/00/00/00
Key Efficiency: 100.0% Bps in/out: 16.3/121.6 Now in/out: 42.0/ 4.8k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
4457 root localhost postfix 0 Query show full processlist
4451 postfix localhost postfix 376 Sleep
====================================================================================================
しかし、いちいちコマンドラインに直接パスワード入れるってのどうなの?と思うのが人の常。自分さえ忘れなきゃいいとかそういう問題じゃなくて、historyから見られてしまったりするのが嫌だ。(ほかのユーザがrootになって、.bash_historyを直接見られるのは仕方ないにしても)せめて画面にパスワードが出るのだけは避けたい。というわけで、何か方法はないかと探してみる。
sh-3.00$ perldoc mytop
Config File
Instead of always using bulky command-line parameters, you can also use a config file in your home directory
("~/.mytop"). If present, mytop will read it automatically. It is read before any of your command-line arguments
are processed, so your command-line arguments will override directives in the config file.
Here is a sample config file "~/.mytop" which implements the defaults described above.
user=root
pass=
host=localhost
db=test
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1
あったよ!というわけで、ホームディレクトリの下に「.mytop」ファイルを作って、そこに書いてやる。
sh-3.00$ vi .mytop
user=root
pass=(パスワードを記述)
host=localhost
db=
delay=1
port=3306
socket=
batchmode=0
header=1
color=1
idle=1
sh-3.00$ chmod 600 .mytop
これで自分とroot以外は読み書きできない。
なお、複数のDBを見たいので、dbの欄は空欄にしておいて、mytopコマンド実行時に、都度引数を与えてあげることにする。
sh-3.00$ mytop -d postfix
これで、さっきの画面が出てくるという寸法。ちなみに、どうせ見るなら最初から入っているDB「mysql」を引数に与えてやって見たほうがよいかも知れない。また、mytop実行時に、ThunderbirdとFFFTPを立ち上げて、それぞれ認証をやらせてみると
sh-3.00$ mytop -d mysql
====================================================================================================
MySQL on localhost (4.1.20) up 0+00:01:52 [13:08:52]
Queries: 217.0 qps: 2 Slow: 0.0 Se/In/Up/De(%): 05/00/00/00
qps now: 2 Slow qps: 0.0 Threads: 3 ( 1/ 0) 00/00/00/00
Key Efficiency: 76.9% Bps in/out: 55.9/ 4.3k Now in/out: 42.0/ 4.6k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
3 root localhost mysql 0
Query show full processlist
5 proftpd localhost proftpd 9 Sleep
4 postfix localhost postfix 24 Sleep
====================================================================================================
このように表示される。その他、運用上の注意としては以下の通り。
- 非常に時間のかかるクエリの場合、Explainするのにも時間がかかる場合もある。
- 運用環境では f コマンドで全文をチェックして開発環境でExplainするなど、運用環境にあまり負荷がかからないように工夫するのがいい。
-- 引用元は株式会社フラッツさま --
mytopを応用すれば、サーバ監視の精度も上がることが期待できそうだ。