dotlinux blog

4 Useful Commandline Tools to Monitor MySQL Performance in Linux

Monitoring the performance of a MySQL database is crucial for ensuring its optimal operation. In a Linux environment, there are several powerful commandline tools that can provide valuable insights into various aspects of MySQL's performance. In this blog post, we'll explore four such tools that can help you keep a close eye on your MySQL server.

2026-06

Table of Contents#

  1. mysqladmin
    • Overview
    • Key Features
    • Usage Examples
  2. pt - query - digest
    • Introduction
    • How it Works
    • Sample Usage
  3. iostat
    • What it Does
    • Monitoring Disk I/O for MySQL
    • Interpretation of Results
  4. vmstat
    • Basics
    • Monitoring System Resources for MySQL
    • Analyzing Output

1. mysqladmin#

Overview#

mysqladmin is a commandline client that comes with the MySQL distribution. It allows you to perform administrative tasks and get basic information about the MySQL server's status.

Key Features#

  • Server Status: You can use it to get information like the number of threads, uptime, and the current load.
  • Process Management: Kill processes (threads) that might be causing issues.
  • Configuration Checks: Check if the server is running with the expected configuration.

Usage Examples#

  • Get Server Uptime:
mysqladmin -u [username] --password=[password] -h [host] uptime

Replace [username], [password], and [host] with your actual MySQL credentials and server address.

  • Check Threads:
mysqladmin -u [username] --password=[password] -h [host] proc

This will list all the active threads in the MySQL server.

2. pt-query-digest#

Introduction#

pt-query-digest is part of the Percona Toolkit. It is extremely useful for analyzing slow query logs. Slow query logs record queries that take a long time to execute, and pt-query-digest can summarize and analyze these logs to help you identify performance bottlenecks.

How it Works#

It reads the slow query log file, parses each query, and then groups and aggregates them based on various criteria like query type, execution time, etc. It provides statistics such as the average query time, the number of times a query was executed, and the total time spent on a particular type of query.

Sample Usage#

Suppose your slow query log is located at /var/log/mysql/slow-query.log. You can run:

pt-query-digest /var/log/mysql/slow-query.log

This will generate a detailed report showing the most time - consuming queries, their patterns, and other relevant information.

3. iostat#

What it Does#

iostat (Input/Output Statistics) is a tool that reports CPU statistics and input/output statistics for devices, partitions, and network filesystems.

Monitoring Disk I/O for MySQL#

MySQL relies heavily on disk I/O for reading and writing data. By monitoring disk I/O using iostat, you can determine if the disk is a bottleneck. For example, if the %util (percentage of time the device was busy) for the disk where MySQL data is stored is consistently high (close to 100%), it might indicate that the disk is overloaded.

Interpretation of Results#

When you run iostat -x [device] (where [device] is the disk device, e.g., sda), you'll get output like:

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq - sz avgqu - sz   await r_await w_await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
  • rrqm/s: The number of read requests merged per second.
  • wrqm/s: The number of write requests merged per second.
  • r/s and w/s: The number of read and write requests per second.
  • rMB/s and wMB/s: The amount of data read and written per second in megabytes.
  • %util: The percentage of CPU time during which I/O requests were issued.

4. vmstat#

Basics#

vmstat (Virtual Memory Statistics) reports information about processes, memory, paging, block I/O, traps, and CPU activity.

Monitoring System Resources for MySQL#

MySQL uses system resources like memory and CPU. vmstat can help you monitor if the system is running out of memory (by looking at the free memory and swap usage) or if the CPU is overloaded (by checking the us (user) and sy (system) CPU time percentages).

Analyzing Output#

When you run vmstat 1 (the 1 means it will update the statistics every 1 second), you'll get output like:

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0      0 102400  1024 409600    0    0     0     0    0    0  0  0 100  0  0
  • r: The number of runnable processes (waiting for CPU time).
  • b: The number of processes in uninterruptible sleep (usually waiting for I/O).
  • free: The amount of free memory in kilobytes.
  • us and sy: The percentage of CPU time spent in user and system space respectively.

Reference#