Shulou(Shulou.com)06/01 Report--

Http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653929717&idx=1&sn=0774d10e4e2271334dd7fd1c4c792813&chksm=bd3b5a9f8a4cd389ff9e82d1be2b5984afb903fbd2e1c55306e04b97a12d573a354ed75fba30#rd

DoDBA tools, a new real-time performance monitoring tool for MySQL 2016-12-16 Lufei Laoye Tea House

Author: Lu Fei, database technology expert, Oracle ACE-A,Go language enthusiast

Lu Fei is a colleague of Lao Ye during the tour of Sohu. He has strong ability to work and learn.

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

At present, running MySQL in Linux is standard for most Internet companies. The performance data indicators of the above images are the real-time status data that I think are more important in Linux,MySQL,InnoDB. However, in the Doing column of the above images, it is actually more important. It is called Doing because indicators such as "processlist,engine innodb status,locks" really reflect what MySQL is doing at this time.

Let's take a look at the standard Oracle database. There are many diagnostic tools in the Oracle database, such as "AWR,ASH,SQL Monitor". You can see at a glance what the database is doing, and even know the performance load and what the database is doing at any time 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 database history, for example, TPS is high, temporary tables are used more, and there is InnoDB Deadlocks, but for MySQL's Doing at that time, I can only say that it is not direct enough. If you are on the scene, you can catch what MySQL is doing, but there are always times when you are not there. What is 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", a free console-based monitoring tool.

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

Characteristics

Golang language development

Can collect performance data related to Linux and MySQL

Can be collected locally or remotely, with multiple sets

Provide functions similar to Linux top's mytop

Based on and generated into Doing log, restore the scene

Can be recorded to a log file

How doDBA tools works

The remote collection system information is collected by connecting to the remote server through ssh (user name and password is required). The collection method is to read meminfo,diskstats,uptime,net,vmstat, cpuinfo, loadavg and other files under Linux's proc, which is consistent with the pmm,zabbix collection method.

Remote collection of MySQL information is collected 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 a rds user, you can use the-rds parameter, and the collection of system information will be automatically ignored when using mytop.

How to use the doDBAGithub home page: https://github.com/dblucyne/dodba_toolsDownload https://raw.githubusercontent.com/dblucyne/dodba_tools/master/doDBA.conf 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 and can be used directly, regardless of any environment. Use help:. / doDBA-help-c string configuration file. (default "doDBA.conf")-h string Connect to host/IP. -sys Print system info. -myall Print system and mysql info. -mysql Print mysql info. -innodb Print innodb info. -mytop Print mysql prcesslist info, like top. -i duration refresh interval in seconds. (default 1s)-t int doing on Threads_running. (default 50)-rds Ignore system info. -log Print to file by day. -nocolor Print to nocolor.

Use example 1. Collect Linux performance data. / doDBA-h=10.1.x.xx-sys

two。 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.txt10.1.x.x110.1.x.x2Shellcat ip.txt with Shell | while read ip; do echo $ip;. / doDBA-h=$ip-mysql-log

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.