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

Introduction to pt (Percona Toolkit) tool

one。 Introduction

1. Find duplicate indexes and foreign keys

Pt-duplicate-key-checker

Example: [root@node1 ~] # pt-duplicate-key-checker-- user=root-- password='abc123'

#

# Summary of indexes

#

# Total Indexes 89

This tool lists duplicate indexes and foreign keys, and generates statements to delete duplicate indexes, which is very convenient.

two。 Write and read operations are not blocked when alter is executed

Pt-online-schema-change

A very famous tool, the working principle is to create an empty table structure like the table you want to perform alter operation, perform table structure modification, and then create a trigger from the original table to synchronize the newly inserted and changed data to the new table, and then copy the original data to the table structure modified table. When the data copy is complete, the original table will be removed and the original table will be replaced with the new table. The default action is to drop the original table. Therefore, if the trigger is already defined in the table, the tool will not work. We should also pay attention to the problem of data backup.

3. Analyze the sql or the whole sql in the log record and make suggestions

Pt-query-advisor

It is of little practical significance, and it is easy to get stuck in the analysis of large files.

4. Formatted display mysql permissions

Pt-show-grants

Example: [root@node1 ~] # pt-show-grants-- user=root-- password='abc123'

It is of little practical significance, mainly to compare mysql permissions and version control.

5. Execute queries on multiple servers and compare the differences

Pt-upgrade

This is very useful when upgrading the server, you can first install and import the data to the new server, and then use this tool to run sql to see what the differences are, and you can find out the differences between different versions.

6. Use explain to analyze how sql uses indexes and generate reports

Pt-index-usage

You can get the sql format in the sql,FILE file directly from the slow query. The sql format in the slow query must be the same as the one in the slow query, if you don't always need to convert it with pt-query-digest. You can also save it to the database without generating a report.

7. The query program performs an aggregated GDB stack trace and summarizes

Pt-pmp

It looks complicated, but it actually belongs to the behavior efficiency tracking of the underlying program.

8. The execution plan from formatted explain is output in tree mode.

Pt-visual-explain

It doesn't make any sense, it's just easy to read.

9. Compare mysql profile and server parameters

Pt-config-diff

At least two profile sources must be specified. Like the diff command under unix, nothing will be output if the configuration is exactly the same, sometimes used to eliminate problems caused by configuration differences.

10. Summarize the configuration and sataus information of mysql

Pt-mysql-summary

After connecting to mysql, query out the status and configuration information and save it to a temporary directory, then format it with awk and other scripting tools

11. Analyze the parameter variables of mysql and give some suggestions.

Pt-variable-advisor

The practical significance is limited, or according to their own needs to configure the most practical, especially multi-instance scenarios, this suggestion is meaningless.

twelve。 Summarize information about mysql deadlocks

Pt-deadlock-logger

By collecting and saving the latest deadlock information on mysql, you can directly print deadlock information and store deadlock information in the database. Deadlock information includes the server where the deadlock occurred, the time when the deadlock occurred, the deadlock thread id, the transaction id of the deadlock, how long the transaction executed when the deadlock occurred, and so on. For frequent deadlocks, it is recommended to open for a period of time to collect information.

13. Summarize mysql foreign key error messages

Pt-fk-error-logger

Extract and save the recent foreign key error information in the mysql database through SHOW INNODB STATUS. You can print the error message directly through parameter control or store the error message in the database table. However, foreign keys are rarely used now, and foreign keys are not recommended.

14. View information on multiple samples of SHOW GLOBAL STATUS

Pt-mext

It is of little practical significance, and it is not difficult to execute it manually and then analyze it.

15. Analyze the sql log and generate a report, and finally give suggestions

Pt-query-digest

Very well-known tools, mostly used to analyze statistics slow query, can also statistics general sql log, run fast, clear information. It can analyze binlog, General log, slowlog, or MySQL protocol data captured by SHOW PROCESSLIST or tcpdump. The analysis results can be output to a file, and the analysis process is to parameterize the conditions of the query statement first, and then group the queries after parameterization to calculate the execution time, times, proportion and so on. We can use the analysis results to find out the problem and optimize it.

16. Count sql logs by time and generate reports

Pt-trend

It is of little practical significance, for example, reading the local slow query log and outputting statistical information.

17. Monitoring mysql replication latency

Pt-heartbeat

