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

2023-06-21 16:45:03 87

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

为了完成这个演示，我们需要CentOS764位和一个具有root权限的用户，安装MySQL。

在Centos7上安装Mytop

由于Mytop无法从Centos存储库中获得，我们需要在服务器上安装ExtrapackageforEnterprisesLinux(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) "
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!

在CentOS7上配置Mytop包

我们必须使用自定义文件.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数据库密码。我们需要输入mysqlroot用户密码，然后按回车键。

# 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命令的快捷键

以下键在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性能的选项，以便我们可以相应地调整数据库。我们可以探索更多的命令和用法，我们也可以使用manmytop命令探索其他选项。

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

在Centos7上安装Mytop

在CentOS7上配置Mytop包

使用和连接到我的顶部

mytop命令的快捷键

热门推荐

随机推荐