Percona Toolkit：高级数据库管理工具

需积分: 13 122 浏览量更新于2024-07-19 收藏 1.19MB PDF 举报

"Percona Toolkit 是一套高级的命令行工具集，主要针对MySQL、MongoDB和系统任务，用于处理手动操作过于复杂或困难的各种任务。它由Percona支持团队开发，包括专业测试和全面文档化的工具，适用于替代一次性脚本。这些工具自包含，安装快速简单，无需额外安装库。该工具包源自Maatkit和Aspersa，是著名的MySQL服务器管理工具包，并由Percona进行开发和支持。更多信息和Percona开发的其他免费开源软件，可访问http://www.percona.com/software/。" Percona Toolkit 提供了一系列功能强大的工具，如用于查看服务摘要信息、检测磁盘状态、分析慢查询日志、查找重复索引以及实现表同步等。这对于数据库管理和优化至关重要。 1. **查看服务摘要信息**: 工具可能提供一个快速概览，显示MySQL服务器的关键性能指标，如连接数、内存使用、查询速率等，帮助管理员了解系统的实时状态。 2. **磁盘检测**: 这些工具可以检查存储设备的健康状况，包括读写速度、I/O性能和空间使用情况，确保数据库的高效运行。 3. **分析慢查询日志**: Percona Toolkit 提供工具用于解析和分析MySQL的慢查询日志，找出执行时间过长的SQL语句，从而帮助优化数据库性能。 4. **查找重复索引**: 通过扫描数据库中的索引，可以发现并消除冗余或重复的索引，减少不必要的存储开销和查询性能影响。 5. **表同步**: 工具支持在不同MySQL实例之间同步数据，确保数据的一致性和高可用性。这对于多数据中心的复制和故障转移策略非常重要。 6. **配置**: Percona Toolkit 还提供了配置选项，允许用户根据特定环境调整工具的行为，以满足不同场景的需求。 7. **其他杂项工具**: 包含各种辅助工具，例如数据库备份、数据迁移、性能基准测试等，进一步增强数据库管理的灵活性和效率。 Percona Toolkit 的文档详细介绍了每个工具的使用方法、参数选项和示例，对于数据库管理员来说，是一份宝贵的资源。版本3.0.5包含了截至2017年的最新功能和改进，保证了工具的稳定性和功能性。 Percona Toolkit 是一个强大且全面的工具集，对于管理和优化MySQL和MongoDB环境至关重要，特别适合需要高效解决复杂问题的专业数据库管理员。

Percona Toolkit Documentation, Release 3.0.5

12 Chapter 2. pt-align

Percona Toolkit Documentation, Release 3.0.5

DESCRIPTION

pt-archiver is the tool I use to archive tables as described in http://tinyurl.com/mysql-archiving. The goal is a

low-impact, forward-only job to nibble old data out of the table without impacting OLTP queries much. You can insert

the data into another table, which need not be on the same server. You can also write it to a ﬁle in a format suitable for

LOAD DATA INFILE. Or you can do neither, in which case it’s just an incremental DELETE.

pt-archiver is extensible via a plugin mechanism. You can inject your own code to add advanced archiving logic

that could be useful for archiving dependent data, applying complex business rules, or building a data warehouse

during the archiving process.

You need to choose values carefully for some options. The most important are --limit, --retries, and

--txn-size.

The strategy is to ﬁnd the ﬁrst row(s), then scan some index forward-only to ﬁnd more rows efﬁciently. Each subse-

quent query should not scan the entire table; it should seek into the index, then scan until it ﬁnds more archivable rows.

Specifying the index with the ‘i’ part of the --source argument can be crucial for this; use --dry-run to examine

the generated queries and be sure to EXPLAIN them to see if they are efﬁcient (most of the time you probably want

to scan the PRIMARY key, which is the default). Even better, examine the difference in the Handler status counters

before and after running the query, and make sure it is not scanning the whole table every query.

You can disable the seek-then-scan optimizations partially or wholly with --no-ascend and --ascend-first.

Sometimes this may be more efﬁcient for multi-column keys. Be aware that pt-archiver is built to start at the

beginning of the index it chooses and scan it forward-only. This might result in long table scans if you’re trying to

nibble from the end of the table by an index other than the one it prefers. See --source and read the documentation

on the i part if this applies to you.

Percona XtraDB Cluster

pt-archiver works with Percona XtraDB Cluster (PXC) 5.5.28-23.7 and newer, but there are three limitations you

should consider before archiving on a cluster:

Error on commit

pt-archiver does not check for error when it commits transactions. Commits on PXC can fail, but

the tool does not yet check for or retry the transaction when this happens. If it happens, the tool will die.

MyISAM tables

Archiving MyISAM tables works, but MyISAM support in PXC is still experimental at the time of

this release. There are several known bugs with PXC, MyISAM tables, and AUTO_INCREMENT

columns. Therefore, you must ensure that archiving will not directly or indirectly result in the use of

default AUTO_INCREMENT values for a MyISAM table. For example, this happens with --dest if

--columns is used and the AUTO_INCREMENT column is not included. The tool does not check for

this!

Non-cluster options

Certain options may or may not work. For example, if a cluster node is not also a slave, then

--check-slave-lag does not work. And since PXC tables are usually InnoDB, but InnoDB doesn’t

support INSERT DELAYED, then --delayed-insert does not work. Other options may also not

work, but the tool does not check them, therefore you should test archiving on a test cluster before archiv-

ing on your real cluster.

14 Chapter 3. pt-archiver

Percona Toolkit Documentation, Release 3.0.5

OUTPUT

If you specify --progress, the output is a header row, plus status output at intervals. Each row in the status output

lists the current date and time, how many seconds pt-archiver has been running, and how many rows it has

archived.

If you specify --statistics, pt-archiver outputs timing and other information to help you identify which

part of your archiving process takes the most time.

ERROR-HANDLING

pt-archiver tries to catch signals and exit gracefully; for example, if you send it SIGTERM (Ctrl-C on UNIX-

ish systems), it will catch the signal, print a message about the signal, and exit fairly normally. It will not execute

--analyze or --optimize, because these may take a long time to ﬁnish. It will run all other code normally,

including calling after_ﬁnish() on any plugins (see “EXTENDING”).

In other words, a signal, if caught, will break out of the main archiving loop and skip optimize/analyze.

OPTIONS

Specify at least one of --dest, --file, or --purge.

--ignore and --replace are mutually exclusive.

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

3.6. OUTPUT 15

Percona Toolkit Documentation, Release 3.0.5

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

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

16 Chapter 3. pt-archiver

剩余445页未读，继续阅读

lvmuheng

粉丝: 2
资源: 1

Percona Toolkit：高级数据库管理工具

PerconaToolkit-2.2.6官方文档 英文版

percona-toolkit.tar.gz

percona-toolkit-3.0.2

PerconaToolkit说明.docx

PerconaToolkit 3.2.pdf

PerconaToolkit-3.2.1.pdf

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

percona-toolkit-3.0.8_x86_64.tar.gz

MySQL DBA最佳运维工具percona toolkit使用手册

percona-toolkit --centos7

最新资源

PerconaToolkit-2.2.6官方文档英文版