10

I'm using a MySQL database to store logs from multiple servers. The database's growth on average is 50 GB per day. Its total size is now 1.7 TB.

It has ID and datetime columns.

I want to move data older than one week to another database on the same server.

What are the steps needed for this?

As of now I do this:

  1. Create the Archive Database and table.
  2. Insert into ArchiveDB.table select * from old_tbl where datetime <= Archivedate
  3. Then DELETE FROM old_tbl WHERE datetime <= Archivedate

I want to implement this with the following conditions:

  • Insert and delete via batches.
  • Reclaim the space after deleting from the old database.
  • Update stats without downtime.

...Or if you know of any best practice then let me know.

2 Answers 2

9

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

2
  • This is a good one, But unfortunately, we already deleted 30% for data (1 week). So I need to reclaim the space and update the stats.
    – TheDataGuy
    Jul 11, 2017 at 5:23
  • Dropping a partition does not impact the stats on the rest of the table. And it reclaims the space (assuming innodb_file_per_table=ON).
    – Rick James
    Jul 11, 2017 at 14:10
3

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.