MySQL real-time performance monitoring tool DODBA tools

What is DODBA tools?

DODBA Tools is a console-based remote monitoring tool that does not need to install any software on the local/remote system, it collects real-time performance status data of the operating system, MySQL, InnoDB in real time, and can generate doing log files to help you quickly understand/ Optimized system and MySQL database.

Characteristics:

• Golang language development.
• Can collect Linux and MySQL related performance data.
• Can be collected locally or remotely, can be more than one.
• Provides functionality similar to the Linux top mytop.
• Based on and take place into doing log, restore the scene.
• Can be logged to a log file.

How DODBA Tools Works

The remote collection system information is connected to the remote server via SSH (username and password required), 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 do I use DODBA?

GitHub Home: Https://github.com/dblucyne/dodba_tools

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

Download it and you can use it directly, and not depend on any environment. However, in the dodba.conf configuration file, you can first fill in the information you need to set.

{  "Host": "",  "Huser": "Root",  "hport": "\",  "hpwd": "  ",  "Muser": "Root",  "mpwd":  "123456",  "mport": "3306"}

　　

Use Help:

$./dodba--helpusage of./DODBA:-C String configuration file. (default "dodba.conf")-H string Connect to HOST/IP. -HP string Connect host port. (default "")-help Display this help. -HP string Connect host password. -hu string Connect Host user. (default "root")-I duration refresh interval in seconds. (default 1ns)-innodb Print InnoDB info. -log Print to file by day. -MP string Connect mysql port. (default "3306")-mp string Connect mysql password. -mu string Connect mysql user. -myall Print system and MySQL info. -mysql Print MySQL info. -mytop Print MySQL prcesslist info, like top. -nocolor Print Nocolor. -rds Ignore System Info. -sys Print System Info. -t int mysql trace on threads_running. (Default 50)

　　

Working with instances

1. Collecting Linux performance data

$./DODBA-H=127.0.0.1-SYSDODBA Tools on host 127.0.0.1---------+----LOAD--AVG----+-----cpu-usage-----+--swap--+---- NET----+-----------IO-----------Time     |   1m    5m   10m| usr  sys  IOW  ide|  Si  so| recv  send|   r/s   w/s  rb/s  wb/s---------+-----------------+-------------------+--------+-----------+--------- ---------------19:23:51 | 1.35  0.72  0.41| 4.2  3.0  3.2 89.6|   0   0|16384.0p16384.0p|  2396  768K 69.2m19:23:52 | 1.35  0.72  0.41| 4.8   2.9 3.1 89.2| 0   0|16384.0p16384.0p|  2502  1.1M 71.1M

　　

2. Collect MySQL performance data

$./DODBA-H=127.0.0.1-MYSQLDODBA Tools on host 127.0.0.1---------+-------mysql-status-------+-----Threads-----+---- -slow-----+---bytes---+---------locks----------Time     |   QPS  TPS  ins  upd  del| run  con cre cac| SQL  tmp dtmp| recv  send| locki lockw opent Openf---------+--------------------------+-----------------+--------------+-----------+------------------------ 19:27:32 |    0    0|   3   0   6|   0    1    0|   5K   37k|     0   538    9319:27:33 |    Panax  Notoginseng    5  134|   3   0   6|   0    5    1|  35K  396k|   216     0   538    9319:27:34 |    8    8   44|   4   0   6|   0    5    0|  26K  220k|     0   538    93

3. Collect InnoDB performance data

$./DODBA-H=127.0.0.1-INNODBDODBA Tools on host 127.0.0.1---------+------innodb--rows-----+--------- Innodb--pages--------+-------innodb--data-------+--innodb-log--time     |    Read  ins  upd  del|    Data    free   Dirty Flush|reads writes  read Written|fsyncs written---------+-----------------------+------ ------------------------+--------------------------+--------------19:30:00 |    4741  458    0|  350602    8195   18869   125|    0    141    0K    4.0m|     1     66k19:30:01 |     106  521    3    0|  350602    8195   18858   137|    0    151    0K    4.4m|     1     72k19:30:02 |    7982  406    2|  350603    8194   18860   160|    0    202    0K    5.1m|     3     84K

　　

4. Collect MySQL and Linux performance data

$./dodba-h 127.0.0.1-myall

5. Mytop–like Linux Top

$./dodba-h 127.0.0.1-mytopdodba tools-19:32:36 on host 127.0.0.1  up to days load:0.64 0.75 0.63 cpu:1.99% User, 2.84% sys, 0.00% Nice, 0.28% iowait, 94.89% idlemem:  23.3G Total, 23.0G used, 317.6M free, 0K buffers Swap:  24.0G Total, 0K used, 24.0G free, 13.2G Cached mysql:16 QPS, TPS, 0 slowsql, 2 run         ID          USER      ip/host          DB     Ti Me state SQL--          ----      --------          -     ----           -----                            ---    2495440        Zabbix    127.0.0.1                    0            init          show full processlist

　　

6. Use the shell to collect more than one

$ cat Ip.txt10.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. Collect the log files

$./dodba-h=127.0.0.1-mysql-log$ Cat 127001_dodba_mysql_20161219.log dodba Tools on host 127.0.0.1---------+-------Mys Ql-status-------+-----Threads-----+-----Slow-----+---bytes---+---------locks----------Time     |   QPS  TPS  ins  upd  del| run  con cre cac| SQL  tmp dtmp| recv  send| locki lockw opent Openf---------+--------------------------+-----------------+--------------+-----------+------------------------ 19:35:30 |    5    0|   3   98   0   7|   0    2    1|   9K  8.0m|     0   538    9519:35:31 |    5    0|   2   98   0   7|   0    3    0|  38K  1.7m|     0   538    93

　　

8. Turn on the doing function

Use the "-T" parameter to set thresholds based on the number of threads_running, set to record "processlist,engine InnoDB status" information into Dodba.log logs, and reproduce the scene.

$./dodba-h=127.0.0.1-mysql-t=3

　　

9. View doing logs

$ tail-f dodba.log2016/12/19 19:51:52----------------processlist---------------id:2314306user:zabbixhost: Localhostdb:zabbixcommand:querytime:0state:updateinfo:insert into History_uint (itemid,clock,ns,value) VALUES ( 35632,1482148312,240404292,274)

　　

MySQL real-time performance monitoring tool DODBA tools