We’re using Piwik 1.7.1 in a somewhat large installation (about 400k actions/week), and of course our database is getting bigger and bigger. Initially we didn’t setup Piwik to delete old logs, and decided to do so before the database grow out of control. We then setup the software to delete logs 6 months old, 100k at a time, on a daily-basis. We started using Piwik on May 2011 so there’s already a lot of log entries worth do delete.
We then noticed that apparently Piwik is bringing the web server to an halt every time it start to clean up the logs. We can see in our monitoring software that a lot of piwik.php requests starts to pile up when the deletion is being run. This make Apache reach its MaxClient setting very quickly, which of course prevent legitimate users to access all sites hosted on the server until the clean up finishes. Not nice.
The actual problem is that Piwik is running the deletions at 9pm local time. This is the time of day that server access is at peak, thus the worst time to do such resource-intensive task. I searched the interface for a way to change the time of day the deletions occur to no avail. The hint that this may be the very cause of the problem is that “Next scheduled deletion in” always show the time remaining to the next 9pm.
Yesterday, before 9pm, I cheated a little by changing the value of the “lastDelete_piwik_logs” key in the options table directly using SQL. The UI did respond to the change (i.e. next scheduled deletion was the time remaining to 3am, since I changed “lastDelete_piwik_logs” do 3am yesterday) and everything went fine (no downtime yesterday). Today at 10pm, I was notified by our monitor software that the web server stopped responding at… ops… 9pm. Looking at monitor reports I saw lots of stuck piwik.php at time the server stopped. Went to the Piwik UI and to my dismay, the “lastDelete_piwik_logs” went back to display the time remaining to 9pm tomorrow, meaning the clean up did run at 9pm today. sigh
(BTW, It would be good if the “Last deletion was on:” also displayed the time of day the deletion took place, instead of only year/month/day.)
So my question is: how to configure Piwik so that old log deletions occur at a specific time of day, say 3am?