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

PREVIOUS POST
NEXT POST

pt-archiverPercona Toolkit’s pt-archiver 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 or USE 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

From archive records from one table to another table on same server or different, you can run something like

Please check this before you use default file option (-F) in –source  http://www.percona.com/doc/percona-toolkit/2.1/pt-archiver.html#cmdoption-pt-archiver–dest

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

Percona Toolkit’s 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. You can get more information here.

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. Follow this URL for more info on that.

Bugs related to pt-archiver: https://bugs.launchpad.net/percona-toolkit/+bugs?field.tag=pt-archiver

More details about pt-archiver: http://www.percona.com/doc/percona-toolkit/2.2/pt-archiver.html

PREVIOUS POST
NEXT POST

Comments

  1. Rachel says

    I execute following command, always encounter following error:
    C:\Program Files\MySQL\MySQL Server 5.5\bin>pt-archiver –source h=localhost,D=a
    mi,t=table –user root –password root –file ‘c:\test.txt’ –where
    “id=’915′” –no-check-charset –commit-each –limit 1
    Cannot open ‘c:\test.txt': Invalid argument

    Appreciate your comments and suggestion ASAP.

  2. Nilnandan Joshi says

    Hi Rachel,

    Percona Toolkit is not fully supported to Windows though some utilities works fine. Here, it looks like pt-archiver can’t understand the path of –file. (seems it’s problem of backslash v/s forward slash). Can you try to run same command with –file options like –file ‘C:/test.txt’ OR –file ‘C:\\test.txt’ i.e

    >pt-archiver –source h=localhost,D=ami,t=table –user root –password root –file ‘c:/test.txt’ –where “id=915″ –no-check-charset –commit-each –limit 1

  3. Irvin says

    I am looking to use pt-archiver to archive several fast growing tables. I want to put the script in a cron job but don’t necessarily want to have the password in the script.

    Will pt-archiver ever use –login-path=xxx ? This seems a much more secure method of running scripts in batch mode.

Leave a Reply

Your email address will not be published. Required fields are marked *