Database Cleanup

Work’s been busy, so I’m only just starting to get some time to continue writing my plugin – Medieval Realms for Rising World.

Continuing on from my bulk INSERT code I was testing – which works fantastically. Although at the time of writing that post I was only going up to 10 inserts per statement, I throttled it up to 10,000 inserts per statement and the reward was ever giving.

After many test runs I realised I had not cleaned up my database.

SELECT COUNT(*) FROM TABLE;
Count(*) = 186,971,562

Yikes. 187 million rows. 187 million rows of random test data to be precise.

This is the largest database I’ve ever had to deal with to date. This was my first attempt at deleting so many rows, so I did a little benchmark experimentation.

DELETE FROM TABLE WHERE id < 100,000,000 LIMIT 1000;
//Duration 0.016 sec
DELETE FROM TABLE WHERE id < 100,000,000 LIMIT 1,0000;
//Duration 0.062 sec
DELETE FROM TABLE WHERE id < 100,000,000 LIMIT 100,000;
//Duration 0.578 sec
DELETE FROM TABLE WHERE id < 100,000,000 LIMIT 1,000,000;
//Duration 8.703 sec
DELETE FROM TABLE WHERE id < 100,000,000 LIMIT 10,000,000;
//Duration TIMEOUT/FAIL.
DELETE FROM TABLE WHERE id < 100,000,000 LIMIT 2,000,000;
//Duration 25.203 sec
DELETE FROM TABLE WHERE id < 100,000,000 LIMIT 3,000,000;
//Duration TIMEOUT/FAIL.
DELETE FROM TABLE WHERE id < 100,000,000 LIMIT 2,500,000;
//Duration TIMEOUT/FAIL.

At this point I realised even the SELECT COUNT(*) FROM TABLE was throwing a 2003 error. Yikes.

– push it to the limit – 

Yep. That killed the service. 😲

After a service restart. . .

DELETE FROM TABLE WHERE id < 100,000,000 LIMIT 2,500,000;
//Duration 27.812 sec

Okay, so deleting 2.5 million rows at a time seems stable. I’m guessing there is a 30 second timeout on MySQL commands which causes the problem As for the service crash. 🤷‍♂️

That still means I would have to run this command 74.8 times to clear the table (yes I know I could delete the whole table and recreate it. . .  but that’s not part of this exercise).

Ideally I need to find a (simple) way to iterate this command 75 times (or until row count = 0).

Time to get my researching cap out.

TBC. . .