Search code examples
mysqldatabasedatabase-designinnodb

I'm using a MySQL table to log user data (1000 new rows / min). How can I rotate the rows after 2 days?

To give you an example, say I have a site where the users can add unlimited cron jobs (frequency of 1s) and I log the results of each job in a Mysql table.

Now since I don't have unlimited storage (and also because I don't want my table to get huge) I want to restrict the logs history to last 30 days only.

Previously on an older version of Mysql, I was running DELETE FROM logs where created_at < DATE_SUB(NOW(), INTERVAL 30 DAY), to clean my table, but this causes my tables to frequently crash and get corrupted. It also locks my table during DELETE which makes the site very slow.

Anyway, so how can I implement row rotation (like log rotation) for my Mysql table or rows? Is deleting rows my only option or are there some other option?

Solution

  • You could use a database partition

    https://dev.mysql.com/doc/refman/5.7/en/partitioning-types.html

    Example

    PARTITION BY HASH( MONTH(column) )

    This gives 7 partitions. Then you can simply 

    ALTER TABLE t1 TRUNCATE PARTITION p0;

    To delete a single partition.