Percona Toolkit 2.2：全面的MySQL管理工具集

需积分: 6 75 浏览量更新于2024-07-20 收藏 1.17MB PDF 举报

Percona Toolkit 2.2 是一个强大的、功能丰富的MySQL管理工具集，由Percona LLC及其关联公司于2016年8月16日发布。这个版本提供了多种实用的数据库管理和优化工具，旨在帮助用户更有效地管理和维护MySQL服务器。以下是一些关键工具的简要概述： 1. **安装**：文档首先介绍了如何获取和安装Percona Toolkit，确保用户能够将其集成到他们的系统中。 2. **工具列表**： - **pt-align**: 用于调整数据文件的物理顺序，提高磁盘I/O性能。 - **pt-archiver**: 数据备份和归档工具，支持增量和差异备份。 - **pt-conﬁg-diff**: 比较和跟踪MySQL配置的变化，便于管理和审计。 - **pt-deadlock-logger**: 监控并记录死锁事件，有助于排查并发问题。 - **pt-diskstats**: 分析磁盘I/O统计，优化存储性能。 - **pt-duplicate-key-checker**: 检查和报告表中的重复键，提升数据完整性。 - **pt-ﬁfo-split**: 分割大型FIFO（先进先出）队列，处理大文件传输。 - **pt-find**: 在数据库中搜索特定模式或条件，进行数据挖掘。 - **pt-fingerprint**: 识别和分析查询模式，用于性能优化。 - **pt-fk-error-logger**: 记录外键约束错误，有助于修复数据一致性问题。 - **pt-heartbeat**: 监控主从复制状态，确保数据同步。 - **pt-index-usage**: 分析索引使用情况，指导索引优化。 - **pt-ioprofile**: 详细记录SQL查询的I/O操作，用于性能分析。 - **pt-kill**: 杀死运行中的MySQL进程，但在实际使用时应谨慎操作。 - **pt-mext**: 用于管理MySQL扩展（plugins），如存储引擎和插件的控制。 - **pt-mysql-summary**: 数据库概览工具，提供简洁的性能指标。 - **pt-online-schema-change**: 在不中断服务的情况下执行在线表结构更改。 - **pt-pmp**: 进程管理工具，监控和调整MySQL服务进程。 - **pt-query-digest**: 查询优化器助手，提供查询分析和建议。 - **pt-show-grants**: 显示用户的权限和授权信息，管理用户权限。 - **pt-sift**: 数据过滤和处理工具，用于数据清洗和预处理。 - **pt-slave-delay**: 跟踪复制延迟，确保数据复制的一致性。 - **pt-slave-find**: 找出可能的问题奴隶，用于故障排查。 - **pt-slave-restart**: 定时或手动重启从库，恢复复制状态。 - **pt-stalk**: 监控多台MySQL服务器的性能和健康状况。 - **pt-summary**: 性能概览工具，快速查看服务器状态。 - **pt-table-checksum**: 检查和修复表的完整性。 - **pt-table-sync**: 同步表数据，确保数据一致性。 - **pt-table-usage**: 分析表空间使用情况，管理数据存储。 - **pt-upgrade**: 升级工具，协助进行Percona Toolkit的版本更新。 - **pt-variable-advisor**: 变量建议器，优化MySQL配置参数。 - **pt-visual-explain**: 提供可视化的SQL解释，便于理解执行计划。 3. **配置**：文档还涵盖了配置文件、数据源名称（DSN）规格和环境变量的设置，以确保工具的正确使用和适应不同的环境需求。这些工具覆盖了从数据管理、性能监控、配置优化到故障排查等多个方面，使得Percona Toolkit 2.2成为MySQL管理员不可或缺的实用工具集合。通过学习和利用这些工具，用户可以显著提升MySQL数据库的稳定性和性能。

Percona Toolkit Documentation, Release 2.2.19

If --check-slave-lag is given, this deﬁnes how long the tool pauses each time it discovers that a slave

is lagging. This check is performed every 100 rows.

--check-slave-lag

