I tried out the convenient features of percona-toolkit, which makes MySQL operations easier.
I'm Tomoya Suzuki (@oshiborii), an intern in the Service Reliability Group (SRG) of the Media Management Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article introduces some of the tools from percona-toolkit, a suite of tools that assists with MySQL operation and monitoring, that I find particularly useful. I hope this article will be helpful to readers who perform MySQL operation and monitoring on a daily basis.
pt-upgrade
pt-upgradeThis tool executes the same query against different versions of MySQL servers and records the differences in execution results, execution speed, and whether or not errors occurred.
As you may know, when upgrading to a major version of MySQL, queries that previously worked may no longer be executable, and the response time of certain queries may drastically slow down due to changes in the optimizer's behavior. This tool allows you to anticipate which queries will become slow, and if so, which ones.
The basic usage is as follows:
*Some of the command examples below are excerpted from the official website.
In the simplest case, it would look like this. This tool compares the execution results of two MySQL servers, so you basically need to specify two hosts. However, it also supports comparing 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 = ?That covers the basic usage, but there are also the following points to keep in mind.
- It cannot be used in a production environment (or more accurately, it can be used, but it shouldn't be).
- Update queries cannot be executed (in principle).
—no-read-onlypt-fingerprint
pt-fingerprintThis tool extracts fingerprints from query logs. Using this tool, you can check what types of queries frequently appear in slow query logs.
The basic usage of pt-fingerprint is as follows:
In the simplest case, it would look like this. Simple, right?
The execution result will be as follows:
However, since this tool simply converts SQL statements to fingerprints on a one-to-one basis, it will output duplicate fingerprints as shown above. A little ingenuity is needed to use it more effectively. For example,I want to sort and display the most frequently occurring fingerprints in the slow query log by their frequency of appearance.In that case, the command would be as follows:
This will produce the following execution result:
However, the same thing can actually be achieved using mysqldumpslow.
I think the strength of pt-fingerprint lies in its simple functionality of only providing the ability to retrieve a fingerprint, making it convenient when combined with other tools. If you simply want to analyze slow queries, mysqldumpslow is often more suitable.
pt-query-digest
pt-query-digestThis is a relatively commonly used and convenient tool. This tool neatly summarizes and outputs statistics from slow query logs. It can be used to find out the execution time of the slowest queries or to understand the time ranges in which slow queries are concentrated.
The basic usage is as follows:
In the simplest case, you just need to specify the file for the slow query log.
The execution result will be as follows:
Rows examinemaxFollowing the overall results, statistical information for each query is also output.
The content is almost the same.
pt-duplicate-key-checker
pt-duplicatekey-checkerThis is a tool for detecting duplicate or redundant indexes. Specifically, it can detect the following types of indexes:
- The specified column names and order must match exactly.
- The left side of the specified column name and order must be a partial match.
For example, consider the following table:
Let's assume we've added the following index to this table.
In this case, the left side of idx1 and idx2 is considered redundant and detected because the column names and their order match. On the other hand, as follows:If the order in which the columns are specified is different, it will not be considered redundant.In the case of composite indexes, the usage changes significantly depending on the order in which the columns are specified, so this behavior is correct.
If idx1 and idx2 are completely duplicates, the output will look like this. The output contains an ALTER statement to drop the duplicate index, and information about which index is duplicated is included as a comment. Therefore, this output itself can be executed as an SQL file.
*Some modifications have been made to the output, so the actual output may differ.
pt-index-usage
pt-index-usagept-index-usage is 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 the query log, which is received as input, is EXPLAIN'd.
The basic usage is as follows:
If you run this as the root user against a local MySQL instance, it will look like this:
This tool can be used to identify unnecessary indexes. Since the presence of unnecessary indexes can trigger incorrect optimizer behavior, it would be beneficial to periodically run this tool to retain only the necessary indexes.
The execution result will be as follows. Similar to pt-duplicate-key-checker, the output will contain ALTER statements that drop unused indices.
general_logIn conclusion
SRG is looking for new team members.
If you are interested, please contact us here.
SRG runs a podcast where we chat about the latest hot IT technologies and books. We hope you'll enjoy listening to it while you work.