We enabled the option to delete old log data older than 90 days, once a week. Last night Piwik purge the log table for the first time. Before, the Tracker tables were: 13GB, 72 million rows. After last night, the Tracker tables are now: 30 million rows, but still 13 GB. I imagine this is because OPTIMIZE TABLE was never run or perhaps didn’t finish? Won’t running this on such a large table lock up Piwik’s database for hours?
Anyway, since this purging of log tables, archive.php will not finish successfully. I have got it to starting processing reports for sites again (after killing it and restarting many times), but now it stalls indefinitely on Scheduled Tasks:
[2013-12-17 16:31:27] [e2157703] SUMMARY
[2013-12-17 16:31:27] [e2157703] Total daily visits archived: 7506
[2013-12-17 16:31:27] [e2157703] Archived today's reports for 796 websites
[2013-12-17 16:31:27] [e2157703] Archived week/month/year for 796 websites.
[2013-12-17 16:31:27] [e2157703] Skipped 7 websites: no new visit since the last script execution
[2013-12-17 16:31:27] [e2157703] Skipped 2 websites day archiving: existing daily reports are less than 3600 seconds old
[2013-12-17 16:31:27] [e2157703] Skipped 0 websites week/month/year archiving: existing periods reports are less than 3600 seconds old
[2013-12-17 16:31:27] [e2157703] Total API requests: 3184
[2013-12-17 16:31:27] [e2157703] done: 796/798 100%, 7506 v, 796 wtoday, 796 wperiods, 3184 req, 5619635 ms, no error
[2013-12-17 16:31:27] [e2157703] Time elapsed: 5619.635s
[2013-12-17 16:31:27] [e2157703] ---------------------------
[2013-12-17 16:31:27] [e2157703] SCHEDULED TASKS
[2013-12-17 16:31:27] [e2157703] Starting Scheduled tasks...
It stalls here for hours. I see no queries (except for incoming visitor log data) in the database. What could it be doing? I have to kill the script and manually start it again to process more log data.
If it is trying to finish last night’s purge, any other advice on how to reclaim the unused space after the purging of the tracker tables? The purging doesn’t help our performance until we can get the size down. OPTIMIZE TABLE will lock Piwik for hours or possibly days. Please advise. We’re stuck with an unusable Piwik setup and 800 client websites without tracking…