It will generate a data table and record the delay time, but the function is not very good. If there is no full control of the database or network problems, it is easy to use it with caution, which is easy to cause data inconsistency.

18. Set the slave server to lag behind the master server for a specified time

Pt-slave-delay

It is recommended that no, the function is not good, it is easy to hang up the master-slave structure, and it will be troublesome if we have to redo the master-slave structure.

19. Find and print all mysql replication hierarchies from the server

Pt-slave-find

Connect to the mysql master server and find all its slaves, and then print out the hierarchical relationships of all the slave servers.

20. Monitor mysql replication errors and try to restart mysql replication

Pt-slave-restart

Monitor one or more mysql replication errors and try to restart replication when the slave stops. It belongs to monitoring related.

21. Check mysql replication consistency

Pt-table-checksum

Well-known tools, which only need to be executed on master, usually collaborate with pt-table-sync. How it works: pt-table-checksum executes a check statement on the master to check the consistency of mysql replication online, then passes it to the slave through replication, and then updates the value of master_src through update. Determine whether the replication is consistent by detecting the values of this_src and master_src from the top. Finally, write the check report to a certain location and wait for the pt-table-sync call. According to the principle, it requires Statement and Mixed for binlog_format to work properly, and for row mode, it will report an error and cannot be used. So before use, you should pay attention to change the configuration, set global binlog_format=STATEMENT to use, otherwise you will not be able to use it, you can change it back after running. Also pay attention to the need for certain permissions, it is best to use root.

twenty-two。 Synchronize the data of the difference table of mysql master-slave library

Pt-table-sync

A well-known tool, master-slave replication itself has some defects, in extreme cases, there will be more data from the database, or less data, this time the need for artificial repair, this tool can help us. Principle: through the data obtained by pt-table-checksum, list or directly execute the modified replicated differential data to make it resynchronized. Always change the data on the master, and then synchronize it to the slave, it will not change directly to the slave data. The change on the master is based on the current data on the master, and the data on the master will not be changed. Be careful to back up your data before use to avoid data loss. It's best to replace it with-- print or-- dry-run before executing execute to see what data will be changed.

23. Disk io Monitoring tool

Pt-diskstats

It's a bit like iostat, but this tool is interactive and more detailed than iostat. You can also analyze data collected from remote machines.

24. Simulate the cut file and pass it to the first-in, first-out queue through the pipe

Pt-fifo-split

The actual use is not much, by reading the data in a large file and printing to the fifo file, each time it reaches the specified number of lines to print an EOF character to the fifo file, after reading, close the fifo file and remove it, and then rebuild the fifo file to print more lines. This ensures that each time you read it, you will be able to read the number of rows until the read is complete. Note that this tool can only work on unix-like operating systems. This program is very useful for importing data from large files into the database

25. Display an overview of system information

Pt-summary

The tool will run multiple commands to collect system status and configuration information, save it to a file in a temporary directory, and then run some unix commands to format these results, preferably with root or authorized users.

twenty-six。 Collect mysql data for diagnostics

Pt-stalk

When you encounter some difficult and complicated diseases, you can test and obtain the problem data. How it works: pt-stalk waits for the trigger condition to trigger and then collects data to help diagnose errors. It is designed to run daemons with root privileges, so you can diagnose intermittent problems that you cannot observe directly. The default diagnostic trigger condition is SHOW GLOBAL STATUS. You can also specify processlist as the diagnostic trigger condition, using the-- function parameter.

twenty-seven。 Archive the records of a table in the mysql database to another table or file

Pt-archiver

This tool only archives old data and will not have too much impact on the OLTP query of online data. You can insert the data into other tables on another server, or you can write it to a file to facilitate the use of load data infile commands to import data. You can also use it to perform delete operations. This tool deletes data from the source by default. Please pay attention when using it.

twenty-eight。 Looks up the mysql table and executes the specified command, similar to the find command.

Pt-find

A small library is of little use, but it's useful when you have a lot of libraries and tables, and you forget the name of the table you want to look up.

29.Kill drop mysql statement that meets the specified condition

Pt-kill

If there is no specified file, pt-kill connects to mysql and finds the specified statement through SHOW PROCESSLIST, otherwise pt-kill reads the mysql statement from the file containing SHOW PROCESSLIST results. For example: delete connections that run for more than 60 seconds.

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.