Mysqlstat is a command-line tool designed for real-time monitoring and analysis of performance metrics and related information of MySQL servers

MySQL & MariaDB Percona Server for MySQL 5.7
,
1

mysqlstat is a command-line tool designed for real-time monitoring and analysis of performance metrics and related information of MySQL servers.

It assists DBAs (Database Administrators) and developers in identifying and resolving database performance issues.

The main functionalities of the mysqlstat tool are as follows:

  • Real-time Monitoring: mysqlstat can monitor and display real-time metrics such as QPS, TPS, and network bandwidth usage of the MySQL server.

  • Query Analysis: It can show the most frequently executed SQL statements, helping to identify and optimize queries with poor efficiency.

  • Table File Analysis: mysqlstat can list the most frequently accessed table files (.ibd), aiding in identifying hot tables and disk usage.

  • Lock Blocking: The tool can display the currently blocked SQL statements due to locks, assisting in identifying and resolving lock-related issues.

  • Automatic killing of currently locked SQL statements.

  • Deadlock Information: mysqlstat provides information about deadlocks, helping DBAs understand and resolve deadlock issues.

  • Index Analysis: It can identify duplicate or redundant indexes, facilitating index optimization and reducing storage space consumption.

  • Connection Count Statistics: The tool can provide statistics on the total number of connections from different application IPs, helping to understand the database’s connection load.

  • Table Size Statistics: mysqlstat can provide size statistics for each table in the database, aiding in understanding the storage occupation of tables.

  • Binlog Analysis: It can analyze which tables have high TPS during peak periods, assisting in identifying performance bottlenecks or optimizing hot tables.

  • Viewing Master-Slave Replication Information: The tool can provide information about the status and delay of the master-slave replication, facilitating monitoring and management of the replication environment.

2

Hello @hiller1231,
How is mysqlstat different from https://github.com/innotop/innotop ?

3 
Data source:
mysqlstat mainly relies on MySQL Performance Schema to obtain performance data, thus requiring MySQL version 5.5 or higher and having Performance Schema enabled.
innotop uses SHOW ENGINEINNODB STATUS and other MySQL commands to obtain performance data, so it can run on older MySQL versions, but it relies on the InnoDB storage engine.

Monitoring scope:
mysqlstat provides a wider range of monitoring information, including QPS, TPS, network bandwidth usage, table file access, lock blocking, deadlock information, index analysis, connection statistics, table size statistics, Binlog analysis, etc.
innotop mainly focuses on InnoDB storage engine-related information, such as transactions, locks, etc. It provides more focused monitoring and analysis on InnoDB's internal operations.

User interface:
mysqlstat provides a command-line interface to display performance data in text format.
innotop provides an interactive TUI (Text-based User Interface) that allows users to browse and analyze performance data in a more user-friendly way.

Problem location:
The comprehensive monitoring capabilities of mysqlstat make it more suitable for comprehensive performance analysis, including query efficiency, disk usage, and connection load.
innotop is more focused on the internal situation of the InnoDB engine, which helps to locate issues related to InnoDB.

Overall, mysqlstat is suitable for comprehensive MySQL performance monitoring and analysis, especially on newer MySQL versions. While innotop is more suitable for gaining insights into the performance and issues of the InnoDB storage engine and can be used on various MySQL versions. The choice of tool depends on your specific requirements and MySQL environment.