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

This article summarizes some tools used in the daily work of MySQL DBA, which are convenient for beginners and easy for themselves to consult.

Let's first introduce the tools for infrastructure (CPU, IO, network, etc.) inspection:

Vmstat, sar (sysstat toolkit), mpstat, oprofile, nicstat, dstat, iotop, tsar, iostat can be mastered, and the functions are more or less the same (dstat and sar are commonly used by individuals).

CPU:

Mpstat-P ALL 1 is displayed every 1s

You can see the imbalance in CPU scheduling in the figure above. So we can try to bind a process with a pid of 700 to the fourth CPU. Taskset-p 03 700

Disk:

Vmstat-S m 1 10

Iostat-xkdz 1

Iotop

Pt-ioprofile-profile-pid=$ (pidof mysqld)-cell=sizes-run-time=30

Memory:

Vmtouch [very cool tools] reference: http://blog.yufeng.info/archives/1903

Function:

See which parts of a file (or directory) are in memory

Transfer files to memory

Clear the file out of memory

Lock files in memory without being swapped out to disk

1. See how much of the / bin directory is in memory.

$vmtouch / bin/

Files: 92

Directories: 1

Resident Pages: 348max 1307 1M/5M 26.6%

Elapsed: 0.003426 seconds

two。 See how many files (big-dataset.txt) are in memory.

How much ofbig-dataset.txt is currently in memory?

$vmtouch-v big-dataset.txt

Big-dataset.txt

[] 0/42116

Files: 1

Directories: 0

Resident Pages: 0can421160and164m 0%

Elapsed: 0.005182 seconds

Then read in part of the file

$tail-n 10000big-dataset.txt > / dev/null

Then check it out again with vmtouch:

$vmtouch-vbig-dataset.txt

Big-dataset.txt

[oOOOOOOO] 4950/42116

Files: 1

Directories: 0

Resident Pages: 4950max 42116 19M/164M 11.8%

Elapsed: 0.006706 seconds

We can see that there is no data in memory at the beginning of big-datset.txt, and after reading some of the data with the tail command, there is data from 19MB into memory.

3. Clear files (a.txt) out of memory

$vmtouch-vea.txt

Evicting a.txt

Files: 1

Directories: 0

Evicted Pages: 42116 (164m)

Elapsed: 0.076824 seconds

The main function of vmtouch is to do the warm-up of data, that is, for the data to be used, read them into memory in advance through vmtouch, rather than read them from the hard disk when needed, which can improve the efficiency of the system.

Network:

Nicstat-z 1 (you need to compile and install it by yourself on the Internet)

Comprehensive tools:

Dstat-tclmdny 1

Oprofile

[this tool is too powerful, but we need to install kernel with debug first. Please consult the documentation for details]

When there is no problem with the infrastructure, we can consider the parameter tuning and performance optimization of MySQL.

Commonly used performance analysis tools are:

Mytop 、 innotop 、 orzdba 、 dodba 、 mysqltuner.pl . (all these tools are easy to use.)

In addition, common monitoring tools with web interface are:

Zabbix

Lepus Sky Rabbit

Official website: http://www.lepus.cc/page/opensource

Professional mysql monitoring software.

Can intuitively monitor the master-slave status, the interface is more dazzling. Support for analysis of slow queries (based on pt-query-digest implementation)

