Percona Toolkit 3.0.3：全面的数据库管理工具集

需积分: 13 105 浏览量更新于2024-07-20 1 收藏 1.14MB PDF 举报

Percona Toolkit 3.0.3 是一个全面的MySQL工具集，由Percona LLC及其关联公司开发，于2017年5月19日发布。这个版本包含了众多实用工具，旨在帮助数据库管理员进行高效管理和优化，适用于Percona MySQL环境。以下是一些关键工具及其功能概述： 1. **安装**：文档首先介绍了如何获取和安装Percona Toolkit，这对于初次使用者或需要更新现有安装的用户非常重要。 2. **工具列表**： - **pt-align**: 用于调整MySQL表中的行位置，以提高磁盘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**: 找到符合特定条件的行，执行SQL查询或数据清洗任务。 - **pt-fingerprint**: 识别和跟踪MySQL服务器的身份特征。 - **pt-fk-error-logger**: 日志工具，记录外键约束错误，便于追踪和解决错误。 - **pt-heartbeat**: 实时监控MySQL服务器的健康状况和连接情况。 - **pt-index-usage**: 分析索引的使用情况，优化查询性能。 - **pt-ioprofile**: CPU和I/O性能分析工具，用于诊断和优化资源利用。 - **pt-kill**: 进程控制工具，可以强制停止或重启MySQL进程。 - **pt-mext**: 用于管理和扩展MySQL内存分配，增强系统性能。 - **pt-mongodb-query-digest**: MongoDB查询分析工具，适用于与MySQL交互的应用场景。 - **pt-mongodb-summary**: MongoDB查询汇总报告，便于理解查询性能。 - **pt-mysql-summary**: 类似于mongodb-summary，但专用于MySQL查询分析。 - **pt-online-schema-change**: 在线模式下进行数据库结构更改，减少服务中断。 - **pt-pmp**: Performance Monitoring Plugin，实时监控MySQL性能指标。 - **pt-query-digest**: SQL查询优化器，提供查询计划的深入分析。 - **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**: 提供升级指南，确保平稳迁移至新版本。 - **pt-variable-advisor**: 智能地建议MySQL变量设置，提升性能。 - **pt-visual-explain**: 可视化解释SQL查询，帮助理解和优化查询计划。 3. **配置**：文档还包含关于配置文件的管理和理解，确保正确配置这些工具以适应不同的环境需求。 Percona Toolkit 3.0.3提供了丰富的工具，覆盖了从安装、监控、性能优化到数据管理和维护的各个方面，是MySQL数据库运维人员不可或缺的实用工具集。通过合理利用这些工具，数据库管理员能够有效提升系统的稳定性和效率。

Percona Toolkit Documentation, Release 3.0.3

--txn-size and --commit-each are mutually exclusive.

--low-priority-insert and --delayed-insert are mutually exclusive.

--share-lock and --for-update are mutually exclusive.

--analyze and --optimize are mutually exclusive.

--no-ascend and --no-delete are mutually exclusive.

DSN values in --dest default to values from --source if COPY is yes.

--analyze

type: string

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

Runs ANALYZE TABLE after ﬁnishing. The argument is an arbitrary string. If it contains the letter ‘s’, the

source will be analyzed. If it contains ‘d’, the destination will be analyzed. You can specify either or both. For

example, the following will analyze both:

--analyze=ds

See http://dev.mysql.com/doc/en/analyze-table.html for details on ANALYZE TABLE.

--ascend-first

Ascend only ﬁrst column of index.

If you do want to use the ascending index optimization (see --no-ascend), but do not want to incur the

overhead of ascending a large multi-column index, you can use this option to tell pt-archiver to ascend only the

leftmost column of the index. This can provide a signiﬁcant performance boost over not ascending the index at

all, while avoiding the cost of ascending the whole index.

See “EXTENDING” for a discussion of how this interacts with plugins.

--ask-pass

Prompt for a password when connecting to MySQL.

--buffer

Buffer output to --file and ﬂush at commit.

Disables autoﬂushing to --file and ﬂushes --file to disk only when a transaction commits. This typically

means the ﬁle is block-ﬂushed by the operating system, so there may be some implicit ﬂushes to disk between

commits as well. The default is to ﬂush --file to disk after every row.

The danger is that a crash might cause lost data.

The performance increase I have seen from using --buffer is around 5 to 15 percent. Your mileage may vary.

--bulk-delete

Delete each chunk with a single statement (implies --commit-each).

Delete each chunk of rows in bulk with a single DELETE statement. The statement deletes every row between

the ﬁrst and last row of the chunk, inclusive. It implies --commit-each, since it would be a bad idea to

INSERT rows one at a time and commit them before the bulk DELETE.

The normal method is to delete every row by its primary key. Bulk deletes might be a lot faster. They also

might not be faster if you have a complex WHERE clause.

This option completely defers all DELETE processing until the chunk of rows is ﬁnished. If you have a plugin

on the source, its before_delete method will not be called. Instead, its before_bulk_delete method

is called later.

12 Chapter 2. Tools

Percona Toolkit Documentation, Release 3.0.3

WARNING: if you have a plugin on the source that sometimes doesn’t return true from is_archivable(),

you should use this option only if you understand what it does. If the plugin instructs pt-archiver not to archive

a row, it will still be deleted by the bulk delete!

--[no]bulk-delete-limit

default: yes

Add --limit to --bulk-delete statement.

This is an advanced option and you should not disable it unless you know what you are doing and why! By

default, --bulk-delete appends a --limit clause to the bulk delete SQL statement. In certain cases, this

clause can be omitted by specifying --no-bulk-delete-limit. --limit must still be speciﬁed.

--bulk-insert

Insert each chunk with LOAD DATA INFILE (implies --bulk-delete --commit-each).

Insert each chunk of rows with LOAD DATA LOCAL INFILE. This may be much faster than inserting a row

at a time with INSERT statements. It is implemented by creating a temporary ﬁle for each chunk of rows, and

writing the rows to this ﬁle instead of inserting them. When the chunk is ﬁnished, it uploads the rows.

To protect the safety of your data, this option forces bulk deletes to be used. It would be unsafe to delete each

row as it is found, before inserting the rows into the destination ﬁrst. Forcing bulk deletes guarantees that the

deletion waits until the insertion is successful.

The --low-priority-insert, --replace, and --ignore options work with this option, but

--delayed-insert does not.

If LOAD DATA LOCAL INFILE throws an error in the lines of The used command is not

allowed with this MySQL version, refer to the documentation for the L DSN option.

--charset

short form: -A; type: string

Default character set. If the value is utf8, sets Perl’s binmode on STDOUT to utf8, passes the mysql_enable_utf8

option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode

on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.

Note that only charsets as known by MySQL are recognized; So for example, “UTF8” will work, but “UTF-8”

will not.

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.

14 Chapter 2. Tools

Percona Toolkit Documentation, Release 3.0.3

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 15

Percona Toolkit Documentation, Release 3.0.3

--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.

16 Chapter 2. Tools

剩余372页未读，继续阅读

jonas2013

粉丝: 0
资源: 7

Percona Toolkit 3.0.3：全面的数据库管理工具集

percona-toolkit.tar.gz

PerconaToolkit 3.2.pdf

percona-toolkit-3.2.0.tar.gz

ubuntu16.04 percona-toolkit安装

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

使用rpm安装percona-toolkit3.3.1

percona-toolkit的安装及使用

percona-toolkit工具

使用rpm安装percona-toolkit3.3.1需要的依赖

如何通过二进制文件安装percona-xtradb-cluster5.7.40

最新资源