如何在 CentOS 7 上使用 Mytop 监控 MySQL 性能
websiteinternetweb services
本文将介绍如何安装和配置 Mytop 来监控 MySQL 性能。Mytop 是一款开源的 MySQL 性能监控工具，它使用命令行来监控 MySQL，类似于 Linux 系统监控工具 top，可以连接到 MySQL 并运行 show process list 和 show global status 命令，并将信息汇总成易于理解的格式。我们可以使用 mtop 来监控 MySQL 的实时线程、运行时间和查询情况，它还会显示正在运行查询的用户以及他们使用的数据库，这些信息可用于性能调优。
要完成此演示，我们需要 CentOS 7 64 位系统、具有 root 权限的用户以及已安装的 MySQL。
在 CentOS 7 上安装 Mytop
由于 CentOS 软件仓库中没有 Mytop，我们需要在服务器上安装 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!
在 CentOS 7 上配置 Mytop 软件包
我们需要使用自定义的 .mytop 文件来配置 mytop。要创建配置文件，请运行以下命令，并添加以下配置 -
# vi ~/.mytop host=localhost db=mysql delay=10 port=3306 socket= batchmode=0 color=1 idle=2
当我们以 root 用户身份登录并运行 mytop 命令时，将使用此配置文件。
使用和连接到 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 --gt; 此选项用于指定 mytop 使用特定的 SQL 语句登录 MySQL。 -p --gt; 将使用用户登录密码。
还可以使用其他选项。
-h --gt; 用于指定 MySQL 数据库的端口或主机名。 -s --gt; 用于指定延迟时间(以秒为单位)，默认值为 5 秒。
要查看 mytop 的所有可用选项，可以使用以下命令。
# man mytop
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 命令探索其他选项。