Deployment of PMM monitoring (deployment with docker containers is simple and the interface is cool, but it still doesn't feel practical enough. )

Pt kit kit for the command line:

The series of tools of the percona-toolkit family, which have so many functions, have been split into several articles in my blog and basically listed the usage.

In addition, the following two command line tools are recommended:

Innotop [enhanced version of mytop]

Reference: / / www.percona.com/blog/2013/10/14/innotop-real-time-advanced-investigation-tool-mysql/

Download address: git clone https://github.com/innotop/innotop.git [yum can also be installed directly in the epel source]

Configuration method:

Yum install perl-TermReadKey perl-Time-HiRes perl-DBD-MySQL perl-DBI

. / innotop-- help can view the supported parameters. However, we do not enter the password on the command line, and all of these parameters are generally not needed.

. / innotop-- after write enters the interactive interface, enter @ to enter the relevant parameters as prompted, and then exit the command line. A .innotop / innotop.conf is generated in the current directory, which records the password information of the data. [as long as we are prompted in the last step whether to save the password to the text, select n to save only the configuration information without recording the password]

Enter? to display all supported commands, as shown in the following figure:

Press the Q key to exit the inntop console interface.

In the current directory, automatically generate a hidden folder of .innotop. Innotop.conf stores the connection mode of the database.

If we do not exit the Dashboard interface and type I directly, we can see the IO-related status, as shown below:

Enter Q to view query list

Then press e and enter thread ID to show the execution plan or f to show the full sql statement, or o to display the system-optimized statement (requires the version of MySQL to support EXPLAIN EXTENDED)

Innotop obtains the complete sql statement through information_schema.processlist and filters out idle threads based on COMMAND.

Enter S, and then select the test connection configuration above to view Variables & Status.

Enter B to display the status of the Buffer Pool:

Enter M to view the status of master-slave replication. The following is a screenshot on master:

Mytop

It's relatively simple.

Mytop-user root-pass root

Mysqltuner

A pl script where mysqltuner enters a user name and password when prompted to analyze and give recommendations for optimization. It can also be installed directly using yum install mysqltuner.

Orzdba

Http://code.taobao.org/p/orzdba/src/trunk/

Chmod + x orzdba is fine.

I personally often use the following two, and the other states are obtained by using dstat, sar and other commands.

Note: there is no place to enter the user name and password by default for the calls of the following two parameters, so it is recommended to write the password in the client section of / etc/my.cnf.

. / orzdba-mysql-C 10-I 1

. / orzdba-innodb-C 10-I 1

Tcpdump captures the instant SQL of packet capture

Step1 、

Tcpdump-s 65535-x-nn-Q-tttt-I eth0-c 5000 port 3306 > / root/dump.txt

The above command parameters mean:

-s # snaplen, that is, the intercept length of the packet

-nn # displays addresses and ports in numeric format [frequently used]

-Q # fast output. Output only a small amount of protocol information. [often used]

-tttt # add date printing before the timestamp of each line [frequently used]

-c # after receiving the specified number of packets, tcpdump stops.

Step2 、

The result can be analyzed by pt-query-digest-type tcpdump/root/dump.txt.

#! / bin/bash

# collect and analyze 1000 packets of sql packet data in real time

# Date:2017/05/02

# Note that the NICs of some machines are eth2 or multiple NICs, which need to be modified in the script

Source/etc/profile

[!-e / usr/sbin/tcpdump] & & yum install tcpdump-y > / dev/null 2 > & 1

# if you grab too little data, you can modify-c 5000 grab more data for analysis

Tcpdump-s 65535-x-nn-Q-tttt-I eth0-c 5000 port 3306 > / root/dump.txt & & pt-query-digest-- type tcpdump / root/dump.txt > / root/pt_result.log

If [$?-eq 0]; then

Echo "output Statistical results to / root/pt_result.log" & & rm-f / root/dump.txt

Else

Echo "Program running error"

Fi

Dodba [recommended]

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.

Remote system information is collected by connecting to the remote server through ssh (username and password or establishing trust). 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.

Http://www.ywnds.com/?p=7499

Download address: https://github.com/dblucyne/dodba_tools

Wget https://raw.githubusercontent.com/dblucyne/dodba_tools/master/doDBA--no-check-certificate

Wget https://raw.githubusercontent.com/dblucyne/dodba_tools/master/doDBA.conf--no-check-certificate

Chmod + x doDBA

The function is very powerful, the official address: https://github.com/dblucyne/dodba_tools introduces in great detail

There is a more comprehensive introduction on how to use it on the official account of the operation and maintenance gang. Here are some of my excerpts:

. / doDBA-help View help

Collect Linux performance data

. / doDBA-h=192.168.2.11-sys # to get information via ssh remote connection

Collect MySQL performance data

. / doDBA-h=127.0.0.1-mysql

Before execution, you need to modify the user name, password and port of mysql on the doDBA.conf configuration:

{

"Host":

"Huser": "root"

"Hport": "22"

"Hpwd":

"Muser": "root"

"Mpwd": "Abcd@1234"

"Mport": "3306"

}

# one thing to note here is that when collecting remote mysql, you must first authorize the native IP on the target MySQL, otherwise an error similar to the figure below will occur.

Collect InnoDB performance data

. / doDBA-h=127.0.0.1-innodb

Collect MySQL and Linux performance data

. / doDBA-h=127.0.0.1-myall

Features similar to Mytop

. / doDBA-h=127.0.0.1-mytop

Collect log files

. / doDBA-h=127.0.0.1-mytop-log

Enable the Doing function

. / doDBA-h=127.0.0.1-myall-tweak 3 # automatically executes show processlist and show engine innodb status\ G when there are more than 3 active threads and saves the collected information to dbdba.log

Cooperate with shell batch collection

The cat ip.txt content is as follows:

192.168.2.11

192.168.2.12

Cat ip.txt | while read ip; do echo $ip;. / doDBA-h=$ip-mysql-log

Important recommendation: my.cnf Parameter Calculator (recommended):

Http://www.mysqlcalculator.com/

For servers with different physical hardware, some parameters of my.cnf need to be adjusted. If the parameters are set too large, it may lead to OOM. Therefore, it is necessary to use this calculator to calculate.

Some of the above is based on the ppt of NetEase and Wang Hongquan.

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.