A tutorial on collecting system and performance information for Mysql with Percona-toolkit _mysql

System Class Tools
1. Pt-diskstats
Function Introduction:
It's an interactive monitoring tool for Gun/linux.
Usage Introduction:
Pt-diskstats [OPTION ...] [FILES]
Printing disk IO Statistics for gun/linux is a bit like iostat, but the tool is interactive and more detailed than Iostat. Data collected from remote machines can be analyzed.
Use examples:
Example 1: View the status of all disks on this computer:

Pt-diskstats

Example 2: View only the status of a native SDA2 disk

Pt-diskstats--devices-regex Sda2

2. Pt-fifo-split
Function Introduction:
Simulate the cutting file and pass through the pipeline to the first in first out queue without the actual cutting file
Usage Introduction:
pt-fifo-split [Options] [FILE ...]
Pt-fifo-split read the data in large files and print to the FIFO file, each reached the specified number of lines to the FIFO file to print an EOF character, read finished, close off the FIFO file and remove, and then rebuild the FIFO file, print more lines. This ensures that each time you read it, you will be able to read the line count until the read is complete. Note This tool can only work on Unix-like operating systems. This program is very useful for the data import database of large files, specific to see http://www.mysqlperformanceblog.com/2008/07/03/ how-to-load-large-files-safely-into-innodb-with-load-data-infile/.

Use examples:
Example 1: An example of reading a 1 million-line record at a time:

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

Example 2: One reads 1 million rows at a time, specifies the FIFO file as/tmp/my-fifo, 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_k ey_checks=0; Set sql_log_bin=0; Set unique_checks=0; Load data local infile '/tmp/my-fifo ' to table load_test fields terminated by ' \ t ' lines terminated by ' \ n ' (col1, col2) ;"
  Sleep 1;
Done

3. Pt-summary
Function Introduction:
A friendly collection and display of system Information profiles, this tool is not a tuning or diagnostic tool that produces a report that is easy to compare and send mail.
Usage Introduction:

Pt-summary

Rationale: This tool runs and commands to collect system status and configuration information, save it to a file in a temp directory, and then run some UNIX commands to format the results, preferably with the root user or a privileged user running the command.
Use examples:
Example 1: View the Local System Information overview

Pt-summary

4. Pt-stalk
Function Introduction:
Collect MySQL's data for diagnostics when problems arise
Usage Introduction:
Pt-stalk [Options] [--MYSQL options]
Pt-stalk waits for the trigger condition to trigger and then collects data to help with the error diagnosis, which is designed to run with root, so you can diagnose intermittent problems that you cannot directly observe. The default diagnostic trigger condition is show GLOBAL STATUS. You can also specify Processlist as a diagnostic trigger condition, using the--function parameter.
Use examples:
Example 1: Specifies that the diagnostic trigger condition is status, triggers when the run statement is over 20, and the collected data is stored in the/tmp/test directory:

Pt-stalk--function status--variable threads_running--threshold-----dest/tmp/test 5

Example 2: Specifies that the diagnostic trigger condition is processlist and that more than 20 states are statistics triggered, and the collected data is stored in the/tmp/test directory:

Pt-stalk--function processlist--variable State--match statistics--threshold-----dest/tmp/test -h192.168.3.135

Post the information collected after the trigger condition is reached:

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 class Tools
1. Pt-index-usage
Function Introduction:
Read the SUSSU statement from the log file and use explain to analyze how they used the index. After the analysis is completed, a report is generated about the index not being queried for use.
Usage Introduction:
Pt-index-usage [OPTION ...] [FILE ...]
You can get the SQL format from the Sql,file file directly from the slow query, which must be the same as in the slow query, if you don't always need to convert it with pt-query-digest. You can also save it directly to the database without generating a report, as shown in the following example
Use examples:
View index usage examples from SQL in a full query:

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

To save the analysis results to a database example:

Pt-index-usage/data/dbdata/localhost-slow.log--host=localhost--user=root--password=zhang@123--no-report-- Create-save-results-database

Using--create-save-results-database automatically generates databases and tables to hold the results.
2. PT-PMP
Function Introduction:
Perform an aggregated gdb stack trace for the query program, advance the stack trace, and then summarize the tracking information.
Usage Introduction:
PT-PMP [OPTIONS] [FILES]
Use examples:

Pt-pmp-p 21933
pt-pmp-b/usr/local/mysql/bin/mysqld_safe

3. Pt-visual-explain
Function Introduction:
Format explain out of the execution plan according to tree mode output, easy to read.
Usage Introduction:
Pt-visual-explain [OPTION ...] [FILE ...]
Option please refer to the official website, no one by one cases here!
Use examples:
See examples of AAA files that contain explain results:

Pt-visual-explain AAA

To view an example of a AAA file that contains query statements:

Pt-visual-explain--connect AAA--user=root--password=zhang@123

Example of direct view of explain output through pipelines:

Mysql-uroot-pzhang@123-e "Explain select email from test.collect_data where id=101992419" |pt-visual-explain