Best Practice to Archive Old Data in MySQL
With InnoDB and PARTITIONs
, you could set up daily PARTITION BY RANGE(TO_DAY(...))
and use "Transportable tablespaces" to disassociate a day from the table and move it separately. This will be a lot faster than the queries it would take to do the INSERT...SELECT
and DELETE
. Once detached, the partition (now a table in its own right) can be moved to another database or server without impacting the ingestion of more data.
One drawback: InnoDB's disk footprint is a lot more than Archive's.
What kinds of queries do you apply to the logs?
Blogs: partitioning and chunking lengthy deletes
I suggest using a combination of Percona's pt-archiver (used to archive rows from a MySQL table into another table or a file) and Percona's pt-online-schema-change (can be run "online" with a neutral ALTER
to reclaim the disk space, assuming you're running with innodb_file_per_table=ON
).