How VPS - How to use/setup VPS
  • Home
  • Management guides
    • Web servers software
      • Directadmin
      • Hocvps Script
      • Centmin Mod
      • CWP
      • Kloxo-MR
      • Plesk
    • Control Panels
    • Securing VPS/Servers
      • SSL Certificates
      • Upgrading
      • Authentication
  • Operating System
    • CentOS
    • Fedora
    • Debian
    • Linux
    • Arch
    • BSD
    • CoreOS
  • Reviews
  • Coupon
    • Domain Coupon
    • Hosting Coupon
No Result
View All Result
  • Home
  • Management guides
    • Web servers software
      • Directadmin
      • Hocvps Script
      • Centmin Mod
      • CWP
      • Kloxo-MR
      • Plesk
    • Control Panels
    • Securing VPS/Servers
      • SSL Certificates
      • Upgrading
      • Authentication
  • Operating System
    • CentOS
    • Fedora
    • Debian
    • Linux
    • Arch
    • BSD
    • CoreOS
  • Reviews
  • Coupon
    • Domain Coupon
    • Hosting Coupon
No Result
View All Result
How VPS - How to use/setup VPS
No Result
View All Result
Home Operating System Linux

Using Mytop to Monitor MySQL Performance

How VPS by How VPS
January 1, 2020
in Linux
0
0
SHARES
27
VIEWS
Share on FacebookShare on Twitter

Contents

  1. Introduction
  2. Prerequisites
  3. Step one: Install mytop using EPEL yum repository
  4. Step two: Configure mytop with a customized configuration file
  5. Step three: Use mytop to monitor MySQL performance
  6. Want to contribute?


Introduction

Mytop is a free console-based tool for monitoring performance of MySQL. It is similar to the “top” utility, but it displays MySQL queries. With mytop, you can instantaneously monitor MySQL uptime, threads, queries, users’ behaviors and other real-time status information, which can be helpful for MySQL performance optimization.

In this tutorial, I will show you how to install, configure, and use mytop on a newly-created Vultr server based on the One-Click LEMP Application.

Prerequisites

Before proceeding, you need to:

  • Deploy a server instance with the One-Click LEMP Application.

  • Log in as a non-root user with sudo privileges. You can find how to create a non-root user in this article.

Step one: Install mytop using EPEL yum repository

With the help of EPEL (Extra Packages for Enterprise Linux) yum repository, you can easily install mytop on your server. By default, the EPEL yum repository has been installed when using the One-Click LEMP Application.

You can confirm the EPEL repository’s existence with:

sudo yum repolist

You will see the epel Extra Packages for Enterprise Linux 6 - x86_64 repository listed.

To protect packages in the EPEL repository from being updated or overridden by packages in other repositories, you need to install the “protectbase” plugin:

sudo yum install yum-plugin-protectbase.noarch -y

Then edit the file /etc/yum.repos.d/epel.repo, input:

cd /etc/yum.repos.d
sudo vi epel.repo

Append a line protect=1 to the section [epel]:

[epel]
name=Extra Packages for Enterprise Linux 6 - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch
failovermethod=priority
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
protect=1

Save and close the file.

Finally, install mytop with:

sudo yum install mytop -y

Step two: Configure mytop with a customized configuration file

To simplify the usage of mytop, you can create a customized configuration file named /root/.mytop. When you run mytop as root or a non-root user with sudo privileges, the program will invoke this configuration file automatically. If you want to run it as a non-root user without sudo privileges, you need to put the configuration file in the non-root user’s home directory.

For your reference, here is a sample of the configuration file /root/.mytop:

user=root
pass=
host=localhost
db=mysql
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

The contents in this file provide default arguments to the mytop program, reducing your manual input when using it. However, if you run the program with arguments manually inputted, the command-line arguments will override corresponding arguments in the configuration file.

You can modify these arguments according to your specific conditions. Some of the meanings of the arguments are listed below:

  • user: The database username.
  • pass: The password for the database user. For security purposes, you can leave it blank and input the password manually.
  • host: The database host address.
  • db: The database name.
  • delay: Display refresh interval in seconds.

Full explanations for each argument can be found in the manual page:

man mytop

Step three: Use mytop to monitor MySQL performance

As mentioned before, the mytop utility use both arguments in configuration file and command line arguments, and the latter will override the former accordingly.

Thus, with a configuration file in order, you just need to input the command with few arguments.

For example, if you want to input your password with a prompt for better security, you can input:

sudo mytop --prompt

Then input the password for the default database and user root, which can be found in /root/.my.cnf, to enter the mytop program interface.

Here is another example. If you want to monitor a specific database, you can use:

sudo mytop -d yourdatabasename --prompt

Replace yourdatabasename with your own one.

In the mytop program interface, you will find something like:

MySQL on localhost (5.6.26-log)                                                  up 0+08:36:33 [12:07:15]
 Queries: 921.0  qps:    0 Slow:     0.0         Se/In/Up/De(%):    00/00/00/00
             qps now:    0 Slow qps: 0.0  Threads:    1 (   1/   0) 00/00/00/00
 Key Efficiency: 100.0%  Bps in/out:   0.8/140.7   Now in/out:   9.7/ 1.9k

      Id      User         Host/IP         DB      Time    Cmd Query or State
       --      ----         -------         --      ----    --- ----------
       14      root       localhost      mysql         0  Query show full processlist

