Introducing MySQL’s real-time performance monitoring tool

Real-time performance status data of the operating system and mysql database are particularly important, especially when there is performance jitter. These real-time performance data can quickly help you locate the performance of the system or MySQL database. Bottlenecks, just like when you use command tools such as "top, sar, iostat" on a Linux system, you can immediately determine whether the performance bottleneck of the OS is on IO or CPU, so it is more important to collect/display these performance data. What important real-time performance status indicators can reflect the performance load of the system and MySQL database?

Currently, running MySQL on Linux is standard for most Internet companies. The performance data indicators in the above picture are the real-time indicators that I think are more important in Linux, MySQL, and InnoDB. Status data, however, the Doing column in the above picture is actually more important. The reason why it is called Doing is because indicator items such as "processlist, engine innodb status, locks" truly reflect what MySQL is doing at this time.

Let’s take a look at the Oracle database. The Oracle database provides many diagnostic tools such as "AWR, ASH, SQL Monitor", etc. You can see at a glance what the database is doing. What, you can even know the performance load and what the database was doing at any time period in the past 30 days.

Although there are excellent monitoring tools such as "zabbix, PMM" in MySQL, they can only reflect some performance data curves of the database history. For example, TPS is high, temporary tables are used too much, and InnoDB Deadlocks, but for what MySQL was doing at the time, I can only say that it was not direct enough. If you are on site, you can catch what MySQL is doing, but there are always times when you are not on site. If you were asked what was the reason for the performance jitter of the database last night? How to quickly reproduce the scene to find the cause of the jitter?

The answer is to use "doDBA tools", which is a free console-based monitoring tool.

What are doDBA tools

doDBA tools is a console-based remote monitoring tool. It does not require any software to be installed on the local/remote system. It can collect real-time data on the operating system, MySQL, InnoDB provides real-time performance status data and can generate Doing log files to help you quickly understand/optimize the system and MySQL database.

Features

基于golang语言开发
可收集Linux、MySQL相关性能数据
可本地或远程收集，可多台
mytop --Like Linux TOP
基于并发生成Doing日志，复现现场
可记录到日志文件
doDBA tools 工作原理

Remotely collect system information by connecting to the remote server through ssh (user name and password or establishing trust). The collection method is by reading the Linux proc file meminfo, diskstats, uptime, net, vmstat, cpuinfo, loadavg and other files, which are consistent with pmm and zabbix collection methods.

Remotely collect MySQL information by connecting to the MySQL database through MySQL tcp. You only need to grant the connection user PROCESS and SELECT permissions.

The collection of system information and MySQL information can be separated. If you only want to collect system information, you only need to provide the system user name and password. If you only collect MySQL, you can only provide MySQL connection information. If you are an rds user, you can Using the -rds parameter, the collection of system information will be automatically ignored when using mytop.

How to use

Github主页:
https://github.com/dblucyne/dodba_tools
Download：
wget https://raw.githubusercontent.com/dblucyne/dodba_tools/master/doDBA --no-check-certificatewget https://raw.githubusercontent.com/dblucyne/dodba_tools/master/doDBA.conf --no-check-certificatechmod +x doDBA

You can use it directly after downloading it, and it does not depend on any environment.

Usage help:

./doDBA -help
  -c string
    configuration file.(default "doDBA.conf")
  -h string
    Connect to host/IP.
  -sys
    Print linux info.
  -myall
    Print linux and mysql info.
  -mysql
    Print mysql info.
  -innodb
    Print innodb info.
  -mytop
    Print mysql prcesslist,like top.
  -i duration
    refresh interval in seconds.(1s)
  -t int
    doing on Threads_running.(50)  
 -rds        
   Ignore system info.
  -log
    Print to file by day.
  -nocolor
    Print to nocolor.

Usage examples

1. Collect Linux performance data

./doDBA -h=10.1.x.xx -sys

2 . Collect MySQL performance data

./doDBA -h=10.1.x.xx -mysql

3. Collect InnoDB performance data

./doDBA -h=10.1.x.xx -innodb

##4. Collect MySQL and Linux performance data

./doDBA -h=10.1.x.xx -myall

5. mytop --like linux top

./doDBA -h=10.1.x.xx -mytop

6. 借助Shell收集多台

cat ip.txt
10.1.x.x110.1.x.x2
Shell
cat ip.txt | while read ip;  do  echo $ip; ./doDBA -h=$ip -mysql -log </dev/null &  done

7. 收集到日志文件

./doDBA -h=10.1.x.xx -mysql -log

8. 开启Doing功能

使用【-t】参数可以基于Threads_running的数量设置阈值，设置后可记录「processlist，engine innodb status」信息到dodba.log日志中，--复现现场。

/doDBA -h=10.1.x.xx -myall -t=3

9. 查看Doing日志

tail -f dodba.log

以上就是MySQL 的实时性能监控利器的全部内容。

相关参考：MySQL教程

The above is the detailed content of Introducing MySQL’s real-time performance monitoring tool. For more information, please follow other related articles on the PHP Chinese website!