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

需积分: 9 55 浏览量更新于2024-07-14 收藏 1.25MB PDF 举报

"Percona Toolkit 是一套高级的命令行工具集合，主要用于MySQL、MongoDB和系统任务，由Percona支持团队用于处理复杂或难以手动执行的任务。这些工具是私人或一次性脚本的理想替代品，因为它们经过专业开发、正式测试并有完整的文档。安装快速简便，无需安装额外库。Percona Toolkit源自Maatkit和Aspersa两个著名的MySQL服务器管理工具包，由Percona开发和支持，并提供其他免费开源软件。" Percona Toolkit 包含了一系列强大的工具，如用于数据库性能优化、数据迁移、查询分析等。在【描述】中提到的"pt-osc"是其中的一个工具，全称为"pt-online-schema-change"，它允许在不锁定表的情况下进行在线的表结构修改，这对于大型数据库系统来说尤其重要，因为它可以避免在维护期间对业务造成中断。在手册的【内容】部分，我们可以看到文档分为四个部分： 1. 获取Percona Toolkit：这部分会介绍如何下载和安装Percona Toolkit，包括不同操作系统下的安装步骤和依赖项。 2. 工具：这部分详细介绍了各个工具的功能、用法和参数设置，pt-osc应该就包含在这里，它会解释如何安全地在线修改表结构，包括添加、删除或修改列，以及如何处理索引和外键。 3. 配置：这部分可能涵盖了工具的配置文件选项，用户可以根据自己的需求调整工具的行为。 4. 杂项：通常会包含一些常见问题解答、版本历史和社区支持信息。 pt-osc的使用是Percona Toolkit中的一个重要知识点，它通过创建一个临时表来复制原始表的数据，然后在新表上应用结构变更，最后将数据切回新表，期间使用触发器确保新旧表之间的数据同步。这个过程可以在不停止服务的情况下完成，极大地降低了维护成本和风险。在实际操作中，使用pt-osc需要考虑的因素包括但不限于： - 表的大小和复杂性，因为在线修改可能需要额外的磁盘空间和处理时间。 - 数据一致性，pt-osc通过触发器来保持新旧表的数据同步，但需要确保在变更期间没有新的写入操作破坏这种一致性。 - 性能影响，虽然pt-osc尽力减少影响，但在变更过程中，查询性能可能会有所下降。 Percona Toolkit 提供了强大的数据库管理和优化工具，pt-osc则是其中解决在线修改表结构问题的关键工具，对于需要频繁更新表结构且对服务稳定性要求高的数据库管理员来说，这是一个不可或缺的工具。通过阅读完整的手册，用户可以深入了解如何有效利用这些工具来提高数据库系统的效率和可用性。

Percona Toolkit Documentation, Release 3.2.1

12 Chapter 2. pt-align

Percona Toolkit Documentation, Release 3.2.1

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

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

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

--channel

type: string

Channel name used when connected to a server using replication channels. Suppose you have two mas-

ters, master_a at port 12345, master_b at port 1236 and a slave connected to both masters using channels

16 Chapter 3. pt-archiver

剩余476页未读，继续阅读

r_martian

粉丝: 162
资源: 1

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

Percona toolkit

percona-toolkit-3.3.1-1-最新版.zip

Percona 工具组使用手册

jquery-3.2.1.js & jquery-3.2.1.min.js

mpich-3.2.1-installguide.pdf

OpenSceneGraphReferenceDocs-3.2.1.rar_osg3.2.1

spring-framework-3.2.1--3.x

redis-3.2.1.rar

activemq-3.2.1.jar

wireshark-3.2.1.rar

最新资源