Purging data in MySQL

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hi,

I am trying to configure a job which does data purges historical data in a table ( data older than 2 months) in chunks. The table has a primary key as well as date column. My plan is to use the date column range to find all the primary key’s and using that delete the rows in chunks

However, I have nor been able to find a substitute in mysql for the oracle PL/SQL countepart. I found something similar in the following link but am not able to get it to work

[MySQL Big DELETEs

](MySQL Big DELETEs)

For example, for a table with primary key:id and timestamp column:ts

set @a = SELECT MIN(id) FROM table1

LOOP

@z = SELECT id FROM table1 WHERE id >= @a ORDER BY id LIMIT 1000,1

If @z is null

 exit LOOP -- last chunk

DELETE FROM table1

 WHERE id >= @a

  AND id < @z

  AND ts < DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY)

SET @a = @z

sleep 1 – be a nice guy, especially in replication

ENDLOOP

Last chunk:

DELETE FROM tbl

WHERE id >= @a

AND ts < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
2

Hi Rkot.

In terms of MySQL, you don’t need to reinvent the wheel with PL/SQL. Percona has a tool named pt-archiver that works exactly to purge data in a graceful way with minimal impact. The tool can even check for load, the number of connections on the server, in case of slaves, monitor the slave lag, and either pause or abort the purge in case of critical load. It also allows for limiting the number of rows you want to purge each time before going to a sleep.

Have a look at https://www.percona.com/doc/percona-toolkit/LATEST/pt-archiver.html

3

Hi,

We would like to schedule it as a job and run through the application without DBA intervention and host access , hence a script would be more helpful . Is there any way to do it ?