The tool does not check them, therefore you should test archiving on a testĬluster before archiving on your real cluster. Tables are usually InnoDB, but InnoDB doesn’t support INSERT DELAYED, then Is not also a slave, then -check-slave-lag does not work. Pt-archiver works with Percona XtraDB Cluster (PXC) 5.5.28-23.7 and newer,īut there are three limitations you should consider before archiving onĬertain options may or may not work. See -source and read theĭocumentation on the i part if this applies to you. In long table scans if you’re trying to nibble from the end of the table by an Be aware that pt-archiver is built to start at theīeginning of the index it chooses and scan it forward-only. Sometimes this may be more efficientįor multi-column keys. You can disable the seek-then-scan optimizations partially or wholly with Even better, examine theĭifference in the Handler status counters before and after running the query,Īnd make sure it is not scanning the whole table every query. Want to scan the PRIMARY key, which is the default). Sure to EXPLAIN them to see if they are efficient (most of the time you probably Specifying the index with the ‘i’ part of the -source argument canīe crucial for this use -dry-run to examine the generated queries and be Table it should seek into the index, then scan until it finds more archivable Each subsequent query should not scan the entire The strategy is to find the first row(s), then scan some index forward-only toįind more rows efficiently. You need to choose values carefully for some options. You can inject your ownĬode to add advanced archiving logic that could be useful for archivingĭependent data, applying complex business rules, or building a data warehouse Pt-archiver is extensible via a plugin mechanism. Or you can do neither, in which case it’s just an incremental DELETE. You can also write it to a file in a format suitable for LOAD DATA You can insert the data into another table, which need not be on the same Job to nibble old data out of the table without impacting OLTP queries much. Pt-archiver is the tool I use to archive tables as described in Percona Toolkit is mature, proven in the real world, and well tested,īut all database tools can pose a risk to the system and the databaseīackup your production server and verify the backups Pt-archiver -source h =host,D =db,t =child -purge \ -where 'NOT EXISTS(SELECT * FROM parent WHERE col=l)' RISKS ¶
0 Comments
Leave a Reply. |