This is the default thread view of mytop, you can always switch to this view by pressing t.

The top four lines constitute the header which can be toggled on or off by pressing Shift + h. The header contains general information about your MySQL server.

Beneath the header, you can see the status data of current MySQL threads.

To get help, press ? (Shift + /) when the program is running.

To quit the program, press q.

More details about the display and usage of mytop can be found in its man page:

man mytop

That’s it. You can use the data gathered from mytop to implement informed MySQL optimization.

Want to contribute?

You could earn up to $300 by adding new articles

Submit your article
Suggest an update
Request an article
How VPS

How VPS

Related Posts

How to Install Apache Tomcat 8 on CentOS 7
Linux

How to Install Apache Tomcat 8 on CentOS 7?

February 11, 2020
Linux

Setup IonCube Loader on Ubuntu 14

January 1, 2020
Linux

Converting from MySQL to MariaDB on Ubuntu

January 1, 2020
Next Post

Install MediaWiki on the One-Click LEMP Application

Using Finnix Rescue CD to Rescue, Repair, or Backup Your Linux System

Installing Joomla! on Ubuntu 16.04

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Follow Us

  • 121 Followers
  • 87.2k Followers

Recommended

How to Install the Phoenix Framework on CentOS 7

3 years ago
How to Use ‘cat’ and ‘tac’ Commands with Examples in Linux

Track Bandwidth On Linux Using vnStat

3 years ago

11 Linux Kernel Boot-time Parameters Explained

4 years ago

How to Install Directus 6.4 CMS on a Fedora 26 LAMP VPS

3 years ago

Instagram

    Please install/update and activate JNews Instagram plugin.

Categories

  • Arch
  • Authentication
  • Backups
  • BSD
  • Centmin Mod
  • CentOS
  • Control Panels
  • CoreOS
  • CWP
  • Debian
  • Directadmin
  • Encryption
  • Fedora
  • Firewalls
  • Hocvps Script
  • Hosting providers
  • Kloxo-MR
  • Linux
  • Mitigations
  • Operating System
  • Plesk
  • Reviews
  • Securing VPS/Servers
  • Security Patches
  • SSL Certificates
  • Uncategorized
  • Upgrading
  • VPS/Servers management guides
  • Vulnerability Detection
  • Web servers software
  • Webhosting Control Panel

Topics

Apache Web Server Bluehost Review 2019 Bluehost Review 2020 Bluehost Review 2021 Centmin Mod CentminMod centos install htop fsck htop install HTTP DoS attack Install Snort on an Ubuntu install Zabbix on CentOS install Zabbix on CentOS 7 Linux Commands linux guide linux install htop linux vps setup guide MariaDB MariaDB Error Mysql mysqld error optimize MariaDB optimize Mysql snort Ubuntu
No Result
View All Result

Highlights

Top Free Web Hosting Control Panels To Manage VPS/Dedicated Servers

Webmin Reviews

Virtualmin Reviews

CentOS Web Panel Reviews

Ajenti Reviews

ISPConfig Reviews

Trending

Failed to download metadata for repo 'appstream' on Centos 8
CentOS

How to fix error: Failed to download metadata for repo ‘appstream’ on Centos 8

by How VPS
February 25, 2022
0

I tried to update some extensions by use yum on centOs which I specified in Dockerfile. After...

How to Fix MySQL Error "Plugin 'InnoDB' registration as a STORAGE ENGINE failed"?

How to Fix MySQL Error “Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed”?

November 17, 2020
How to optimize Mysql or MariaDB

How to optimize Mysql or MariaDB

November 3, 2020
Top Free Web Hosting Control Panels To Manage VPS/Dedicated Servers

Top Free Web Hosting Control Panels To Manage VPS/Dedicated Servers

February 17, 2020
Webmin Reviews

Webmin Reviews

February 17, 2020
How VPS – How to use/setup VPS

We bring you the best Premium WordPress Themes that perfect for news, magazine, personal blog, etc. Visit our landing page to see all features & demos.
LEARN MORE »

Recent News

  • How to fix error: Failed to download metadata for repo ‘appstream’ on Centos 8 February 25, 2022
  • How to Fix MySQL Error “Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed”? November 17, 2020
  • How to optimize Mysql or MariaDB November 3, 2020

Categories

  • Arch
  • Authentication
  • Backups
  • BSD
  • Centmin Mod
  • CentOS
  • Control Panels
  • CoreOS
  • CWP
  • Debian
  • Directadmin
  • Encryption
  • Fedora
  • Firewalls
  • Hocvps Script
  • Hosting providers
  • Kloxo-MR
  • Linux
  • Mitigations
  • Operating System
  • Plesk
  • Reviews
  • Securing VPS/Servers
  • Security Patches
  • SSL Certificates
  • Uncategorized
  • Upgrading
  • VPS/Servers management guides
  • Vulnerability Detection
  • Web servers software
  • Webhosting Control Panel

[mc4wp_form]

© 2018 JNews - City News Magazine WordPress theme. All rights belong to their respective owners.
JNews is a top selling 2018 WordPress News, Blog, Newspaper & Magazine Theme.

No Result
View All Result
  • Home

© 2023 JNews - Premium WordPress news & magazine theme by Jegtheme.