Tutorial _ MySQL

This article describes how to use percona-toolkit to collect system and performance information for MySQL. you can easily enter basic server information into the database. For more information, see System Tools
1. pt-diskstats
Function introduction:
Is an interactive monitoring tool for GUN/LINUX.
Usage:
Pt-diskstats [OPTION...] [FILES]
Print disk io statistics for GUN/LINUX, which is a bit like iostat, but this tool is interactive and more detailed than iostat. Data collected from remote machines can be analyzed.
Example:
Example 1: view the status of all disks on the local machine:

pt-diskstats

Example 2: only view the status of the local sda2 disk

pt-diskstats --devices-regex sda2

2. pt-fifo-split
Function introduction:
Simulate a cut file and pass it to the first-in-first-out queue through a pipe without actually cutting the file
Usage:
Pt-fifo-split [options] [FILE...]
Pt-fifo-split reads data from large files and prints the data to the fifo file. each time it reaches the specified number of rows, it prints an EOF character to the fifo file. after reading the data, disable and remove the fifo file, then recreate the fifo file and print more rows. This ensures that you can read the specified number of rows each time you read until the read is completed. Note that this tool can only work in unix-like operating systems. This program is very useful for importing data from large files to the database. for details, refer to examples /.

Example:
Example 1: an example of reading 1 million rows of records each time:

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

Example 2: Specify/tmp/my-fifo file for each 1 million rows read, and use the load data command to import the file to mysql:

pt-fifo-split infile.txt --fifo /tmp/my-fifo --lines 1000000while [ -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:
Collects and displays system information in a friendly manner. This tool is not an optimization or diagnosis tool. it generates a report that is easy to compare and send emails.
Usage:

pt-summary

Principle: This tool will run and run multiple commands to collect system status and configuration information, first save it to the files in the temporary directory, and then run some unix commands to format the results, it is best to run this command using a root user or a user with permissions.
Example:
Example 1: view the local system information

pt-summary

4. pt-stalk
Function introduction:
Collect mysql data for diagnosis when a problem occurs.
Usage:
Pt-stalk [OPTIONS] [-- mysql options]
Pt-stalk waits for trigger conditions to trigger, and then collects data to help diagnose errors. it is designed as a daemon that runs with root permissions. Therefore, 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 the diagnostic trigger condition and use the -- function parameter.
Example:
Example 1: specify the diagnostic trigger condition as status. when the statement is run more than 20 times, the collected data is stored in the/tmp/test Directory:

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

Example 2: Set the diagnosis trigger condition to processlist. if more than 20 statuses are set to statistics trigger, the collected data is stored in the/tmp/test Directory:

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

Paste the information collected after the trigger condition is met:

2012_06_04_17_31_49-df2012_06_04_17_31_49-disk-space2012_06_04_17_31_49-diskstats2012_06_04_17_31_49-hostname2012_06_04_17_31_49-innodbstatus12012_06_04_17_31_49-innodbstatus22012_06_04_17_31_49-interrupts2012_06_04_17_31_49-log_error2012_06_04_17_31_49-lsof2012_06_04_17_31_49-meminfo2012_06_04_17_31_49-mutex-status12012_06_04_17_31_49-mysqladmin2012_06_04_17_31_49-netstat2012_06_04_17_31_49-netstat_s2012_06_04_17_31_49-opentables12012_06_04_17_31_49-opentables22012_06_04_17_31_49-output2012_06_04_17_31_49-pmap2012_06_04_17_31_49-processlist2012_06_04_17_31_49-procstat2012_06_04_17_31_49-procvmstat2012_06_04_17_31_49-ps2012_06_04_17_31_49-slabinfo2012_06_04_17_31_49-sysctl2012_06_04_17_31_49-top2012_06_04_17_31_49-trigger2012_06_04_17_31_49-variables2012_06_04_17_31_49-vmstat2012_06_04_17_31_49-vmstat-overall

Performance Tools
1. pt-index-usage
Function introduction:
Read the plug-in statement from the log file and explain how they use the index. After the analysis is completed, a report about the index that has not been used for query is generated.
Usage:
Pt-index-usage [OPTION...] [FILE...]
You can obtain the SQL directly from the slow query. the SQL format in the FILE must be the same as that in the slow query. if it is not always necessary, use pt-query-digest to convert it. You can also save the report to the database without generating it. for details, see the following example.
Example:
An example of how to view index usage from an SQL statement in full query:

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

Save the analysis result to the database example:

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

Use -- create-save-results-database to automatically generate a database and a table to save the results.
2. pt-pmp
Function introduction:
Execute the aggregated GDB stack trace for the query program. the advanced stack trace is used to summarize the tracing information.
Usage:
Pt-pmp [OPTIONS] [FILES]
Example:

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

3. pt-visual-explain
Function introduction:
The explain execution plan is formatted and output in tree mode for easy reading.
Usage:
Pt-visual-explain [OPTION...] [FILE...]
For more information about option, see the official website!
Example:
Example of an aaa file containing the explain result:

pt-visual-explain aaa

Example of viewing aaa files containing query statements:

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

An example of the explain output result can be directly viewed in a pipeline:

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