How to remove data older than X months?

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);"

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.