How to Install Mytop on Ubuntu 16

Database performance is critical to the speed of many applications. While many developers and administrators focus on connection speed, CPU use and other factors, the bottleneck is often an unoptimized database query or update. Further, poor database configuration can significantly constrain performance. Mytop is a great utility for investigating MySQL performance issues. This tutorial guides you through its installation and configuration.

Getting Started

To complete this guide, you will need the following:
• 1 Node (Cloud Server or Dedicated Server) with Ubuntu 16.04 installed.
• A running MySQL/MariaDB installation.

For the purposes of this guide, it is assumed that MySQL is already installed, configured and running.

Tutorial

Let’s get started. First we need to install the Mytop package along with its dependencies.

apt-get update
apt-get install mytop -y

Create a .mytop file in your root directory. This file will configure Mytop with default settings for the root user. The password line should contain your MySQL root password.

cd /root
nano .mytop
user=root
pass=PASSWORD
host=localhost
db=mysql
delay=1
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

You can now run the “mytop” command to connect to your MySQL database and examine its performance.

MySQL on localhost (5.5.47-MariaDB) up 0+00:47:45 [06:36:47]
Queries: 1.2k qps: 0 Slow: 0.0 Se/In/Up/De(%): 56/06/13/01
qps now: 2 Slow qps: 0.0 Threads: 1 ( 1/ 0) 00/00/00/00
Key Efficiency: 76.9% Bps in/out: 260.6/400.4 Now in/out: 48.1/11.2k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
25 root localhost mysql 0 Query show full processlist

Mytop gives you many statistics on your database’s operation. The first is your database’s uptime, how long it has been since the server starts. You’ll also see the number of active threads sorted by their idle times, along with a list of running queries.

To learn how Mytop works, try running it on a server with an existing MySQL application. For instance, run it on a WordPress server and watch your metrics while browsing the site. WordPress is actively developed and optimized, so Mytop results will give you a sense for what you might expect to see with applications that behave well.

Now, when another MySQL issue strikes, you have yet another diagnostic tool in your belt to determine what is wrong. If the number of running queries is high, it may be necessary to add additional indexes so they complete more quickly. If too many or few threads are active, a MySQL configuration tweak may be in order. “Explain” and other SQL features work on the database level, but sometimes it is necessary to examine the server itself in order to resolve an issue.

Conclusion

MySQL is a popular database at the core of many common internet-facing applications. Mytop is useful in diagnosing and resolving any issues with its configuration or operation, and is essential for anyone who depends on MySQL for any daily task. If this guide was helpful to you, kindly share it with others who may also be interested.