type: string; repeatable: yes

Pause archiving until the speciﬁed DSN’s slave lag is less than --max-lag. This option can be speciﬁed

multiple times for checking more than one slave.

--columns

short form: -c; type: array

Comma-separated list of columns to archive.

Specify a comma-separated list of columns to fetch, write to the ﬁle, and insert into the destination table. If

speciﬁed, pt-archiver ignores other columns unless it needs to add them to the SELECT statement for ascending

an index or deleting rows. It fetches and uses these extra columns internally, but does not write them to the ﬁle

or to the destination table. It does pass them to plugins.

See also --primary-key-only.

--commit-each

Commit each set of fetched and archived rows (disables --txn-size).

Commits transactions and ﬂushes --file after each set of rows has been archived, before fetching the next set

of rows, and before sleeping if --sleep is speciﬁed. Disables --txn-size; use --limit to control the

transaction size with --commit-each.

This option is useful as a shortcut to make --limit and --txn-size the same value, but more importantly

it avoids transactions being held open while searching for more rows. For example, imagine you are archiving

old rows from the beginning of a very large table, with --limit 1000 and --txn-size 1000. After some

period of ﬁnding and archiving 1000 rows at a time, pt-archiver ﬁnds the last 999 rows and archives them,

then executes the next SELECT to ﬁnd more rows. This scans the rest of the table, but never ﬁnds any more

rows. It has held open a transaction for a very long time, only to determine it is ﬁnished anyway. You can use

--commit-each to avoid this.

--config

type: Array

Read this comma-separated list of conﬁg ﬁles; if speciﬁed, this must be the ﬁrst option on the command line.

--database

short form: -D; type: string

Connect to this database.

--delayed-insert

Add the DELAYED modiﬁer to INSERT statements.

Adds the DELAYED modiﬁer to INSERT or REPLACE statements. See

http://dev.mysql.com/doc/en/insert.html for details.

--dest

type: DSN

DSN specifying the table to archive to.

This item speciﬁes a table into which pt-archiver will insert rows archived from --source. It uses the same

key=val argument format as --source. Most missing values default to the same values as --source, so

you don’t have to repeat options that are the same in --source and --dest. Use the --help option to see

which values are copied from --source.

12 Chapter 2. Tools

Percona Toolkit Documentation, Release 2.2.19

WARNING: Using a default options ﬁle (F) DSN option that deﬁnes a socket for --source causes pt-

archiver to connect to --dest using that socket unless another socket for --dest is speciﬁed. This means

that pt-archiver may incorrectly connect to --source when it connects to --dest. For example:

--source F=host1.cnf,D=db,t=tbl --dest h=host2

When pt-archiver connects to --dest, host2, it will connect via the --source, host1, socket deﬁned in

host1.cnf.

--dry-run

Print queries and exit without doing anything.

Causes pt-archiver to exit after printing the ﬁlename and SQL statements it will use.

--file

type: string

File to archive to, with DATE_FORMAT()-like formatting.

Filename to write archived rows to. A subset of MySQL’s DATE_FORMAT() formatting codes are allowed in

the ﬁlename, as follows:

%d Day of the month, numeric (01..31)

%H Hour (00..23)

%i Minutes, numeric (00..59)

%m Month, numeric (01..12)

%s Seconds (00..59)

%Y Year, numeric, four digits

You can use the following extra format codes too:

%D Database name

%t Table name

Example:

--file '/var/log/archive/%Y-%m-%d-%D.%t'

The ﬁle’s contents are in the same format used by SELECT INTO OUTFILE, as documented in the MySQL

manual: rows terminated by newlines, columns terminated by tabs, NULL characters are represented by N, and

special characters are escaped by . This lets you reload a ﬁle with LOAD DATA INFILE’s default settings.

If you want a column header at the top of the ﬁle, see --header. The ﬁle is auto-ﬂushed by default; see

--buffer.

--for-update

Adds the FOR UPDATE modiﬁer to SELECT statements.

For details, see http://dev.mysql.com/doc/en/innodb-locking-reads.html.

