MySQL Production library Big Table Delete

MySQL Production library Big Table Delete

The general line of business growth faster, resulting in some expression to the critical value of the table, the number of table rows more than 2000w and query frequently, such as the table business does not have more aggregate query, you can consider to archive part of the historical data by time. We recommend 2 kinds of deletion methods I have used before.

1. Executes after a primary key or index split, using stored procedures

   It is important to note that this large table is deleted and if it is executed in the main library, try to change the session to a statement format to ensure that replication latency does not occur

   The statement is as follows: Set session binlog_format= ' STATEMENT ';
   

CREATE PROCEDURE Sp_delete_data () beginset session binlog_format= ' STATEMENT '; label:while (1 = 1) do delete from table whe  Re aaa like ' bbb% ' LIMIT 10000;  SET @a1 = Row_count ();  IF @a1 = 0 then LEAVE label;  END IF; END while; END

The number of rows used by limit can be tested on the wire to maximize the number of deleted rows without affecting the performance of the business library.

2. Use the Pt-archiver in Percona tools to clean up outdated data, do data archiving, or you can do both

Be aware that the parameters are--Limit the --Txn-size thePerformance tuning parameter specific values can be tested for tuning optimizations, as many transaction submissions as possible to improve execution efficiency.

1) Clean up outdated data

pt-archiver--source h=localhost,p=3306,u=ceshi,p=111111,d=test,t=example --charset=utf8 --purge --where ' id<10000000'  --progress--limit=3000--txn-size=3000--statistics

2) data is archived to another table, either locally or offsite, by adding--no-delete to decide whether to retain the original table data

Pt-archiver--source h=localhost,p=3306,u=ceshi,p=111111,d=test,t=example --dest h=localhost,p=3306,u= ceshi,p=111111,d=test,t=example1 --charset=utf8 --where ' id<10000000' --progress 3000 --limit=3000--txn-size=3000--statistics

This article from "Cloud Light Breeze Light" blog, declined reprint!

MySQL Production library Big Table Delete