Removing Freedom-01 CoreProtect Data from before February 2021

In order to try to get the Database size under control and to hopefully improve the speed of CoreProtect I'm going to download, Archive and then delete any data from before Feb 2021 (From Oct 30 2020 to Jan 31 2021 inclusive).

The data will be archived in the ATLAS Internal archive, and should we ever need to wipe Freedom-01 the Database files will be re-built and published as part of the world downloads.

@redeastwood#12672 I already have on a number of other posts, the only concerns were because people got confused and thought it was a map reset / similar. This was more for admins to be aware of as it should see a performance improvement but equally you might see less data on some longer standing builds.

Some initial testing is showing that this will be a very "Expensive" operation when it comes to MySQL (The dump part at least) and I suspect the delete aspect will be as well. It may end up being better off at least starting this when the server is offline, to make sure it has as much of a head start as it can get I would suggest.

@untuned#14569 The issue is the query locks the table for the duration of the query, which prevents people looking up or making changes to the Database. On paper it sounds like a good idea, but unfortunately it'd mean we'd need to ideally shut the server down for hours every day to do it, or risk losing CoreProtect data.

And all our data primarily is in the block data, there is a little data elsewhere but 99.9% by the looks of it is in the block data, so the other data is not really a major concern at the moment.

For anyone curious, because the Database is so big, I've realized actually pulling data by time is going to be a non-starter, but because the data seems (As far as I can tell anyway) to be chronological, I can pull it based on the number of rows.

As a test I pulled back the 21999999th record of the Database, which came out to be Tue Nov 03 2020 06:42:30 GMT+0000 (Greenwich Mean Time)

I then also pulled back the 69999999th record which came out to be Sat Nov 14 2020 05:46:27 GMT+0000 (Greenwich Mean Time)

Hopefully that gives you an idea on the sort of scale we're talking...

There's a total of 1350660840 Records, and it looks like if I try to pull out too many more records than I was there, it might cause all sorts of things to go horribly wrong, so it'll just be a bit more manual trying to get everything pulled out properly!

Upon some further testing and investigation, it looks like this will require an outage of the server to actually run the deletes, especially given how long they take. I'm going to see what I can do in breaking them up into smaller amounts and see if that gives some joy, but it's causing server stability issues and we're nowhere near done...

It looks like breaking the deletes down into much smaller batches seem to allow the server to function (With some delays of a few mins while the operation is running) without needing a full outage.

The downside is we're not deleting anywhere near as much data as quickly as I had liked, so far we're deleting everything up to what CoreProtect has marked as RowID 70898453 which is about the first 70000000 rows of the Database. We probably need to do this 5-6 more times to get the database down to a remotely manageable state, so this will likely be a slightly slower project than I had hoped...

So the actual deletion of the data won't be an issue, however we will need to optimize the database, which in this case will re-create it from scratch. Due to the way it works it locks up the table, and is very time consuming, so we might need to do that as an outage on Freedom-01 for a couple of hours soon.

So for total transparency, I have cocked up a bit.

I've accidentality deleted an unknown amount of records towards the start of the database. I'd thought it was a SELECT query but was a DELETE. The query ran for just over 10 mins so based off of the previous deletes 9999999 records took just over 10mins to delete when I was intentionally deleting them.

It will all be 2020 data, just didn't intend to delete any of it! Sorry folks.

Data between Sat Nov 14 2020 05:46:27 GMT+0000 (Greenwich Mean Time) and Sat Nov 28 2020 17:37:40 GMT+0000 (Greenwich Mean Time) is what appears to have been deleted. Which is about the amount of data I had hoped to actually download.

@StevenNL2000#14937 This is on the production database. I had tried to run a select to get the record point I was interested in.

The data was going to be deleted anyway but I wanted to archive it first. So shouldn't really make a difference.

To update, we've now got as far as Dec 24 2020, Once the overnight maintenance tonight is completed I hope we should see a fair performance improvement in CoreProtect.

We're up to Tue Jan 26 2021

I'll probably wipe out some of Feb as well, need to work out what the best way to do the final batch is going to be.

@DragonSlayer2189#15684 It'll sit in the ATLAS Corp Sharepoint until I need to use it, or until we do a map re-set at which point it'll be published with the rest of the CoreProtect data and the world files.