在本文中，我们将学习如何安装和配置 Mytop 来监控 MySQL 性能。Mytop 是一个开源的 MySQL 性能监控工具，这个工具使用命令行来监控 MySQL，它看起来像 Linux 系统监控工具 top，它会连接 MySQL 并运行 show process list 和 show global status 命令并汇总信息以人类可以理解的意义完整的格式。我们可以使用 mtop 来监控 MySQL 实时线程、正常运行时间和查询，还会显示运行查询的用户和他们正在使用的数据库，这些信息可用于性能调整。
为了完成这个演示，我们需要 CentOS 7 64 位和一个具有 root 权限的用户，安装 MySQL。
由于 Mytop 无法从 Centos 存储库中获得，我们需要在服务器上安装 Extra package for Enterprises Linux (EPEL)。该 EPEL 存储库由维护、创建和管理用于 Linux 的高质量开源附加包集的团队维护。
下面是在服务器机器上安装和启用 EPEL 存储库的命令。
# yum install epel-release output Loaded plugins: fastestmirror Dependencies Resolved ======================================================================================================================= Package Arch Version Repository Size ======================================================================================================================= Updating: epel-release noarch 7-7 epel 14 k Transaction Summary ======================================================================================================================= Upgrade 1 Package Total download size: 14 k Is this ok [y/d/N]: y Downloading packages: Delta RPMs disabled because /usr/bin/applydeltarpm not installed. warning: /var/cache/yum/x86_64/7/epel/packages/epel-release-7-7.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY Public key for epel-release-7-7.noarch.rpm is not installed epel-release-7-7.noarch.rpm | 14 kB 00:00:02 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 Importing GPG key 0x352C64E5: Userid : "Fedora EPEL (7) <epel@fedoraproject.org>" Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5 Package : epel-release-7-6.noarch (@extras) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 Is this ok [y/N]: y Running transaction check Running transaction test Transaction test succeeded Running transaction Updating : epel-release-7-7.noarch 1/2 Cleanup : epel-release-7-6.noarch 2/2 Verifying : epel-release-7-7.noarch 1/2 Verifying : epel-release-7-6.noarch 2/2 Updated: epel-release.noarch 0:7-7 Complete!
要验证 yum 存储库列表是否更新，我们可以使用以下命令
# yum repolist output yumrepolist Loaded plugins: fastestmirror Determining fastest mirrors epel/metalink | 12 kB 00:00 * base: linux.cc.lehigh.edu * epel: ftp.osuosl.org * extras: mirror.fusioncloud.co * remi-safe: fr.mirror.babylon.network * rpmforge: mirror.lug.udel.edu * updates: centos.mirror.constant.com base | 3.7 kB 00:00 base/primary_db | 4.7 MB 00:00 epel | 4.3 kB 00:00 epel/primary_db | 5.8 MB 00:00 extras | 3.4 kB 00:00 extras/primary_db | 36 kB 00:00 remi-safe | 2.9 kB 00:00 remi-safe/primary_db | 285 kB 00:00 rpmforge | 1.9 kB 00:00 updates | 3.4 kB 00:00 updates/primary_db | 726 kB 00:00 repo id repo name status WandiscoSVNWandisco SVN Repo 73 base CentOS-6 - Base 6,696 epel Extra Packages for Enterprise Linux 6 - x86_64 12,156 extras CentOS-6 - Extras 60 remi-safe Safe Remi's RPM repository for Enterprise Linux 6 - x86_64 659 rpmforge RHEL 6 -RPMforge.net- dag 245 updates CentOS-6 - Updates 131 repolist: 20,000
由于我们已经更新了服务器上的 EPEL 存储库，现在我们可以使用以下命令使用 EPEL 存储库安装 mytop 包。
# yum install mytop –y output Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: centos.webwerks.com * epel: mirror.rise.ph * extras: centos.webwerks.com * updates: centos.webwerks.com Resolving Dependencies --> Running transaction check …. …. …. Dependency Installed: perl.x86_64 4:5.16.3-286.el7 perl-Carp.noarch 0:1.26-244.el7 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBD-MySQL.x86_64 0:4.023-5.el7 perl-DBI.x86_64 0:1.627-4.el7 perl-Data-Dumper.x86_64 0:2.145-3.el7 perl-Encode.x86_64 0:2.51-7.el7 perl-Exporter.noarch 0:5.68-3.el7 perl-File-Path.noarch 0:2.09-2.el7 perl-File-Temp.noarch 0:0.23.01-3.el7 perl-Filter.x86_64 0:1.49-3.el7 perl-Getopt-Long.noarch 0:2.40-2.el7 perl-HTTP-Tiny.noarch 0:0.033-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7 perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PathTools.x86_64 0:3.40-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7 perl-Pod-Escapes.noarch 1:1.04-286.el7 perl-Pod-Perldoc.noarch 0:3.20-4.el7 perl-Pod-Simple.noarch 1:3.28-4.el7 perl-Pod-Usage.noarch 0:1.63-3.el7 perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 perl-Socket.x86_64 0:2.010-3.el7 perl-Storable.x86_64 0:2.45-3.el7 perl-TermReadKey.x86_64 0:2.30-20.el7 perl-Text-ParseWords.noarch 0:3.29-4.el7 perl-Time-HiRes.x86_64 4:1.9725-3.el7 perl-Time-Local.noarch 0:1.2300-2.el7 perl-constant.noarch 0:1.27-2.el7 perl-libs.x86_64 4:5.16.3-286.el7 perl-macros.x86_64 4:5.16.3-286.el7 perl-parent.noarch 1:0.225-244.el7 perl-podlators.noarch 0:2.5.1-3.el7 perl-threads.x86_64 0:1.87-4.el7 perl-threads-shared.x86_64 0:1.43-6.el7 Complete!
我们必须使用自定义文件 .mytop 来配置 mytop 以创建配置文件，运行以下命令，添加下面给出的配置 –
# vi ~/.mytop host=localhost db=mysql delay=10 port=3306 socket= batchmode=0 color=1 idle=2
当我们从 root 用户登录运行 mytop 命令时将使用此配置文件。
下面是用于检查mysql性能的命令，其中--prompt用于提示mytop输入mysql数据库密码。我们需要输入 mysql root 用户密码，然后按回车键。
# mytop –prompt output MySQL on localhost (5.6.31) up 0+00:01:04 [06:25:39] Queries: 5.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00 qps now: 1 Slow qps: 0.0 Threads: 1 ( 1/ 0) 00/00/00/00 Key Efficiency: 100.0% Bps in/out: 9.3/381.8 Now in/out: 19.5/ 3.7k Id User Host/IP DB Time Cmd Query or State -- ---- ------- -- ---- --- ---------- 3 root localhost mysql 0 Query show full processlist
现在我们将使用特定用户运行 mytop 命令
# mytop -u root –p outputMySQL on localhost (5.6.31) up 0+00:11:40 [06:36:15] Queries: 131.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 01/00/00/00 Key Efficiency: 100.0% Bps in/out: 6.2/870.8 Id User Host/IP DB Time Cmd Query or State -- ---- ------- -- ---- --- ---------- 8 root localhost mysql 0 Query show full processlist
在上面的例子中，我们使用了以下选项
-u -> This option is used for specifying the mytop to use specific SQL use for loging in to the MySQL -p -> will use the user login password
可以使用其他选项
-h -> to specify the ports or hostname for the MySQL database. -s -> to specify the delay in seconds the default time is 5 seconds
要查看我的顶部可用的完整选项，我们可以使用以下命令
# man mytop
以下键在 mytop 运行时执行各种操作。未实施的则以此类推。包含它们是为了让用户了解即将发生的事情。
? Display help. c Show "command counters" based on the Com_* values in SHOW GLOBAL STATUS. This is a new feature. Feedback welcome. d Show only threads connected to a particular database. f Given a thread id, display the entire query that thread was (and still may be) running. F Disable all filtering (host, user, and db). h Only show queries from a particular host. H Toggle the header display. You can also specify either "header=0" or "header=1" in your config file to set the default behavior. i Toggle the display of idle (sleeping) threads. If sleeping threads are filtered, the default sorting order is reversed so that the longest running queries appear at the top of the list. I Switch to InnoDB Status mode. The output of "SHOW INNODB STATUS" will be displayed every cycle. In a future version, this may actually summarize that data rather than producing raw output. k Kill a thread. m Toggle modes. Currently this switches from `top' mode to `qps' (Queries Per Second Mode). In this mode, mytop will write out one integer per second. The number written reflects the number of queries executed by the server in the previous one second interval. More modes may be added in the future. o Reverse the default sort order. p Pause display. q Quit mytop r Reset the server's status counters via a FLUSH STATUS command. s Change the sleep time (number of seconds between display refreshes). u Show only threads owned by a giver user.
通过上面的文章，我们将学习如何使用 mytop 命令和一些用于监控 mysql 性能的选项，以便我们可以相应地调整数据库。我们可以探索更多的命令和用法，我们也可以使用 man mytop 命令探索其他选项。