If your database starts getting too big, you can purge old raw items.
You should run a backup before purging data. Seriously, you should. No matter how much testing we do on the purge script, there's no reasonable way we can guarantee you it won't run wild and delete all your data. It's dangerous purging data. It's also super easy to give the purge script the wrong date and purge data you didn't mean to.
Next, test your backups, because it's amazing how often backups are corrupt, incomplete, or just plain broken. I wish I were kidding. And it's usually too late when you find out.
utils.sh script has an option for purging raw item data. It
requires you to know the project id, project slug, and a unix timestamp
for when to start your purge. It'll purge all raw items in that project
older than the timestamp. Therefore,
utils.sh also has an option to
give you a list of all your projects and their slugs.
First, get your project information:
$ ./utils.sh --proj-list Running utilities Listing information for all projects +----+------------+----------+-------------+ | id | account_id | slug | description | +----+------------+----------+-------------+ | 1 | 1 | HProject | HProject | +----+------------+----------+-------------+ All desired utilities ran successfully
slug columns are the interesting ones. Note that you'll
need to run a separate purge for each project.
Next, get the unix timestamp where you want your purge to start. This will purge all items the same age or older than the given timestamp. Start out by getting the current timestamp (which is in seconds), then subtract the number of seconds back to when you want your purge to start. Here's how to get the timestamp for the current time.
$ date +%s 1473719424
Since there are 86,400 seconds in a day, if we wanted to purge everything one day old or older, we'd calculate this by subtracting 86,400 from the timestamp we got.
1473719424 - 86400 = 1473633024
Now that you've gotten the project information, you can run the purge on each project to get rid of all items older than a day.
$ ./utils.sh --purge 1:HProject:1473633024
Once you've purged the data, you'll need to compact the raw item table in the database to reclaim the space.
Currently, you'll need to compact the raw item table by hand. We don't have a utility for that yet. This will be easiest for people who manage their own databases. If you use the mysql server included in the on-prem distribution, you'll need to get some more instructions from us on how to do that.
$ mysql -u root -p mox_raw mysql> ALTER TABLE raw_item FORCE;
This may cause locking issues, depending on which version of myqsl you are running. Therefore it's not a bad idea to shut down the system while you're compacting the raw item table.