A tutorial using ES0en ES1en to gather system and performance information for MySQL

System class tools
1. pt-diskstats
Function Introduction:
GUN is an interactive monitoring tool for GUN/LINUX
Usage Introduction:
pt-diskstats [OPTION...] [FILES]
Print disk io statistics for GUN/LINUX, similar to iostat, but this tool is interactive and more detailed than iostat. Data collected from remote machines can be analyzed.
Use examples:
Example 1: Viewing the status of all disks on the machine:

pt-diskstats

Example 2: Only view the status of the native sda2 disk

pt-diskstats --devices-regex sda2

2. pt-fifo-split
Function Introduction:
The simulated cut file is piped to the first-in, first-out queue without the actual cut file
Usage Introduction:
pt-fifo-split [options] [FILE ...]
pt-fifo-split reads the data in the large file and prints to the fifo file. Each time the specified number of lines is reached, the EOF character is printed to the fifo file. When the reading is complete, the fifo file is closed and removed, then the fifo file is rebuilt and more lines are printed. This will ensure that each time you read, you will be able to read the specified number of rows until the reading is complete. Note that this tool only works on the class unix operating system. This program is very useful for large file data import database, specific can view http: / / www mysqlperformanceblog. com 2008/07/03 / how - to - load - large - files - safely - into - innodb - with - load - data - infile /.

Use examples:
Example 1:1 an example of reading 1 million rows at a time:

pt-fifo-split --lines 1000000 hugefile.txt
while [ -e /tmp/pt-fifo-split ]; do cat /tmp/pt-fifo-split; done

Example 2:1 reads 1 million lines at a time, specifies fifo file as /tmp/ ES69en-ES70en, and imports it into mysql using the load data command:

pt-fifo-split infile.txt --fifo /tmp/my-fifo --lines 1000000
while [ -e /tmp/my-fifo ]; do
  mysql -e "set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile '/tmp/my-fifo' into table load_test fields terminated by '\t' lines terminated by '\n' (col1, col2);"
  sleep 1;
done

3. pt-summary
Function Introduction:
This tool is not a tuning or diagnostic tool; it produces a report that can be easily compared and emailed.
Usage Introduction:

pt-summary

How it works: The utility runs multiple commands to collect system status and configuration information, saves it to a file in a temporary directory, and then runs 1 unix command to format the results, preferably with an root user or a user with permission.
Use examples:
Example 1: View an overview of local system information

pt-summary

4. pt-stalk
Function Introduction:
Collect mysql's diagnostic data when problems occur
Usage Introduction:
pt-stalk [OPTIONS] [-- MYSQL OPTIONS]
pt-stalk waits for the trigger condition to trigger, then collects data to aid error diagnosis. It is designed to be a daemon that runs with root permissions, so you can diagnose intermittent problems that you can't directly observe. The default diagnostic trigger is SHOW GLOBAL STATUS. You can also specify processlist as the diagnostic trigger, using the --function parameter.
Use examples:
Example 1: Specify diagnostic trigger condition as status and trigger when the statement exceeds 20 at the same time. The collected data is stored in /tmp/test directory:

pt-stalk --function status --variable Threads_running --threshold 20 --dest /tmp/test -- -uroot -pzhang@123 -h192.168.3.135

Example 2: Specify diagnostic trigger condition as processlist and more than 20 states as statistics trigger. The collected data is stored in /tmp/test directory:

pt-stalk --function processlist --variable State --match statistics --threshold 20 --dest /tmp/test -- -uroot -pzhang@123 -h192.168.3.135

Post 1 Information collected after reaching the trigger condition:

2012_06_04_17_31_49-df
2012_06_04_17_31_49-disk-space
2012_06_04_17_31_49-diskstats
2012_06_04_17_31_49-hostname
2012_06_04_17_31_49-innodbstatus1
2012_06_04_17_31_49-innodbstatus2
2012_06_04_17_31_49-interrupts
2012_06_04_17_31_49-log_error
2012_06_04_17_31_49-lsof
2012_06_04_17_31_49-meminfo
2012_06_04_17_31_49-mutex-status1
2012_06_04_17_31_49-mysqladmin
2012_06_04_17_31_49-netstat
2012_06_04_17_31_49-netstat_s
2012_06_04_17_31_49-opentables1
2012_06_04_17_31_49-opentables2
2012_06_04_17_31_49-output
2012_06_04_17_31_49-pmap
2012_06_04_17_31_49-processlist
2012_06_04_17_31_49-procstat
2012_06_04_17_31_49-procvmstat
2012_06_04_17_31_49-ps
2012_06_04_17_31_49-slabinfo
2012_06_04_17_31_49-sysctl
2012_06_04_17_31_49-top
2012_06_04_17_31_49-trigger
2012_06_04_17_31_49-variables
2012_06_04_17_31_49-vmstat
2012_06_04_17_31_49-vmstat-overall

Performance tools
1. pt-index-usage
Function Introduction:
Inserts are read from the log file, and explain analyzes how they utilize the index. Once the analysis is complete, a report is generated that the index has not been used by the query.
Usage Introduction:
pt-index-usage [OPTION...] [FILE...]
sql can be obtained directly from the slow query. The sql format in the FILE file must be 1 with the slow query. If it is not 1, es149EN-ES150en-ES151en conversion 1 is required. You can also save the report directly to the database without generating it, as shown in the following example
Use examples:
Examples of index usage from sql in a full query:

pt-index-usage /data/dbdata/localhost-slow.log --host=localhost --user=root --password=zhang@123

Save the analysis results to the database example:

pt-diskstats --devices-regex sda2

0

Using -- create-ES164en-ES165en-database automatically generates databases and tables to hold the results.
2. pt-pmp
Function Introduction:
Perform the aggregated GDB stack trace for the query program, advance stack trace, and then aggregate the trace information.
Usage Introduction:
pt-pmp [OPTIONS] [FILES]
Use examples:

pt-diskstats --devices-regex sda2

1

3. pt-visual-explain
Function Introduction:
Format explain out of the execution plan according to tree output, easy to read.
Usage Introduction:
pt-visual-explain [OPTION...] [FILE...]
option please refer to the official website, here are not 11 examples!
Use examples:
See an example of an aaa file containing explain results:

pt-diskstats --devices-regex sda2

2

See an example of an aaa file that contains a query:

pt-visual-explain --connect aaa --user=root --password=zhang@123

Examples of explain output viewed directly through a pipe:

pt-diskstats --devices-regex sda2

4