--header

Print column header at top of --file.

Writes column names as the ﬁrst line in the ﬁle given by --file. If the ﬁle exists, does not write headers; this

keeps the ﬁle loadable with LOAD DATA INFILE in case you append more output to it.

--help

Show help and exit.

--high-priority-select

Adds the HIGH_PRIORITY modiﬁer to SELECT statements.

See http://dev.mysql.com/doc/en/select.html for details.

2.2. pt-archiver 13

Percona Toolkit Documentation, Release 2.2.19

--host

short form: -h; type: string

Connect to host.

--ignore

Use IGNORE for INSERT statements.

Causes INSERTs into --dest to be INSERT IGNORE.

--limit

type: int; default: 1

Number of rows to fetch and archive per statement.

Limits the number of rows returned by the SELECT statements that retrieve rows to archive. Default is one

row. It may be more efﬁcient to increase the limit, but be careful if you are archiving sparsely, skipping over

many rows; this can potentially cause more contention with other queries, depending on the storage engine,

transaction isolation level, and options such as --for-update.

--local

Do not write OPTIMIZE or ANALYZE queries to binlog.

Adds the NO_WRITE_TO_BINLOG modiﬁer to ANALYZE and OPTIMIZE queries. See --analyze for

details.

--low-priority-delete

Adds the LOW_PRIORITY modiﬁer to DELETE statements.

See http://dev.mysql.com/doc/en/delete.html for details.

--low-priority-insert

Adds the LOW_PRIORITY modiﬁer to INSERT or REPLACE statements.

See http://dev.mysql.com/doc/en/insert.html for details.

--max-flow-ctl

type: ﬂoat

Somewhat similar to –max-lag but for PXC clusters. Check average time cluster spent pausing for Flow Control

and make tool pause if it goes over the percentage indicated in the option. Default is no Flow Control checking.

This option is available for PXC versions 5.6 or higher.

--max-lag

type: time; default: 1s

Pause archiving if the slave given by --check-slave-lag lags.

This option causes pt-archiver to look at the slave every time it’s about to fetch another row. If the slave’s lag

is greater than the option’s value, or if the slave isn’t running (so its lag is NULL), pt-table-checksum sleeps

for --check-interval seconds and then looks at the lag again. It repeats until the slave is caught up, then

proceeds to fetch and archive the row.

This option may eliminate the need for --sleep or --sleep-coef.

--no-ascend

Do not use ascending index optimization.

The default ascending-index optimization causes pt-archiver to optimize repeated SELECT queries so they

seek into the index where the previous query ended, then scan along it, rather than scanning from the beginning

of the table every time. This is enabled by default because it is generally a good strategy for repeated accesses.

14 Chapter 2. Tools

Percona Toolkit Documentation, Release 2.2.19

Large, multiple-column indexes may cause the WHERE clause to be complex enough that this could actually

be less efﬁcient. Consider for example a four-column PRIMARY KEY on (a, b, c, d). The WHERE clause to

start where the last query ended is as follows:

WHERE (a > ?)

OR (a = ? AND b > ?)

OR (a = ? AND b = ? AND c > ?)

OR (a = ? AND b = ? AND c = ? AND d >= ?)

Populating the placeholders with values uses memory and CPU, adds network trafﬁc and parsing overhead, and

may make the query harder for MySQL to optimize. A four-column key isn’t a big deal, but a ten-column key

in which every column allows NULL might be.

Ascending the index might not be necessary if you know you are simply removing rows from the beginning

of the table in chunks, but not leaving any holes, so starting at the beginning of the table is actually the most

efﬁcient thing to do.

See also --ascend-first. See “EXTENDING” for a discussion of how this interacts with plugins.

--no-delete

Do not delete archived rows.

Causes pt-archiver not to delete rows after processing them. This disallows --no-ascend, because enabling

them both would cause an inﬁnite loop.

If there is a plugin on the source DSN, its before_delete method is called anyway, even though pt-archiver

will not execute the delete. See “EXTENDING” for more on plugins.

