Master MySQL Monitoring with mytop: Install, Configure, and Track Real‑Time Metrics

Linux provides the top command for system performance, and mytop offers a similar interface focused on MySQL status, displaying important metrics like QPS.

Usage Overview

mytop shows two main sections: the upper part lists various indicators, and the lower part lists MySQL threads. The first line shows version information, the second line shows overall statistics, and the third line provides real‑time data for the current refresh cycle.

Key indicators include:

Queries – total queries processed

qps – average queries per second

Slow – total slow queries

Se/In/Up/De(%) – percentages of SELECT, INSERT, UPDATE, DELETE

qps now – queries per second in the current cycle

Slow qps – slow queries per second in the current cycle

Threads – current connections (active and cached)

Key Efficiency – proportion of keys read from cache

Bps in/out – average MySQL inbound/outbound traffic

Now in/out – inbound/outbound traffic in the current cycle

Below the metrics, mytop lists thread details sorted by idle time, showing thread ID, user, client address, database, and full query statement. It continuously runs show full processlist to gather this information.

Helpful Keyboard Commands

Press ? for help. Other shortcuts include: h – filter by client address s – filter by user name k – kill a thread m – switch to QPS mode

Installation and Configuration (CentOS 7 example)

Install required repositories and mytop:

rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-8.noarch.rpm

yum install yum-plugin-protectbase.noarch -y

yum install mytop -y

Create and edit /root/.mytop with the following content:

host=localhost
user=root
pass=111111
db=mysql
port=3306
socket=/tmp/mysql.sock
delay=5
batchmode=0
color=1
idle=1

You can omit the password for security and provide it at runtime using mytop --prompt. The delay setting controls the refresh interval.

After installation and configuration, run mytop to start monitoring.