Start a new topic

Clear arpr_logs automatically

After using arpReach for about a year, the database has grown to over 25GB. It looks like most of the storage is in the arpr_logs table. It seems like this is just storing subject and some basic info about emails that were sent. Is there a way that we could get that table added to the auto-purge jobs to help keep the database at a more reasonable size?


Thanks!


4 people like this idea

25GB is insane. Did you clear it manually?

 

I've been working on deleting it. The issue is that I'm running a three node Percona MySQL cluster on AWS, and if I delete more than about 100,000 records at a time it will cause my websites to start getting SQL deadlocks on writes to the DB. I set up a cron job that runs every 5 minutes and deletes 50,000 records at a time. It should be caught up in a few days and then I'll add an index on the create date column and set a job to delete data older than a month.
Can you share that cron job with me? I don't know how to code them from scratch. Also, what host are you using.

 

I'm using Amazon AWS as my host (running on an instance type c1.medium dedicated for my ARP mail server).


In my crontab file ("sudo crontab -e" on Ubuntu) I have this command to run the script every 5 minutes:
*/5 * * * * /root/clearArpLogs.sh>/dev/null 2>&1


And the contents of clearArpLogs.sh are:


#!/bin/bash
mysql -u DBUSER -pDBPASS -e "DELETE FROM  `DBNAME`.`arpr_logs` WHERE stamp_create < ( UNIX_TIMESTAMP( ) -3888000 ) ORDER BY id ASC LIMIT 50000"

You will need to replace DBUSER, DBPASS, and DBNAME with the proper values for your database information. Also the LIMIT 50000 can be reduced once the logs table has been deleted to a reasonable size.


Since I'm using InnoDB for my database engine, the actual database files won't shrink in size on disk unless I run an ALTER TABLE command, and I haven't decided when/if I'm going to do that or not yet.

Login or Signup to post a comment