如何在 CentOS 7 上使用 Mytop 监控 MySQL 性能

在本文中，我们将学习如何安装和配置 Mytop 来监控 MySQL 性能。Mytop 是一款用于 MySQL 性能的开源监控工具，它使用命令行来监控 MySQL，看起来像 Linux 系统监控工具 top，它会连接到 MySQL 并运行 show process list 和 show global status 命令，并将信息以人类易于理解的格式汇总。我们可以使用 mtop 监控 MySQL 实时线程、运行时间和查询，它还会显示运行查询的用户以及他们正在使用的数据库，这些信息可用于性能调优。

为了完成此演示，我们需要 CentOS 7 64 位系统，以及拥有 root 权限的用户和已安装的 MySQL。

在 Centos 7 上安装 Mytop

由于 Mytop 在 Centos 存储库中不可用，因此我们需要在服务器上安装企业 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) <[email protected]>"
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 命令时，将使用此配置文件。

使用和连接到 my Top

以下是用于检查 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

要查看 my top 可用的所有选项，可以使用以下命令

# 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 命令探索其他选项。

更新于: 2020年1月23日

680 次查看

开启你的 职业生涯

通过完成课程获得认证

开始学习
广告