--optimize

type: string

Run OPTIMIZE TABLE afterwards on --source and/or --dest.

Runs OPTIMIZE TABLE after ﬁnishing. See --analyze for the option syntax and

http://dev.mysql.com/doc/en/optimize-table.html for details on OPTIMIZE TABLE.

--password

short form: -p; type: string

Password to use when connecting. If password contains commas they must be escaped with a backslash:

“exam,ple”

--pid

type: string

Create the given PID ﬁle. The tool won’t start if the PID ﬁle already exists and the PID it contains is different

than the current PID. However, if the PID ﬁle exists and the PID it contains is no longer running, the tool will

overwrite the PID ﬁle with the current PID. The PID ﬁle is removed automatically when the tool exits.

--plugin

type: string

Perl module name to use as a generic plugin.

Specify the Perl module name of a general-purpose plugin. It is currently used only for statistics (see

--statistics) and must have new() and a statistics() method.

The new( src = $src, dst => $dst, opts => $o )> method gets the source and destination DSNs, and their

database connections, just like the connection-speciﬁc plugins do. It also gets an OptionParser object ($o) for

accessing command-line options (example: ‘‘$o-‘‘get(‘purge’);>).

The statistics(\%stats, $time) method gets a hashref of the statistics collected by the archiving

job, and the time the whole job started.

2.2. pt-archiver 15

Percona Toolkit Documentation, Release 2.2.19

--port

short form: -P; type: int

Port number to use for connection.

--primary-key-only

Primary key columns only.

A shortcut for specifying --columns with the primary key columns. This is an efﬁciency if you just want

to purge rows; it avoids fetching the entire row, when only the primary key columns are needed for DELETE

statements. See also --purge.

--progress

type: int

Print progress information every X rows.

Prints current time, elapsed time, and rows archived every X rows.

--purge

Purge instead of archiving; allows omitting --file and --dest.

Allows archiving without a --file or --dest argument, which is effectively a purge since the rows are just

deleted.

If you just want to purge rows, consider specifying the table’s primary key columns with

--primary-key-only. This will prevent fetching all columns from the server for no reason.

--quick-delete

Adds the QUICK modiﬁer to DELETE statements.

See http://dev.mysql.com/doc/en/delete.html for details. As stated in the documentation, in some cases it may

be faster to use DELETE QUICK followed by OPTIMIZE TABLE. You can use --optimize for this.

--quiet

short form: -q

Do not print any output, such as for --statistics.

Suppresses normal output, including the output of --statistics, but doesn’t suppress the output from

--why-quit.

--replace

Causes INSERTs into --dest to be written as REPLACE.

--retries

type: int; default: 1

Number of retries per timeout or deadlock.

Speciﬁes the number of times pt-archiver should retry when there is an InnoDB lock wait timeout or deadlock.

When retries are exhausted, pt-archiver will exit with an error.

Consider carefully what you want to happen when you are archiving between a mixture of transactional and

non-transactional storage engines. The INSERT to --dest and DELETE from --source are on separate

connections, so they do not actually participate in the same transaction even if they’re on the same server.

However, pt-archiver implements simple distributed transactions in code, so commits and rollbacks should

happen as desired across the two connections.

At this time I have not written any code to handle errors with transactional storage engines other than InnoDB.

Request that feature if you need it.

--run-time

type: time

16 Chapter 2. Tools

剩余386页未读，继续阅读

sampan612

粉丝: 0
资源: 3

Percona Toolkit 2.2：全面的MySQL管理工具集

percona-toolkit 2.2安装包

数据库性能调优--原理与技术

MySQL优化 实战视频课程

ubuntu16.04 percona-toolkit安装

使用rpm安装percona-toolkit3.3.1

percona-toolkit工具

percona-toolkit-3.0.12-1.el7.x86_64.rpm

xtrabackup2.3.2 支持MySQL5.5吗

xtrabackup 2.3.10 可以用在MySQL5.5吗

percona-toolkit的安装及使用

最新资源

MySQL优化实战视频课程