Want to archive tables? Use Percona Toolkit’s pt-archiver--

原文地址：https://www.percona.com/blog/2013/08/12/want-to-archive-tables-use-pt-archiver/

Percona Toolkit’s??is one of the best utilities to archive the records from large tables to another tables?or?files. One interesting thing is that?pt-archiver?is a read-write tool. It deletes data from the source?by default,so after archiving you don’t need to delete it separately.

As it is done by default,you should take care before actually running it on then production server. You can test your archiving jobs with the?— dry-run? OR you can use the?–no-delete?option if you’re not sure about. The purpose of this script is mainly to archive?old data from the table without impacting OLTP queries and insert the data into another table on the same/different server OR into a file in a format which is suitable for LOAD DATA INFILE.

How does?pt-archiver?select records to archive??

Pt-archiver?uses the index to select records from the table.?The index is used to optimize repeated accesses to the table.?Pt-archiver?remembers the last row it retrieves from each SELECT statement,and uses it to construct a WHERE clause. It does this using the columns in the specified index that should allow MySQL to start the next SELECT where the last one ended – rather than potentially scanning from the beginning of the table with each successive SELECT.

If you want to run?pt-archiver?with a specific index you can use the?“-i”?option in?–source?DSN options. The?“-i”?option?tells?pt-archiver?which index it should scan to archive. This appears in a?FORCE INDEX?orUSE INDEX?hint in the?SELECT?statements that are used to fetch rows to archive. If you don’t specify anything,?pt-archiver?will auto-discover a good index,preferring a PRIMARY KEY?if one exists. Most of the time,without?“-i”?option,?pt-archiver?works well.

How to run pt-archiver?

For archive records into normal file,you can run something like

Archiving in a replication environment:

In the replication environment it’s really important that the slave should not lag for a long time. So for that,there are two options which we can use while archiving to control the slave lag on slave server.

–check-slave-lag :?Pause archiving until the specified DSN’s slave lag is less than –max-lag. In this option,you can give slave details to connect slave lag. (i.e?–check-slave-lag h=localhost,S=/tmp/mysql_sandbox29784.sock)

–max-lag :?Pause archiving if the slave given by –check-slave-lag lags.

This options causes?pt-archiver?to look at the slave every time when 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-archiver?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.

Some useful options for pt-archiver:

–for-update/-share-lock??:?Adds the FOR UPDATE/LOCK IN SHARE MODE? modifier to SELECT statements.

–no-delete :?Do not delete archived rows.

–plugin :?Perl module name to use as a generic plugin.

–progress :?Print progress information every X rows.

–statistics :?Collect and print timing statistics.

–where :?WHERE clause to limit which rows to archive (required).

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.??for more info on that.

Bugs related to pt-archiver:?

More details about pt-archiver:?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!