Real-time performance monitoring tool for MySQL

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

Currently running MySQL in Linux is the standard of most Internet companies, with the performance data indicator item is I think in linux,mysql,innodb more important real-time state data, but in the doing column is actually more important, it is called doing, because " Indicators such as Processlist,engine InnoDB status,locks"really reflect what MySQL is doing at the moment.

Let's take a look at the Oracle database and provide "awr,ash,sql in the Oracle database monitor"and many other diagnostic tools, you can see through what the database is doing, and even know the performance load of any time zone in the last 30 days and what the database is doing.

Although there are excellent monitoring tools like "zabbix,pmm" in MySQL, they can only reflect some performance data curves of database history, for example, TPS is high, temporary tables are used more, there are innodb deadlocks, but for MySQL then doing, I can only say not directly enough. If you are at the scene, you can catch what MySQL is doing, but you always have to be out of the field, if asked you last night what was the reason for the performance jitter of the database? How to quickly reproduce the scene to find the cause of the jitter?

The answer is that you can use the "dodba tools", a free console-based monitoring tool. What is DODBA tools? DODBA Tools is a console-based remote monitoring tool that does not require any software to be installed on the local/remote system, it collects real-time performance status data from the operating system, MySQL, InnoDB in real time, and can generate doing log files. To help you quickly understand/optimize your system and MySQL database. Characteristics

• Based on Golang language development

• Collects Linux, MySQL-related performance data

• Can be collected locally or remotely, multiple units

• Mytop--like Linux TOP

• Based on and happened to doing log, reproduce the scene

• Log files can be logged

How DODBA Tools Works

The remote collection system information is connected to the remote server via SSH (username password or build trust), and the methods are collected by reading the Linux proc Meminfo,diskstats,uptime,net,vmstat, Cpuinfo, Loadavg and other documents, which are consistent with Pmm,zabbix collection methods.

The remote collection of MySQL information is collected via the MySQL TCP connection to the MySQL database and only needs to be granted to the connecting user process, select permissions.

System Information and MySQL information collection can be separated, if only want to collect system information, only need to provide the system user name password, if only to collect MySQL can only provide MySQL connection information, if it is an RDS user, you can use the-rds parameter, The collection of system information is automatically ignored when using Mytop.

How to use the GitHub home page:

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
下载下来就可以直接使用，不依赖于任何环境。

Use 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.

Use instance 1. collects 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. Collect multiple cat ip.txt with the shell

10.1.x.x1
10.1.x.x2

Shell

while read ip;  do  echo $ip; ./doDBA -h=$ip -mysql -log </dev/null & done

7. Collect the log files./dodba-h=10.1.x.xx-mysql-log

8. Turn on the doing function

Use the "-T" parameter to set a threshold based on the number of threads_running, and then record "processlist,engine InnoDB status" information into the Dodba.log log--to replicate the scene.

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

9. View doing log tail-f Dodba.log

Real-time performance monitoring tool for MySQL