[MySQL] Pruning / Off-loading Database

[daviftorres]

As environments grow, both the number of database transactions and the overall database size increase exponentially.

Some data must remain in the main (hot) database at all times, while other data, useful mainly for historical purposes (investigations, data analysis, etc.), can safely be moved to colder storage to offload the database.

I’m tired of dealing with DBs that grow out of control and teams who just throw CPU and RAM at the problem to buy time. The end of that story always looks like Jurassic Park (lol).

Benefits

• Faster execution of complex transactions
• More responsive WebUI
• Faster backup and restore operations
• Lower load during frequent snapshots (e.g., every 15 minutes), especially if they complete in under 10 seconds
• And more

Observations

• Tables such as cloud.alert and cloud.usage_event currently have no retention policy.
• The Usage service cannot yet be configured to read from the cloud database on the hot MySQL host while writing to a cloud_usage database on a colder MySQL host.

Idea / Proposal

• Periodically move entries older than 90 days into a separate usage_archive database on a colder MySQL host, and also store them in an S3 bucket for DR.

Next

I’d love to hear your thoughts and different perspectives.

[DaanHoogland]

@daviftorres , good idea. It has been proposed before but was never implemented. I think this could be as simple as a set of (cron)scripts that do daily/weekly/monthly archiving. Usage is the bigger problem, but this could well be done for a few of the cloud tables as well.
Describing some best practices in the docs, some of which you already describe here is a good vector of attacking this problem as well.

[daviftorres]

Thanks, @DaanHoogland. I’ll develop this idea along the lines you suggested (using cron jobs or systemd timers) and share the progress here so others can provide feedback as well.

To begin, I’ll focus on backing up and pruning of entries from cloud.alert and cloud.usage_event. If you or anyone else foresees any issues or impacts from cleaning up these tables, please let me know.

Regarding the cloud_usage database, I back it up separately, and in the event of a disaster I wouldn’t prioritize restoring it until the rest of the environment is fully operational.

Regards,