How to remove data older than certain date?

Howto


With time, stats database can grow large and you might want to clean it up because is data you no longer need and makes your queries slow.

You can write a cron job that will run every day and removes entries older than a year.

Create a file named /etc/cron.daily/cleanqstats with this content:

#!/bin/bash

mysql -u qstatsUser -pqstatsPassw0rd qstats -e "DELETE FROM queue_stats WHERE datetime < DATE_SUB(CURDATE(), INTERVAL 12 MONTH);"
mysql -u qstatsUser -pqstatsPassw0rd qstats -e "DELETE FROM queue_stats_mv WHERE datetime < DATE_SUB(CURDATE(), INTERVAL 12 MONTH);"
mysql -u qstatsUser -pqstatsPassw0rd qstats -e "DELETE FROM agent_activity WHERE datetime < DATE_SUB(CURDATE(), INTERVAL 12 MONTH);"

Then be sure to give execute permissions to it:

chmod a+x /etc/cron.daily/cleanqstats

And that’s it. Every day at night, your qstats table will be mantained keeping only last 12 months worth of data.