Hi everyone! I'm currently facing a huge challenge with a MySQL database at my workplace. We have three applications running on Digital Ocean, and they all share the same underlying code and face this massive database issue. One particular table has bloated to hundreds of gigabytes, and although the data is legally required, much of it is considered useless. We've been upgrading the database, but the costs are getting out of hand, and we need to figure out how to resize it more effectively. Are there any strategies for deep storage in Digital Ocean, or should I consider moving this data to a different location? Thanks for any advice!
2 Answers
Have you looked into table partitioning? It's a great option in MySQL where you can split a large table into smaller, manageable pieces while still treating it as a single table from the outside. For instance, you could set up internal partitions by month, which would allow better performance and maintenance. Plus, you can use special syntax to work with just those partitions, making it easier to manage data without affecting performance significantly.
You might want to consider using cold storage for those legal requirements. Just because you can't delete that data doesn't mean it needs to be active in your database. You can use mysqldump to export the unnecessary data to something like Digital Ocean's Spaces, which will save costs, and then truncate your tables. This way, you can still access the data when necessary for audits, but your day-to-day performance will definitely improve! Plus, it’s smart to set up a regular schedule for moving data to cold storage so you don’t run into this problem again in the future.
Thanks! This actually sounds great, I could leverage Digital Ocean's spaces!
Whoa this is crazy. This could optimize query speed so much. Is this available in MySQL 8.0 though? We are planning an upgrade. But I will definitely start studying about it. How did you end up discovering this technique?