I tried out some useful features of percona-toolkit that make MySQL operations easier
My name is Tomoya Suzuki (@oshiborii), and I am an intern in the Service Reliability Group (SRG) of the Media Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article introduces some of the tools in the percona-toolkit that I consider particularly useful for operating and monitoring MySQL. I hope this article will be helpful to readers who operate and monitor MySQL on a daily basis.
pt-upgrade
pt-upgradeis a tool that runs the same query against different versions of MySQL servers and records the differences in the execution results, execution speed, whether or not there were any errors, etc.
As you all know, when upgrading a major version of MySQL, queries that previously worked may no longer be able to be executed, and responses to certain queries may suddenly slow down due to changes in the optimizer's behavior. By using this tool, you can find out in advance whether there are any queries that will slow down, and if so, what kind of queries they are.
The basic usage is as follows:
* Some of the command examples below are excerpted from the official website.
In the simplest case, it looks like this: This tool compares the execution results of two MySQL servers, so you basically need to specify two hosts. However, it also supports comparison with results that have already been executed, in which case you only need to specify one host.
The execution result will be as follows.
*The values inside are dummy values.
select * from test where id = ?The above is the basic usage, but there are some points to note:
- Not suitable for production use (or, more accurately, it can, but shouldn't be).
- Update queries cannot be executed (basically)
—no-read-onlypt-fingerprint
pt-fingerprintis a tool that extracts fingerprints from query logs. By using this tool, you can check which queries appear frequently in the slow query log.
The basic usage of pt-fingerprint is as follows:
In the simplest case, it looks like this: Simple, right?
The execution result will be as follows.
However, this tool simply converts SQL statements into fingerprints one-to-one, so it will output duplicate fingerprints as shown above. To use it more conveniently, you need to use a little ingenuity. For example,I want to display frequently occurring fingerprints in the slow query log sorted by frequency of occurrence.In this case, the command would be as follows:
This will give you the following execution result:
However, you can actually achieve the same thing using mysqldumpslow.
The good thing about pt-fingerprint is that it only provides the simple function of obtaining fingerprints, so it is useful when combined with other tools. If you simply want to analyze slow queries, mysqldumpslow is often a better choice.
pt-query-digest
pt-query-digestis also a relatively commonly used and useful tool. This tool nicely summarizes and outputs statistical information from the slow query log. It can be used to find out the execution time of the slowest query or to understand the range of execution time where slow queries are concentrated.
The basic usage is as follows:
In the simplest case, you just specify a slow query log file.
The execution result will be as follows.
Rows examinemaxFollowing the overall results, statistics for each query are also printed.
The content remains almost the same.
pt-duplicate-key-checker
pt-duplicatekey-checkeris a tool that detects duplicate or redundant indexes. Specifically, it detects the following indexes:
- The specified column names and order must match exactly
- The specified column name and the left side of the order partially match
For example, consider the following table:
Suppose we add the following index to this table:
In this case, the left side of idx1 and idx2 are detected as redundant because the column names and their order match.If the order of columns is different, it is not considered redundant.In the case of composite indexes, this behavior is correct because the order in which the columns are specified can have a significant effect on their usage.
If idx1 and idx2 are completely duplicated, the output will look like this. The output contains an ALTER statement to DROP the duplicated index, and information about which index is duplicated is in a comment. Therefore, this output itself can be executed as an SQL file.
* The output has been partially modified, so the actual output may differ from this.
pt-index-usage
pt-index-usageis a tool that detects unused indexes. pt-index-usage collects all indexes in the target MySQL database and detects unused indexes by comparing them with the index information output when EXPLAINing the query log it receives as input.
The basic usage is as follows:
If you run it as the root user against a local MySQL installation:
This tool can be used to find unnecessary indexes. Since the existence of unnecessary indexes can cause the optimizer to behave incorrectly, it would be great to run this tool periodically and keep only the necessary indexes.
The execution result will be as follows. As with pt-duplicate-key-checker, the output will contain an ALTER statement to drop unused indexes.
general_logConclusion
SRG is looking for people to work with us.
If you're interested, please contact us here.
SRG runs a podcast where we chat about the latest hot topics in IT technology and books. We hope you'll listen to it while you work.