Sign in to follow this  
Followers 0
agentGoonie

More Database Maintenance (ie. stale accounts)

2 posts in this topic

With all the wonderful new updates from Exile comes new maintenance options for us to reduce the amount of junk within our databases. Ie. Deleting cars that haven't been used, etc.  However, I just did some of my own maintenance and deleted accounts and everything associated with those accounts that have not logged into the server in the last 45 days.  I've had my server up for a while and have never done a wipe.  So my Accounts table was giant.  After deleting the old stale accounts and anything associated with them I cut my database size by 50%.

Can we please see some more features available to reduce database size and clutter?

-Goonie

ps. Hugs for all the devs with a reach around for Eichi.

Share this post


Link to post
Share on other sites

Posted (edited)

@agentGoonie

When I created my DonkeyPunch eXpanded Logging for Exile, I created a DBmaint.sql file for those that would like to keep the size of the DB down.

DELETE FROM construction_history WHERE destroyed_at < NOW() - INTERVAL 7 DAY;
DELETE FROM container_history WHERE destroyed_at < NOW() - INTERVAL 7 DAY;
DELETE FROM vehicle_history WHERE destroyed_at < NOW() - INTERVAL 7 DAY;
DELETE FROM item_buy_history WHERE date_time < NOW() - INTERVAL 7 DAY;
DELETE FROM item_sell_history WHERE date_time < NOW() - INTERVAL 7 DAY;
DELETE FROM lock_history WHERE logged_at < NOW() - INTERVAL 7 DAY;
DELETE FROM poptab_send_history WHERE date_time < NOW() - INTERVAL 7 DAY;
DELETE FROM veh_buy_history WHERE date_time < NOW() - INTERVAL 7 DAY;
DELETE FROM veh_paint_history WHERE date_time < NOW() - INTERVAL 7 DAY;
DELETE FROM waste_history WHERE date_time < NOW() - INTERVAL 7 DAY;
DELETE FROM player_history WHERE died_at < NOW() - INTERVAL 7 DAY;

So based on this SQL if you wanted to cleanup your accounts, use:

DELETE FROM account WHERE last_disconnect_at < NOW() - INTERVAL 45 DAY;

You can save this line as AcctMaint.sql and run it as a query once a day or once a week if you want :) 

Edited by DirtySanchez
1 person likes this

Share this post


Link to post
Share on other sites
Advertisement
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.