Removing old data from QueueMetrics

2018-05-29
Your database got big and you don't mind deleting calls data before a certain date. Here's the procedure to do it:

1) Before doing anything on the database make sure you saved a db dump somewhere:
mysqldump -uqueuemetrics -pjavadude queuemetrics > qmdump.sql

2) Change the partition name to the lines you want to delete, in our example we'll target everything created before 01/01/2016 @ 12:00am (UTC), that's 1451606400 in Unix epoch format.

UPDATE queuemetrics.queue_log
SET 'partition' = 'DEL'
WHERE time_id' < '1451606400';


3)Now log in QueueMetrics as admin and go in "System Diagnostic Tools" -> "RAM caching" and clean the caches: now you can go in the realtime page or run some reports in order to verify everything is ok.
The system might seem slower now, we'll address that in the last step.

4) When you are 100% sure everything is ok you can delete all the rows that are part of the DEL partition:

DELETE FROM queuemetrics.queue_log
WHERE 'partition' = 'DEL';


5) Now optimize the table in order to tune the database performances:

OPTIMIZE TABLE queuemetrics.queue_log