Percona-Toolkit工具包之pt-archiver

aaron8219發表於2018-06-18
Hive
 
Preface
 
    There`s a common case that we neet to archive amount of records in some tables to a file or another table in different database even delete them directly.Is there a tool can do all these jobs with satisfying way?Surely it is.pt-archiver can help us archiving that various archive jobs.
 
Introduce
 
    pt-archiver is a single tool of Percona-Toolkit suits which is produced by percona company.It provides  flexible way to archive your data in tables.Let`s see the detail of it.
 
Procedure
 
  1 ###Download and install Percona-Toolkit###
  2 [root@zlm2 09:40:27 ~]wget https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/redhat/7/x86_64/percona-toolkit-3.0.10-1.el7.x86_64.rpm
  3 -- Omitted.
  4 [root@zlm2 09:42:16 ~]yum -y localinstall percona-toolkit-3.0.10-1.el7.x86_64.rpm
  5 -- Omitted.
  6 Installed:
  7   percona-toolkit.x86_64 0:3.0.10-1.el7                                                                                                              
  8 
  9 Dependency Installed:
 10   perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7        perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7        perl-DBD-MySQL.x86_64 0:4.023-6.el7         
 11   perl-DBI.x86_64 0:1.627-4.el7                       perl-Data-Dumper.x86_64 0:2.145-3.el7              perl-Digest.noarch 0:1.17-245.el7           
 12   perl-Digest-MD5.x86_64 0:2.52-3.el7                 perl-IO-Compress.noarch 0:2.061-2.el7              perl-IO-Socket-IP.noarch 0:0.21-5.el7       
 13   perl-IO-Socket-SSL.noarch 0:1.94-7.el7              perl-Mozilla-CA.noarch 0:20130114-5.el7            perl-Net-Daemon.noarch 0:0.48-5.el7         
 14   perl-Net-LibIDN.x86_64 0:0.12-15.el7                perl-Net-SSLeay.x86_64 0:1.55-6.el7                perl-PlRPC.noarch 0:0.2020-14.el7           
 15   perl-TermReadKey.x86_64 0:2.30-20.el7              
 16 
 17 Complete!
 18 
 19 ###After install the Percona-Toolkit,you can check all the tools it contains by command below:###
 20 [root@zlm2 09:44:16 ~]man percona-tookit
 21 TOOLS
 22        This release of Percona Toolkit includes the following tools:
 23 
 24        pt-align
 25            Align output from other tools to columns.
 26 
 27        pt-archiver
 28            Archive rows from a MySQL table into another table or a file.
 29 
 30        pt-config-diff
 31            Diff MySQL configuration files and server variables.
 32 
 33        pt-deadlock-logger
 34            Log MySQL deadlocks.
 35 
 36        pt-diskstats
 37            An interactive I/O monitoring tool for GNU/Linux.
 38 
 39        pt-duplicate-key-checker
 40            Find duplicate indexes and foreign keys on MySQL tables.
 41 
 42        pt-fifo-split
 43            Split files and pipe lines to a fifo without really splitting.
 44 
 45        pt-find
 46            Find MySQL tables and execute actions, like GNU find.
 47 
 48        pt-fingerprint
 49            Convert queries into fingerprints.
 50 
 51        pt-fk-error-logger
 52            Log MySQL foreign key errors.
 53 
 54        pt-heartbeat
 55            Monitor MySQL replication delay.
 56 
 57        pt-index-usage
 58            Read queries from a log and analyze how they use indexes.
 59 
 60        pt-ioprofile
 61            Watch process IO and print a table of file and I/O activity.
 62 
 63        pt-kill
 64            Kill MySQL queries that match certain criteria.
 65 
 66        pt-mext
 67            Look at many samples of MySQL "SHOW GLOBAL STATUS" side-by-side.
 68 
 69        pt-mysql-summary
 70            Summarize MySQL information nicely.
 71 
 72        pt-online-schema-change
 73            ALTER tables without locking them.
 74 
 75        pt-pmp
 76            Aggregate GDB stack traces for a selected program.
 77 
 78        pt-query-digest
 79            Analyze MySQL queries from logs, processlist, and tcpdump.
 80 
 81        pt-show-grants
 82            Canonicalize and print MySQL grants so you can effectively replicate, compare and version-control them.
 83 
 84        pt-sift
 85            Browses files created by pt-stalk.
 86 
 87        pt-slave-delay
 88            Make a MySQL slave server lag behind its master.
 89 
 90        pt-slave-find
 91            Find and print replication hierarchy tree of MySQL slaves.
 92 
 93        pt-slave-restart
 94            Watch and restart MySQL replication after errors.
 95 
 96        pt-stalk
 97            Collect forensic data about MySQL when problems occur.
 98 
 99        pt-summary
100            Summarize system information nicely.
101 
102        pt-table-checksum
103            Verify MySQL replication integrity.
104 
105        pt-table-sync
106            Synchronize MySQL table data efficiently.
107 
108        pt-table-usage
109            Analyze how queries use tables.
110 
111        pt-upgrade
112            Verify that query results are identical on different servers.
113 
114        pt-variable-advisor
115            Analyze MySQL variables and advise on possible problems.
116 
117        pt-visual-explain
118            Format EXPLAIN output as a tree.
119 
120 ###See the parameter of pt-archiver.###
121 [root@zlm2 09:48:23 ~]#pt-archiver --help
122 -- Omitted.
123 
124 ###Parameters demonstration.###
125 Indispensible parameter:  
126 --source [-h|-S] -u -P -D -A
127 
128 Choose only one below:
129 --dest=d -- Usually be used to archive records to another table(can be different instance).
130 --purge -- Usually be used to purge records instead of archive them.
131 --file=s -- Usually be used to archive records into a file.
132 
133 Performance ralevant:
134 --analyze=s -- Run analyze table after archive.
135 --optimize=s -- Run optimize table after archive.
136 --ascend-first -- Ascend just first column of index.
137 --buffer -- Buffer output to file and flush when commit.
138 --bulk-delete -- Delete by chunk.
139 --bulk-insert -- Insert by chunk.
140 --limit=i -- Rows of per statment when arching,default 1.
141 --txn-size=i -- Rows of per trx,default 1.
142 --primary-key-only -- Merely use primary key.
143 
144 Infermation output:
145 --progress=i  -- Print per i rows.
146 --statistics -- Print time statistics.
147 
148 Others parameters:
149 --dry-run -- Just print queries without real doing.
150 --retries=i -- Times of when retry,default 1.
151 --no-delete -- Specify not to delete archived records in source table.
152 
153 ###Take care the rules when specify parameters.###
154 Specify at least one of --dest, --file, or --purge. -- There`re three mode of achive behavior.
155   --ignore and --replace are mutually exclusive.
156   --txn-size and --commit-each are mutually exclusive. -- Usually I`d rather use "txn-size" to specify the timing of commit depending amount of transactions.
157   --low-priority-insert and --delayed-insert are mutually exclusive.
158   --share-lock and --for-update are mutually exclusive.
159   --analyze and --optimize are mutually exclusive.  -- I don`t recommend use "analyze" or "optimize" when archive tables simultaneously,but afterward does when business not busy.
160   --no-ascend and --no-delete are mutually exclusive.
161   DSN values in --dest default to values from --source if COPY is yes.

 

Summary
  • pt-achiver is useful when archive your records of big tables.
  • Make sure that your table has index key on it.Otherwise,it will lead to bad performance when archive.
  • Plz distinguish these two parameter —— “no-delete”&”purge”,they are differently used.
  • More testing is necessary to make out the performance influenced by different parameter.

 

相關文章